Oracle procedure

Trying to call a procedure in a package with 3 input params and 3 output params

In SQL Developer heres my call:
SET serveroutput on;
DECLARE
  P_CVG_ID VARCHAR2(10);
  P_START_DATE DATE;
  P_END_DATE DATE;
  P_MIN_DATE DATE;
  P_MAX_DATE DATE;
  P_MOD_PREM NUMBER;
BEGIN
    /* Assign values to IN parameters */
  P_CVG_ID := '0439125';
  P_START_DATE := to_date('2010-07-08','yyyy-MM-dd');
  P_END_DATE := to_date('2010-07-08','yyyy-MM-dd');

    /* Call procedure within package, identifying schema if necessary */
    WEBSEARCH_INTERFACE_PKG.COVGRP_TOTAL_PREM(
    P_CVG_ID, P_START_DATE, P_END_DATE , P_MIN_DATE, P_MAX_DATE, P_MOD_PREM);

    /* Display OUT parameters */
    dbms_output.put_line('OutParam1: ' || P_MIN_DATE);
    dbms_output.put_line('OutParam2: ' || P_MAX_DATE);
    dbms_output.put_line('OutParam3: ' || P_MOD_PREM);

END;

If youd like to see the procedure just ask.  But this is what I get instead of values

anonymous block completed
OutParam1:
OutParam2:
OutParam3:
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If you are expecting values, then there is a bug/flaw in the procedure.

I would add dbms_output.put_line debug statement inside the procedure to see what it is doing.


If the procedure isn't huge, then you can post it as long as there isn't sensitive information in it.

If it is a LOT of code, I don't have the time to walk through it to debug it for you.
1
Mark GeerlingsDatabase AdministratorCommented:
Apparently the procedure you called from that anonymous PL\SQL block completed without causing an Oracle error, but it did not set the return values you expected.  Either do what slightwv
recommended.  Or, you can add a variable in that procedure (something like: v_step) and some lines at multiple places in the procedure like this:
  v_step := '1';
  v_step := '2';
  etc.
And add a dbms_output.put_line at the end of the procedure to display the current value of: v_step.  If your procedure has an "exception" section at the end, add this line there too:
  dbms_output.put_line('After step: '||v_step);

And, make sure the you have serveroutput set to "on".
0
slightwv (䄆 Netminder) Commented:
>>"exception" section at the end, add this line there too:

There is a potential, albeit it unlikely, issue with this approach:  Nested pl/sql blocks where the exception isn't re-raised to the global handler.  There could be an error that a global handler never sees.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jknj72Author Commented:
Well here is the procedure...I think this has been used in the past so Im not sure where its going wrong?? I will see what happens if I put in the exception, unless you guys can tell by looking at the procedure?  THANKS

procedure covgrp_total_prem(p_cvg_id in cov_period.cvg_id%type,
                                                 p_start_date in date,
                                                 p_end_date in date,
                                                 p_min_date out date,
                                                 p_max_date out date,
                                                 p_mod_prem out number)
is

begin

   select min(pol_effdt)
        , max(pol_effdt)
        , sum(modified_prem+prem_disc+expense_const+ppap+addtn_prem+njccpap)
     into p_min_date
        , p_max_date
        , p_mod_prem
     from (select distinct pol_effdt
                , modified_prem
                , (select nvl(sum(prem),0)
                     from spectrum.expo x
                    where x.carrid = d.carrid
                      and x.polnbr = d.polnbr
                      and x.pol_effdt = d.pol_effdt
                      and x.rpt = d.rpt
                      and x.corr = d.corr
                      and x.class in ('0063','0064')) prem_disc
                , (select nvl(sum(prem),0)
                     from spectrum.expo x
                    where x.carrid = d.carrid
                      and x.polnbr = d.polnbr          
                      and x.pol_effdt = d.pol_effdt
                      and x.rpt = d.rpt
                      and x.corr = d.corr
                      and x.class = '0900') expense_const
                , (select nvl(sum(prem),0)
                     from spectrum.expo x
                    where x.carrid = d.carrid
                      and x.polnbr = d.polnbr
                      and x.pol_effdt = d.pol_effdt
                      and x.rpt = d.rpt
                      and x.corr = d.corr
                      and x.class = '0942') ppap
                , (select nvl(sum(prem),0)
                     from spectrum.expo x
                    where x.carrid = d.carrid
                      and x.polnbr = d.polnbr
                      and x.pol_effdt = d.pol_effdt
                      and x.rpt = d.rpt
                      and x.corr = d.corr
                      and x.class = '0990') addtn_prem
                , (select nvl(sum(prem),0)
                     from spectrum.expo x
                    where x.carrid = d.carrid
                      and x.polnbr = d.polnbr
                      and x.pol_effdt = d.pol_effdt
                      and x.rpt = d.rpt
                      and x.corr = d.corr
                      and x.class = '9046') njccpap    
             from (select carrid
                        , polnbr
                        , pol_effdt
                        , rpt
                        , corr
                        , sum(modified_prem) modified_prem
                     from (select distinct carrid
                                , polnbr
                                , pol_effdt
                                , rpt
                                , corr
                                , round(total_prem * expmod) modified_prem
                             from (select distinct b.carrid
                                        , b.polnbr
                                        , b.pol_effdt
                                        , b.rpt
                                        , b.corr
                                        , c.split_ind
                                        , c.prem
                                        , c.class
                                        , sum(c.prem) over (partition by c.carrid,c.polnbr,c.pol_effdt,c.rpt,c.corr,c.split_ind) total_prem
                                        , c.expmod
                                     from spectrum.cov_period a
                                     join spectrum.usr b on (b.carrid = a.carrid and b.polnbr = a.polnbr and b.pol_effdt = a.period_effdt and b.rpt = '01')
                                     join spectrum.expo c on (c.carrid = b.carrid and c.polnbr = b.polnbr and c.pol_effdt = b.pol_effdt and c.rpt = b.rpt and c.corr = b.corr and c.class_category = 'A' and c.corrected_by is null and c.translate_to_null = 'N')
                                    where a.cvg_id = '0439125'
                                      and a.period_effdt between '08-JUL-10' and '08-JUL-10'))
                    group by carrid,polnbr,pol_effdt,rpt,corr) d
          );
end covgrp_total_prem;
0
slightwv (䄆 Netminder) Commented:
If that is all the code, then the issue appears simple:
The input parameters you provided didn't return any values from the select.

I would take the select from the procedure and hard-code your values from the parameters and run it.  See if you get 'No rows found'.


>> and a.period_effdt between '08-JUL-10' and '08-JUL-10'))

Don't do this.  Use explicit data type conversions:
 and a.period_effdt between to_date('08-JUL-10','DD-MON-YY') and to_date('08-JUL-10','DD-MON-YY')))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
This was an attempt to help out a peer and the dates he gave me were wrong like you said Slight. I changed the dates and it worked so thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.