Home » SQL & PL/SQL » SQL & PL/SQL » How to pass sql statement between procedure? (EBS r12, windows xp,)
How to pass sql statement between procedure? [message #427144] Wed, 21 October 2009 05:44 Go to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

Good day all,

I have a question here.

I need to pass a parameter between procedures. The parameter type is varchar which contains a condition of select statement (is a where caluse).

For example:

procedure xxx is

x_select varchar2(2000);

Begin

if id = 0 then
x_select := 'and a.per_id = b.per_id'
else
x_select := 'and a.per_id = c.per_id'
end if;

grab_data(x_select);

end xxx;

procedure grab_data(x_select varchar2) is

Begin
select * from a, b, c
where dept = 'D'
||x_select;

end grab_data;


This is how i done in my package.
FYI,i am beginer in pl/sql.

This package wont work because (i think) it still recognize that x_select as a varchar, not an sql statement.

How i can make it work?
Re: How to pass sql statement between procedure? [message #427146 is a reply to message #427144] Wed, 21 October 2009 05:49 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Search EXECUTE IMMEDIATE ....here
Re: How to pass sql statement between procedure? [message #427148 is a reply to message #427144] Wed, 21 October 2009 05:52 Go to previous messageGo to next message
ThomasG
Messages: 3184
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 #427149 is a reply to message #427144] Wed, 21 October 2009 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Fundamentals
Chapter 8 Coding Dynamic SQL

Regards
Michel
Re: How to pass sql statement between procedure? [message #427301 is a reply to message #427144] Wed, 21 October 2009 20:54 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

I am using CASE Expressions, since the CASE expressions looks simple and easy to understand. Here is a part of my code :
and a.person_id   =	P_per_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
);


When i run this procedure, there's error saying that 'single-row subquery returns more than one row'.. i know that the query will gives more than one id, thats why i'm using 'in' instead of '=' sign.

My question, is the CASE expressions always allow 1 value return only?
Re: How to pass sql statement between procedure? [message #427302 is a reply to message #427301] Wed, 21 October 2009 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>My question, is the CASE expressions always allow 1 value return only?
The error has nothing to do with CASE.
It has to do with issuing a SELECT within PL/SQL.
The SELECT must contain the INTO clause, because results must go someplace.
INTO <variable>

Since <variable> can hold only a single value, error is thrown when more than 1 row returned.

Re: How to pass sql statement between procedure? [message #427304 is a reply to message #427144] Wed, 21 October 2009 21:09 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

Actually, i put this statement in my cursor..it should be fine, isn't it?
Re: How to pass sql statement between procedure? [message #427305 is a reply to message #427304] Wed, 21 October 2009 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
Some/many/most folks, including me, can not debug code they can not see.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Please CUT & PASTE while using sqlplus so we can see code & error in actual context.
Re: How to pass sql statement between procedure? [message #427306 is a reply to message #427144] Wed, 21 October 2009 22:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #427349 is a reply to message #427144] Thu, 22 October 2009 03:10 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

This procedure is inside this MASBEN_GHS_ENRL_RPT_PKG package.

I only run this single procedure (with parameter included) to see the output and the error came out. When i take out this part :
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
              )


and i try to put a select statement that returns 1 row, the procedure will run smoothly without this error and will print the correct output.

When i put again the statement that returns more than 1 row, the same error came out.
Re: How to pass sql statement between procedure? [message #427352 is a reply to message #427349] Thu, 22 October 2009 03:20 Go to previous messageGo to next message
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))
Re: How to pass sql statement between procedure? [message #427360 is a reply to message #427144] Thu, 22 October 2009 03:58 Go to previous message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

I re-write the codes as advised by JRowbottom. And my package works fine.
I learn a lot from you guys. Thank you very much to all of you. Smile
Previous Topic: Please help
Next Topic: column
Goto Forum:
  


Current Time: Mon Sep 26 23:09:50 CDT 2016

Total time taken to generate the page: 0.06645 seconds