Thursday, November 26, 2015

Oracle Dynamic SQL scripts

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 
IS
  stmt1  VARCHAR2(2048);
  stmt2 VARCHAR2(2048);
  stmt3 VARCHAR2(2048);
  stmt4 VARCHAR2(2048);


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;
  COMMIT;

 END;
/



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
IS
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.







No comments:

Post a Comment