PLSQL -> put into Result Set tabs in SQL window?

[zitot]

Let me define the scope.

I have some SQL query of a bunch of CTEs. To debug more easily I'd like to run select * from CTE1, select * from CTE2, ... in the same SQL window. Because they're CTEs and I don't have ability to make table, I cannot do multiple query from same CTE as the final select statement must be separated by semicolon.

Initially I tried a PLSQL block, defining the CTEs part in a string (minus the final select), created an array of table names, and looped over that array, concatenating the two together so i could do select * from array(i). That part worked.

But when it came time to output, I couldn't figure out how to take PL/SQL output and put it into a result set in an ad-hoc SQL window. The reason I wanted to do that was to generate 5-6 tabs at a time. Instead, I could use DBMS_Output, but I really wanted to use the result set.

So question 1 is: Was there a way for me to take the output from a PLSQL block and generate result sets?

-----

Next thing I tried was saving just the CTE code (minus the final select statement) to a file. Then including that file using the @ directive. quickly realized it's not supported by ad-hoc SQL window, and command window is just command line, so no result set there either. Also, I'm not sure whether the SQL @ directive wants a working SQL file, or partial SQL, or how to return values anyway... is it just a text cut and paste? or it runs the SQL file? not really clear how that would work, need to do some reading

-----

Tried making a long string as a SQL*Plus variable using &var syntax, but that didn't work.. e.g., &var select * from CTE1 [and on the variable dialog put in with cte1 as (select 1 from dual). It didn't like that] to be fair i don't think anyone was ever expected to misuse vars like this

-----

as far as i can tell, templates just paste code into your file. i don't need a 4k loc sql file that is just the same query but with a different final select statement.
If there's a way to include just the template name or a pointer to a template and just pass it the name of the cte to query, instead of including the whole template in the file, that would work.
-----

Does PL/SQL Developer offer an equivalent to a C preprocessor #define directive? Which itself would have been a workaround anyway. But a nice one, I could just do #define MyCTE "With CTE1 as (select 1 from dual), CTE2 as (select 2 from dual), CTE3 as (select 3 from dual)" and then call it over and over like MyCTE select * from CTE1; MYCTE select * from CTE2; MYCTE Select * from CTE3

But I really just wanted to see if PLSQL can output to result set tabs in a SQL window, initially

-----

final workaround: I am going to try running down the CTEs chain, changing the final select statement, and pinning one result set for each CTE one-by-one... umm, i think the tab=name directive is cascading down, lemme check real fast... (nvm im dumb pinned tabs work fine, forgot Ctrl+Click doesn't pin, just selects)

 

[Marco Kalter]

In general output from a PL/SQL Block can be returned as a result set through a cursor bind variable, or by using the dbms_sql.return_result procedure to return an implicit result set. The bind variable option will only work in a PL/SQL Block of a Test Script. Implicit result sets can also be returned from within stored program units. They will automatically be picked op by the Test Window after execution:

create or replace procedure implicit_results_example as
  DeptCursor sys_refcursor;
  EmpCursor  sys_refcursor;
begin
  open DeptCursor for 'select * from dept order by deptno';
  dbms_sql.return_result(DeptCursor);
  open EmpCursor for 'select * from emp order by empno';
  dbms_sql.return_result(EmpCursor);
end;