How to Call Stored Procedure with Reference Column as a Parameter

[Guest_imported]

Oh no, it's me again. I'm trying to call my stored procedure with:
[tt]
EXECUTE update_employees ('E12345', 'last_name', 'Smith');
[/tt]

But I receive:
[tt]
BEGIN update_employees ('E12345', 'last_name', 'Smith'); END;

*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at "SYSTEM.UPDATE_EMPLOYEES", line 9
ORA-06512: at line 1
[/tt]


When I try it without the single quotes around last_name, I get:
[tt]
ERROR at line 1:
ORA-06550: line 1, column 32:
PLS-00201: identifier 'LAST_NAME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
[/tt]

DinaZ
:

 

[Kenrae]

What's xactly on line 9 in this procedure?
Or, alternatively, you can paste your code here so we can take a look at it.

 

[50295541]

Try to ommit space between update_employee and ('E12345'...

 

[sem]

The character value 'Smith' must be quoted but column name must not. Your statement may be smth like
stmt := 'update employees set last_name= ''Smith'''

To build it dynamically you have to check for column type or quote all assigned values hoping oracle correctly converts them for you.

 

[Guest_imported]

Kenrae,

Here is the procedure with line numbers:
[tt]
SQL> CREATE OR REPLACE PROCEDURE update_employees
2 (p_emp_num IN VARCHAR2,
3 p_column_name IN VARCHAR2,
4 p_new_data IN VARCHAR2)
5
6 AS
7 BEGIN
8
9 EXECUTE IMMEDIATE 'UPDATE employees SET '||p_column_name||
10 ' = p_new_data WHERE emp_num = p_emp_num';
11
12 END;
13 /

Procedure created.

SQL> show errors
No errors.
SQL>
[/tt]

DinaZ
:

 

[Kenrae]

What Sem has said is true, you need to have your p_emp_num parameter delimited by " " (like a typical update statement updating a string). And another thing, the parameters can't be inside the execute string. What I mean is that:

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

With dynamic SQL you always need to make a string which is exactly the same you would code normally.