This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Unable to verify user defined collection

I am trying to create a UDC for the following sql:

select a.tablespace_name,
       round(a.bytes_alloc/(1024*1024*1024),0) "TOTAL ALLOC (GB)",
       round(a.bytes_alloc-b.tot_used/(1024*1024*1024),0) "FREE (GB)",
       round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible, 'YES',CASE WHEN maxbytes<bytes THEN bytes ELSE maxbytes END,'NO',bytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
       group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%';

 

Everytime I get tje following error for verification:

Collection verification failed: The following error occurred while retrieving the SQL query : ORA-00933: SQL-Befehl wurde nicht korrekt beendet

 

Whats wrong?

Parents
  • Hi Jörn,

    I collected the needed information:
    We are using JDBC and that does not allow a semicolon at the end of a SQL statement:
    www.fromdev.com/.../javasqlsqlexception-ora-00911-invalid.html

    But in the user defined collection you can use PL/SQL code and then a semicolon is needed.
    So the logic of when to omit the semicolon begin to be complicated.

    We had an enhancement request to remove the semicolon automatically, but it was rejected by product management due to the above reason.

    Does that answer your question?

    Regards
    Nicola
Reply
  • Hi Jörn,

    I collected the needed information:
    We are using JDBC and that does not allow a semicolon at the end of a SQL statement:
    www.fromdev.com/.../javasqlsqlexception-ora-00911-invalid.html

    But in the user defined collection you can use PL/SQL code and then a semicolon is needed.
    So the logic of when to omit the semicolon begin to be complicated.

    We had an enhancement request to remove the semicolon automatically, but it was rejected by product management due to the above reason.

    Does that answer your question?

    Regards
    Nicola
Children
No Data