sqloracle-databaseora-00947

column not allowed here error in oracle SQL insert


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


Solution

  • 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.