Using BLOB type in dynamic SQL in Oracle

Question

Iam trying to create a string for using in execute_immediate statement for inserting into a table. One of the columns used is a BLOB type. Iam using the '||' operator to append columns and build the sql. BLOB type doest seem to work with '||'. See sample code (not original) and error

declare
    FIRST_NAME varchar2(10);
    PICTURE blob; -- blob type
    TEMP_STR varchar2(2000);
begin
    FIRST_NAME := 'Arun';
    -- a blob is created
    DBMS_LOB.CREATETEMPORARY(PICTURE, true); 

-- next line works
insert into BLOB_TEST (PERSON_NAME,PHOTO) values (FIRST_NAME,PICTURE);

-- creating the string
TEMP_STR := 'insert into BLOB_TEST values (''' || first_name||''''||','||PICTURE||')';
-- just to view the string
DBMS_OUTPUT.PUT_LINE(TEMP_STR); 

/* code to be done */
-- execute immediate(temp_str); 
end;


ERROR

Error report:
ORA-06550: line 9, column 17:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

My table BLOB_TEST is 

create table BLOB_TEST
(
PERSON_NAME varchar2(20),
PHOTO  blob
);

Please help. What i want is an option to execute insert statement dynamically to insert BLOB value to a table.

Your problem comes because you're trying to concatenate a string and a blob. You could substr the picture using dmbs_lob.substr but that isn't a viable solution. Use @vincent's code, which is safer and unlikely to destroy your picture. — Ben
– Ben, Commented Jan 13, 2012 at 9:42

Vincent Malgrat · Accepted Answer · 2012-01-13 09:29:34Z

2

Even with dynamic SQL, you should use bind variables. Problem solved.

SQL> CREATE TABLE blob_test (PERSON_NAME VARCHAR2(10), PHOTO BLOB);

Table created.

SQL> DECLARE
  2     FIRST_NAME VARCHAR2(10);
  3     PICTURE    BLOB; -- blob type
  4     TEMP_STR   VARCHAR2(2000);
  5  BEGIN
  6     FIRST_NAME := 'Arun';
  7     DBMS_LOB.CREATETEMPORARY(PICTURE, TRUE);
  8     INSERT INTO BLOB_TEST (PERSON_NAME, PHOTO) VALUES (FIRST_NAME, PICTURE);
  9     TEMP_STR := 'insert into BLOB_TEST values (:person,:myblob)';
 10     execute immediate(temp_str) using first_name, picture;
 11  END;
 12  /

PL/SQL procedure successfully completed.

It is exceedingly rare to NOT use bind variables. Using bind variables guarantees that:

The optimizer plan can be reused if the query is executed again with another set of parameters
Your code can't be affected by SQL injection

edited Jan 13, 2012 at 9:29

answered Jan 13, 2012 at 9:14

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arun manmadhan Over a year ago

Thanks Vincent. That did my job.

Collectives™ on Stack Overflow

Using BLOB type in dynamic SQL in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related