• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

Re : Function

Hi every one
create or replace function employee_billrate_func (employee_number in varchar2, project_name in varchar2)
return number
is
v_bill_rate number;
v_bill_rate1 number;
v_bill_rate2 number;
--v_bill_rate3 number;
begin
dbms_output.put_line('v_bill_rate is');
begin
select distinct pbr2.rate into v_bill_rate
from pa_emp_bill_rate_overrides pbr2,
ap_invoice_lines_all aia,
per_people_x ppf,
pa_projects_all ppa
where pbr2.project_id = aia.project_id
and ppa.project_id = aia.project_id
and aia.attribute1 = ppf.npw_number
and ppf.person_id = pbr2.person_id
and aia.attribute1 is not null
and ppf.npw_number = employee_number
and ppa.name = project_name;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
if v_bill_rate IS NOT null then
dbms_output.put_line('v_bill_rate is'|| v_bill_rate);
end if;
return v_bill_rate;
begin
select distinct pbr3.rate into v_bill_rate1
from pa_job_bill_rate_overrides pbr3,
ap_invoice_lines_all aia,
per_people_f ppf,
pa_projects_all ppa,
per_all_assignments_f paf
where pbr3.project_id = aia.project_id
and ppa.project_id = aia.project_id
and aia.attribute1 = ppf.npw_number
and paf.job_id = pbr3.job_id
and ppf.person_id = paf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag='Y'
and aia.attribute1 is not null
and ppf.npw_number = employee_number
and ppa.name = project_name;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
if v_bill_rate1 IS NOT null then
dbms_output.put_line('v_bill_rate1 is'|| v_bill_rate1);
end if;
return v_bill_rate1;
begin
select distinct pbr.rate into v_bill_rate2
from pa_projects_all ppa,
pa_tasks pta,
ap_invoice_lines_all aia,
per_people_x ppf,
pa_bill_rates_all pbr
where ppa.project_id = pta.project_id
and ppa.project_id = aia.project_id
and pta.task_id = aia.task_id
and ppf.npw_number = aia.attribute1
and ppf.person_id = pbr.person_id
and pbr.org_id = aia.org_id
and ppf.npw_number = employee_number
and ppa.name = project_name
union
select distinct pbr.rate
from pa_projects_all ppa,
pa_tasks pta,
ap_invoice_lines_all aia,
per_all_assignments_f ppf,
per_people_x papf,
pa_bill_rates_all pbr
where ppa.project_id = pta.project_id
and ppa.project_id = aia.project_id
and pta.task_id = aia.task_id
and papf.npw_number = aia.attribute1
and ppf.job_id = pbr.job_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.primary_flag='Y'
and papf.person_id = ppf.person_id
and aia.org_id = pbr.org_id
and papf.npw_number = employee_number
and ppa.name = project_name;
--dbms_output.put_line('v_bill_rate is'|| v_bill_rate2);
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
if v_bill_rate2 IS NOT null then
dbms_output.put_line('v_bill_rate2 is'|| v_bill_rate2);
end if;
return v_bill_rate2;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
/
i am using the above query to return a bill rate when i execute the select statements individually they are working but when i call the function
its not returning any value

saying no data found

comments please
0
iamtechnical
Asked:
iamtechnical
  • 8
  • 6
  • 4
1 Solution
 
sdstuberCommented:
please post your exact tests and results
0
 
iamtechnicalAuthor Commented:
what do u mean exact tests......
because i tested with my DB values
0
 
AkenathonCommented:
You have unreachable code, probably your returns should go inside the preceeding IFs, for instance the first one:

   IF v_bill_rate IS NOT NULL THEN
      DBMS_OUTPUT.put_line ('v_bill_rate is' || v_bill_rate);
   END IF;

   RETURN v_bill_rate;

Open in new window


As it is outside the IF, the function returns NULL whenever your first SELECT returns NULL :-)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sdstuberCommented:
>>> when i execute the select statements individually

post your select statement including the values you tested with

also post your code that executes the function, including the values you tested with
0
 
AkenathonCommented:
By the way: You can have Oracle spot similar issues by using alter session set plsql_warnings='enable:all', see here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams166.htm#REFRN10249
0
 
iamtechnicalAuthor Commented:
create or replace function xxcra_apb_emp_br2 (employee_number in varchar2, project_name in varchar2)
return number
       is
/******************************************************************************
   NAME:       xxcra_apb_emp_br
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        3/14/2011          1. AT&T Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     xxcra_apb_emp_br
      Sysdate:         3/14/2011
      Date and Time:   3/14/2011, 11:16:02 AM, and 3/14/2011 11:16:02 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
Description: Fetch Bill-rate for CWK workers associated with Projects in AP Batch Edit Project
******************************************************************************/
          v_bill_rate     number;
