Re: Cursor Ref Issue: What am I missing?

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Thu, 30 Sep 2010 07:26:48 -0700 (PDT)
Message-ID: <8b6b1320-c6b1-4168-b41a-808b7ec7093a_at_j24g2000yqa.googlegroups.com>

On Sep 30, 9:56 am, KevinS <sear..._at_googlemail.com> wrote:
> Hi all,
>
> The coded below sets up and hopefully demonstrates an issue I am
> having.
>
> The line 'OPEN v_my_cur;' in tha package body produces the error
> 'Expresssion is of wrong type.
>
> The database is Oracle Database 10g Enterprise Edition Release
> 10.2.0.3.0 - 64bit.
>
> Any ideas what's I've missed?
>
> thanks
>
> CREATE TABLE my_table(a VARCHAR2(1),b VARCHAR2(1));
>
> INSERT INTO my_table(a,b) VALUES('a','b');
>
> CREATE OR REPLACE PACKAGE MY_REF_CURSOR_PKG AS
>
>   PROCEDURE MY_prc;
>
> END MY_REF_CURSOR_PKG;
>
> CREATE OR REPLACE PACKAGE BODY MY_REF_CURSOR_PKG
> AS
>
>   TYPE my_rec IS RECORD(
>        a         my_table.b%TYPE,
>        b         my_table.b%TYPE);
>   TYPE my_cur IS REF CURSOR RETURN my_rec;
>
>   Function get_data_fnc
>   RETURN my_cur
>   IS
>     my_rc my_cur;
>   BEGIN
>
>     OPEN my_rc
>     FOR
>     SELECT t.a, t.b
>     FROM my_table t;
>
>     RETURN my_rc;
>
>   END get_data_fnc;
>
>   PROCEDURE my_fnc
>   IS
>
>     v_my_cur  my_cur;
>
>   BEGIN
>
>     v_my_cur := get_data_fnc;
>
>     OPEN v_my_cur;
>
>     LOOP
>     FETCH v_my_cur INTO r_failed_wo;
>     EXIT WHEN v_my_cur%NOTFOUND;
>     END LOOP;
>
>   END my_fnc;
>
> END MY_REF_CURSOR_PKG;

>>OPEN v_my_cur; <<

Isn't v_my_cur already open inside function get_data_fnc? Thomas Received on Thu Sep 30 2010 - 09:26:48 CDT