Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Question Unanswered: Execute Immediate output

    Hi,

    I have a procedure which is someting like this

    Declare
    ...
    Begin
    v_sql = 'select 1 from dual';
    execute immediate v_sql;
    end

    In this case I don't get the output of execute immediate on the screen.
    How do I capture it?
    Regards,
    SD

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

    Re: Execute Immediate output

    Originally posted by sd_100
    Hi,

    I have a procedure which is someting like this

    Declare
    ...
    Begin
    v_sql = 'select 1 from dual';
    execute immediate v_sql;
    end

    In this case I don't get the output of execute immediate on the screen.
    How do I capture it?
    Regards,
    SD
    execute immediate v_sql into variable;
    dbms_output.put_line( 'value='||variable );

  3. #3
    Join Date
    Aug 2003
    Posts
    8
    Hi,
    The statement I wrote was just a framework
    the statement is sometjhing like

    v_sql := 'select ' col_name 'from' table_name;
    execute immediate v_sql;

    here col_name and table_name are both variables and
    the output of execute immediate is a list of values .
    Any idea how this can be tackled.
    I will nbe calling this procaedure from a java servlet and need to pass all the values there.

    SD

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by sd_100
    Hi,
    The statement I wrote was just a framework
    the statement is sometjhing like

    v_sql := 'select ' col_name 'from' table_name;
    execute immediate v_sql;

    here col_name and table_name are both variables and
    the output of execute immediate is a list of values .
    Any idea how this can be tackled.
    I will nbe calling this procaedure from a java servlet and need to pass all the values there.

    SD
    You probably want a REF CURSOR then:

    CREATE OR REPLACE PROCEDURE proc( rc IN OUT SYS_REFCURSOR ) IS
    BEGIN
    OPEN rc FOR 'select ' || col_name || ' from ' || table_name;
    END;

    If SYS_REFCURSOR doesn't work for you (pre-O9i) then you can define your own:

    create or replace package pkg is
    type ref_cursor is ref cursor;
    end;
    /

    CREATE OR REPLACE PROCEDURE proc( rc IN OUT pkg.ref_cursor ) IS
    BEGIN
    OPEN rc FOR 'select ' || col_name || ' from ' || table_name;
    END;

    As for how to call this from a Java servlet, sorry I have no idea.

  5. #5
    Join Date
    Aug 2003
    Posts
    8
    Thanks Tony..

Posting Permissions

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