There are scenarios where you used scheduled jobs to run some script procedure. In such a situation if you need to create/drop temp tables, with scheduled jobs it might cause errors if the temp table creation/deletion is done outside the procedure. Therefore in order to add all that logic into a single procedure you need to use dynamic SQL.
Below is a sample oracle procedure written with dynamic sql. In dynamic sql in order to execute a query you need to add your query login within a statement.So the first step would be to define these statements inside the procedure.
CREATE OR REPLACE PROCEDURE cleanData
Next we can initialize the defined statement variables and execute them accordingly.
stmt := 'CREATE TABLE TEMP_DATA(ID NUMBER)';
EXECUTE IMMEDIATE stmt;
stmt2 := 'INSERT INTO TEMP_DATA (ID) SELECT ID FROM NEW_DATA WHERE DATA_ID IN (4,5,6)';
EXECUTE IMMEDIATE stmt2;
stmt3 := 'INSERT INTO MAIN_DATA WHERE MAIN_ID IN (SELECT ID FROM TEMP_DATA)';
EXECUTE IMMEDIATE stmt3;
stmt4 := 'DROP TABLE TEMP_DATA';
EXECUTE IMMEDIATE stmt4;
What happens in this script is ,
1. create a temp table.
2.Insert data into temp table from an existing table.
3. Add temp table data into another table.
4.Drop temp table.
Note that the statements need to be within ' ' .
Next if you execute this procedure you might receive an error stating user doesn't have sufficient privileges. This is due to oracle security model, and in order to execute dynamic queries you need to specify the permissions given to execute this procedure. Here I'm providing the permissions available of the currently logged in user. This can be done like below by placing an 'AUTHID'.
CREATE OR REPLACE PROCEDURE cleanData AUTHID CURRENT_USER
You could look into http://www.dba-oracle.com/t_authid_current_user.htm
which explains other types of users that can be defined with AUTHID property.