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_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"David" <oracledba_at_eastmail.com> 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
Received on Thu Jan 30 2003 - 21:16:12 CST
