DB02 report

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

HI,

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

Regards.

[Former Member]

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

[Former Member]

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.

[Former Member]

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.