Getting ORA-00904:invalid identifier error while executing a procedure

Question

CREATE OR REPLACE FUNCTION CONCAT_BLOB(A in BLOB,B in BLOB) RETURN BLOB IS
 C BLOB;
BEGIN
DBMS_LOB.APPEND (C,A);
DBMS_LOB.APPEND (C,B);
RETURN C;
END;

CREATE OR REPLACE PROCEDURE update_NEW_REC_tmp is 


 tempBlob BLOB :=utl_raw.cast_to_raw('^');

  begin 

  execute immediate  'update audt set new_rec= CONCAT_BLOB(tempBlob,new_rec)' ;
    commit;

    end;

exec update_NEW_REC_tmp;

while executing the procedure getting an error:ORA-00904: "TEMPBLOB": invalid identifier

1

Why do use dynamic SQL ? – A.B.Cade Nov 06 '13 at 11:14

score 0 · Answer 1 · answered Nov 06 '13 at 10:55

0

I think you have to use binding variables in your anonymous statement:

update audt set new_rec= CONCAT_BLOB(tempBlob,new_rec)

tempBlob and new_rec variables are out of scope.

I think you can helpful Oracle Documentation: click here

answered Nov 06 '13 at 10:55

m.genova

377
6
15

when using binding variablesm, I am getting an error ORA-01008: not all variables bound – user2960028 Nov 06 '13 at 11:08

Getting ORA-00904:invalid identifier error while executing a procedure

1 Answers1