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.

• Dzyann: Oh I read a lot of documentation on PL/SQL and I always thought like you could use SQL inside PL, but I never realized that the DDL statements were allowed. Then only DML are allowed? Or also the DCL and TCL statements?
• Philᵀᴹ: You can use any DML ("SQL"), and DDL within execute immediate. You just can't use explicit DDL within PL/SQL blocks.