Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: stored proc error

    Ok, I did my homework and found instructions on creating an SP that can return a recordset. Below is the code. I am getting the following error...
    Code:
    PLS-00103: Encountered the symbol "end-if-file" when expecting one of the following: begin function package pragma procedure from
    It looks to me like I am missing a begin or end somewhere.

    Code:
    CREATE OR REPLACE PACKAGE authentication_pkg
    AS
      TYPE userid       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;
      TYPE pwd          IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;	
     
      PROCEDURE s_user_authenticate(
            userid IN OUT userid,
            pwd IN pwd,
        	first_name OUT first_name,
        	last_name OUT last_name,
            usergroups OUT usergroups);
     
    END authentication_pkg;
    /
    
    CREATE OR REPLACE PACKAGE BODY authentication_pkg
    AS
    PROCEDURE s_user_authenticate(
            userid IN OUT userid,
            pwd IN pwd,
        	first_name OUT first_name,
        	last_name OUT last_name,
            usergroups OUT usergroups);        
      IS
        CURSOR user_cur  IS
          SELECT userid, first_name, last_name, usergroups
            FROM TMOSER.CZCS_USERS;
         
        recCount NUMBER DEFAULT 0;
      BEGIN
        FOR UserRec IN user_cur LOOP
     	
          recCount:= recCount + 1;
     	
          userid(recCount):=      UserRec.userid;
          first_name(recCount):= UserRec.first_name;
          last_name(recCount):=  UserRec.last_name;
          usergroups(recCount):=  UserRec.usergroups;
    
        END LOOP;
    
      END s_user_authenticate;
     
    END authentication_pkg;
    /

    Thanks in advance for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Maybe if you remove the colon from this statement:

    usergroups OUT usergroups);

    in the package body.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Sacramento
    Posts
    5
    if you take the ";" before the "IS" in the following line it should work.
    PROCEDURE s_user_authenticate(
    userid IN OUT userid,
    pwd IN pwd,
    first_name OUT first_name,
    last_name OUT last_name,
    usergroups OUT usergroups);
    IS

  4. #4
    Join Date
    Jan 2003
    Posts
    67

    Thanks!

    That did the trick! 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
  •