1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ref cursor to collection

Discussion in 'SQL PL/SQL' started by Hellen Avila, Jun 11, 2019.

  1. Hellen Avila

    Hellen Avila Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Costa Rica
    Hi everybody.

    I have this problem, I hope someone can help me.

    create or replace type ObjDatosCatalogo as object
    ( Column1 NUMBER(5),
    Column2 VARCHAR2(30))

    ----------------------------------------------

    CREATE OR REPLACE TYPE TblDatosCatalogo IS Table Of ObjDatosCatalogo

    ------------------------------

    I have a Function, X, this X function calls a procedure Y and this Y procedure retorn a cursor (REF CURSOR), it could be CurInfo. This cursor retorns two columns (column1, colum2) and several records.

    The Function X return TblDatosCatalogo .

    I need to pass the information in CurInfo to TblDatosCatalogo and I need to use Bulk Collection in order to make it faster.

    First, the bulk collection does not understand CurInfo, I supose because this is from another source, CurInfo is not declared in the Function, in my test, when I have the cursor in the Function, Bulk Collect works fine, but, it is real life and I can't have the cursor declared in the Function, I need to search the information in the Y procedure.

    Second, when I do the Fetch, I get the error ORA-06504, but it compiles fine, so, what I 'm doing wrong. Example:

    create or replace function X (Parametro1 IN VARCHAR2)
    return TBLDATOSCATALOGO IS

    vCatalogo tbldatoscatalogo;
    vCurInfoCatalogo package1.tcursorgenerico; (This object is in a package, and it's declaration is: TYPE tcursorgenerico IS REF CURSOR)

    BEGIN

    vCatalogo := tblDatosCatalogo();

    --Calls the procedure
    Y(Parametro1, vCurInfoCatalogo );

    Fetch vCurInfoCatalogo BULK COLLECT
    INTO vCatalogo LIMIT 100;

    Return vCatalogo;
    END;

    When I tried to do the Test, I get the error ORA-06504 pl/sql return types of result set variables or query do not match

    Please, What I 'm doing wrong?

    I don't want to specify every column of the cursor, I'm using a simple example, but I have to process several Functions and Each one has cursors with 30 columns or more! it's crazy!

    Any help, thank you so much!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,685
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    778
    Likes Received:
    149
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.

    1)
    Restriction of REF CURSOR


    2) Also, will be useful : Cursor of variable

    3)The simple of example

    Code (SQL):

    CREATE OR REPLACE package test_coll
    AS
        TYPE rec IS record
        (
           tab_name all_tables.table_name%TYPE,
           STATUS   all_tables.status%TYPE
        );
        TYPE coll IS TABLE OF rec;
     
        FUNCTION cur_to_coll(p_owner all_tables.owner%TYPE)  
          RETURN coll pipelined;
    END;
    /

    CREATE OR REPLACE package body test_coll
    AS
        FUNCTION cur_to_coll(p_owner all_tables.owner%TYPE)
          RETURN coll pipelined
        AS
          v_cur  sys_refcursor;
          v_owner all_tables.owner%TYPE := nvl(p_owner,USER);
          v_coll coll;
        BEGIN
           OPEN v_cur FOR SELECT a.TABLE_NAME,a.STATUS FROM all_tables a WHERE a.owner = v_owner;
           loop
             fetch v_cur bulk collect INTO v_coll LIMIT 100;
             exit WHEN v_coll.COUNT = 0 ;
             FOR z IN 1 .. v_coll.COUNT
             loop
               pipe ROW(v_coll(z));
             END loop;
           END loop;
           RETURN;
        END;
    END;
    /

    SELECT * FROM TABLE(test_coll.cur_to_coll(p_owner => NULL));
     
    N.B. But, a cursor variables to use in certain applications, but not everywhere.
     
    Sadik likes this.