Home » SQL & PL/SQL » SQL & PL/SQL » ref cursor restrictions
ref cursor restrictions [message #232206] Thu, 19 April 2007 15:45 Go to next message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
Having trouble using LAG/LEAD functions in a ref cursor definition. Finding out that even a simple CASE statement is giving me trouble -- had to resort back to DECODE. Are there restrictions on what PL/SQL functions/statements can be used in a ref cursor? The same exact PL/SQL code will work fine as a stand-alone query.
Re: ref cursor restrictions [message #232209 is a reply to message #232206] Thu, 19 April 2007 15:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you use Oracle 8i which does not support CASE in PL/SQL but support it in SQL.
Just upgrade to a newer release.

Regards
Michel
Re: ref cursor restrictions [message #232211 is a reply to message #232209] Thu, 19 April 2007 16:03 Go to previous messageGo to next message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
The same exact SELECT statement works fine as a stand-alone query though. Just can't use these functions in my ref cursor definition. Using 9i.
Re: ref cursor restrictions [message #232212 is a reply to message #232206] Thu, 19 April 2007 16:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
please use CUT & PASTE so we can see exactly what you are doing & how Oracle responds.
How/why do you expect us to guess at a solution when we have NO actual clue about what is & is not happening?

What Oracle error message & code are you seeing?

[Updated on: Thu, 19 April 2007 16:14] by Moderator

Report message to a moderator

Re: ref cursor restrictions [message #232216 is a reply to message #232212] Thu, 19 April 2007 16:15 Go to previous messageGo to next message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
Well EXCUSE ME for having such expectations! My question was simply 'Any restrictions that anyone was aware of on using the LEAD/LAG in a ref cursor -- or CASE statement, for that matter?'
I would gladly post my code here if someone didn't have a quick 'Oh yeah -- you can't do that' or 'Oh you have to do it this way' answer. We weren't there yet. SORRY!
Wow! I just joined!
Re: ref cursor restrictions [message #232217 is a reply to message #232216] Thu, 19 April 2007 16:19 Go to previous messageGo to next message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
CREATE OR REPLACE PACKAGE PKG_CE_NMHC4312 AS
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
TYPE T_REC_NMHC4312 IS RECORD(
--FROM_DATE NUMBER,
--TO_DATE NUMBER,
ORDER_ID NUMBER,
ORDER_DATE NUMBER,
RELEASE_DATE NUMBER,
SHIPMENT_ID NUMBER,
SHIP_DATE NUMBER,
PROD_AREA VARCHAR2(30),
PROD_AREA_DATE NUMBER,
PROD_AREA_DATESTAMP DATE,
CUSTOMER_ID VARCHAR2(30),
CLIENT_ID VARCHAR2(30),
CLIENT_GROUP_ID VARCHAR2(30),
DAYS_IN_AREA NUMBER,
STATUS VARCHAR2(1)
);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
TYPE T_REF_CURSOR IS REF CURSOR RETURN T_REC_NMHC4312;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

PROCEDURE MOSR(
CUR_IREPORT IN OUT T_REF_CURSOR,
i_USER_ID IN VARCHAR2,
i_CCG_LIST IN VARCHAR2,
i_GROUPING IN VARCHAR2,
i_FROM_DATE IN NUMBER,
i_TO_DATE IN NUMBER,
i_PERIODICITY IN VARCHAR2,
i_PERIOD_COUNT IN NUMBER,
i_REPORT_NUMBER IN NUMBER
);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
END PKG_CE_NMHC4312;
/

CREATE OR REPLACE PACKAGE BODY PKG_CE_4312 AS
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
PROCEDURE MOSR(
CUR_IREPORT IN OUT T_REF_CURSOR,
i_USER_ID IN VARCHAR2,
i_CCG_LIST IN VARCHAR2,
i_GROUPING IN VARCHAR2,
i_FROM_DATE IN NUMBER,
i_TO_DATE IN NUMBER,
i_PERIODICITY IN VARCHAR2,
i_PERIOD_COUNT IN NUMBER,
i_REPORT_NUMBER IN NUMBER )

IS
v_GROUPING VARCHAR2(25):= 'BB';
v_BB_RETVAL NUMBER := 0; --BOOK OF BUSINESS.
v_CCG_ID NUMBER := 0;
v_REPORT_FROM NUMBER := i_FROM_DATE;
v_REPORT_TO NUMBER := i_TO_DATE;
v_CALL_COUNT NUMBER := 0;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --


BEGIN


OPEN CUR_IREPORT
FOR

Select --v_REPORT_FROM as From_Date, v_REPORT_TO as To_Date,
b.order_id, b.order_date, b.release_date, b.shipment_id, b.ship_date, b.prod_area,
b.prod_area_date, b.prod_area_datestamp, b.customer_id, b.client_id, b.client_group_id,
case when b.shipment_id = b.lead_shipment_id then
fn_count_weekdays (b.prod_area_date, b.lead_prod_area_date)
else null end as days_in_area,
c.status
From

(
select oi.order_id, oi.shipment_id, oi.order_date, oi.release_date, oi.ship_date, oi.prod_area_datestamp,
oi.prod_area, oi.prod_area_date, oi.customer_id, oi.client_id, oi.client_group_id,
lead(oi.shipment_id) over (order by oi.shipment_id, oi.prod_area_datestamp) as lead_shipment_id,
lead(oi.prod_area_date) over (order by oi.shipment_id, oi.prod_area_datestamp) as lead_prod_area_date
from hc_vw_order_prod_area_ta@abc.world oi
where oi.customer_id = 'XXXXX'
and oi.ship_date between 20070125 and 20070131
order by oi.order_id, oi.shipment_id, oi.prod_area_datestamp
) b,

(
select distinct oi.order_id, FN_SHIPMENT_STATUS(oi.order_id) as status
from hc_vw_order_prod_area_ta@abc.world oi
where oi.customer_id = 'XXXXX'
and oi.ship_date between 20070125 and 20070131
group by oi.order_id
) c
where b.order_id = c.order_id;



END;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
END PKG_CE_4312;
/

Re: ref cursor restrictions [message #232218 is a reply to message #232217] Thu, 19 April 2007 16:21 Go to previous messageGo to next message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
PACKAGE BODY INFO.PKG_CE_4312
On line: 34
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current
Re: ref cursor restrictions [message #232219 is a reply to message #232218] Thu, 19 April 2007 16:23 Go to previous messageGo to next message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
Package spec and body name do match here. Sorry for the editing screwup.
Re: ref cursor restrictions [message #232220 is a reply to message #232206] Thu, 19 April 2007 16:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
1) please use "code tags" to make the code readable as described in the #1 Sticky post
http://www.orafaq.com/forum/t/42428/74940/
2) please post SQL*Plus session showing "The same exact SELECT statement works fine as a stand-alone query"; also using code tags.
3) which is line #34

[Updated on: Thu, 19 April 2007 16:29] by Moderator

Report message to a moderator

Re: ref cursor restrictions [message #232247 is a reply to message #232220] Thu, 19 April 2007 22:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are using remote tables. Are the remote tables on 9i as well? Or 8i?

Ask you DBA whether any initialisation variables might be set that are forcing 8i functionality.

Ross Leishman
Re: ref cursor restrictions [message #232265 is a reply to message #232219] Thu, 19 April 2007 23:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what Ana said.
The only CASE you have in your code is several lines before any lead or lag function. Why do you think there is any relation between this case and this function?

Regards
Michel
Re: ref cursor restrictions [message #232421 is a reply to message #232265] Fri, 20 April 2007 08:02 Go to previous message
filmr
Messages: 7
Registered: April 2007
Location: Albany, NY
Junior Member
Turns out this development server is about the only server left in the company that hasn't been upgraded to 9i. Still running 8i. I never even thought to check when the CASE statement and LEAD/LAG functions worked in my stand-alone query. I should never assume anything Sad.
But thanks for all the quick responses to my post. Next time, I'll get all my facts straight prior to posting.
Previous Topic: ORA-29280: Invalid directory path
Next Topic: Job Scheduling Made easy in 10g - DBMS_SCHEDULER -- DBMS_JOB++++++++
Goto Forum:
  


Current Time: Fri Dec 09 13:48:39 CST 2016

Total time taken to generate the page: 0.09775 seconds