Re: Oracle 8 Pro*C cursor problem
Date: Fri, 16 Mar 2001 09:49:03 -0500
Message-ID: <markh-488877.09490316032001_at_news.xetron.com>
[Quoted] In article <9jhs6.168550$bb.1620278_at_news1.rdc1.tx.home.com>, "Heath & Sharmelle Wickman" <hswick_at_home.com> wrote:
>You don't need to declare your cursor in your oracle declare section. Try
>something like the following. This works on v.8.0.6
>You also don't need to declare your host variables in the oracle declare
>section; I'm not sure which version changed that.
>
>EXEC SQL DECLARE orders_tms_cursor CURSOR FOR
> SELECT ord_id,
> min(ord_tote_tms) "firstorder"
> FROM ord_tote
> WHERE ord_tote_stat_cd IN (:or_szTote_Stat_Onway,
> :or_szTote_Stat_Recirc)
> AND ord_id NOT in
> (SELECT DISTINCT ord_id FROM pack_lane_ord WHERE ord_id IS NOT NULL)
> GROUP BY ord_id
> ORDER BY "firstorder";
>
> EXEC SQL OPEN orders_tms_cursor;
>
> EXEC SQL FETCH orders_tms_cursor
> INTO :or_ord_tote.ord_id;
>
>EXEC SQL CLOSE orders_tms_cursor;
>
Thanks, but that isn't the kind of cursor I'm trying to use. I think this defines a static cursor, which can only be used in that one function. I've used cursors this way, but they don't exist outside of the scope of the function that opened it.
I need a cursor that is persistant for the life of the program, can be passed as a parameter to other functions, etc.
Declaring a cursor as a SQL_CURSOR like this:
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR emp_cursor;
int dept_num;
EXEC SQL END DECLARE SECTION;
is supposed to create a cursor variable that can do this. I don't get
any compiler errors on the declare section, just in the section where it
is opened:
EXEC SQL EXECUTE
BEGIN
OPEN :emp_cursor FOR SELECT ENAME FROM emp
WHERE deptno = :dept_num;
END;
END_EXEC; According to the oracle docs, these cursors must be opened in a PL/SQL block, which is why I did it there.
Why does it think emp (the table name) is an undeclared identifier? It has no problem with ENAME, which is just a column name. Received on Fri Mar 16 2001 - 15:49:03 CET