Home » SQL & PL/SQL » SQL & PL/SQL » please help: pass string into sp
please help: pass string into sp [message #37335] Thu, 31 January 2002 15:32 Go to next message
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
Re: please help: pass string into sp [message #37354 is a reply to message #37335] Fri, 01 February 2002 11:03 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You can just build qour query up as a string and then execute it, but uning bind variable you need a more complex approach.

http://asktom.oracle.com/pls/ask/f?p=4950:8:462327::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:146012348066,
Previous Topic: procedure question
Next Topic: does varchar2 actually take space
Goto Forum:
  


Current Time: Thu Jul 02 11:05:44 CDT 2020