Re: Returning data from temp_tables

From: sybrandb <sybrandb_at_gmail.com>
Date: Mon, 06 Aug 2007 07:39:37 -0700
Message-ID: <1186411177.758123.66760@w3g2000hsg.googlegroups.com>

On Aug 6, 2:54 pm, BGT <bgt0..._at_optonline.net> wrote:
> I am reading as carefully as possible. Possibly there is something
> wrong with the package definition but I still get an error trying to
> open this NESTED cursor the way you say should work.
>
> CREATE OR REPLACE PACKAGE PKG_DASHBOARD AS
>
> TYPE ref_cur is ref cursor RETURN result_master%ROWTYPE; --
> won't take SYS_REFCURSOR
> Procedure list_mailable(MAILLIST OUT ref_cur);
>
> END PKG_DASHBOARD;
> /
>
> CREATE OR REPLACE PACKAGE BODY PKG_DASHBOARD AS
> PROCEDURE list_mailable(maillist OUT ref_cur) IS
>
> OPEN maillist FOR 'SELECT * FROM result_master A WHERE labno = :1
> and a.labno in(select labno from sample_demog_master where link
> in(select link
> from sample_demog_master where labno=link))' USING c1rec;
> LOOP
> FETCH maillist INTO c3rec;
> EXIT WHEN maillist%NOTFOUND;
>
> PLS-00455: cursor 'MAILLIST' cannot be used in dynamic SQL OPEN
> statement

OK,
Let me spell it out for you, for once.
You are asking for it, as you don't seem to read my responses.

The ref_cur type is redundant. You don't need it. Sys_refcursor was invented to make this and similar types redundant.

Also you can't mix *weakly typed* cursors (all ref cursors) and strongly typed cursors.

Your procedure returns a ref cursor. In your procedure you DON'T Fetch from this cursor, this is the responsibility of the caller.

Bind variables are *ordinary* variables. You may put them in a record, but you just can't use the entire record.

You are getting PLS-0455 *because* dynamic sql requires a *weakly typed* cursor

Your procedure should have been
 CREATE OR REPLACE PACKAGE BODY PKG_DASHBOARD AS  PROCEDURE list_mailable(maillist OUT sys_refcursor) IS  p_labno number(10);
 begin

    OPEN maillist
    FOR 'SELECT * FROM result_master A WHERE labno = :1

             and a.labno in(select labno from sample_demog_master where link

              in (select link
                   from  sample_demog_master where

labno=link))'

    USING p_labno; -- p_labno would need to be initialized first   end;
  end;
/

Rest assured I won't respond to you anymore. I have gone at length to explain it to you, but I'm not getting any message through. I'm not sure why you keep coming up with erroneous code. I feel like you don't read my replies at all, and/or you don't reproduce them in your system, to verify they actually work.
Consequently I am just wasting my time.

--
Sybrand Bakker
Senior Oracle DBA

Received on Mon Aug 06 2007 - 09:39:37 CDT