Re: Invalid Cursor - why ?

On Nov 13, 1:40 pm, Georg Scholz <georgsch..._at_gmail.com> wrote:
> Hello,
>
> I am getting the error "invalid cursor" --- I have spent hours on
> this, I have no clue.
>
> I am calling a procedure from a remote database, which is linked via a
> database link.
> The procedure is declared as follows:
>
> CREATE OR REPLACE PACKAGE Pkg_Reports
> AS
> TYPE refcur IS REF CURSOR;
> PROCEDURE GetReport (rep OUT refcur, v_startDate IN DATE);
> END Pkg_Reports;
> /
>
> PROCEDURE GetReport (rep OUT refcur, v_startDate IN DATE);
> IS
> BEGIN
> OPEN rep FOR SELECT .... ;
> END;
> /
>
> I am calling the procedure as follows:
>
> CREATE OR REPLACE procedure PROC_TEST
> as
> c PKG_REPORTS.refcur_at_mylink
>
> v1 varchar2(8;
> v2 varchar2(20);
> v3 number;
> begin
> PKG_REPORTS.GerReport_at_mylink(c, sysdate);
> if c%ISOPEN THEN
> loop
> fetch c into v1, v2, c3; -- <-- RUN-TIME ERROR
> end loop;
> end if;
> end;
> /
>
> Everything is compiling fine. The cursor is open. The variables have
> correct types.
> But still I am getting a runtime error "Invalid cursor" in the line
> marked above. What am I doing wrong?
>
> We are using Oracle 9.2.0.8.
>
> Any help is highly appreciated.
>
> Thank you in advance
> Georg Scholz

First, as noted in a prior post:

         v1 varchar2(8;

should be:

         v1 varchar2(8);

Next, I notice you're trying to populate c3, not v3:

        fetch c into v1, v2, c3; -- <-- RUN-TIME ERROR

This should be:

        fetch c into v1, v2, v3;

Try those changes and see if this fixes your issues.

David Fitzjarrell Received on Tue Nov 13 2007 - 15:16:50 CST