CODEWITHSUNDEEP
oracle-databaseplsql
Patrick
Patrick

Reputation: 139

Oracle display resultset of dynamic sql

i am pretty new to oracle and i am searching for two days already for a solution for my problem.

i have a view which should have a dynamic column and table name. something like that:

DECLARE
 plsql_block VARCHAR2(500);
BEGIN
   plsql_block := 'SELECT CONCAT('some','column') FROM CONCAT('some','table')';
   EXECUTE IMMEDIATE plsql_block
END;

This would work but how to i display the result? I already tried it with DBMS.Output and a Loop but thats not exactly what i want. i need that it is displayed as a normal result set in the GUI when i run this command. Does anyone has a hint for me how i am doing this in oracle?

I am Thankful for every answer Thanks pat

Upvotes: 0

Views: 1327

Answers (2)

William Robertson
William Robertson

Reputation: 16001

If you are on Oracle 12c (with the corresponding Oracle client), you can do this:

declare
    l_resultset sys_refcursor;
    l_sql_text varchar2(500) :=q'{select 'Hello, 12c!' as greeting from dual}';
begin
    open l_resultset for l_sql_text;
    dbms_sql.return_result(l_resultset);
end;

(Untested, because I'm not near a 12c command line right now.)

Upvotes: 0

Avrajit Roy
Avrajit Roy

Reputation: 3303

Actually I don't understand your dynamic query. But as per my understanding this query is multirow result result set. So you need to use BULK collect and iterate throuh the output for just the purpose of display.

There are two approaches

1) Just to display the output.

    SET serveroutput ON;
    DECLARE
      plsql_block VARCHAR2(500);
      lv_col1     VARCHAR2(10):='1';
      lv_col2     VARCHAR2(10):='2';
    type tab_var
    IS
      TABLE OF VARCHAR2(10);
      tab tab_var;
    BEGIN
      plsql_block := 'SELECT CONCAT('||lv_col1||','||lv_col2||') FROM dual';
      EXECUTE immediate plsql_block bulk collect INTO tab;
      FOR i IN tab.first..tab.last
      LOOP
        dbms_output.put_line(tab(i));
      END LOOP;
    END;

2) Approach will be refactor this into a function and then use it as below.

Creating a Table Type

create or replace 
type string_table
IS TABLE OF VARCHAR2(100);

CREATE OR REPLACE
  FUNCTION func_mu
    RETURN string_table
  AS
    plsql_block VARCHAR2(500);
    lv_col1     VARCHAR2(10):='1';
    lv_col2     VARCHAR2(10):='2';
    tab string_table;
  BEGIN
    plsql_block := 'SELECT CONCAT('||lv_col1||','||lv_col2||') FROM dual';
    EXECUTE immediate plsql_block bulk collect INTO tab;
    RETURN tab;
  END;

SELECT * FROM TABLE(func_mu);

Upvotes: 1

Related Questions