4

ORACLE: So far nothing I have tried has worked. I wish to display on the screen the results of select * from my_table. In this case my_table = select table_name from all_tables where owner='ABC' and name like 'ABC%'. Table name would be a plus, but column name is a necessity. I can do this in seconds with DB2, but can't quite translate for Oracle.

My attempt: 

    variable refcur refcursor;
    declare
    my_select     varchar2(64);
    cursor c_tables is
        select table_name
          from all_tables 
         where owner='ABC' and table_name like 'ABC%';
    begin
    for x in c_tables
      loop
         dbms_output.put_line(x.table_name);  
         my_select := 'select * from ' || x.table_name;
         open :refcur for my_select;
      end loop;

   exception
     when no_data_found
     then dbms_output.put_line('Nothing is found');
   end;
   /

In all of my attempts, the best I have gotten is table does not exist Thanks

user3586521
  • 63
  • 1
  • 2
  • 5

2 Answers2

0

I don't know how you're logged on, but if you're NOT logged on as ABC you'll need to include the schema along with the table name, e.g.

my_select := 'select * from ' || x.owner || '.' || x.table_name;

Also, opening a cursor doesn't fetch anything from it, or display the data anywhere. You'll need to add logic to fetch data from the cursor, display the data, and close the cursor. And because the table name isn't fixed the database can't tell ahead a time what your row looks like so you'll need to get familiar with the DBMS_SQL package, which is used for handling dynamic SQL like this.

Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
0

you can user below example:

create or replace procedure app_test(v_tab varchar2) is
  type cur_type is ref cursor;
  my_cur   cur_type;
  v_name   varchar2(20);
  dyna_sql varchar2(4000);
begin
  dyna_sql := 'select username from ' || v_tab || ' where rownum=1';
  open my_cur for dyna_sql;
  fetch my_cur
    into v_name;
  while my_cur%found LOOP
    fetch my_cur
      into v_name;
    DBMS_output.put_line(v_name);
  end loop;
  close my_cur;
end app_test;
wangr
  • 1