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

Thanks for your reply.

1. The oracle version is 8.1.7.0.0 Enterprise Edition on Sun Solaris 2.8
2. If dbms_sql.fetch_rows(c) = 0 (that is, no rows in the cursor as the result of "select"), then it will jump to

dbms_sql.close_cursor(c);
return retstr;

And with 'retstr varchar2(1024) := null;' we know that a null value is returned in this case.

Guang

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CBCA2D1.FC8D2309_at_exesolutions.com>...
> 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 - 21:09:41 CDT