Home » SQL & PL/SQL » SQL & PL/SQL » parameter Package IF THEN Else (oracle10g)
parameter Package IF THEN Else [message #334219] Tue, 15 July 2008 22:19 Go to next message
ayubfkhan
Messages: 3
Registered: February 2007
Location: Baltimore
Junior Member
I have a package which has 5 functions.
each function will be passed 4 parameters.
condition
1. IF all four parameters are null then return everything.which I have managed it.

2. IF any one of parameter is provided then return results according to that particular field.

For example

IF p_emp_id is NOT null then
select * from employees where empid = p_emp_id;
end if;

HOW DO I SAY "GIVE ME RESULT ACCORDING TO WHATEVER PARAMETER IS GIVEN .?

Thanks
I am attaching the actual script if someone can make sense out of this.
Ayub



create or replace PACKAGE REPORTS_PKG AS

type result_set is ref cursor;

function dua_report(p_begin_date date default null
,p_end_date date default null
,p_disaster_id number default null
,p_contact_id number default null
) return result_set;

create or replace PACKAGE BODY ACSIS_REPORTS_PKG AS

function dua_report(p_begin_date date default null
,p_end_date date default null
,p_disaster_id number default null
,p_contact_id number default null
) return result_set AS
v_result_set result_set;
v_begin_date date;
v_end_date date;
v_disaster_id number;
v_contact_id number;

BEGIN
--
-- If All parameters are not provided then return everything %%%% START %%%%%
--
open v_result_set for
select distinct dst.disaster_id
--v.dcln_type_cd||'-'||v.dstr_nr||'-'||v.st_cd as DISASTER_NUMBER
-- ,c.first_name
-- ,c.last_name
-- ,d.obligation_letter_date
-- ,d.benefits_obligated_amount
-- ,d.admin_obligated_amount
-- ,d.benefits_obligated_amount + d.admin_obligated_amount total_obligated_anount
from acsis_disaster dst
,acsis_dua d
-- ,acsis_dua_contacts dc
-- ,acsis_contacts c
--,acsis_disaster_v v
where dst.disaster_id = d.disaster_id;

-- passing parameters;
-- if begin date is not null but all other parameters are null
IF p_begin_date is not null and p_end_date is null and p_disaster_id is null and p_contact_id is null
then
open v_result_set for
select dst.disaster_id
,v.dcln_type_cd||'-'||v.dstr_nr||'-'||v.st_cd as DISASTER_NUMBER
,c.first_name
,c.last_name
,d.obligation_letter_date
,d.benefits_obligated_amount
,d.admin_obligated_amount
,d.benefits_obligated_amount + d.admin_obligated_amount total_obligated_anount
from acsis_disaster dst
,acsis_dua d
,acsis_dua_contacts dc
,acsis_contacts c
,acsis_disaster_v v
where dst.disaster_id = d.disaster_id
and dc.dua_id = d.dua_id
and c.contact_id = dc.contact_id
and dst.disaster_number = v.dstr_nr
-- Passing 4 parameters....
-- and c.contact_id = p_contact_id
-- and dst.disaster_id = p_disaster_id
and d.obligation_letter_date >= p_begin_date;

END IF;
Re: parameter Package IF THEN Else [message #334220 is a reply to message #334219] Tue, 15 July 2008 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above


4*3*2 = 24 different possible combinations of input parameters

[Updated on: Tue, 15 July 2008 22:25] by Moderator

Report message to a moderator

Re: parameter Package IF THEN Else [message #334235 is a reply to message #334219] Tue, 15 July 2008 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Create your SQL statement in a string and open the cursor for it.
Of course, you have test the values that are given to the procedure to prevent from SQL injecting.

Regards
Michel
Re: parameter Package IF THEN Else [message #334364 is a reply to message #334235] Wed, 16 July 2008 05:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't know of a way to do an Injection attack on a Ref Cursor opened using bind variables like that one was.

Option 1)
Build up a string containing SQL for the query. Depending on what parameters they've passed in, construct the query according to your own rules

Option 2)
Include all the parameter clauses, in this form:
AND t.column = NVL(p_parameter,t.column)
Previous Topic: Table Joining
Next Topic: Error In quert
Goto Forum:
  


Current Time: Fri Dec 09 07:27:50 CST 2016

Total time taken to generate the page: 0.06937 seconds