Page 1 of 1

PHP - oci - oracle package

Posted: Fri Oct 01, 2004 7:35 am
by Deddog
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;
Edit patrikg: USE

Code: Select all

-tags around code!!![/color]

Posted: Fri Oct 01, 2004 8:36 am
by mudkicker
please use code tags & highlighting..