Oracle Dynamic sql:使用撇号值更新列

Question

我正在尝试使用动态查询更新列。更新值有一个撇号(中华人民共和国~(。我收到以下错误。

/*Example Table Data*/
CREATE TABLE CUST_DATA
(
ID number,
LEGAL_ENTIRY_ID   VARCHAR2(50),
CUST_ID           VARCHAR2(50),
CUST_NAME         VARCHAR2(50)
);
INSERT INTO CUST_DATA VALUES(1,'ICC02','CH001','TEST123');
INSERT INTO CUST_DATA VALUES(1,'ICC02','LN001','TEST456');
/
CREATE TABLE RR1
(
ID                VARCHAR2(50),
ADJUSTMENT_VALUE  VARCHAR2(50)
);
INSERT INTO RR1 VALUES('CH001',q'[PEOPLE'S REPUBLIC OF CHINA~ THE]');
INSERT INTO RR1 VALUES('IN001','REPUBLIC OF INDIA');
INSERT INTO RR1 VALUES('US001','USA');
INSERT INTO RR1 VALUES('RU001','RUSSIA');
/
COMMIT;
/*PLSQL Block*/
DECLARE
TYPE T_VARCHAR  IS TABLE OF VARCHAR2(50);
V_CUST_ID       T_VARCHAR;
V_ADJ_VALUE   T_VARCHAR;
V_TABLE_NAME    VARCHAR2(30);
V_SQL             VARCHAR2(100);
BEGIN
SELECT ID,ADJUSTMENT_VALUE BULK COLLECT INTO V_CUST_ID,V_ADJ_VALUE FROM RR1 ORDER BY ID;
V_TABLE_NAME    := 'CUST_DATA';
FOR I IN 1 .. V_CUST_ID.COUNT LOOP
V_SQL := 'UPDATE '||DBMS_ASSERT.SQL_OBJECT_NAME(V_TABLE_NAME)||
' SET CUST_NAME = '||DBMS_ASSERT.ENQUOTE_LITERAL(V_ADJ_VALUE(I))||
' WHERE CUST_ID = '||DBMS_ASSERT.ENQUOTE_LITERAL(V_CUST_ID(I));
DBMS_OUTPUT.PUT_LINE('V_SQL -->'||V_SQL);
EXECUTE IMMEDIATE V_SQL;
END LOOP;
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -->'||SQLERRM||' / '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
ERROR -->ORA-06502: PL/SQL: numeric or value error / ORA-06512: at "SYS.DBMS_ASSERT", line 342
ORA-06512: at "SYS.DBMS_ASSERT", line 411
ORA-06512: at line 11

"CUST_NAME"栏正在更新为"中华人民共和国~"。请让我知道是否有解决方法？

Answer 1

使用绑定变量，然后解决所有问题(包括更好的性能和SQL注入的风险(：

V_SQL := 'UPDATE '||DBMS_ASSERT.SQL_OBJECT_NAME(V_TABLE_NAME)||
' SET CUST_NAME = :CustName '||
' WHERE CUST_ID = :CustId';
EXECUTE IMMEDIATE V_SQL USING V_ADJ_VALUE(I), V_CUST_ID(I);

对于非动态语句，您还可以使用：

INSERT INTO RR1 VALUES('CH001','PEOPLE''S REPUBLIC OF CHINA~ THE');

我不是 100% 确定(即我没有测试它(，但您可以在这样的单个语句中运行更新：

DECLARE
TYPE T_ADJUSTMENT_REC IS RECORD (
ID RR1.ID%TYPE,
ADJUSTMENT_VALUE RR1.ADJUSTMENT_VALUE%TYPE );
TYPE T_ADJUSTMENT_TYPE IS TABLE OF T_ADJUSTMENT_REC ;
T_ADJUSTMENT T_ADJUSTMENT_TYPE;
V_TABLE_NAME    VARCHAR2(30);
V_SQL             VARCHAR2(100);
BEGIN
SELECT T_ADJUSTMENT_REC(ID,ADJUSTMENT_VALUE) 
BULK COLLECT INTO T_ADJUSTMENT 
FROM RR1 ORDER BY ID;
V_TABLE_NAME    := 'CUST_DATA';
V_SQL := 'UPDATE (SELECT CUST_NAME, CUST_ID, ADJUSTMENT_VALUE FROM '||V_TABLE_NAME||
' JOIN TABLE(:t) ON ID = CUST_ID) SET CUST_NAME = ADJUSTMENT_VALUE';
EXECUTE IMMEDIATE V_SQL USING T_ADJUSTMENT;
END;

也许 FORALL 也有解决方案，我从未在动态 SQL 中使用过 FORALL(带有绑定变量(。