Re: REF CURSOR

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 02 Sep 1998 14:42:01 GMT
Message-ID: <35f25918.6538151@192.86.155.100>

A copy of this was sent to dvsingh1_at_my-dejanews.com (if that email address didn't require changing) On Tue, 01 Sep 1998 12:23:07 GMT, you wrote:

I reproduced your error with:

create or replace package foo_pkg
as

    function foo return varchar2;
    PRAGMA RESTRICT_REFERENCES ( foo , WNDS ); end;
/

create or replace package body foo_pkg
as
function foo return varchar2
as

    TYPE t_SearchID IS REF CURSOR RETURN emp%rowtype; BEGIN
    null;
end;
end;
/

Errors for PACKAGE BODY FOO_PKG:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PLS-00707: unsupported construct or internal error [2601]
0/0      PL/SQL: Compilation unit analysis terminated

SQL> This is bug 406666 found in 7.3.2.1 and fixed in 7.3.3.2 and up. It makes it not possible to use REF CURSORS in functions callable from SQL. If you remove the pragma restrict_references, this will work but the function will not be callable from sql.

Looking at your code however, you do not need a ref cursor to achieve what you want (and hence we can code this to be callable from SQL). you may code the query as:

if ( p_field_name in ( 'ARTIST', 'COMPOSER' ) ) then    

   for x in ( select unique

         substr(decode(p_field_name,'ARTIST',artist,'COMPOSER',composer ),1,1) 
                from ...... ) loop
      ....

   end loop;
else

   return ERROR;
end if;

We can use DECODE to pick off the column you are interested in rather then using a big if/then/elsif/elsif/end if block.

Hope this helps.

>Hi...
> The additional Informations needed are below along with ealier mesg :
> Oracle7 Server Release 7.3.2.1.0 - Production Release
> PL/SQL Release 2.3.2.0.0 - Production
>I am using an telnet/CRT client on windows env.. to log into my acc on UNIX..
>then I am invoking sqlplus from there to access DB running on Sun Solaris
>system.
>A piece of code.......................................................
>CREATE OR REPLACE PACKAGE bvapi_newc AS
> FUNCTION cd4you_start_alphabets(p_cat_oid IN NUMBER, p_field_name
>IN VARCHAR2) RETURN VARCHAR2;
> PRAGMA RESTRICT_REFERENCES ( cd4you_start_alphabets , WNDS, WNPS );
> END bvapi_newc;
>CREATE OR REPLACE PACKAGE BODY bvapi_newc AS
> FUNCTION cd4you_start_alphabets(p_cat_oid IN NUMBER, p_field_name IN
>VARCHAR2)
> RETURN VARCHAR2
> IS
> TYPE t_Singlechar IS RECORD (
> v_singlechar CHAR(1));
>
> TYPE t_SearchID IS REF CURSOR RETURN t_Singlechar; -- TYPE t_SearchID IS
>REF CURSOR; v_Letter t_Singlechar; v_AlphaStr VARCHAR2(26) := NULL;
>v_CursorVar t_SearchID; BEGIN -- Get the content_type and store_id using
>p_cat_oid from BV_CATEGORY
>
> -- Open Curosor for processing
> IF UPPER(p_field_name)='ARTIST' THEN
> OPEN v_CursorVar FOR
> SELECT UNIQUE(UPPER(SUBSTR(ARTIST,1,1)))
> FROM bv_music_product
> WHERE oid IN (SELECT oid
> FROM bv_content_ref
> WHERE parent_oid = p_cat_oid)
> AND deleted = 0
> AND status = 1;
>
> ELSIF UPPER(p_field_name)='COMPOSER' THEN
> OPEN v_CursorVar FOR
> SELECT UNIQUE(UPPER(SUBSTR(COMPOSER,1,1)))
> FROM bv_music_product
> WHERE oid IN (SELECT oid
> FROM bv_content_ref
> WHERE parent_oid = p_cat_oid)
> AND deleted = 0
> AND status = 1;
> ELSE
> /* Wrong Value passed to it */
> Return v_AlphaStr;
>
> END IF;
>
> LOOP
> FETCH v_CursorVar INTO v_Letter;
> -- Exit if nor rec found
> EXIT WHEN v_CursorVar%NOTFOUND;
> IF(ASCII(v_Letter.v_Singlechar) BETWEEN 65 AND 90 ) THEN
> v_AlphaStr := v_AlphaStr||v_Letter.v_Singlechar;
> END IF;
> END LOOP;
> -- Close cursor
> CLOSE v_CursorVar;
> RETURN v_AlphaStr;
>
> EXCEPTION WHEN OTHERS THEN
> IF v_CursorVar%ISOPEN Then
> CLOSE v_CursorVar;
> END IF;
> RETURN NULL;
> END cd4you_start_alphabets;
>END bvapi_newc;
>-------------------------------------------------------------------------
>Now someone pl help me out......
>Thanks in Advance...
>D.V.Singh
>
>In article <35eacf64.14845216_at_192.86.155.100>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to dvsingh1_at_my-dejanews.com
>> (if that email address didn't require changing)
>> On Mon, 31 Aug 1998 12:15:20 GMT, you wrote:
>>
>> >Hi,
>> > I am having a trouble compiling a function containing REF CUROSR
>declation.
>> >
>> > I am using REF CURSOR to open a cursor associted with a query on one
>> >field. The cursor name is associated with around 5 queries , the only diff
>> >being the field name queried. Now When I try to install / compile this
>> >function , it gives an INVALID TYPE error., actual o/p is given below.....
>> >-----------------------------------------------------------------------
>> >
>> >Package created.
>> >
>> >
>> >Warning: Package Body created with compilation errors.
>> >
>> >Errors for PACKAGE BODY BVAPI_NEWC:
>> >
>> >LINE/COL ERROR
>> >-------- -----------------------------------------------------------------
>> >0/0 PLS-00707: unsupported construct or internal error [2601]
>> >0/0 PL/SQL: Compilation unit analysis terminated
>> >
>> >SQL>
>> >
>> >Please help me out ......Thanks in Advance.....
>>
>> please help *us* (post it on the net) out with a test case, database version
>> number down to at least 3 digits, platform you are working on and so on.
>>
>> the above is simply not enough information to work with.
>>
>> >
>> >D.V.Singh
>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Herndon VA
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 02 1998 - 09:42:01 CDT