| please help: pass string into sp [message #37335] |
Thu, 31 January 2002 15:32  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
Hi,
I have a sp called a function which parse the parameter strings from appl. into SQL like string, and this string is used in the sp's where clause, but it didn't return any rows while it did return a few rows if I hard code the exact formated string in the where clause. Below is part of my code :
1. function rpt_parseStrAll (facility_code) return varchar2
parameters passed in: 'us.abc.com;au.dc.com;us.efg.com'
the major part code like:
select instr(facility_code, ';', 1, i-1) into num1 from dual;
select instr(facility_code, ';', 1, i) into num2 from dual;
select substr(facility_code, num1+1, num2-num1-1) into facility from dual;
newstr:= newstr||', '||''''||facility||''''; => 'us.abc.com', 'au.dc.com', 'us.efg.com'
2. CREATE OR REPLACE PACKAGE strParseTestpkg IS
PRAGMA SERIALLY_REUSABLE;
TYPE det_rec is RECORD
(deptname VARCHAR2(60));
TYPE curType IS REF CURSOR return det_rec;
function strParse (
location IN VARCHAR2)
RETURN curType;
END;
/
CREATE OR REPLACE PACKAGE BODY strParseTestpkg IS
PRAGMA SERIALLY_REUSABLE;
function strParse (
location IN VARCHAR2)
return curType AS
newstr VARCHAR2(2000) :=null;
sum_cv curType;
BEGIN
newstr := rpt_parseStrAll(location);
DBMS_OUTPUT.PUT_LINE(newstr);
OPEN sum_cv FOR select dname from department
where loc in ( newstr ) ;
RETURN sum_cv;
END;
END;
Thank you for your help
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
