Re: REF CURSOR

From: <dvsingh1_at_my-dejanews.com>
Date: Tue, 01 Sep 1998 12:23:07 GMT
Message-ID: <6sgovb$906$1@nnrp1.dejanews.com>

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 Received on Tue Sep 01 1998 - 07:23:07 CDT