Results 1 to 2 of 2

Thread: Oracle Package

  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: Oracle Package

    I have a package with a stored procedure "s_user_authenticate" that returns a recordset (table) and it works great. Now I want to add another stored procedure "s_get_customer_data" but I am getting the following error: "PL/SQL Statement Ignored" not much help! The highlighted line is:
    Code:
    recCount:= recCount + 1;
    in the "s_get_customer_data" stored procedure. I don't see anything wrong with the syntax. I can stick more then one stored procedure in a package, right? Any ideas would be appreciated.

    Code:
    CREATE OR REPLACE PACKAGE czcs_pkg
    AS
      -- s_user_authenticate
      TYPE userid       IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
      TYPE user_name     IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
      TYPE upassword    IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
      TYPE first_name   IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
      TYPE last_name    IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;	
      TYPE usergroups   IS TABLE OF NUMBER(10)   INDEX BY BINARY_INTEGER;
    
      -- s_get_customer_data
      TYPE custid IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
      TYPE customer_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
      TYPE addr1 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
      TYPE addr2 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
      TYPE city IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
      TYPE state IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
      TYPE zipcode IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
      TYPE logo_path IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
      TYPE npc IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
      TYPE update_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
      TYPE create_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
      TYPE isactive IS TABLE OF CHAR(3) INDEX BY BINARY_INTEGER;
      TYPE update_userid IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;    
        
    
      PROCEDURE s_user_authenticate(
            o_userid OUT userid,
            o_user_name IN OUT user_name,
            o_upassword IN OUT upassword,
        	o_first_name OUT first_name,
        	o_last_name OUT last_name,
            o_usergroups OUT usergroups);
    
      PROCEDURE s_get_customer_data(
            o_cust_id IN custid,
            o_customer_name OUT customer_name,
            o_addr1 OUT addr1,
            o_addr2 OUT addr2,
            o_city OUT city,
            o_state OUT state,
            o_zipcode OUT zipcode,
            o_logo_path OUT logo_path, 
            o_npc OUT npc,
            o_update_date OUT update_date,
            o_create_date OUT create_date,
            o_isactive OUT isactive,
            o_update_userid OUT update_userid);
     
    END czcs_pkg;
    /
    
    
    CREATE OR REPLACE PACKAGE BODY czcs_pkg
    AS
    
      PROCEDURE s_user_authenticate(
            o_userid OUT userid,
            o_user_name IN OUT user_name,
            o_upassword IN OUT upassword,        
        	o_first_name OUT first_name,
        	o_last_name OUT last_name,
            o_usergroups OUT usergroups)        
      IS
        CURSOR user_cur  IS
            SELECT a.USERID, a.USER_NAME, a.UPASSWORD, a.FIRST_NAME, a.LAST_NAME, b.GID AS usergroups
            FROM TMOSER.CZCS_USERS a, TMOSER.CZCS_USER_GROUPS b
            WHERE a.USERID=b.USERID AND a.USER_NAME=user_name AND a.UPASSWORD=upassword; 
         
        recCount NUMBER DEFAULT 0;
        BEGIN
        FOR UserRec IN user_cur LOOP
     	
          recCount:= recCount + 1;
     	
          o_userid(recCount):=     UserRec.userid;
          o_user_name(recCount):=  UserRec.user_name;
          o_upassword(recCount):=  UserRec.upassword;
          o_first_name(recCount):= UserRec.first_name;
          o_last_name(recCount):=  UserRec.last_name;
          o_usergroups(recCount):= UserRec.usergroups;
    
        END LOOP;
        
      END s_user_authenticate;
     
       PROCEDURE s_get_customer_data(
            o_cust_id IN OUT custid,
            o_customer_name OUT customer_name,
            o_addr1 OUT addr1,
            o_addr2 OUT addr2,
            o_city OUT city,
            o_state OUT state,
            o_zipcode OUT zipcode,
            o_logo_path OUT logo_path, 
            o_npc OUT npc,
            o_update_date OUT update_date,
            o_create_date OUT create_date,
            o_isactive OUT isactive,
            o_update_userid OUT update_userid)
     
          IS
            CURSOR customer_cur  IS
                SELECT CUSTID, CUSTOMER_NAME, ADDR1, ADDR2,
                        CITY, STATE, ZIPCODE, LOGO_PATH,
                        NPC, UPDATE_DATE, CREATE_DATE,
                        ISACTIVE, UPDATE_USERID
                FROM TMOSER.CZCS_CUSTOMERS
                WHERE CUSTID=custid;
    
                recCount NUMBER DEFAULT 0;
                
            BEGIN
            FOR CustRec IN customer_cur LOOP
     	
            recCount:= recCount + 1;
     	
            o_custid(recCount):= CustRec.custid;
            o_customer_name(recCount):= CustRec.customer_name;
            o_addr1(recCount):= CustRec.addr1;
            o_addr2(recCount):= CustRec.addr2;
            o_city(recCount):=  CustRec.city;
            o_state(recCount):= CustRec.state;
            o_zipcode(recCount):= CustRec.zipcode;
            o_logo_path(recCount):= CustRec.logo_path;     
            o_npc(recCount):= CustRec.npc;
            o_update_date(recCount):= CustRec.update_date;
            o_create_date(recCount):= CustRec.create_date; 
            o_isactive(recCount):= CustRec.isactive;    
            o_update_userid(recCount):= CustRec.update_userid;
    
            END LOOP;
    
        END s_get_customer_data; 
      
    END czcs_pkg;
    /
    Thanks in advance for your help.

  2. #2
    Join Date
    Jan 2003
    Posts
    67

    Got it

    This "o_custid(recCount):= CustRec.custid;" is an "IN" var not and out and should not be in the cursor.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •