PHP - oci - oracle package
Posted: Fri Oct 01, 2004 7:35 am
Edit patrikg: USE
Edit patrikg: USE
Code: Select all
-tags around code!!![/color]
Hello All,
i am trying to get results from the below oracle package using php5 oci8. I have wrtten the below function to do so but i am totaly perplexed as to how to write the sql needed to obtain any results. My function calls another function which has been tested and works.
This extra functions errors and indicates the problem lies when I try to run OCIExecute. I therefore feel confident that the problem lies with my sql statement.
Please help..Code: Select all
MY FUNCTION
$sql = sprintf("
BEGIN
select web_search.details (:p_mem_no) from dual;
END;
");
// Set up our Bind args...
$bargs = array();
array_push($bargs, array("p_mem_no", $mem_memno, -1));
// run the query...
$stmt = $this->query("*****", $sql, $bargs);
------------------------------------------------------------------------------------
PRE-SUPPLIED ORACLE PACKAGE.
create or replace package web_search AS
FUNCTION details ( p_mem_no IN NUMBER)
RETURN VARCHAR2;
FUNCTION details ( p_surname IN VARCHAR2,
p_inits IN VARCHAR2,
p_postcode IN VARCHAR2)
RETURN VARCHAR2;
end web_search;
/
/
create or replace package body web_search
AS
TYPE memrec_type IS RECORD
(mem_no member_prospects.member_prospect_no%TYPE,
initials member_prospects.initials%TYPE,
surname member_prospects.name%TYPE,
post_code member_prospects.post_code%TYPE,
mpr_type member_prospects.mpr_type%TYPE ,
sty_scheme_type_code member_prospects.sty_scheme_type_code%TYPE ,
title_desc titles.title_desc%TYPE );
memrec memrec_type;
l_outrec VARCHAR2(200);
FUNCTION renew_date ( p_member_no IN usages.mem_member_no%TYPE)
RETURN DATE
IS
-- Cursor declarations
CURSOR c_renew_date (cp_member_no usages.mem_member_no%TYPE)
IS
SELECT max(usa.renewal_date)
FROM usages usa
where usa.spt_pro_product_code = -2
and usa.mem_member_no = cp_member_no;
l_renewal_date usages.renewal_date%TYPE;
BEGIN
OPEN c_renew_date(p_member_no);
FETCH c_renew_date into l_renewal_date;
IF c_renew_date%NOTFOUND THEN
l_renewal_date := NULL;
END IF;
CLOSE c_renew_date;
RETURN l_renewal_date;
END renew_date;
FUNCTION get_string ( memrec IN memrec_type )
RETURN VARCHAR2
IS
delim CONSTANT VARCHAR2(1) := '/';
l_event_type_code usage_events.uet_usage_event_type_code%TYPE;
l_renewal_date VARCHAR2(8) := NULL;
l_string VARCHAR2(200);
BEGIN
IF memrec.mpr_type = 'P' THEN
l_event_type_code := -98; -- Prospect
ELSIF memrec.sty_scheme_type_code = -1 THEN
l_event_type_code := -99; -- BRL member
ELSE
l_event_type_code := member_status(memrec.mem_no); --CSMA member status
l_renewal_date := to_char(renew_date(memrec.mem_no), 'YYYYMMDD');
END IF;
l_string := l_event_type_code||delim||
l_renewal_date||delim||
memrec.title_desc||delim||
memrec.initials||delim||
memrec.surname||delim||
memrec.post_code;
RETURN l_string;
END get_string;
FUNCTION details ( p_surname IN VARCHAR2,
p_inits IN VARCHAR2,
p_postcode IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR c_get_member (cp_surname VARCHAR2,
cp_postcode VARCHAR2,
cp_inits VARCHAR2 )
IS
SELECT mpr.member_prospect_no,
mpr.initials,
mpr.name,
mpr.post_code,
mpr.mpr_type,
mpr.sty_scheme_type_code,
tit.title_desc
FROM member_prospects mpr, titles tit
WHERE mpr.tit_title_code = tit.title_code
AND mpr.name = cp_surname
AND mpr.post_code = cp_postcode
AND mpr.initials = cp_inits
;
rec_member c_get_member%ROWTYPE;
BEGIN
OPEN c_get_member(p_surname, p_postcode, p_inits );
FETCH c_get_member INTO rec_member;
IF c_get_member%NOTFOUND THEN
l_outrec := 0; -- no member found
ELSE
l_outrec := get_string(rec_member);
END IF;
RETURN l_outrec;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR = '||SQLERRM);
END details;
FUNCTION details ( p_mem_no IN NUMBER)
RETURN VARCHAR2
IS
CURSOR c_get_member (cp_mem_no NUMBER)
IS
SELECT mpr.member_prospect_no,
mpr.initials,
mpr.name,
mpr.post_code,
mpr.mpr_type,
mpr.sty_scheme_type_code,
tit.title_desc
FROM member_prospects mpr, titles tit
WHERE mpr.tit_title_code = tit.title_code
AND mpr.member_prospect_no = cp_mem_no
;
rec_member c_get_member%ROWTYPE;
BEGIN
OPEN c_get_member(p_mem_no);
FETCH c_get_member INTO rec_member;
IF c_get_member%NOTFOUND THEN
l_outrec := 0; -- no member found
ELSE
l_outrec := get_string(rec_member);
END IF;
RETURN l_outrec;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR = '||SQLERRM);
END details;
END web_search;Code: Select all
-tags around code!!![/color]