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?

  • Hello j.sloot,

    please remove the semicolon at the end of the statement.
    I just tested it and without semicolon the verification was successful.

    I know it sounds strange as a proper ending of a SQL statement is the semicolon.
    If you want to have more background details why it was implemented like this, I can check internally. Let me know if I should do that for you.

    Regards
    Nicola
  • Hi Nicola,

    thanks for your answer. I have more the question why the semicolon will not be removed automatically.

    BR Jörn
  • Hi Jörn,

    I will check internally and update you once I have that detail. Could be an enhancement request...

    Regards
    Nicola
  • 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