dbms_sql解决动态SQL字符过长问题

Question

下面这个存储过程是一个包里用来执行SQL的入口，对执行一般SQL（字符比较短的）执行都是正常的（里面有一段注释的代码），但是有一些字符太长的SQL不能直接执行，所以在参数传递时用了CLOB类型，使用DBMS_SQL包来执行，但是下面的代码执行好像有问题？
--执行SQL语句
  PROCEDURE P_EXECUTE_SQL(A_OBJECT_NAME VARCHAR2,
                          A_CONTENT     VARCHAR2,
                          A_SQL         CLOB) IS
  
    V_CURSOR        NUMBER;
    V_INDEX         NUMBER := 1;
    V_COUNT_EXECUTE NUMBER;
    --V_SQL_SUB       VARCHAR2(32767);
    V_SQLS DBMS_SQL.VARCHAR2A;
   
  BEGIN
    IF A_SQL IS NOT NULL THEN
      /*EXECUTE IMMEDIATE V_SQL_SUB;
      P_WRITE_LOG_SEGMENTS(A_OBJECT_NAME,
                           A_CONTENT || '(' || SQL%ROWCOUNT || ')',
                           A_SQL);
      COMMIT;*/
    
      --生成要执行的动态SQL
      BEGIN
      
        LOOP
        
          V_SQLS(V_INDEX) := SUBSTR(A_SQL, (V_INDEX - 1) * 4000 + 1, 4000);
          IF LENGTH(V_SQLS(V_INDEX)) < 4000 THEN
            EXIT;
          ELSE
            V_INDEX := V_INDEX + 1;
          END IF;
        
        END LOOP;
      
        V_CURSOR := DBMS_SQL.OPEN_CURSOR;
        FOR I IN 1 .. V_SQLS.LAST LOOP
          P_WRITE_LOG_SEGMENTS(A_OBJECT_NAME, A_CONTENT, V_SQLS(I));
        END LOOP;
      
        DBMS_SQL.PARSE(C             => V_CURSOR,
                       STATEMENT     => V_SQLS,
                       LB            => V_SQLS.FIRST,
                       UB            => V_SQLS.LAST,
                       LFFLG         => TRUE,
                       LANGUAGE_FLAG => DBMS_SQL.NATIVE);
        V_COUNT_EXECUTE := DBMS_SQL.EXECUTE(V_CURSOR);
        P_WRITE_LOG_SEGMENTS(A_OBJECT_NAME,
                             A_CONTENT || '(' || V_COUNT_EXECUTE || ':' ||
                             SQL%ROWCOUNT || ')',
                             A_SQL);
        DBMS_SQL.CLOSE_CURSOR(C => V_CURSOR);
      EXCEPTION
        WHEN OTHERS THEN
          IF DBMS_SQL.IS_OPEN(C => V_CURSOR) THEN
            DBMS_SQL.CLOSE_CURSOR(C => V_CURSOR);
          END IF;
          RAISE;
      END;
    
    END IF;
  
  END P_EXECUTE_SQL;

Answer 1

很想知道你的CLOBSQL是怎么操作的

Answer 2

研究了一下，其实是参数问题导致的爆汗！！！
现在再把代码贴一下
[sql]
--执行SQL语句
  PROCEDURE P_EXECUTE_SQL(A_OBJECT_NAME LOG_SEGMENTS_1.OBJECT_NAME%TYPE,
                          A_CONTENT     LOG_SEGMENTS_1.CONTENT%TYPE,
                          A_SQL         LOG_SEGMENTS_1."SQL"%TYPE,
                          A_IS_LOG      NUMBER DEFAULT 1) IS
  
    V_CURSOR         NUMBER;
    V_INDEX          NUMBER := 1;
    V_COUNT_EXECUTE  NUMBER;
    V_SQL_SUB        VARCHAR2(32767);
    V_SQL_SUB_LENGTH NUMBER := 2000;
    V_SQLS           DBMS_SQL.VARCHAR2A;
  
  BEGIN
    IF A_SQL IS NOT NULL THEN
    
      --是否需要并行
      BEGIN
        IF DBMS_LOB.INSTR(A_SQL, 'PARALLEL') > 0 THEN
          EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
        END IF;
      END;
    
      --动态执行SQL
      BEGIN 
        LOOP
          V_SQL_SUB := DBMS_LOB.SUBSTR(A_SQL,
                                       V_SQL_SUB_LENGTH,
                                       (V_INDEX - 1) * V_SQL_SUB_LENGTH + 1);
          V_SQLS(V_INDEX) := V_SQL_SUB;
          IF LENGTH(V_SQL_SUB) < V_SQL_SUB_LENGTH THEN
            EXIT;
          ELSE
            V_INDEX := V_INDEX + 1;
          END IF; 
        END LOOP;
      
        V_CURSOR := DBMS_SQL.OPEN_CURSOR; 
        DBMS_SQL.PARSE(C             => V_CURSOR,
                       STATEMENT     => V_SQLS,
                       LB            => V_SQLS.FIRST,
                       UB            => V_SQLS.LAST,
                       LFFLG         => FALSE,
                       LANGUAGE_FLAG => DBMS_SQL.NATIVE);
        V_COUNT_EXECUTE := DBMS_SQL.EXECUTE(V_CURSOR);
        IF A_IS_LOG = 1 THEN
          P_WRITE_LOG_SEGMENTS(A_OBJECT_NAME,
                               A_CONTENT || '(' || V_COUNT_EXECUTE || ':' ||
                               SQL%ROWCOUNT || ')',
                               A_SQL);
        END IF;
      
        DBMS_SQL.CLOSE_CURSOR(C => V_CURSOR);
      EXCEPTION
        WHEN OTHERS THEN
          IF DBMS_SQL.IS_OPEN(C => V_CURSOR) THEN
            DBMS_SQL.CLOSE_CURSOR(C => V_CURSOR);
          END IF;
          RAISE;
      END;
    
    END IF;
  
  END P_EXECUTE_SQL;
[/sql]