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

Yes. Though which of many possibilities depends on what version of Oracle. Something you neglected to provide. Does the version you have contain native dynamic SQL?

BTW: What is the point of 'retstr varchar2(1024) := null;'?

Daniel Morgan

G M wrote:

> 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 - 17:16:56 CDT