Re: Anyway to speed up this pl/sql code?

    I have not tried this but ,
    If you make the "idlist" an object type you can select from it using the table cast operator. Then you can rewrite your dynamic sql as a static cursor accepting a parameter. ( static sql being faster). Next you can do bulk fetch from the opened cursor and operate on it.

    Additions/corrections from the experts are welcome.

Sunil.

"G M" <zlmei_at_hotmail.com> wrote in message news:50a5e6b6.0204161239.717ec84c_at_posting.google.com...
> Hi:
>
> Do you think there is a way to optimize the following PL/SQL code (in
> terms of performance)? An example of the parameter "idlist" could be
> "1,2,3".
>
> TIA.
>
> Guang
>
> ------------
> function getMethods(idlist in varchar2) return varchar2 is
> c integer;
> dum integer;
> retstr varchar2(1024) := null;
> base_sql varchar2(256) := 'select distinct method from XYZ '||
> 'where method is not null and id in ';
>
> begin
> c := dbms_sql.open_cursor;
> dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
> dbms_sql.define_column(c, 1, method, 128);
> dum := dbms_sql.execute(c);
>
> loop
> if dbms_sql.fetch_rows(c)>0 then
> dbms_sql.column_value(c, 1, method);
> -- doing something to set retstr here
> else
> dbms_sql.close_cursor(c);
> return retstr;
> end if;
> end loop;
> end getMethods;
> -------------
Received on Tue Apr 16 2002 - 23:23:17 CDT