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