Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

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
post Sep 22 2017, 04:47 AM
Post #1


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.


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

I cannot use INTO as I dont know the no. of columns/query.
Go to the top of the page
+Quote Post
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,



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