Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: dynamic sql and union

    Hi All,

    I'm using a Dynamic sql in a procedure as mentioned below-

    procedure temp(master_acct_nbr in varchar2,
    po_history out resultset
    )
    is

    begin

    sql_Stmt := 'select a.id, a.status, b.trck_id
    from tab1 a, tab2 b
    where a.id = b.id
    b.offer_cd = '||'''ABC'''||'
    and a.nbr in ('||master_acct_nbr||')
    UNION
    SELECT A.ID, A.NAME
    FROM TAB3@dblink a';

    --OPEN po_history FOR SQL_STMT;
    --execute immediate sql_stmt;

    end temp;

    How can I execute the SQL stmt. I checked online and it says
    execute immediate sql_stmt using ???? ;

    Or is there any other way to execute it.
    Any inputs is greatly appreciated.

    Thanks in advance!
    Last edited by nandinir; 07-27-09 at 18:00.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You should always print out the contents of sql_Stmt before executing it.

    Post results back here.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2005
    Posts
    57
    The manual states:
    To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

    Performing SQL Operations with Native Dynamic SQL

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by nandinir
    How can I execute the SQL stmt. I checked online and it says
    execute immediate sql_stmt using ???? ;

    Or is there any other way to execute it.
    Any inputs is greatly appreciated.
    The only dynamic part of the code is the content of the IN list.
    There is no need to do it dynamically, you may use one of the methods posted in this article by Tom Kyte: http://tkyte.blogspot.com/2006/06/varying-in-lists.html.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    It works now! I used
    OPEN po_history FOR SQL_STMT;

    and I'm able to see the results too.

    Thanks guys for the inputs.

Posting Permissions

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