--          v_bill_rate1    number;
  --        v_bill_rate2    number;  
    begin
    dbms_output.put_line('v_bill_rate is');    
     
         select distinct pbr2.rate into v_bill_rate
              from  pa_emp_bill_rate_overrides pbr2,
              ap_invoice_lines_all aia,
              per_people_x   ppf,
              pa_projects_all ppa
              where pbr2.project_id = aia.project_id
              and ppa.project_id = aia.project_id
              and aia.attribute1 = ppf.npw_number
              and ppf.person_id = pbr2.person_id
              and aia.attribute1 is not null  
              and ppf.npw_number = employee_number
              and ppa.name = project_name;
           
            dbms_output.put_line('executed first query '|| v_bill_rate);  
     
        IF v_bill_rate IS not NULL THEN
       
                    RETURN V_BILL_RATE;
                   
        else
            select distinct pbr3.rate into v_bill_rate
              from  pa_job_bill_rate_overrides pbr3,
              ap_invoice_lines_all aia,
              per_people_f   ppf,
              pa_projects_all ppa,
              per_all_assignments_f paf
              where pbr3.project_id = aia.project_id
              and ppa.project_id = aia.project_id
              and aia.attribute1 = ppf.npw_number
              and paf.job_id = pbr3.job_id
              and ppf.person_id = paf.person_id
              and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
              and paf.primary_flag='Y'
              and aia.attribute1 is not null
              and ppf.npw_number = employee_number
              and ppa.name = project_name;
             
              IF v_bill_rate IS not NULL THEN
       
                    RETURN V_BILL_RATE;
             
            else
                select distinct pbr.rate into v_bill_rate
                from pa_projects_all ppa,
                pa_tasks pta,
                ap_invoice_lines_all aia,
                per_people_x ppf,
                pa_bill_rates_all pbr
                where ppa.project_id = pta.project_id
                and ppa.project_id = aia.project_id
                and pta.task_id = aia.task_id
            and ppf.npw_number = aia.attribute1
            and ppf.person_id = pbr.person_id
            and pbr.org_id = aia.org_id
            and ppf.npw_number = employee_number
            and ppa.name =  project_name
        union
        select distinct pbr.rate --into v_bill_rate2
            from pa_projects_all ppa,
            pa_tasks pta,
            ap_invoice_lines_all aia,
            per_all_assignments_f ppf,
            per_people_x papf,
            pa_bill_rates_all pbr
            where ppa.project_id = pta.project_id
            and ppa.project_id = aia.project_id
            and pta.task_id = aia.task_id
            and papf.npw_number = aia.attribute1
            and ppf.job_id = pbr.job_id
            and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
            and ppf.primary_flag='Y'
            and papf.person_id = ppf.person_id
            and aia.org_id = pbr.org_id
            and papf.npw_number = employee_number
            and ppa.name =  project_name;
                RETURN V_BILL_RATE;
       
                   
            END if;
           
         end if;
       
exception
            when others then
           dbms_output.put_line('error1 '||sqlerrm);
end xxcra_apb_emp_br2;
 /


modified little bit but getting same error again
0
 
iamtechnicalAuthor Commented:
@SDSTUBER

select distinct pbr2.rate , ppa.name, aia.attribute1
              from  pa_emp_bill_rate_overrides pbr2,
              ap_invoice_lines_all aia,
              per_people_x   ppf,
              pa_projects_all ppa
              where pbr2.project_id = aia.project_id
              and ppa.project_id = aia.project_id
              and aia.attribute1 = ppf.npw_number
              and ppf.person_id = pbr2.person_id
              and aia.attribute1 is not null  
              and ppf.npw_number = 'C06423'
              and ppa.name = 'K16060'

this is what a i tried


0
 
sdstuberCommented:
and what was the result of running that query?

how did you invoke your function?

something like this?

select  xxcra_apb_emp_br2 ('C06423', 'K16060')  from dual;

what does it return?



0
 
iamtechnicalAuthor Commented:
error
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APPS.XXCRA_APB_EMP_BR2", line 114

dbms output is

v_bill_rate is
error1 ORA-01403: no data found
0
 
AkenathonCommented:
You have now erased the exception blocks... so the first SELECT that doesn't find anything rightfully raises the 1403.

The 6503 just menas that ALL your SELECTs returned NULL, so the returns (which are now contained within the IFs) never get executed. Why don't you unconditionally return NULL at the bottom of your function when you are not finding anything? Or else, put the last return OUTSIDE the IFs...
0
 
sdstuberCommented:
that's your problem...

one of your queries is returning no rows

