Results 1 to 2 of 2

Thread: ref cursor

  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: ref cursor

    I have a package and procedure that returns a Oracle reference cursor to the
    calling program. This works fine. (Attached file packagetest.sql) In the
    second package, (packagetest1.sql) I create a cursor, loop through it, and
    insert values into a temporary table, and this works fine. What I don't
    know, or understand is, is there a way to take the values that I am
    inserting into the temporary table in the second script (packagetest1.sql),
    and instead, add the values to the reference cursor that will be passed back
    to the calling program?

    Do you guys have a quick solution or idea that might at least point me in the right direction.

    packagetest.sql (contents)

    --------------------------------------------------------------
    -- Package 'pkg_mytest'.
    --------------------------------------------------------------

    CREATE OR REPLACE PACKAGE pkg_mytest
    AS TYPE sql_ref_cursor IS REF CURSOR;

    PROCEDURE pr_mytest
    (
    o_cursor OUT SQL_REF_CURSOR
    );

    END pkg_mytest;
    /


    --------------------------------------------------------------
    -- Package Body for 'pkg_mytest'.
    --------------------------------------------------------------

    CREATE OR REPLACE PACKAGE BODY pkg_mytest

    AS

    ------------------------------------------------------
    -- Stored procedure 'pr_mytest'
    -- Code Name 'MyTest'
    ------------------------------------------------------

    PROCEDURE pr_mytest
    (
    o_cursor OUT SQL_REF_CURSOR
    )

    AS

    BEGIN

    OPEN o_cursor FOR
    SELECT
    table_name
    FROM
    user_tables;

    -- Error Handling controlled in Data Access Layer.

    END pr_mytest;


    END pkg_mytest;
    /

    packagetest1.sql (contents)


    CREATE TABLE dttemp_table
    (
    col_1 VARCHAR2(30),
    col_2 VARCHAR2(30)
    );


    --------------------------------------------------------------
    -- Package 'pkg_mytest1'.
    --------------------------------------------------------------

    CREATE OR REPLACE PACKAGE pkg_mytest1
    AS TYPE sql_ref_cursor IS REF CURSOR;

    PROCEDURE pr_mytest1
    (
    o_cursor OUT SQL_REF_CURSOR
    );

    END pkg_mytest1;
    /


    --------------------------------------------------------------
    -- Package Body for 'pkg_mytest1'.
    --------------------------------------------------------------

    CREATE OR REPLACE PACKAGE BODY pkg_mytest1

    AS

    ------------------------------------------------------
    -- Stored procedure 'pr_mytest1'
    -- Code Name 'MyTest'
    ------------------------------------------------------

    PROCEDURE pr_mytest1
    (
    o_cursor OUT SQL_REF_CURSOR
    )

    AS

    CURSOR c_tables IS
    SELECT
    table_name
    FROM
    user_tables;

    BEGIN

    FOR v_table IN c_tables LOOP

    BEGIN

    -- This is where I would like to put the data into a cursor (o_cursor)
    -- instead of in the temp table.

    INSERT INTO
    dttemp_table
    (
    col_1,
    col_2
    )
    SELECT
    table_name,
    column_name
    FROM
    user_tab_columns
    WHERE
    table_name = v_table.table_name;


    END;


    END LOOP;


    -- Error Handling controlled in Data Access Layer.

    END pr_mytest1;



    END pkg_mytest1;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    try this:
    Code:
    PROCEDURE pr_mytest1
    (
      o_cursor OUT SQL_REF_CURSOR
    )
    
    AS
    
    BEGIN
    
      OPEN o_cursor FOR
        SELECT table_name,
               column_name
        FROM   user_tab_columns;
    
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •