Update statement works in normal mode, but not in procedure

Question

I want to upload image from directory to database blob field. For those reason I write this code. Code alone works well but is not working as a procedure. What is the problem ? I cannot understand. Here is the code:

DECLARE
  dest_loc  BLOB;
  src_loc   BFILE;
BEGIN 
  src_loc:= BFILENAME('ALL_IMG_DIR','SDFGASDF1544.jpg');
  DBMS_LOB.FILEOPEN(src_loc);
  DBMS_LOB.CREATETEMPORARY(dest_loc,true);
  DBMS_LOB.LOADFROMFILE(dest_lob => dest_loc, src_lob  => src_loc,amount=>dbms_lob.getlength(src_loc) );

  UPDATE STUDENT     
    SET  IMAGE=dest_loc     
  WHERE 
    REG_CODE = 'SDFGASDF1544';        
    DBMS_LOB.CLOSE(src_loc);     
end;

But when I write this code as a procedure, like

CREATE OR REPLACE PROCEDURE img_to_blob_student(Vreg_code varchar2)
is
  dest_loc  BLOB;
  src_loc   BFILE;
BEGIN            
  src_loc   := BFILENAME('ALL_IMG_DIR','SDFGASDF1544.jpg');      
  DBMS_LOB.FILEOPEN(src_loc);    
  DBMS_LOB.CREATETEMPORARY(dest_loc,true);    
  DBMS_LOB.LOADFROMFILE(
    dest_lob => dest_loc, 
    src_lob  => src_loc,
    amount=>dbms_lob.getlength(src_loc) 
  );
  UPDATE STUDENT 
  SET  IMAGE=dest_loc     
  WHERE REG_CODE = 'SDFGASDF1544';        
  DBMS_LOB.CLOSE(src_loc);          
end;

And call like

img_to_blob_student('123');

I get

ERROR IS: `ORA-00900: invalid SQL statement in procedure`

score 1 · Accepted Answer · answered Mar 24 '16 at 07:30

1

to call the procedure, did you use the execstatement?

exec img_to_blob_student('123');

answered Mar 24 '16 at 07:30

J. Chomel

8,193
15
41
69

One question sir, can i use table name and column name dynamically? – Arif Mar 24 '16 at 08:11
Hi Arif, of course you can, but it will require you a little more digging into Oracle `ALL TABLES` and the `EXECUTE IMMEDIATE * your constructed statement * `. This should be widely explained how here in StackOverflow... e.g. [here](http://stackoverflow.com/questions/1241611/oracle-batching-ddl-statements-within-a-execute-immediate) – J. Chomel Mar 24 '16 at 08:17
Create, delete and update works in EXECUTE IMMEDIATE but how may i used SELECT here. – Arif Mar 24 '16 at 08:20
you can also SELECT something INTO a variable. But this is a whole subject, and we should not try to discuss it her in the comments of this question. – J. Chomel Mar 24 '16 at 08:27

Update statement works in normal mode, but not in procedure

1 Answers1