Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Oracle - execute Select query without INTO in plsql block at runtime
swetabh
post Sep 22 2017, 04:47 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 19-September 17
Member No.: 52,174



I need to execute Select query in a procedure.
- the Select queries are unknown till run time, these will be passed as parameter

Main aim is to collect the stats from v$sql, v$sql_plan etc.

I have tried execute immediate and few other methods but failed. I can execute these queries by connecting sqlplus but I want to do this by a proc.

Requirement:

create or replace procedure p1 (p_sql IN clob)
as
begin
--execute Select query which is passed in p_sql
--capture v$sql
end;
/

I cannot use INTO as I dont know the no. of columns/query.
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 22 2017, 06:07 AM
Post #2


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Swetabh,

There is a cool feature to accomplish this. You need to use DBMS_SQL.DESCRIBE_COLUMNS and DBMS_SQL.DESCRIBE_COLUMNS2 if you not sure of the number of columns to your dynamic query.

You can see examples for these procedures from my notes below,

http://www.dba-oracle.com/t_plsql_dbms_sql...ibe_columns.htm

http://www.dba-oracle.com/t_plsql_dbms_sql...be_columns2.htm


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:48 PM