In Oracle's SQL Developer, I can execute a "dynamic" SQL select statement in the Script Output pane with something like:
script
var tabName = 'all_users';
sqlcl.setStmt('select * from ' + tabName);
sqlcl.run();
/
Now, I am wondering if it is possible to execute a dynamic select statement such that its result is displayed in the result grid.
SqlDev is implemented in Java which includes Nashorn scripting engine. Therefore, you can basically execute JDBC in your Nashorn script. Here is one way to do it.
Paste
select REGIONS,
COUNTRIES,
LOCATIONS,
DEPARTMENTS,
JOBS,
EMPLOYEES,
JOB_HISTORY,
TM_USER_INFO,
USER_ROLES,
PAYMENT_PRICE_SHOP,
SHOP_USER from dual
into your worksheet.
Open "Code Outline" panel. Type "arbori", or open the following "querySQL.arbori" file:
include "std.arbori"
prelude: runOnce -> {
var ConnectionResolver = Java.type('oracle.dbtools.db.ConnectionResolver');
var connectionList = ConnectionResolver.getConnectionNames();
var conn = ConnectionResolver.getConnection('IdeConnections%23local_hr');
}
queries: [node) column -> {
var node = tuple.get('node');
var table = target.input.substring(
target.src[node.from].begin,
target.src[node.to-1].end
);
var query = 'select * from ' + table;
var ps = conn.createStatement();
var rs = ps.executeQuery(query);
while( rs.next() )
print(rs.getString(1));
}
It outputs the first column of each table to the standard java output
, so it would require some reverse engineering to get a handle to the SqlDev script output panel and channel the result there.
Dynamic SQL can be displayed in an Oracle SQL Developer grid using a function returning a ref cursor (sort of), a polymorphic table function (18c+), or Oracle Data Cartridge (requires custom PL/SQL packages and types).
As explained in this answer, the output from a function returning a ref cursor can be displayed in the "Output Variables" window. The example from the answer used static SQL, but it's pretty easy to make it dynamic:
create or replace function refcursor_function return sys_refcursor as
c sys_refcursor;
begin
open c for 'select * from all_objects';
return c;
end;
/
The downside is that getting the results takes a few more clicks than a normal query, and the Output Variables grid is not nearly as powerful as the regular results grid. If you just need a window for viewing and copying and pasting, Output Variables is good enough. But it doesn't allow any advanced GUI features.
Since Oracle 18c, you can create a polymorphic table function that accepts input and has variable output. You have to program how to handle the tables and columns, but if you just need simple pass-through logic it's not that difficult. See my answer here for an example of a query that returns every column from a table excluding specific columns. The results are "regular" SQL as far as any program knows, and will work in any grid GUI.
My open source program Method4 can run dynamic SQL in SQL. After you download and install the packages and types, you can write a SQL statement that generates another SQL statement. If you need to use PL/SQL to generate the query, you may need to use a PL/SQL WITH function. Like the polymorphic table function, the results look like normal SQL and will work in any grid.
select * from table(method4.dynamic_query(
q'[
--Query that builds another query.
select replace(
q'!
select * from #TABLE_NAME#
!', '#TABLE_NAME#', table_name) sql_statement
from
(
--Enter your script here that returns a table name to select from.
select 'ALL_USERS' table_name
from dual
)
]'
));
You may want to add some details about what exactly you're trying to do and why; that might help narrow down the possible solutions.
