Getting results in a result set from dynamic SQL in Oracle

14,652

Solution 1

You seem to be asking for a chunk of PL/SQL code that will take an arbitrary query returning result set of undetermined structure and 'forward/restructure' that result set in some way such that is can easily be rendered by some "custom GUI tool".

If so, look into the DBMS_SQL for dynamic SQL. It has a DESCRIBE_COLUMNS procedure which returns the columns from a dynamic SELECT statement. The steps you would need are,

  1. Parse the statement
  2. Describe the result set (column names and data types)
  3. Fetch each row, and for each column, call the datatype dependent function to return that value into a local variable
  4. Place those local variables into a defined structure to return to the calling environment (eg consistent column names [such as col_1, col_2] probably all of VARCHAR2)

As an alternative, you could try building the query into an XMLFOREST statement, and parse the results out of the XML.


Added : Unlike SQL Server, an Oracle PL/SQL call will not 'naturally' return a single result set. It can open up one or more ref cursors and pass them back to the client. It then becomes the client's responsibility to fetch records and columns from those ref cursors. If your client doesn't/can't deal with that, then you cannot use a PL/SQL call. A stored function can return a pre-defined collection type, which can allow you to do something like "select * from table(func_name('select * from countries'))". However the function cannot do DML (update/delete/insert/merge) because it blows away any concept of consistency for that query. Plus the structure being returned is fixed so that

select * from table(func_name('select * from countries'))

must return the same set of columns (column names and data types) as

select * from table(func_name('select * from persons'))

It is possible, using DBMS_SQL or XMLFOREST, for such a function to take a dynamic query and restructure it into a pre-defined set of columns (col_1, col_2, etc) so that it can be returned in a consistent manner. But I can't see what the point of it would be.

Solution 2

The closest thing I could think of is to create a dynamic view for which permission is required. This will certainly involve using a PL/SQL block and a SQL query and no procedure/function. But, any dynamic query can be converted and viewed from the Result Grid as it's going to be run as a select query.

DEFINE view_name = 'my_results_view';
SET FEEDBACK OFF
SET ECHO OFF
DECLARE
  l_view_name VARCHAR2(40)     := '&view_name';
  l_query     VARCHAR2(4000)   := 'SELECT 1+level as id,
                                  ''TEXT''||level as text  FROM DUAL ';
  l_where_clause VARCHAR2(4000):= 
                           ' WHERE TRUNC(1.0) =  1 CONNECT BY LEVEL < 10';
BEGIN
     EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW '
                       || l_view_name
                       || ' AS '
                       || l_query
                       || l_where_clause;
END;
/
 select * from &view_name;

enter image description here

Share:
14,652
Michael Sorens
Author by

Michael Sorens

• Software designer/architect, application/website developer, author, educator--see full brand page. • Broad exposure to diverse firms from R&amp;D at a Fortune 500 firm to principal software designer at a tiny startup company. • Worked on projects including: wep apps, content management systems, laser control, multi-user systems, database tools, color laser printer firmware. • Over 100 articles published on Simple-Talk.com and DevX.com covering topics (TDD, code review, source control, documentation, debugging, code smells, usability, visualization, testing) and technologies (C#, PowerShell, .NET, LINQ, JavaScript, AngularJs, XML, WPF, WinForms, database) -see full article list. • Open-source endeavors:   (1) SqlDiffFramework, a database comparison tool for comparing heterogeneous data sources.   (2) DocTreeGenerator, combines help pages of your PS cmdlets into a tree-structured HTML web site.   (3) MonitorFactory, a PowerShell framework to generate near-real-time monitors for any data resources.   (4) XmlDoc2CmdletDoc, generates individual help pages for binary PowerShell cmdlets.   (5) collection of developer tools/libraries in several languages (see API bookshelf); • Taught at community colleges and at University of Phoenix. • Member LinkedIn ( http://www.linkedin.com/in/michaelsorens )

Updated on June 04, 2022

Comments

  • Michael Sorens
    Michael Sorens almost 2 years

    This question is similar to a couple others I have found on StackOverflow, but the differences are signficant enough to me to warrant a new question, so here it is:

    I want to obtain a result set from dynamic SQL in Oracle and then display it as a result set in a SqlDeveloper-like tool, just as if I had executed the dynamic SQL statement directly. This is straightforward in SQL Server, so to be concrete, here is an example from SQL Server that returns a result set in SQL Server Management Studio or Query Explorer:

    EXEC sp_executesql N'select * from countries'
    

    Or more properly:

    DECLARE @stmt nvarchar(100)
    SET @stmt = N'select * from countries'
    EXEC sp_executesql @stmt
    

    The question "How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?" addresses the first half of the problem--executing dynamic SQL into a cursor. The question "How to make Oracle procedure return result sets" provides a similar answer. Web search has revealed many variations of the same theme, all addressing just the first half of my question. I found this post explaining how to do it in SqlDeveloper, but that uses a bit of functionality of SqlDeveloper. I am actually using a custom query tool so I need the solution to be self-contained in the SQL code. This custom query tool similarly does not have the capability to show output of print (dbms_output.put_line) statements; it only displays result sets. Here is yet one more possible avenue using 'execute immediate...bulk collect', but this example again renders the results with a loop of dbms_output.put_line statements. This link attempts to address the topic but the question never quite got answered there either.

    Assuming this is possible, I will add one more condition: I would like to do this without having to define a function or procedure (due to limited DB permissions). That is, I would like to execute a self-contained PL/SQL block containing dynamic SQL and return a result set in SqlDeveloper or a similar tool.


    So to summarize:

    • I want to execute an arbitrary SQL statement (hence dynamic SQL).
    • The platform is Oracle.
    • The solution must be a PL/SQL block with no procedures or functions.
    • The output must be generated as a canonical result set; no print statements.
    • The output must render as a result set in SqlDeveloper without using any SqlDeveloper special functionality.

    Any suggestions?

  • Michael Sorens
    Michael Sorens about 14 years
    Thanks, but this proposed solution violates point 4 in my summary (no print statements) and point 5. Both of those are required because I am using, as stated, a custom GUI tool to see my results (think of it as a very basic SqlDeveloper app).
  • Michael Sorens
    Michael Sorens about 14 years
    There is a similar mechanism, as I indicated with a link in my question, but this suggestion violates point 5 in my summary (no special SqlDeveloper functionality--the code block needs to be completely self-contained).
  • Michael Sorens
    Michael Sorens about 14 years
    Thanks for the input, Gary. I am not familiar with either DBMS_SQL or XMLFOREST so I read a bit on both. Perhaps I am missing something but I do not believe either technique does what I am asking. Regarding your DBMS_SQL suggestion I see 2 issues. First, it appears to need advance knowledge of what is in the target statement. Second, it does not get me to a simple result set like the SQL Server example. One clarification: yes, I am using a custom GUI tool, but the solution must first work in SqlDeveloper using just PL/SQL code, and I do not see how either technique you mention can do that.