| PL/SQL Packages & Dynamic SQL [message #38147] |
Mon, 25 March 2002 17:14  |
Hersel Ahdout
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
I appreciate any help.
Is there anyway to pass the results of a Dynamic SQL statements that is build withing a stored procedure to SQL/PLUS.
I want my users to have the capability of building a query which I can then refine before submitting it to the database. I know I need to use DBMS_SQL.PARSE & DBMS_SQL.EXECUTE. Is there anyway that I can pass the reultant cursor as a RefCursor to the calling user (in SQL Plus or other client applications).
Thanks very much.
|
|
|
|
| Re: PL/SQL Packages & Dynamic SQL [message #38208 is a reply to message #38147] |
Mon, 01 April 2002 12:03  |
Rita Wilemon
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
|
Everything I have read says that you cannot return a refCursor from a dbms_sql dynamic query. If you find a way I would be also like to know. We have had to do a write a procedure with a bunch of if /then comparisons of field values to construct different "open cur for select..." statements in order to return ref_cursor values. That appears to be the only solution we have found.
|
|
|
|
| Re: PL/SQL Packages & Dynamic SQL [message #38238 is a reply to message #38147] |
Wed, 03 April 2002 12:55 |
Hersel Ahdout
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
After searching other places, I found it to be quite simple. You do not need to use DBMS package to parse and create a cursor.
All you do is you build your sql statement and use Open Cursor for the statement:
If your procedure is:
TYPE DocTyp IS REF cursor;
Procedure GetEmp(empno varchar2, cursor IN OUT DocTyp) As
sqlstatement Varchar2(2000);
BEGIN
sqlstatement := 'Select * from emp where empno = :en';
Open cursor for
sqlstatement
using vempno;
END GetEmp;
Of course, the select could be any thing you wish and it gets prepared and processed at the time of execution. It is advisable however, to parametrize it as much as possible (In case the same procedure is called by more than one user and they both look for the same field set just different empnos, Oracle will share the prepared version of the dynamic Query).
I hope it helps you. It certainly got me out of the bind. No I can allow my developers build dynamic queries which my procedure control for Paging, etc.
Hersel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
