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 at 8:15 PM.

  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,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO