12 Replies Latest reply: Oct 16, 2013 10:59 AM by Gurbs RSS

    Help with procedure

    Gurbs

      Hi all,

       

      I am having some issues with a procedure, and am not sure how to fix it.

      I have the following procedure (a colleague wrote this):

       

      CREATE OR REPLACE PROCEDURE SP_ORIFLAME_DEBTOR_FEES_ROI

      (v_n_expediente NUMBER) AS

       

      v_fees number(18,2);

      vSaldo number(18,2);

      vMovMax number(10);

      vDeuda_id colldesk.deuda.deuda_id%type;

      BEGIN

       

        select 3.45

        into v_fees

        from dual;

       

        if v_fees < 15  then

          --get invoice

          select min(deuda_id)

          into vDeuda_id

          from deuda d

          where d.n_expediente = v_n_expediente and d.estado < 4 and codigo_cliente in ('6498')

          and saldo > 0 and f_ult_vencim = trim(sysdate-14);

       

          if vDeuda_id is not null then

                --get next linenumber

             vMovMax := colldesk.case_management_pkg.GetValueFichaCont_sp(vDeuda_id);

             if vMovMax is null then

                 vMovMax := 1;

                 vSaldo := 0;

             else

                 --get saldo

                 vSaldo := colldesk.case_management_pkg.GetSaldoFichaCont_sp(vDeuda_id, vMovMax);

                 if vSaldo is null then

                   vSaldo := 0;

                 end if;

                 vMovMax := vMovMax + 1;

             end if;

       

             colldesk.case_management_pkg.InsertFichaContable_sp(vDeuda_id, vMovMax, to_char(sysdate,'dd-mm-yyyy'),

             'dd-mm-yyyy', 3.45, 0,  vSaldo+3.45  , 'D', 0, 'COLLDESK');

          end if;

        end if;

       

      END SP_ORIFLAME_DEBTOR_FEES_ROI;

       

      However, when I run this, I get the following error:

       

      ORA-06550: line 1, column 7:

      PLS-00306: wrong number or types or arguments in call to 'SP_ORIFLAME_DEBTOR_FEES_ROI'

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

       

      Any thoughts?

       

      Thanks in advance

        • 1. Re: Help with procedure
          Priyasagi

          Hi,

           

          Post, how you are running this procedure.

          What are the arguments you are passing to the procedure?

          • 2. Re: Help with procedure
            ranit B

            Hi,

             

            Please show how did you call this procedure?

            - While executing the proc you need to pass a NUMBER argument (coz the proc will expect that)

             

            Is this proc present inside a package?

            (Also, I can see there's a tag "procedure_in_package"... Reason? )

             

            For better response, it is always advisable to provide details like - Database version (select * from v$version) and all necessary related code snippets.

             

            A good practice: Always wrap your code using the Quote option (in Advanced editor, click on the >> button)


            HTH

            -- Ranit

            • 3. Re: Help with procedure
              Hoek

              Another thought: error messages are documented:

               

              PLS-00306: wrong number or types of arguments in call to 'string'

              Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

              Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

              http://docs.oracle.com/cd/E11882_01/server.112/e17766/pcmus.htm#PLS-00306

              Oracle Database Online Documentation 11g Release 2 (11.2)

              • 4. Re: Help with procedure
                Gurbs

                Hi,

                 

                The procedure is run during a set of night jobs. It should run for all cases that have an estado < 4, saldo > 0 and f_ult_vencim of 14 days ago.

                 

                I never wrote a full procedure myself, only adjusted existing ones. So I might ask some stupid questions

                 

                Thanks

                • 5. Re: Help with procedure
                  kendenny

                  Are you trying to execute the procedure without a parameter? I believe ranitB gave you the correct answer.
                  But I can't help wondering about this:

                   

                  and f_ult_vencim = trim(sysdate-14)

                   

                  Did you mean to use trunc rather than trim?

                  • 6. Re: Help with procedure
                    Gurbs

                    Yes, I think so. If any case in the database under creditor 6498 (codigo_cliente), with a status that is open (estado < 4), with an outstanding amount > 0 (Saldo) and a due date of 14 days in the past (f_ult_vencim) then it should be updated.

                    • 7. Re: Help with procedure
                      kendenny

                      The procedure requires a parameter. If you're not executing it wih a parameter, that's why you're getting the error. The parameter is what you want it to select for the value of n_expediente.

                      • 8. Re: Help with procedure
                        Gurbs

                        Okay, I don't want to fill in only 1 parameter, I want it to loop through the data and fetch all the records that are due. I changed the beginning of the procedure to this:

                         

                        CREATE OR REPLACE PROCEDURE SP_ORIFLAME_DEBTOR_FEES_ROI IS

                         

                        v_n_expediente number(18,2);

                        v_fees number(18,2);

                        vSaldo number(18,2);

                        vMovMax number(10);

                        vDeuda_id colldesk.deuda.deuda_id%type;

                        BEGIN

                         

                        I need to create a loop to fill in v_n_expediente. The question is, how do I write that?

                        • 9. Re: Help with procedure
                          Gurbs

                          Okay I managed to create a loop, but somehow it is still not working. It is coming back with the error:

                           

                          ORA:01422: exact fetch returns more than requested number of rows

                          ORA-06512: at "LOCAL_IT.SP.ORIFLAME_DEBTOR_FEES_ROI", line 30

                          ORA-06512: at line 1

                           

                          I am using the following procedure:

                           

                          CREATE OR REPLACE PROCEDURE SP_ORIFLAME_DEBTOR_FEES_ROI IS

                           

                          v_n_expediente number(18,2);

                          v_fees number(18,2);

                          vSaldo number(18,2);

                          vMovMax number(10);

                          vDeuda_id colldesk.deuda.deuda_id%type;

                           

                          cursor c_n_expediente is

                            select n_expediente from deuda where estado < 4 and codigo_cliente in ('6498')

                            and saldo > 0 and f_ult_vencim < trunc(sysdate-14);

                           

                          BEGIN

                           

                            select 3.45

                            into v_fees

                            from dual;

                           

                            open c_n_expediente;

                            loop

                              fetch c_n_expediente into v_n_expediente;

                              exit when c_n_expediente%notfound;

                           

                            /*select n_expediente into v_n_expediente

                            from deuda where estado < 4 and codigo_cliente in ('6498')

                            and saldo > 0 and f_ult_vencim = trunc(sysdate-14);*/

                           

                            if v_fees = 3.45  then

                              --get invoice

                              select deuda_id

                              into vDeuda_id

                              from deuda d

                              where n_expediente = v_n_expediente;

                           

                              if vDeuda_id is not null then

                                    --get next linenumber

                                 vMovMax := colldesk.case_management_pkg.GetValueFichaCont_sp(vDeuda_id);

                                 if vMovMax is null then

                                     vMovMax := 1;

                                     vSaldo := 0;

                                 else

                                     --get saldo

                                     vSaldo := colldesk.case_management_pkg.GetSaldoFichaCont_sp(vDeuda_id, vMovMax);

                                     if vSaldo is null then

                                       vSaldo := 0;

                                     end if;

                                     vMovMax := vMovMax + 1;

                                 end if;

                           

                                 colldesk.case_management_pkg.InsertFichaContable_sp(vDeuda_id, vMovMax, to_char(sysdate,'dd-mm-yyyy'),

                                 'dd-mm-yyyy', 3.45, 0,  vSaldo+3.45 , 'D', 0, 'COLLDESK');

                              end if;

                            end if;

                          end loop;

                          close c_n_expediente;

                          END SP_ORIFLAME_DEBTOR_FEES_ROI;

                           

                          Please would someone be able to help me?

                          • 10. Re: Help with procedure
                            kendenny

                            Looks like your problem is this:

                            select deuda_id

                            into vDeuda_id

                            from deuda d

                            where n_expediente = v_n_expediente;

                            If n_expediente is not unique in the table deuda you'll get this.

                            SELECT INTO requires that there be only one row which satisfies the WHERE clause.

                            You can get around the error by selecting max(deuda_id) or min(deuda_id) but I don't know if that's what you want or not.

                            • 11. Re: Help with procedure
                              Gurbs

                              So simple, yet so freakinly annoying. There was 1 case duplicated in our database. Usually, n_expediente is a unique number, but the case was loaded twice into the system. once I filtered this out, the procedure worked! Thanks a lot all

                              • 12. Re: Help with procedure
                                Frank Kulash

                                Hi,

                                 

                                Now that you've found the problem, make sure it can't happen again.

                                Create a UNIQUE constraint on the table, if appropriate.  If not change the procedure.

                                 

                                select  MIN (deuda_id)

                                into    vDeuda_id

                                from    deuda d

                                where   n_expediente = v_n_expediente;

                                will never return more than 1 row.  The same goes if you use any other aggregate function, like AVG, MAX or SUM in place of MIN.  I don't know which, if any, would do what you want.

                                If you cant do anything else, raise a user-defined exception that precisely identifies the problem.