Oracle 执行长SQL

　　遇到非常长的SQL语句无法执行该怎么办呢？看下面的解决办法：

/*-----------------------------------
|执行长SQL，如参数、值非常大
----------------------------------------*/
CREATE OR REPLACE PROCEDURE SP_EXECUTELOG(SSQL IN VARCHAR2, SLONG IN CLOB) IS
BEGIN
  EXECUTE IMMEDIATE SSQL
    USING SLONG;
  COMMIT;
END SP_EXECUTELOG;



--使用方式
CREATE TABLE TEMPTABLE
(
 ID NUMBER,
 STR CLOB
)

--------------示例1，普通方式----------------------------
BEGIN
SP_EXECUTELOG('INSERT INTO TEMPTABLE(ID,STR) VALUES(1,:STR)',TO_CLOB('14'));
END;


--------------示例2，引号嵌套类型----------------------------
DECLARE 
 SQLSTR VARCHAR2(32767):='UPDATE T_TABLE
   SET INETWORKTYPE_ID      = 1,
       SWHERERULES_CN       = '''',
       IUSERID              = 2804,
       DUPDATEDATE          = TO_DATE(''2012-04-19'', ''YYYY-MM-DD''),
       SPERFTYPE            = ''PERF_GSMCELL'',
       SSQL                 = :STR
 WHERE STEMPLATEID = ''37606''';
 VAL CLOB:=TO_CLOB('SELECT TO_CHAR(DDATE, '''' YYYY '''') Y
  FROM TABLE
 WHERE DDATE >= (SYSDATE - INTERVAL '''' 1 '''' YEAR)');

BEGIN
SP_EXECUTELOG(SQLSTR,VAL);
END;

 

转载于:https://www.cnblogs.com/myssh/archive/2012/04/19/2457761.html