Procedure declarations section

669816

Hi,
I'm working on my first procedure (I'm new to PL/SQL) and am having some confusion around the declarations section. When I run the following code it says "PLS-00201: identifier 'L_MONTHLY_DED_AMNT' must be declared" for each value I have in my declarations section. From everything I've read in books and online it appears to me that I've declared them properly, and messing around with different variations does nothing to resolve my problem.

PROCEDURE create_contract (
   l_prov_num          IN       NUMBER,
   l_divison           IN       NUMBER,
   l_clinic_source     IN       VARCHAR2,
   l_clinic_location   IN       VARCHAR2,
   l_tot_ded_amnt      IN       NUMBER,
   l_num_deductions    IN       NUMBER,
   l_tot_ded_amnt      IN       NUMBER,
   l_emp_id            IN       NUMBER,
   l_mrn               IN       NUMBER,
   l_emp_first_name    IN       VARCHAR2,
   l_emp_last_name     IN       VARCHAR2,
   l_pat_first_name    IN       VARCHAR2,
   l_pat_last_name     IN       VARCHAR2,
   l_personkey         OUT      NUMBER
)
IS
   l_monthly_ded_amnt   NUMBER;      -- := l_tot_ded_amnt / l_num_deductions;
   l_contr_start_date   DATE; --:= SYSDATE;
   l_current_cutoff     DATE; --:= SYSDATE;
BEGIN

   l_monthly_ded_amnt := l_tot_ded_amnt / l_num_deductions;

   SELECT TO_CHAR (CURRENT_DATE, 'DD-MON-YYYY')
     INTO l_contr_start_date
     FROM DUAL;

   SELECT cutoff_date
     INTO l_current_cutoff
     FROM prd_ded_cutoffs
    WHERE (cutoff_month || '-' || cutoff_year) =
                                            TO_CHAR (CURRENT_DATE, 'MON-YYYY');

   IF l_contr_start_date > l_current_cutoff
   THEN
      SELECT TO_DATE (TO_CHAR ((  ADD_MONTHS (SYSDATE, l_num_deductions + 1)
                                - TO_CHAR (ADD_MONTHS (SYSDATE,
                                                       l_num_deductions + 1
                                                      ),
                                           'dd'
                                          )
                                + 1
                               ),
                               'dd-mon-yyyy'
                              )
                     )
        INTO l_contr_end_date
        FROM DUAL;
   ELSE
      SELECT TO_DATE (TO_CHAR ((  ADD_MONTHS (SYSDATE, l_num_deductions)
                                - TO_CHAR (ADD_MONTHS (SYSDATE,
                                                       l_num_deductions
                                                      ),
                                           'dd'
                                          )
                                + 1
                               ),
                               'dd-mon-yyyy'
                              )
                     )
        INTO l_contr_end_date
        FROM DUAL;
   END IF;

   validate_emp (l_emp_id,
                 l_mrn,
                 l_emp_first_name,
                 l_emp_last_name,
                 l_pat_first_name,
                 l_pat_last_name,
                 l_personkey
                );
   INSERT INTO prd_contract
               (contract_key, person_key, provider_key, division,
                ccc_clinic_source, ccc_clinic_location,
                total_deduction_amount, number_of_deductions,
                monthly_deduction_amount, remaining_balance,
                remaining_deductions, contract_start_date, contract_end_date
               )
        VALUES (prd_contract_seq.NEXTVAL, l_personkey, l_prov_num, l_divison,
                l_clinic_source, l_clinic_location,
                l_tot_ded_amnt, l_num_deductions,
                l_monthly_ded_amnt, l_tot_ded_amnt,
                l_num_deductions, l_contr_start_date, l_contr_end_date
               );

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001,
                                  'An error was encountered - '
                               || SQLCODE
                               || ' -ERROR- '
                               || SQLERRM
                              );
END;

Edited to include all code.

Edited by: Tinlemon on Jul 21, 2009 12:46 PM

Frank Kulash

Hi,

You should start with a very, very small procedure and gradually add code, especially if this is your first procedure.

Start over. (What you've done already isn't wasted; you can cut from it and paste it into the new proceudre.)
As a first step, try creating the procedure with no arguments, and one local variable (like l_monthly_ded_amnt).
If you have problems, post the complete code (starting with "*CREATE* PROCEDURE").

I don't see any obvious errors in the part you posted.

Frank Kulash

Hi,

You should start with a very, very small procedure and gradually add code, especially if this is your first procedure.

Start over. (What you've done already isn't wasted; you can cut from it and paste it into the new proceudre.)
As a first step, try creating the procedure with no arguments, and one local variable (like l_monthly_ded_amnt).
If you have problems, post the complete code (starting with "*CREATE* PROCEDURE").

I don't see any obvious errors in the part you posted.

637614

How did you end the procedure? did you use ' / '?

669816

Hi Frank,
Thanks for your suggestion. I tested each part individually as I created the procedure (I'm using SQL Navigator so I just highlight the code to run) and didn't run into any problems, but I think your suggestion of stripping it down even further to the arguments is probably the way to go. I'll let you know how it goes.

SomeoneElse

You have two statements that begin: SELECT TO_DATE (TO_CHAR ...

It's a totally unnecessary round trip to the database to SELECT these from DUAL.

Just assign the values directly:

l_contr_end_date := to_date(etc.)...;

669816

Thank for the suggestion, I have put it into place.

637614

One of the ways you could try is to create a funtion for l_monthly_ded_amnt (l_tot_ded_amnt, l_num_deduction) and then call this function in the procedure????

William Robertson

This exception handler adds nothing, for several reasons:

EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001,
                                  'An error was encountered - '
                               || SQLCODE
                               || ' -ERROR- '
                               || SQLERRM
                              );

Firstly, PL/SQL's default behaviour for dealing with unhandled exceptions is to pass it back up the calling tree, so whatever called the procedure will get an exception along with an error message stack. This is exactly what you want, so there is no point writing your own code to duplicate the default behaviour.

Secondly, it is a limitation of PL/SQL that when you re-raise an exception in an exception handler you lose the information about the line that originally put you there, so in this case your attempt to duplicate the default behaviour is actually not as good as what you would have got if you'd done nothing. You can work around this to some extent with [dbms_utility.format_error_backtrace|http://www.oracle-developer.net/display.php?id=318], but in this case it's not worth it.

Thirdly, the purpose of RAISE_APPLICATION_ERROR is to let you define your own application-related errors, not to repeat Oracle's system ones. If you just want to re-raise the exception (perhaps after doing something constructive like logging it), then use RAISE. Here you probably want to say something about what failed and why, perhaps including the parameters that were passed. If there's nothing useful to add, then don't have an exception handler at all. To include the (reduced) Oracle exception stack, set the third argument to TRUE - never include SQLERRM or SQLCODE in the message text. In any case, SQLCODE is just the error number that is already included at the start of SQLERRM after "ORA-", so all it is doing here is jumbling up the standard Oracle error message.

http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

669816

Thanks William, I have removed that exception information out of the code. I simply took it from another procedure I found on the internet somewhere so I didn't really know why I was doing it, but it didn't seem to cause any problems so I just let it be. I know, not good practice to use code that I don't know what it is doing.

669816

I don't know what the problem ended up being, but as I stepped through the routine using Frank's suggestion of breaking it down to the line level things got working properly. Thank you all for your help.