which then raises a no_data_found exception
your code captures the exception but then doesn't reraise an exception or return a value, it simply ends which isn't allowed

figure out which query is returning no rows and either fix it so it does, or handle the exception
0
 
iamtechnicalAuthor Commented:
@sdstuber

yeah i need that , every query need not raise a output for me......

actually the thg is if i have rate from first select stmt, it shd return value there itself and exit the function
else it shd go to next select,

even though all the select stmts has output it shd return which comes first
0
 
iamtechnicalAuthor Commented:
Akenathon:

can you make it more precise
would you please modify according to your argument for me
0
 
sdstuberCommented:
if you want to check for no data found on each query, you must do so explicitly
CREATE OR REPLACE FUNCTION xxcra_apb_emp_br2(employee_number IN VARCHAR2, project_name IN VARCHAR2)
    RETURN NUMBER
IS
    /******************************************************************************
       NAME:       xxcra_apb_emp_br
       PURPOSE:

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        3/14/2011          1. AT&T Created this function.

       NOTES:

       Automatically available Auto Replace Keywords:
          Object Name:     xxcra_apb_emp_br
          Sysdate:         3/14/2011
          Date and Time:   3/14/2011, 11:16:02 AM, and 3/14/2011 11:16:02 AM
          Username:         (set in TOAD Options, Procedure Editor)
          Table Name:       (set in the "New PL/SQL Object" dialog)
    Description: Fetch Bill-rate for CWK workers associated with Projects in AP Batch Edit Project
    ******************************************************************************/
    v_bill_rate   NUMBER;
--          v_bill_rate1    number;
--        v_bill_rate2    number;
BEGIN
    DBMS_OUTPUT.put_line('v_bill_rate is');

    BEGIN
        SELECT DISTINCT pbr2.rate
          INTO v_bill_rate
          FROM pa_emp_bill_rate_overrides pbr2,
               ap_invoice_lines_all aia,
               per_people_x ppf,
               pa_projects_all ppa
         WHERE     pbr2.project_id = aia.project_id
               AND ppa.project_id = aia.project_id
               AND aia.attribute1 = ppf.npw_number
               AND ppf.person_id = pbr2.person_id
               AND aia.attribute1 IS NOT NULL
               AND ppf.npw_number = employee_number
               AND ppa.name = project_name;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            v_bill_rate  := NULL;
    END;

    DBMS_OUTPUT.put_line('executed first query ' || v_bill_rate);

    IF v_bill_rate IS NOT NULL
    THEN
        RETURN v_bill_rate;
    ELSE
        BEGIN
            SELECT DISTINCT pbr3.rate
              INTO v_bill_rate
              FROM pa_job_bill_rate_overrides pbr3,
                   ap_invoice_lines_all aia,
                   per_people_f ppf,
                   pa_projects_all ppa,
                   per_all_assignments_f paf
             WHERE     pbr3.project_id = aia.project_id
                   AND ppa.project_id = aia.project_id
                   AND aia.attribute1 = ppf.npw_number
                   AND paf.job_id = pbr3.job_id
                   AND ppf.person_id = paf.person_id
                   AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
                   AND paf.primary_flag = 'Y'
                   AND aia.attribute1 IS NOT NULL
                   AND ppf.npw_number = employee_number
                   AND ppa.name = project_name;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_bill_rate  := NULL;
        END;

        IF v_bill_rate IS NOT NULL
        THEN
            RETURN v_bill_rate;
        ELSE
            BEGIN
                SELECT DISTINCT pbr.rate
                  INTO v_bill_rate
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_people_x ppf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND ppf.npw_number = aia.attribute1
                       AND ppf.person_id = pbr.person_id
                       AND pbr.org_id = aia.org_id
                       AND ppf.npw_number = employee_number
                       AND ppa.name = project_name
                UNION
                SELECT DISTINCT pbr.rate                                         --into v_bill_rate2
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_all_assignments_f ppf,
                       per_people_x papf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND papf.npw_number = aia.attribute1
                       AND ppf.job_id = pbr.job_id
                       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
                                              AND ppf.effective_end_date
                       AND ppf.primary_flag = 'Y'
                       AND papf.person_id = ppf.person_id
                       AND aia.org_id = pbr.org_id
                       AND papf.npw_number = employee_number
                       AND ppa.name = project_name;
            EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    v_bill_rate  := NULL;
            END;

            RETURN v_bill_rate;
        END IF;
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('error1 ' || SQLERRM);
END xxcra_apb_emp_br2;

Open in new window

0
 
AkenathonCommented:
He was capturing all exceptions in the very first version, but he then removed all exception blocks. All that was really needed was to move the return statements inside the IFs which check for NULL return values...
0
 
iamtechnicalAuthor Commented:
good one , but still have doubts
0
 
