Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Angry Unanswered: Create Cursor with Dynamic SQL

    I am attempting to create a store procedure that contains a cursor. I would like to Dynamically create the cursor and I am having difficulty.

    Code:
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

    <Define Variables>

    CURSOR cur_gl_bucket
    IS
    SELECT <column name >
    FROM ledger_stat
    WHERE <statement>

    BEGIN

    Select <column name> from table;

    OPEN Cursor cur_gl_bucket with retrieved column name.

    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    The <column name> is selected from a table based on the parameters passed into the procedure. I would then like to OPEN the cursor with the retrieved column name dynamically imbedded in the Select statment for the cursor.

    I successfully use DBMS_SQL.PARSE and EXECUTE later in the procedure but have been unable to get it to work when I open the cursor. I guess the question is can this be done, and if so what is the syntax for doing this......

    Regards
    Patrick McCarthy
    mailto: patrick_mccarthy@canmail.com

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Create Cursor with Dynamic SQL

    Looks like you are on Oracle?

    create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

    <Define Variables>

    TYPE refcur IS REF CURSOR;
    cur_gl_bucket refcur;

    v_column_name VARCHAR2(30);

    BEGIN

    Select <column name> INTO v_column_name from table;

    OPEN cur_gl_bucket FOR 'SELECT '||v_column_name||' FROM ledger_stat WHERE <statement>';

Posting Permissions

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