PL/SQL Loop

Hi, Can I get some help from here?

I have an application(ColdFusion) which generate thounds of dynamic inserts and it takes time to run. Insteading of making a separate db call everytime inserting, I'd like to pass bulk insert scripts to store procedure which do inserts.

Sth like:
execute DO_INSERT('insert into test values(1);insert into test values(2);insert into test values(3)');

CREATE OR REPLACE PROCEDURE DO_INSERT(tSQL IN VARCHAR2) IS

  v_Cursor        NUMBER;
  v_NumRows       INTEGER; 		
              				

BEGIN
  /* Open the cursor for processing. */
  v_Cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_Cursor, tSQL, DBMS_SQL.V7);   v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);   DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
    RAISE;
END DO_INSERT;
/
SHOW ERRORS; But the sp failed because invalid character ";".

I wonder if there is any way I can do a loop using ";" as the delimiter
so I can do single insert each time.
Such as:
Loop i (str= tSQL delimite = ";")

    insert ..( i);
end loop

Can PL/SQL do that?

Thanks Received on Thu Jan 30 2003 - 19:02:27 CST