How to call a sql script in a procedure?

[slimdave]

Originally posted by zxmgh
Then what's the good practice?

Perhaps you could describe the purpose of the SQL scripts -- what led you to want to do things this way, 'cos I'm sure we could think of a more robust solution.

[zxmgh]

The reason I have to do this stupid thing is that since we all know that in the database object creation script, we get used to using DROP statement before the CREATE statement to make sure the object is gone before it will be generated. But, if the object doesn't exist, the script will throw out the following error message:

ORA-00942: table or view does not exist

Personally I am OK with this healthy error message, but our QA guys don't like it, because they use an automatic error checking system to test our software, it will catch every error message start with "ORA-" and pause for the QA guys to investigate the problem and manually hit a button to continue... If I tell you guys that we have about 1500 database objects to create, then you will know that the poor QA guy have to click the button 1500 times to test the software.

So what I plan to do is to check the existance of the database objects first, if it is there, drop it and recreate it; if it is not there, just create it.

By the way, I have been working on Oracle for 5 straight years, not a just-graduate-dummy!

[slimdave]

Fair enough then. Having to work with anally-retentive dummies is a pretty good reason for having to do wierd stuff.

You could call the script like ...

begin
@c:/test_script.sql
end;
/

.. as long as the individual SQL statements were wrapped up in execute immediate ...


execute immediate 'drop table my_table';
execute immediate 'create table my_table (my_column number)';


.. and put an exeception handler in the anonymous block, but as soon as the DROP failed, the exception would kick in and the create would never happen.

What you would probably have to do is rewrite your script itself to say such stuff as ...


declare
my_number number;
begin
select count(*) into my_number from user_tables
where table_name = 'MY_TABLE';
if my_number = 1
then
execute immediate 'drop table my_table';
end if;
execute immediate 'create table my_table (my_column number)';
end;
/


It's a pain to do it, but perhps yo could explain the additional costs associated to your own (and the QA) manager, and just let them decide whether to pay so much extra for such a trivial issue to workaround. Sounds expensive to me, but you've researched the issue, right, and you're just presenting them with the choices.