Re: PL/SQL Loop

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 31 Jan 2003 17:08:34 -0800
Message-ID: <92eeeff0.0301311708.71563b53@posting.google.com>

oracledba_at_eastmail.com (David) wrote in message news:<15b372c2.0301301702.6ccce6a4_at_posting.google.com>...
> 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

Although I would have preffered to do it in java, but since you are on V7, here is a plsql example. Make sure the input_line_ is <= 32766 in length.

create or replace function foo (

   input_line_ IN VARCHAR2,
   token_ IN NUMBER,
   delimiter_ IN VARCHAR2,
   string_ OUT VARCHAR2 ) RETURN BOOLEAN IS

   retval_       BOOLEAN := TRUE;
   start_        NUMBER := 1;
   from_         NUMBER;
   to_           NUMBER;

   input_text_ VARCHAR2(32767) := input_line_ || delimiter_; BEGIN
   string_ := NULL;

   IF ( (input_line_ IS NULL)  OR 
        (token_ < 1)           OR
        (INSTR(input_text_, delimiter_, start_, token_) = 0) ) THEN
      retval_ := FALSE;
   ELSE
      from_ := start_;
      IF (token_ = 1) THEN         
         to_ := INSTR(input_text_, delimiter_, start_, token_);
         IF (to_ = 0) THEN
            string_ := input_text_;
         ELSIF (to_ = from_) THEN
            string_ := SUBSTR(input_text_, from_, to_);
         ELSE
            string_ := SUBSTR(input_text_, from_, to_ - from_);
         END IF; 
      ELSE
         from_ := INSTR(input_text_, delimiter_, start_, token_ - 1);
         to_ := INSTR(input_text_, delimiter_, start_, token_);
         string_ := SUBSTR(input_text_, from_, to_ - from_);
      END IF;
      string_ := LTRIM(RTRIM(string_, delimiter_), delimiter_);

   END IF;
   RETURN retval_;
END foo;
/

Call this function in a loop inside your procedure e.g.

> CREATE OR REPLACE PROCEDURE DO_INSERT(tSQL IN VARCHAR2)
> IS
> v_Cursor NUMBER;
> v_NumRows INTEGER;

    delim_          VARCHAR2(1) := ';';
    ret_            VARCHAR2(1000);
    token_          NUMBER := 1;
 		


>               				

> BEGIN
     WHILE (foo(tSQL, token_, delim_, ret_) = TRUE) LOOP

> v_Cursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(v_Cursor, ret_, DBMS_SQL.V7);
> v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
> DBMS_SQL.CLOSE_CURSOR(v_Cursor);

         token_ := token_ + 1;
      END LOOP;

> EXCEPTION
> WHEN OTHERS THEN
> DBMS_SQL.CLOSE_CURSOR(v_Cursor);
> RAISE;
> END DO_INSERT;
> /

Regards
/Rauf Sarwar Received on Fri Jan 31 2003 - 19:08:34 CST