Question

Iam试图为在执行时使用打脚灯,以便插入一张桌子。使用的一栏是BLOB型。 Iam利用“The ́s”来安装 column子。 BLOB型公司似乎与德国航天公司合作。见样本代码(非原文)和错误

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:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

My table BLOB_TEST is 

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

请提供帮助。想要的是,可以灵活地插入声明,将BLOB的数值插入表格。

Answer 1

即便有活力,你也应当使用bind/a>。问题已经解决。

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.

相对于NOT使用约束变量而言,它极为罕见。使用约束变量保证:

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

友情链接