Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Stored Procedure errors

    I have a fuction and procedure that work together to validate passwords in an Oracle DB:

    Procedure valid_user_pro(tempLogin_Id in ccmsdba.personnel.LOGIN_ID%Type,
    tempPass in ccmsdba.personnel.PASSWORD%Type) as tempDummy varchar2(1);
    Begin
    Select '1'
    Into tempDummy
    From ccmsdba.personnel pe
    Where pe.LOGIN_ID = temLogin_ID
    And pe.PASSWORD = tempPass;
    Exception
    When No_data_found then
    Select '0' into tempDummy;
    end;
    end procedure;


    FUNCTION valid_user_func
    (tempLogin_id ccmsdba.personnel.LOGIN_ID%Type,tempPass ccmsdba.personnel.PASSWORD%Type) Return Boolean as

    Begin
    valid_user_pro(tempLogin_id, tempPass);
    Return True;
    Exception
    When Others then
    Return False;
    End;

    end function;



    When I compile this Procedure, I keep getting two errors:


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/57 PLS-00103: Encountered the symbol "TEMPDUMMY" when expecting one
    of the following:
    language

    13/28 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    . ( , % from

    Does anyone have an idea on what I am doing wrong?

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Stored Procedure errors

    On your exception you have

    Select '0' into tempDummy;

    without a table to get the data from. You must have a From clause so

    Select '0' into tempDummy from Dual;

    should work.

    Couple things:

    - If you want variable of 1 character use CHAR(1) instead of Varchar
    - Why do you seperate the check into 2 objects when it can be done in the function

    I personally would write it more like this

    PHP Code:
    FUNCTION valid_user_function (
      
    tempLogin_id ccmsdba.personnel.LOGIN_ID%Type,
      
    tempPass ccmsdba.personnel.PASSWORD%Type
      
    Return 
    Boolean 

    As

       
    Cursor c_valid_user is
          Select 
    'Y'
           
    From ccmsdba.personnel pe
           Where pe
    .LOGIN_ID temLogin_ID
             
    And pe.PASSWORD tempPass;
             
       
    lv_valid_ind  CHAR(1);
       
    lv_results    BOOLEAN;         

    Begin

       Open c_valid_user
    ;
       
    Fetch into lv_valid_ind;
       
          If 
    c_valid_user%notfound Then
             lv_results 
    := FALSE;
          Else
             
    lv_results := TRUE;
          
    End If;
       
       
    Close c_valid_user;

       Return 
    lv_results;

    Exception
       When Others Then
          
    Return False;
       
    End

Posting Permissions

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