EXECUTE IMMEDIATE in plsql

Question

How to get a result from this code

EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name

through for loop

The usual method looks like this

for items in (select * from this_table)
loop
htp.p(items.id);
end loop;

Solution 1

If you really need to select * from dynamic table name, then I'd probably go with dbms_sql

Type for a record:

create type tq84_varchar2_tab as table of varchar2(4000);
/

Type for a result set (which is an array of records):

create type tq84_varchar2_tab_tab as table of tq84_varchar2_tab;
/

The function that does the select and returns an instance of the result set:

create or replace function tq84_select_star_from_table(table_name in varchar2) 
    return tq84_varchar2_tab_tab
as
    stmt_txt     varchar2(4000);
    cur          number;
    columns_desc dbms_sql.desc_tab;
    column_cnt   number;

    result_set   tq84_varchar2_tab_tab;

begin

    stmt_txt := 'select * from ' || table_name;    

    cur := dbms_sql.open_cursor;

    dbms_sql.parse(cur, stmt_txt, dbms_sql.native);
    dbms_sql.describe_columns(cur, column_cnt, columns_desc);
    dbms_sql.close_cursor(cur);


    stmt_txt := 'select tq84_varchar2_tab(';

    for i in 1 .. column_cnt loop 

        if i != 1 then
           stmt_txt := stmt_txt || ',';
        end if;

        stmt_txt := stmt_txt || columns_desc(i).col_name;

    end loop;

    stmt_txt := stmt_txt || ') from ' || table_name;

--  dbms_output.put_line(stmt_txt);

    execute immediate stmt_txt 
    bulk collect into result_set;

    return result_set;


end tq84_select_star_from_table;

The function can then be used with something like:

declare
  records   tq84_varchar2_tab_tab;
begin

  records := tq84_select_star_from_table('user_objects');

  for i in 1 .. records.count loop
      dbms_output.put_line (records(i)(5) || ': ' || records(i)(1));
  end loop;

end;
/

Solution 2

Here is a simple function which dynamically opens a cursor variable, using the passed table name parameter.

create or replace function get_details_by_dno
    ( p_tab in user_tables.table_name%type
      , p_dno in dept.deptno%type )
    return sys_refcursor
is
    rv sys_refcursor;
    stmt varchar2(32767);
begin
    stmt := 'select * from '
        ||p_tab
        ||' where deptno = :1';
    open rv for stmt using p_dno;
    return rv;
end;
/

It also uses the DEPTNO as a filter; consequently the function will fail if we pass a table which doesn't have such a column.

Some clients can interpret the ref cursor's metadata. For instance JDBC and ODBC ResultSets can do this. SQL*Plus can do it:

SQL> exec :rc := get_details_by_dno('DEPT', 50)

PL/SQL procedure successfully completed.

SQL> print rc

    DEPTNO DNAME          LOC           REGION
---------- -------------- ------------- ----------
        50 HOUSEKEEPING   INTERNAL

SQL> exec :rc := get_details_by_dno('EMP', 50)

PL/SQL procedure successfully completed.

SQL> exec :rc := get_details_by_dno('EMP', 50)

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      8085 TRICHLER   PLUMBER         8061 08-APR-10       3500                    50
      8060 VERREYNNE  PLUMBER         8061 08-APR-08       4000                    50
      8061 FEUERSTEIN PLUMBER         7839 27-FEB-10       4500                    50
      8100 PODER      PLUMBER         8061                 3750                    50

SQL>

PL/SQL cannot do this. So we need to be explicit about the table and column names.

create or replace procedure print_details_by_dno
    ( p_tab in user_tables.table_name%type
      , p_dno in dept.deptno%type )
is
    rc sys_refcursor;
    emp_rec emp%rowtype;
    dept_rec dept%rowtype;
begin
    rc :=  get_details_by_dno( p_tab , p_dno );

    if p_tab = 'EMP' then
        fetch rc into emp_rec;
        while rc%found loop
            dbms_output.put_line('ename='||emp_rec.ename||' empno='||emp_rec.empno);   
            fetch rc into emp_rec;
        end loop;
    elsif p_tab = 'DEPT' then
        fetch rc into dept_rec;
        while rc%found loop
            dbms_output.put_line('dname='||dept_rec.dname);   
            fetch rc into dept_rec;
        end loop;
    end if;
end;
/

Let's see it running:

SQL> set serveroutput on
SQL> exec print_details_by_dno('EMP',50)
ename=TRICHLER empno=8085
ename=VERREYNNE empno=8060
ename=FEUERSTEIN empno=8061
ename=PODER empno=8100

PL/SQL procedure successfully completed.

SQL> exec print_details_by_dno('DEPT',50)
dname=HOUSEKEEPING

PL/SQL procedure successfully completed.

SQL>

Solution 3

You need to declare a cursor from the dynamic sql and loop through it.

An example of this is available in the manual:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDJHAHE

Solution 4

TYPE RefCurTyp IS REF CURSOR;
sql  VARCHAR2(200);
cursor RefCurTyp;
id  VARCHAR2(200);
BEGIN
  sql := 'SELECT * FROM ' || table_name;

  OPEN cursor FOR sql;
  LOOP
    FETCH cursor INTO id;

    htp.p(id);     

    EXIT WHEN cursor%NOTFOUND;
  END LOOP;

  CLOSE cursor;
END;

View more solutions

Comments

[Zhasulan Berdibekov]

In conventional method does not need to declare any variables, just going on there all the static, and I want it to be similar to the conventional method, except that it must be dynamic.

[Zhasulan Berdibekov]

And I do not have problems with Google :)

[Tony Andrews]

So simplify the example to get what YOU want - OPEN emp_cv FOR 'SELECT * FROM ' || table_name; LOOP ... END LOOP;

[Zhasulan Berdibekov]

I care about this moment OPEN emp_cv

[Tony Andrews]

You can't just say FOR items IN ('SELECT * FROM ' || table_name') as you have presumably discovered. So you MUST declare the cursor variable.

[vc 74]

I considered table_name refered to a single varchar2 column table so id will contain the current record's value for this column

[Zhasulan Berdibekov]

The expression 'RESULT_SET' in the INTO list is of wrong type