How to use variable and drop in test script?

Question

I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C.

They provided me with some "Test" scripts that I shoud be able to run to test that my changes don't break the underlying logic of the package. But they are not working.

Also I noticed they have hardcoded some values in their original scripts and I am trying to use variables instead. I have never worked with Oracle so I am confused.

In Toad I have a Script along these lines:

declare
myTestId VARCHAR(32);

begin

select  '23423DSSF34SD234' into myTestId from dual;

--Here my drop for my_test_table1 and my_test_table2
drop table my_test_table1;
drop table my_test_table2;

--Here create table my_test_table1 and my_test_table2

--Here I have a bunch of inserts

--Here are some selects statements

end;

However when I run the script it fails when it reaches the "Drop" statements it doesn't like them. How can I perform drop statements in my script?

I have been reading around and it seems that in Oracle it is not a good idea to create temporary tables like this? What would be the best approach?

Answer 1

drop, along with create, alter, truncate etc is a DDL (Data definition Language) statement. DDL is not allowed in PL/SQL blocks. Your script is a PL/SQL block (it has begin and end, along with variable declarations).

The way to work around it is to use execute immediate:

declare
myTestId VARCHAR(32);

begin

select  '23423DSSF34SD234' into myTestId from dual;

--Here my drop for my_test_table1 and my_test_table2
execute immediate 'drop table my_test_table1';
execute immediate 'drop table my_test_table2';

--Here create table my_test_table1 and my_test_table2

--Here I have a bunch of inserts

--Here are some selects statements

end;

Be aware that executing DDL in a PL/SQL block in this fashion will implicitly perform a commit. Any errors (such as ORA-00942: table or view does not exist) will also abort the script execution, though there are ways around this. You could first query the ALL_TABLES data dictionary few to check if the table exists before attempting to drop it, for example.

Variable use is massive subject... If you have a specfic question about it, you'd be better off asking a new question on the site.

As far as creating temporary tables are concerned, this is different than creating a true temporary table, as frequently used in Sybase and SQL Server - don't worry about it, your approach is fine for test scripts.