Oracle Dynamic SQL

You can create SQL statement dynamically and make the script more generic to make it work with any table.

  DECLARE
    TYPE COL_TYPE IS TABLE OF VARCHAR2(40);
    COL_NAME COL_TYPE;
    COL_DATA_TYPE COL_TYPE;
    TAB1_NAME VARCHAR2(30) :='A_USERS'; 
  BEGIN
      SQL_STMT := 'SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '''|| TAB1_NAME ||'''';
       EXECUTE IMMEDIATE SQL_STMT   BULK COLLECT INTO COL_NAME ,COL_DATA_TYPE;
  END;
 /

Oracle 'EXECUTE IMMEDIATE' will help you to run dynamic sql. If   sql command return data  you can store it in a variable. Above example, SQL statement will return  columns name and data type of a table and it will be store in COL_NAME ,COL_DATA_TYPE variable. You can access value from COL_NAME array in following way.

FOR INDX IN 1 .. COL_NAME.COUNT 
   LOOP   
       DBMS_OUTPUT.PUT_LINE ('COL_NAME: ' || COL_NAME(index));
  END LOOP;

Also if you can create separate code block and pass bind variable   P_REC and IN T_REC  value while executing the statement and output will be store in MISS_MATCH variable.

 
 DECLARE
 P_REC  A_USERS%ROWTYPE;
  T_REC  A_USERS%ROWTYPE;
 BEGIN
               SQL_STMT :=  'DECLARE 
                          P_REC  '|| TAB1_NAME ||'%ROWTYPE; 
                          T_REC  '|| TAB2_NAME ||'%ROWTYPE;  
                       BEGIN    P_REC := :P_REC;  T_REC := :T_REC;   
                       SELECT DECODE(P_REC.' || COL_NAME(INDX) || ',T_REC.' || COL_NAME(INDX) || ',0,1)
INTO :MISS_MATCH FROM DUAL;                       
        
           EXECUTE IMMEDIATE SQL_STMT USING IN  P_REC , IN T_REC , OUT MISS_MATCH;
  END;
/

Do you like this post? Please link back to this article by copying one of the codes below.

URL: http://sknath.blogspot.com/2016/08/oracle-dynamic-sql.html HTML link code: <a href="http://sknath.blogspot.com/2016/08/oracle-dynamic-sql.html">Oracle Dynamic SQL </a> BB (forum) link code: [url=http://sknath.blogspot.com/2016/08/oracle-dynamic-sql.html]Oracle Dynamic SQL [/url]