Oracle10 sp execute the very very long sql!

1. Use the clob to concatenate the sql string.

2. Use DBMS_SQL to execute SQL.

 

PROCEDURE      update_xx (
       inputParam               IN clob,
       errArray                                                          out SQL_ERR_TYPE_ARRAY
)

 

v_data                         DBMS_SQL.Varchar2_Table;

 

sql_statemt                      clob;
indx                            NUMBER := 1;
error                                            SQL_ERR_TYPE;
errorIndex                                  int:=1;

i                                     NUMBER := 0;
j                                     NUMBER := 0;
clobLong                      NUMBER;
tempSql                                  DBMS_SQL.varchar2a;
l_offset                         NUMBER :=1;

 

BEGIN

    errArray := new SQL_ERR_TYPE_ARRAY();

    sql_statemt := 'select ....' || inputParam;

 

    clobLong := dbms_lob.getlength(sql_statemt);
          begin
           loop
              exit when l_offset > clobLong;
              j := j + 1;
              tempSql(j) := substr( sql_statemt, l_offset, 30000 );
              l_offset := l_offset + 30000;
           end loop;
          end;

   -- define array

    DBMS_SQL.DEFINE_ARRAY(v_cursor, 1, v_data, 500, indx);

    v_execute := DBMS_SQL.EXECUTE(v_cursor);

    loop
          v_execute := DBMS_SQL.FETCH_ROWS(v_cursor);
        
          -- put data to array
          DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_data);

          for dataIndex in 1 .. v_execute loop
             i := i + 1;

             dbms_output.put_line('fetch rows is ' || v_data(i));

             -- put udpate sql

             EXCEPTION
                     WHEN OTHERS THEN
                              error := new SQL_ERR_TYPE(v_asset_id(i), SQLCODE, SQLERRM);
                          errArray.extend();
                          errArray(errorIndex) := error;
                          errorIndex := errorIndex+1;

          end loop;

          EXIT WHEN v_execute < 500;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_cursor);

END update_xx;