Home » SQL & PL/SQL » SQL & PL/SQL » Problem in my package procedure in a select query
Problem in my package procedure in a select query [message #435972] Sun, 20 December 2009 06:38 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
    PROCEDURE PROCESS_ROW( P_Event             IN VARCHAR2, 
                           P_Old_Record_Key    IN VARCHAR2, 
                           P_Ledger_Id         IN VARCHAR2,
                           P_Reference         IN VARCHAR2,
                           P_Fund              IN VARCHAR2) IS
                            
    l_cash_txn_id   PF_CASH_TXNS.CASH_TXN_ID%TYPE;
    l_client_id     PF_CASH_TXNS.CLIENT_ID%TYPE;
    l_plan_id       PF_CASH_TXNS.PLAN_ID%TYPE;
    l_account       VARCHAR2(1);
    l_plan_id       VARCHAR2(100);
    
    BEGIN
        l_cash_txn_id := P_Ledger_Id;
        
        l_client_id := SUBSTR(P_Reference, -6);
            
        l_account := SUBSTR(P_Reference, 1, 1); -- Get first letter of reference code, this is the account type.
   
        SELECT PLAN_ID INTO l_plan_id FROM INTEGRATION.MAPPING WHERE account = l_account;
       
    END PROCESS_ROW;


The problem is at this line:
SELECT PLAN_ID INTO l_plan_id FROM INTEGRATION.MAPPING WHERE account = l_account;

The error(s) I'm getting is:
PLS-00371: at most one declaration for 'L_PLAN_ID' is permitted
PL/SQL: ORA-00904: : invalid identifier


The INTEGRATION.MAPPING table is currently empty, I'm not sure if this would have anything to do with it?
Re: Problem in my package procedure in a select query [message #435973 is a reply to message #435972] Sun, 20 December 2009 06:43 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Hmm. This is very odd.

I changed

l_plan_id VARCHAR2(100);

to

l7_plan_id VARCHAR2(100);

and now the errors have gone. Could anyone explain this?
Re: Problem in my package procedure in a select query [message #435974 is a reply to message #435973] Sun, 20 December 2009 06:52 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

PLS-00371: at most one declaration for 'L_PLAN_ID' is permitted


You had declared 'L_PLAN_ID' two times.

One time as PF_CASH_TXNS.PLAN_ID%TYPE, one time as VARCHAR2(100)
Re: Problem in my package procedure in a select query [message #435976 is a reply to message #435972] Sun, 20 December 2009 07:50 Go to previous message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Silly me.

Thanks for that Thomas.
Previous Topic: display two digits after decimal point,
Next Topic: left and right functions?
Goto Forum:
  


Current Time: Tue Feb 18 22:19:35 CST 2025