sdstuberCommented:
please don't close questions with penalty grades unless you're not getting feedback from the participants

0
 
iamtechnicalAuthor Commented:
@sdstuber

CREATE OR REPLACE FUNCTION xxcra_apb_emp_br3(employee_number IN VARCHAR2, project_name IN VARCHAR2)
    RETURN NUMBER
IS
    /******************************************************************************
       NAME:       xxcra_apb_emp_br
       PURPOSE:

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        3/14/2011          1. AT&T Created this function.

       NOTES:

       Automatically available Auto Replace Keywords:
          Object Name:     xxcra_apb_emp_br
          Sysdate:         3/14/2011
          Date and Time:   3/14/2011, 11:16:02 AM, and 3/14/2011 11:16:02 AM
          Username:         (set in TOAD Options, Procedure Editor)
          Table Name:       (set in the "New PL/SQL Object" dialog)
    Description: Fetch Bill-rate for CWK workers associated with Projects in AP Batch Edit Project
    ******************************************************************************/
    v_bill_rate   NUMBER:=9999;
--          v_bill_rate1    number;
--        v_bill_rate2    number;
BEGIN
    DBMS_OUTPUT.put_line('v_bill_rate is');

    BEGIN
    if (employee_number is not null) then
        SELECT DISTINCT nvl(pbr2.rate ,888) into v_bill_rate
          --INTO v_bill_rate
          FROM pa_emp_bill_rate_overrides pbr2,
               ap_invoice_lines_all aia,
               per_people_x ppf,
               pa_projects_all ppa
         WHERE     pbr2.project_id = aia.project_id
               AND ppa.project_id = aia.project_id
               AND aia.attribute1 = ppf.npw_number
               AND ppf.person_id = pbr2.person_id
               AND aia.attribute1 IS NOT NULL
               AND ppf.npw_number = employee_number
               AND ppa.name = project_name;
               
       DBMS_OUTPUT.put_line('1. executed first query ' || v_bill_rate);        
    end if;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
        DBMS_OUTPUT.put_line('2. executed first query ' || v_bill_rate);
            v_bill_rate  := NULL;
   
    END;

    DBMS_OUTPUT.put_line('executed first query ' || v_bill_rate);

    IF v_bill_rate IS NOT NULL
    THEN
        RETURN v_bill_rate;
    ELSE
        BEGIN
            SELECT DISTINCT pbr3.rate
              INTO v_bill_rate
              FROM pa_job_bill_rate_overrides pbr3,
                   ap_invoice_lines_all aia,
                   per_people_f ppf,
                   pa_projects_all ppa,
                   per_all_assignments_f paf
             WHERE     pbr3.project_id = aia.project_id
                   AND ppa.project_id = aia.project_id
                   AND aia.attribute1 = ppf.npw_number
                   AND paf.job_id = pbr3.job_id
                   AND ppf.person_id = paf.person_id
                   AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
                   AND paf.primary_flag = 'Y'
                   AND aia.attribute1 IS NOT NULL
                   AND ppf.npw_number = employee_number
                   AND ppa.name = project_name;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_bill_rate  := NULL;
        END;
       
        dBMS_OUTPUT.put_line('executed second query ' || v_bill_rate);

        IF v_bill_rate IS NOT NULL
        THEN
            RETURN v_bill_rate;
        ELSE
            BEGIN
                SELECT DISTINCT pbr.rate
                  INTO v_bill_rate
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_people_x ppf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND ppf.npw_number = aia.attribute1
                       AND ppf.person_id = pbr.person_id
                       AND pbr.org_id = aia.org_id
                       AND ppf.npw_number = employee_number
                       AND ppa.name = project_name
                UNION
                SELECT DISTINCT pbr.rate                                         --into v_bill_rate2
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_all_assignments_f ppf,
                       per_people_x papf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND papf.npw_number = aia.attribute1
                       AND ppf.job_id = pbr.job_id
                       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
                                              AND ppf.effective_end_date
                       AND ppf.primary_flag = 'Y'
                       AND papf.person_id = ppf.person_id
                       AND aia.org_id = pbr.org_id
                       AND papf.npw_number = employee_number
                       AND ppa.name = project_name;
            EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    v_bill_rate  := NULL;
                END;
                dBMS_OUTPUT.put_line('executed third query ' || v_bill_rate);
            RETURN v_bill_rate;
        END IF;
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('error1 ' || SQLERRM);
END xxcra_apb_emp_br3;


Made some changes to your code and executed the function
this is o/p i am getting

"v_bill_rate is
2. executed first query 9999
executed first query
executed second query
executed third query"

u can see their its directly going to dbms line ...without executing the first select query

help me out

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now