sql if table exists drop table using srw.do_sql !!! 1

[cli]

Hi,

would totally appreciate help..new to sql

I am tyring to check if a table exists from trigger in oracle reports before creating it. My code complied fine but has an error saying command not ended properly when irun it.

code:

******************************************************
function BeforePForm return boolean is
begin

srw.do_sql('DROP TABLE IF EXISTS REP_OVERPAYMENT_OVERPAID_APPS');

srw.do_sql('Create table REP_OVERPAYMENT_OVERPAID_APPS
(COUNT_IMH_IMAP_HOLDING_ID NUMBER(6))');

commit;



return (TRUE);

end;
*******************************************************

 

[JamesGordon]

There is no 'if exists' in a drop table statement.
You could try and select some data from it and on success drop the table.

 

[sem]

You may try to drop it ignoring errors:

begin
srw.do_sql('DROP TABLE REP_OVERPAYMENT_OVERPAID_APPS;');
exception
when others then null;
end;

srw.do_sql('Create table REP_OVERPAYMENT_OVERPAID_APPS
(COUNT_IMH_IMAP_HOLDING_ID NUMBER(6))');

But in general it's not very good idea to create a table from report trigger.

 

[cli]

Hi,

thanks a million for this suggestion... I have tried this and if the table isn't there this piece of sql runs fine, but if the table is there it still doesn't drop...it doesn't seem to execute the drop command


also why is it a bad idea to create a temp table in the before report trigger ?

cheers

 

[cli]

just found my mistake ....syntax i am afraid !!

would still be interested to know why it is not a good idea to create tables from triggers thanks !!

 

[sem]

Creating and dropping tables as well as most DDL operations are very expensive. In fact they entail a greate number of real inserts/updates of dictionary tables. So if you need just a session-wide place to store some data you may use pl/sql tables or other variables.

 

[cli]

Thanks