Oracle dynamic PL/SQL. I cant get it to work

Question

So here is my code:

CREATE OR REPLACE PROCEDURE UPDATE_USER
(
  updateColumn IN USERS.column_name%type,
  changeStr IN VARCHAR2,
  unID IN VARCHAR2
)
IS
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE
      users
    SET :1 = :2
    WHERE
      uniqueID = :3'
  USING updateColumn, changeStr, unID; 
END;
/

I've searched for other answers on this and as far as I can tell this should work. However I get the error: 'Error(3,25): PLS-00302: component 'COLUMN_NAME' must be declared'

Thanks.

Does your `USERS` table actually have a column called `column_name`? It seems not from what you're trying to do. You probably need to declare `updateColumn` as `user_tab_columns.colmn_name%type`, or a simple `varchar2`. You can't dynamically set the column name in the update using a bind variable though. — Alex Poole, Dec 12 '12 at 11:06
@AlexPoole The answer in this is what I was aiming at: [this question](http://stackoverflow.com/questions/3690038/is-it-possible-to-pass-table-name-as-a-parameter-in-oracle) — Tom Stone, Dec 12 '12 at 11:15

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

The error message specifies line 3, character 25, which points to column_name in the declaration of the updateColumn parameter. It appears that you are trying to pass the column name to update as a parameter, but that means that at compile time the column isn't known, so its type can't be known. This also doesn't really make sense - if it's a number column then you'd be trying to pass the column name into a numeric parameter, which wouldn't work anyway. If you don't want to declare it as a simple varchar2, you could instead use user_tab_columns.column_name%type.

But you can't dynamically set the column name in the update statement using a bind variable. It would compile, but would get an ORA-01747 on execution from the apparent name starting with a colon. You'd need to concatenate it, something like:

CREATE OR REPLACE PROCEDURE UPDATE_USER
(
  updateColumn IN user_tab_columns.column_name%type,
  changeStr IN VARCHAR2,
  unID IN VARCHAR2
)
IS
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE
      users
    SET ' || updateColumn || ' = :1
    WHERE
      uniqueID = :2'
  USING changeStr, unID; 
END;
/

But you'd need to sanitise the column name to avoid SQL injection. APC's answer to the question you linked to mentions using the DBMS_ASSERT package, for example.

edited May 23 '17 at 10:34

Community

1
1

answered Dec 12 '12 at 11:21

Alex Poole

183,384
11
179
318

Thankyou for this Alex. I did have the concatonation in before, however i removed becuase of this error at runtime: `ORA-00971: missing SET keyword`. The `user_tab_columns.column_name%type` appears to be an improvement however I am still getting that runtime error. – Tom Stone Dec 12 '12 at 11:31
@TomStone - are you sure you have whitespace between the `SET` and the closing quote before the concatenation? Might be worth printing out the query as it will actually be executed to check. – Alex Poole Dec 12 '12 at 11:37
Sorry that error has gone now and I am getting `ORA-01006: bind variable does not exist ORA-06512: at "THOMASSTONE.UPDATE_USER", line 9 ORA-06512: at line 1` – Tom Stone Dec 12 '12 at 11:39
@TomStone - well, just guessing, but did you change the bind variable numbers back to `:1` and `:2`, and remove `updateColumn` from the `using` clause? – Alex Poole Dec 12 '12 at 11:41

Oracle dynamic PL/SQL. I cant get it to work

1 Answers1