parameter Package IF THEN Else [message #334219] |
Tue, 15 July 2008 22:19 |
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 #334364 is a reply to message #334235] |
Wed, 16 July 2008 05:29 |
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)
|
|
|