?
Solved

Oracle Dynamic Sql

Posted on 2003-03-26
3
Medium Priority
?
465 Views
Last Modified: 2012-08-13
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
Comment
Question by:vivage
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 840 total points
ID: 8216771
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
 
LVL 17

Expert Comment

by:walterecook
ID: 10360611
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question