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?
Best Answer
drop
, along withcreate
,alter
,truncate
etc is aDDL
(Data definition Language) statement.DDL
is not allowed in PL/SQL blocks. Your script is a PL/SQL block (it hasbegin
andend
, along with variable declarations).The way to work around it is to use
execute immediate
:Be aware that executing
DDL
in a PL/SQL block in this fashion will implicitly perform acommit
. Any errors (such asORA-00942: table or view does not exist
) will also abort the script execution, though there are ways around this. You could first query theALL_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.