Oracle – How to write dynamic sql in Oracle Stored procedure

dynamic-sqloraclestored-procedures

Basically in my update sql query column names going to be dynamic like 

update bi_employee set <this_is_dynamic_column> where emp_id = 12

Below is the stored procedure that I have written so far.

CREATE OR replace PROCEDURE Sp_run_employee_updates
IS
  CURSOR c_emp IS
    SELECT *
    FROM   BI_EMPLOYEE_UPDATE
    WHERE  EFFECTIVE_DATE = To_date('30-Apr-2012', 'dd-mm-yy');
BEGIN
    FOR employee_update IN c_emp LOOP
        declare update_sql varchar2(225);
        update_sql := 'UPDATE BI_EMPLOYEE   SET     '
                      || employee_update.column_name
                      || '= employee_update.new_value  WHERE     emp_id = '
                      || employee_update.employee_id;
    END LOOP;
END;

Its giving me foloowing errors

Error(17,13): PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar The symbol "" was substituted for "=" to continue.

Error(22,5): PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior The symbol "begin" was substituted for "UPDATE" to continue.

Error(31,6): PLS-00103: Encountered the symbol ";" when expecting one of the following: loop

Best Answer

a- This should be like this: 

to_date('30-Apr-2012','dd-mon-yyyy');

b- You can do it like this: 

CREATE OR REPLACE
PROCEDURE SP_RUN_EMPLOYEE_UPDATES IS  

  update_sql varchar2(225);

  CURSOR c_emp IS
   SELECT * 
   FROM BI_EMPLOYEE_UPDATE 
   WHERE EFFECTIVE_DATE = to_date('30-Apr-2012','dd-mon-yyyy');

BEGIN

 FOR employee_update in c_emp LOOP

     update_sql :=  'UPDATE BI_EMPLOYEE SET ' || employee_update.column_name || 
                    '= :1 WHERE emp_id = :2' ;

  execute immediate update_sql using employee_update.new_value, employee_update.employee_id;

 END LOOP;

END SP_RUN_EMPLOYEE_UPDATES;
Related Topic