topic Re: DB02 report in Application Development and Automation Discussions

DB02 report

Former Member — Mon, 05 Aug 2013 10:55:54 GMT

Hi Experts,

I need to do innerjoin for two tables DBA_DATA_FILES and DBA_FREE_SPACE. Fetched fields "tablename_space" and "Size_mb" frorm table "DBA_DATA_FILES" and then i need to innerjoin with table "DBA_FREE_SPACE" to get the data for field "Free_mb" using where clause as DBA_DATA_FILES - tablename_space = DBA_FREE_SPACE - tablename_space.

Below is my code for the same :

EXEC SQL PERFORMING loop_output.
     SELECT a~TABLESPACE_NAME, SUM(NVL(BYTES,0))/1024,
                  a~((SUM(NVL(BYTES,0)) * 100)/1024) SIZE_MB,
                  b~TABLESPACE_NAME SUM(NVL(BYTES,0))/1024,
                  b~((SUM(NVL(BYTES,0)) * 100)/1024) FREE_MB

FROM DBA_DATA_FILES AS a

       INNER JOIN DBA_FREE_SPACE AS b
       into :tab1
         where a~TABLESPACE_NAME = b~TABLESPACE_NAME
       GROUP BY TABLESPACE_NAME
ENDEXEC.

Please do let me know if my above code is correct, if not let me know the correct code as i'm getting error at this statement ("ORA-00911: invalid character").

Thanks in advance. Eagerly waiting for reply.

Regards,

Gururaj.

Re: DB02 report

Former Member — Mon, 05 Aug 2013 12:19:30 GMT

Hi Gururaj,

You shouldn't put ',' in the select statement and I am not sure if you can do calculations in the select statement, it should be as below:

EXEC SQL PERFORMING loop_output.
     SELECT a~TABLESPACE_NAME SUM(NVL(BYTES,0))/1024
                  a~((SUM(NVL(BYTES,0)) * 100)/1024) SIZE_MB
                  b~TABLESPACE_NAME SUM(NVL(BYTES,0))/1024
                  b~((SUM(NVL(BYTES,0)) * 100)/1024) FREE_MB

FROM DBA_DATA_FILES AS a

       INNER JOIN DBA_FREE_SPACE AS b
       into tab1
         where a~TABLESPACE_NAME = b~TABLESPACE_NAME
       GROUP BY TABLESPACE_NAME
ENDEXEC.

Check this, if this doesnt work, please also remove the calculations and select only fields and see. Let us know if any more help is required.

Re: DB02 report

Former Member — Mon, 05 Aug 2013 12:50:05 GMT

Hi A N,

Modified as per your advice. But still getting the same error and profram termination.

Attached my updated code:

   EXEC SQL PERFORMING loop_output.
     SELECT a~TABLESPACE_NAME SUM(NVL(BYTES,0))/1024
            a~SIZE_MB
            b~TABLESPACE_NAME SUM(NVL(BYTES,0))/1024
            b~FREE_MB

       FROM DBA_DATA_FILES     AS a

       INNER JOIN DBA_FREE_SPACE AS b
       into :tab1
         where a~TABLESPACE_NAME = b~TABLESPACE_NAME
       GROUP BY TABLESPACE_NAME
ENDEXEC.

Regards,

Gururaj.

Re: DB02 report

Former Member — Tue, 06 Aug 2013 04:56:28 GMT

Hi Gururaj,

Have you tried as below without giving any special characters such as : or , and without any operations in the select?:

EXEC SQL PERFORMING loop_output.
     SELECT a~TABLESPACE_NAME
            a~SIZE_MB
            b~TABLESPACE_NAME
            b~FREE_MB

       FROM DBA_DATA_FILES     AS a

       INNER JOIN DBA_FREE_SPACE AS b
       into tab1
         where a~TABLESPACE_NAME = b~TABLESPACE_NAME
       GROUP BY TABLESPACE_NAME
ENDEXEC.

Re: DB02 report

Former Member — Tue, 06 Aug 2013 06:54:40 GMT

Hi A N,

I have tried as per you code given, But getting error if colon ( : ) is not used before tab1. The error is -

"INTO ..." specification missing supported in the OO context.

So if i use colon before tab1, the report gets activated, but on terminates on execution and the error is - "ORA-00911: invalid character"

The code is as below:

   EXEC SQL PERFORMING loop_output.
     SELECT a~TABLESPACE_NAME
            a~SIZE_MB
            b~TABLESPACE_NAME
            b~FREE_MB

       FROM DBA_DATA_FILES     AS a

       INNER JOIN DBA_FREE_SPACE AS b
       into :tab1
         where a~TABLESPACE_NAME = b~TABLESPACE_NAME
       GROUP BY TABLESPACE_NAME
ENDEXEC.

Regards,

Gururaj.

Re: DB02 report

Former Member — Wed, 07 Aug 2013 04:04:33 GMT

HI,

Eagerly waiting for your response. As i need to complete this task on priority.

Regards.

Re: DB02 report

Former Member — Wed, 07 Aug 2013 11:44:16 GMT

Hi Gururaj,

I would suggest you to do a global search for SELECT in program SAPLS_ORA_COCKPIT_5. This is the underlying program for DB02. I found they have used commas in the select query. Maybe you will get an idea from here

Re: DB02 report

Former Member — Wed, 07 Aug 2013 11:48:16 GMT

You may also try this ( space before and after '/' . They have used comma in the select while using concatenate statement not otherwise. So maybe you can try the below select statement with and without comma

EXEC SQL PERFORMING loop_output.
     SELECT a~TABLESPACE_NAME, SUM(NVL(BYTES,0)) / 1024,
                  a~((SUM(NVL(BYTES,0)) * 100) / 1024) SIZE_MB,
                  b~TABLESPACE_NAME SUM(NVL(BYTES,0)) / 1024,
                  b~((SUM(NVL(BYTES,0)) * 100) / 1024) FREE_MB

FROM DBA_DATA_FILES AS a

       INNER JOIN DBA_FREE_SPACE AS b
       into :tab1
         where a~TABLESPACE_NAME = b~TABLESPACE_NAME
       GROUP BY TABLESPACE_NAME
ENDEXEC.

Re: DB02 report

Former Member — Wed, 07 Aug 2013 12:54:34 GMT

HI,

In this program , they have used comma and also the calculation part in the select queries only.

also they have used the query statement as below :

query = `select` &

` a.ts,` &

` a.blk_kb,` &

` decode(lower(a.contents),'temporary', d.size_mb, b.size_mb),` &

` decode(lower(a.contents),'temporary', d.size_mb-e.used_mb, c.free_mb),` &

i have already tried like this, but getting the same error.

regards,

Gururaj.