Laura,
Here's
one. In this case, if the in variable 'p_actualenddate' is passed into the
procedure, and additional 'and' clause is added to the where clause. Any
questions, give me a shout.
PROCEDURE
ListScheduleforDelete(
p_employid IN
VARCHAR2,
p_status IN
WTW_EMPL_WEEKENDING_SCHEDULE.SCHED_STATUS_CODE%TYPE,
p_actualenddate IN
WTW_EMPLOYMENT.EMPLOYMENT_START_DATE%TYPE,
p_refCursor IN
OUT empRS
)
IS
sel_string VARCHAR2(2000)
:=
' SELECT
WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_ID, ' ||
'
WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_WEEKEND_DATE,
'
||
'
WTW_EMPL_WEEKENDING_SCHEDULE.TOTAL_ACTUAL_HRS_NBR, ' ||
'
WTW_EMPL_SCHED_STATUS_CODE.SCHED_LONG_TXT, ' ||
'
WTW_EMPL_WEEKENDING_SCHEDULE.TOTAL_SCHED_HRS_NBR ' ||
' FROM
WTW_EMPL_WEEKENDING_SCHEDULE, WTW_EMPL_SCHED_STATUS_CODE ' ||
' WHERE
WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_ID = :p_employid AND
'
||
'
WTW_EMPL_WEEKENDING_SCHEDULE.SCHED_STATUS_CODE = :p_status AND
'
||
'
WTW_EMPL_SCHED_STATUS_CODE.SCHED_STATUS_CODE(+) =
WTW_EMPL_WEEKENDING_SCHEDULE.SCHED_STATUS_CODE ';
where_string VARCHAR2(400);
where_str VARCHAR2(7)
:= ' AND
';
ActualDatePlusSeven DATE;
BEGIN
IF p_actualenddate IS NOT NULL
THEN
ActualDatePlusSeven
:= p_actualenddate + 7;
where_string
:= where_str || '
WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_WEEKEND_DATE >= '
||
'''' || ActualDatePlusSeven || '''' || ' ';
END
IF;
OPEN
p_refCursor FOR
sel_string ||
where_string
||
' ORDER BY 2 DESC' USING p_employid, p_status;
END ListScheduleforDelete;
--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 4:57 PM
To: Multiple recipients of list ORACLE-L
Subject: Dynamic SQLWe have Oracle version 8.0.5 and need to use dynamic sql. Through research I know that there is a dbms_sql package that is suppose to support this, but we cannot find an example of what we are needing to do. We have been told that we can do it easily in '8i' but we are not able to upgrade yet.
We are trying to populate a reference cursor via a procedure with a select statement. Has anyone done this and if so can you furnish an example? I may need to tell more about what we are doing and if so please tell me.
Thank you,
Laura
