• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

Oracle Dynamic Sql

I have a dynamic sql like ' Select || fields|| from || tab|| where || where_clause||
here fields,tab & where_clause is populated dynamically.
I have a requirmeent, wherein,I have to execute this query ,which will result in multiple rows and result of each rows to be concatenated (1,'abc','23-03-2003')  and retuned ,to be used in a Insert proc which I have.
I have the logic for concatenation of values depending on the datatype,but how do I loop through each record for the above query????

Can anybody help me or anybody has a reusable code for doing this.
0
vivage
Asked:
vivage
1 Solution
 
andrewstCommented:
Here is an example using Native Dynamic SQL:

  1  declare
  2    type refcur is ref cursor;
  3    rc refcur;
  4    v_column varchar2(30) := 'ENAME';
  5    v_table varchar2(30) := 'EMP';
  6    v_value varchar2(2000);
  7  begin
  8    open rc for 'select '||v_column||' from '||v_table;
  9    loop
 10      fetch rc into v_value;
 11      exit when rc%notfound;
 12      dbms_output.put_Line( v_value );
 13    end loop;
 14* end;
SQL> /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

This requires that you know when you build the program the number (and type unless you can assume varchar2 for all) of columns in the dynamic select statement.  If the number of columns will vary, you will have to use DBMS_SQL:

  1  declare
  2    c number := dbms_sql.open_cursor;
  3    r number;
  4    v_column varchar2(30) := 'ENAME';
  5    v_table varchar2(30) := 'EMP';
  6    v_value varchar2(2000);
  7  begin
  8    dbms_sql.parse( c, 'select '||v_column||' from '||v_table, dbms_sql.native );
  9    dbms_sql.define_column( c, 1, v_value, 2000 );
 10    r := dbms_sql.execute( c );
 11    loop
 12      r := dbms_sql.fetch_rows(c);
 13      exit when r = 0;
 14      dbms_sql.column_value( c, 1, v_value );
 15      dbms_output.put_Line( v_value );
 16    end loop;
 17    dbms_sql.close_cursor(c);
 18* end;
SQL> /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.
0
 
walterecookCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: andrewst {http:#8216771}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now