1

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?

3

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.

  • 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? – Dzyann May 23 '14 at 16:28
  • You can use any DML ("SQL"), and DDL within execute immediate. You just can't use explicit DDL within PL/SQL blocks. – Philᵀᴹ May 23 '14 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.