PL/SQL Loop

[David]

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

[Jim Kennedy]

Cold Fusion does allow bind variables, use bind variables. That will be
much faster then your pl/sql method. They call it something other than bind
variables. Sorry I looked it up under google for a Cold Fusion Developer at
one time. I think I looked up Cold Fusion Oracle bind performance.
Jim

--
Replace part of the email address: kennedy-down_...@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"David" <orac...@eastmail.com> wrote in message
news:15b372c2.03013...@posting.google.com...

[Rauf Sarwar]

orac...@eastmail.com (David) wrote in message news:<15b372c2.03013...@posting.google.com>...

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