Stored Procedure How to Reference Column as a Parameter

[Guest_imported]

Hi again everyone,

I'm new to Oracle8i and PL/SQL. I am trying to create a procedure to update a table called "employees". I want to enter the employee number, a column name, and the new data. Then the procedure will find the employee number and update the column with the new data. I am having problems with referencing the column, can anyone give me the correct syntax?


Here is what I have with the errors I receive:
[tt]
CREATE OR REPLACE PROCEDURE update_employees
(p_emp_num IN VARCHAR2,
p_column_name IN VARCHAR2,
p_new_data IN VARCHAR2)

AS
BEGIN
UPDATE employees SET p_column_name = p_new_data
WHERE emp_num = p_emp_num;
END;
/

show errors


LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: SQL Statement ignored
6/20 PLS-00417: unable to resolve "P_COLUMN_NAME" as a column
[/tt]

Thanks,
DinaZ
:

 

[Kenrae]

That's a job for dynamic SQL. You need that:

CREATE OR REPLACE PROCEDURE update_employees
(p_emp_num IN VARCHAR2, 
p_column_name IN VARCHAR2, 
p_new_data IN VARCHAR2)

AS
BEGIN

EXECUTE IMMEDIATE 'UPDATE employees SET '||p_column_name||
                  ' = p_new_data WHERE emp_num = p_emp_num';

END;
/

Hope this helps.

 

[banna2000]

AS
BEGIN
UPDATE employees SET p_column_name = p_new_data
WHERE emp_num = p_emp_num;
END;
/

In your above statement the p_column_name is to be replaced by the actual coloumn name of the table.

prashant

 

[Guest_imported]

Kenrae,

Success! Thank you so much! Guess I'll be reading up on dynamic SQL now.

DinaZ
: