column not allowed here error in oracle SQL insert

Question

Hi I am getting this error while trying to insert the INPUT parameter into error table

CREATE OR REPLACE PROCEDURE consolidate_SI(  
primary_SI   IN NUMBER,  
secondary_SI IN NUMBER )  
IS  
v_primary_si number;  
v_secondary_si number;   
Begin  
 v_primary_si:= primary_si;  
EXECUTE IMMEDIATE 'insert into error_log ( identifier, error_message) values 
(''Successfully updated'',v_primary_si)';  
execute immediate 'commit';  
End;

I am getting the below error at run time

ORA-00984: column not allowed here

Welcome to Stack Overflow. You can format source code with the [Code Sample `{}`](https://i.imgur.com/3ppJhiE.png) toolbar button—I've done it for you this time. You also have a preview pane right below the editor so you can see how changes look like before you publish them. — Álvaro González, Jul 03 '18 at 09:54

score 3 · Accepted Answer · answered Jul 03 '18 at 09:55

You don't need dynamic SQL:

CREATE OR REPLACE PROCEDURE consolidate_SI(primary_SI IN NUMBER, secondary_SI IN NUMBER) IS
    v_primary_si                            NUMBER;
    v_secondary_si                          NUMBER;
BEGIN
    v_primary_si    := primary_si;

    insert into error_log ( identifier, error_message) values ('Successfully updated',v_primary_si);

    commit;
END;

Besides, the issue is in the way you reference the variable v_primary_si in your dynamic SQL.

score 2 · Answer 2 · answered Jul 03 '18 at 09:58

The dynamic SQL context doesn't know what v_primary_si is - the PL/SQL variables are not in scope inside that SQL context - so it sees it as a column identifier by default: hence the error. To use the PL/SQL variable you would use and supply it as a bind variable:

EXECUTE IMMEDIATE 'insert into error_log ( identifier, error_message) values 
  (''Successfully updated'',:v_primary_si)'
USING v_primary_si;

But neither that nor the commit need to be dynamic here, you can just do:

Begin
  v_primary_si:= primary_si; -- presumably you need this as a new variable later?
  insert into error_log ( identifier, error_message)
  values ('Successfully updated', v_primary_si);
  commit;
End;

column not allowed here error in oracle SQL insert

2 Answers2