Home » SQL & PL/SQL » SQL & PL/SQL » How to pass sql statement between procedure? (EBS r12, windows xp,)
|
|
Re: How to pass sql statement between procedure? [message #427148 is a reply to message #427144] |
Wed, 21 October 2009 05:52   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
You COULD use dynamic SQL (Execute Immediate)
But that should be avoided like hell, because it causes all kinds of performance, debugging and security (SQL Injection) problems so it generally crates more problem than it solves, especially for beginners.
The better solution would be to re-write the query in a way so that it works with defined parameters (possibly by using CASE statements in the where clause)
|
|
|
|
|
|
|
|
Re: How to pass sql statement between procedure? [message #427306 is a reply to message #427144] |
Wed, 21 October 2009 22:47   |
myura
Messages: 66 Registered: July 2007 Location: Malaysia
|
Member |
 
|
|
Here is my procedure:
PROCEDURE get_data ( P_per_id number, P_ler_id number, P_plan_typ_id number, P_opt_id number, dpdt_ind number ) IS
CURSOR c_header IS
SELECT 'EMPLOYEE IC' EMPLOYEE_IC, 'EMPLOYEE NAME (AS IN IC)' EMPLOYEE_NAME, 'DEPENDENT NAME (AS IN IC)' DEPENDENT_NAME
,'STAFF ID' STAFF_ID, 'RELATIONSHIP' RELATIONSHIP, 'DOB' DOB,'SEX' SEX, 'CHANGE IN PLAN' CHANGE_IN_PLAN, 'EFFECTIVE DATE' EFFECTIVE_DATE
,'CATEGORY' CATEGORY, 'HS PLAN' HS_PLAN,'DEPARTMENT' DEPARTMENT, 'BRANCH' BRANCH, 'REMARKS' REMARKS
FROM DUAL;
CURSOR c_body is
select a.national_identifier EMPLOYEE_IC
,a.last_name EMPLOYEE_NAME
,b.last_name DEPENDENT_NAME
,a.employee_number STAFF_ID
,b.sex SEX
,b.national_identifier dpdt_ic
,b.date_of_birth DOB
,d.meaning RELATIONSHIP
,c.contact_type contact_type
,decode(a.PERSON_TYPE_ID,1120,'Permanent',1125,'Contract') HS_PLAN
,f.location_code BRANCH
,g.name DEPARTMENT
from
per_all_people_f a,
per_all_people_f b,
per_contact_relationships c,
hr_lookups d,
per_assignments_f e,
hr_locations_all f,
HR_ALL_ORGANIZATION_units g
where
a.person_id = c.person_id
and c.contact_person_id = b.person_id
AND d.lookup_code = c.contact_type
AND a.business_group_id = c.business_group_id
AND Trunc(sysdate) BETWEEN b.effective_start_date AND b.effective_end_date
AND Trunc(sysdate) BETWEEN a.effective_start_date AND a.effective_end_date
and a.business_group_id = 101
AND d.lookup_type = 'CONTACT'
and a.person_id = P_per_id
and e.assignment_id = a.employee_number
AND Trunc(sysdate) BETWEEN e.effective_start_date AND e.effective_end_date
and e.location_id = f.location_id
and e.organization_id = g.organization_id
and b.person_id in (
CASE
WHEN dpdt_ind = 0 THEN (select dpnt_person_id
from ben_elig_dpnt
where trunc(sysdate) BETWEEN elig_strt_dt AND elig_thru_dt
and per_in_ler_id = P_ler_id
and ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
from Ben_elig_per_elctbl_chc
where per_in_ler_id = P_ler_id
and pl_typ_id = P_plan_typ_id
and oipl_id = P_opt_id
)
)
WHEN dpdt_ind = 1 THEN
(select dpnt_person_id from ben_elig_cvrd_dpnt_f where per_in_ler_id = P_ler_id)
END
)
order by decode(c.contact_type,'S',1,'C1',2,'C2',3,'T1',4,'T2',5,'A1',6,'A2',7);
BEGIN
--dbms_output.put_line(' '||P_select);
FOR r_header IN c_header LOOP
dbms_output.put_line(
RPad(r_header.EMPLOYEE_IC,15,' ')||'|'||RPad(r_header.EMPLOYEE_NAME,75,' ')||'|'||RPad(r_header.DEPENDENT_NAME,75,' ')
||'|'||RPad(r_header.STAFF_ID,30,' ')||'|'||RPad(r_header.RELATIONSHIP,15,' ')||'|'||RPad(r_header.SEX,15,' ')
||'|'||RPad(r_header.relationship,30,' '));
end loop;
for r_body in c_body loop
dbms_output.put_line(
RPad(r_body.EMPLOYEE_IC,15,' ')||'|'||RPad(r_body.EMPLOYEE_NAME,75,' ')||'|'||RPad(r_body.DEPENDENT_NAME,75,' ')
||'|'||RPad(r_body.STAFF_ID,30,' ')||'|'||RPad(r_body.RELATIONSHIP,15,' ')||'|'||RPad(r_body.SEX,15,' ')
||'|'||RPad(r_body.relationship,30,' '));
end loop;
END get_data;
And below is the error message:
Connecting to the database mas-dv2.
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "APPS.MASBEN_GHS_ENRL_RPT_PKG", line 226
ORA-06512: at line 14
EMPLOYEE IC |EMPLOYEE NAME (AS IN IC) |DEPENDENT NAME (AS IN IC) |STAFF ID |RELATIONSHIP |SEX |RELATIONSHIP
Process exited.
Disconnecting from the database mas-dv2.
|
|
|
Re: How to pass sql statement between procedure? [message #427342 is a reply to message #427306] |
Thu, 22 October 2009 02:43   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The error that you've posted did not happen in the procedure that you've posted.
Nothing in that procedure will return a Too Many Rows error.
You need to look in the package APPS.MASBEN_GHS_ENRL_RPT_PKG at somewhere round line 226.
|
|
|
|
Re: How to pass sql statement between procedure? [message #427352 is a reply to message #427349] |
Thu, 22 October 2009 03:20   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I stand corrected.
It looks like CASE will only return a single value, rather than a result set (which seems reasonable).
You could rewrite the case like this:and ((dpdt_ind = 0 and b.person_id in (select dpnt_person_id
from ben_elig_dpnt
where trunc(sysdate) BETWEEN elig_strt_dt AND elig_thru_dt
and per_in_ler_id = P_ler_id
and ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
from Ben_elig_per_elctbl_chc
where per_in_ler_id = P_ler_id
and pl_typ_id = P_plan_typ_id
and oipl_id = P_opt_id
)
))
OR dpdt_ind = 1 and b_person_id in (select dpnt_person_id from ben_elig_cvrd_dpnt_f where per_in_ler_id = P_ler_id))
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:13:17 CST 2025
|