Getting results from a set from dynamic SQL to Oracle

Question

This question is similar to a couple of others I found in StackOverflow, but the differences are significant enough for me to warrant a new question, so here it is:

I want to get a result set from dynamic SQL in Oracle, and then display it as a result in a tool like SqlDeveloper, just as if I would execute a dynamic SQL statement directly. This is true in SQL Server, so to be specific, 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 correctly:

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

The question "How to return a result set / cursor from an anonymous Oracle PL / SQL block that executes Dynamic SQL?" affects the first half of the problem - running dynamic SQL in the cursor. The question “How to force Oracle result sets” provides a similar answer. Web search has revealed many variations of the same topic, and they all concern only the first half of my question. I found this postexplaining how to do this in SqlDeveloper, but it uses a bit of SqlDeveloper functionality. I actually use a custom query tool, so I need this solution to be self-sufficient in SQL code. This custom query tool likewise does not have the ability to display print output (dbms_output.put_line); it displays only result sets. Here is another possible way using "do an immediate ... bulk collection", but this example displays the results again using the dbms_output.put_line loop. This link is trying to solve this topic, but the question that has not received an answer, too.

, , : , (- ). PL/SQL, SQL, SqlDeveloper .

---

, :

• SQL (, SQL).
• Oracle.
• PL/SQL .
• ; . ​​
• SqlDeveloper - SqlDeveloper.

?

Answer 1

, PL/SQL, , "/", , " ".

, DBMS_SQL SQL. DESCRIBE_COLUMNS, SELECT. , ,

• ( )
• ,
• , (, [ col_1, col_2], , VARCHAR2)

XMLFOREST XML.

---

: SQL Server, Oracle PL/SQL "" . . . / , PL/SQL-. , - "select * from table (func_name (" select * from countries "))". DML (update/delete/insert/merge), . , ,

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

( )

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

, DBMS_SQL XMLFOREST, (col_1, col_2 ..), . , .

Answer 2

.

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

  -- Open cursor & specify bind argument in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;


declare
  v_rc    sys_refcursor;
begin
   v_rc := get_dept_emps(10);  -- This returns an open cursor
   dbms_output.put_line('Rows: '||v_rc%ROWCOUNT);
   close v_rc;
end;

. http://forums.oracle.com/forums/thread.jspa?threadID=886365&tstart=0

Answer 3

TOAD script v_result. , ( Excel ). , ( ). "TOAD" .

DECLARE
   v_result      sys_refcursor;
   v_dynamic_sql   VARCHAR2 (4000);
BEGIN
   v_dynamic_sql := 'SELECT * FROM user_objects where ' || ' 1 = 1';

   OPEN :v_result FOR (v_dynamic_sql);
END;

Oracle SQL Developer , .

Answer 4

The closest I could think of was to use a dynamic view. This will certainly require the use of a PL / SQL block and an SQL query, rather than a procedure / function. But any dynamic query can be converted and viewed from the result table, since it will be executed as a selection query.

DEFINE view_name = 'my_results_view';
SET FEEDBACK OFF
SET ECHO OFF
DECLARE
     l_view_name VARCHAR2(40) := 'my_results_view';
     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;