Re: API works fine, but returns no results to ASP front end

From: <pobox002_at_bebub.com>
Date: 1 Sep 2005 07:02:04 -0700
Message-ID: <1125579898.177490.192590@f14g2000cwb.googlegroups.com>

jimi_xyz_at_hotmail.com wrote:
> I build up the string b/c its dynamic SQL

You shouldn't use dynamic SQL for this, it will lead to over parsing and shared pool fragmentation.

You already have the in list in an array type, hopefully a SQL one, so you can do something like this.

SQL> create or replace type tabstr_t as table of varchar2(80);   2 /

Type created.

SQL> create or replace function f (p_list in tabstr_t)   2 return sys_refcursor
  3 as
  4 l_c sys_refcursor;
  5 begin
  6 open l_c for

  7      select e.* from emp e,
  8      (select upper(column_value) x
  9        from table(cast(p_list as tabstr_t)))
 10      where ename = x;

 11 return l_c;
 12 end;
 13 /

Function created.

SQL> var c refcursor
SQL> exec :c := f(tabstr_t('smith','scott','ford'))

PL/SQL procedure successfully completed.

SQL> print c

 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ---------- --------- ------ --------- ------ ------ ------
  7369 SMITH      CLERK       7902 17-DEC-80    800            20
  7788 SCOTT      ANALYST     7566 09-DEC-82   3000            20
  7902 FORD       ANALYST     7566 03-DEC-81   3000            20

SQL>

-- 
MJB

Received on Thu Sep 01 2005 - 09:02:04 CDT