Anyway to speed up this pl/sql code?

From: G M <zlmei_at_hotmail.com>
Date: 16 Apr 2002 13:39:04 -0700
Message-ID: <50a5e6b6.0204161239.717ec84c@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 - 15:39:04 CDT