CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
user2607028
user2607028

Reputation: 345

How select tablename from user_table

I have dinamics tables on my system, it change one number on the name so I do not have a fixed name. I wish to select the lasted table but only the name of the table to make a select to this table.

SELECT * FROM ((SELECT * FROM (select a.tablespace_name || '.' || a.table_name AS TABLE_NAME
from user_tables a
wherE a.tablespace_name='USERNAME'
and a.table_name like '%_DIN'
ORDER BY TABLE_NAME DESC)
WHERE ROWNUM = '1'))

for example. the table name of

SELECT * FROM (select a.tablespace_name || '.' || a.table_name AS TABLE_NAME
    from user_tables a
    wherE a.tablespace_name='USERNAME'
    and a.table_name like '%_DIN'
    ORDER BY TABLE_NAME DESC)
    WHERE ROWNUM = '1'

return: 7_DIN

is a name of one table in my tablespace I want to take this name and make a select of this table.

something like 

select * from 7_DIN

All in one statement

Upvotes: 1

Views: 4545

Answers (1)

ThinkJet
ThinkJet

Reputation: 6745

Below is code step-by-step, you can test it in this SQLFiddle.

Sample tables used for test:

create table t1(n number)
/
create table t2(n number)
/
create table t13(n number)
/
insert into t1(n) values(1)
/
insert into t2(n) values(2)
/
insert into t13(n) values(13)
/

Declare types used as selection result, same as row type of tables with dynamic names:

create or replace type t_row as object(n number)
/
create or replace type t_rowlist as table of t_row
/

Function which searches for last table and selects data from them into collection, then returns collection as table data:

create or replace function get_last_data return t_rowlist
as
  v_table varchar2(30);
  v_rows  t_rowlist;
begin

  select table_name into v_table from (
    select * from user_tables where table_name like 'T%'
    order by lpad(substr(table_name,2),40,'0') desc
  )  
  where rownum = 1;

  execute immediate 'select t_row(n) from '|| v_table
  bulk collect into v_rows;

  return v_rows;

end;
/

Create view based on function data:

create or replace view last_data_view as
  select * from table(get_last_data)
/

This works good only if dynamic tables don't have a big amount of data.
Otherwise it's better to use pipelined functions. To do so, just replace function implementation with code below:

create or replace function get_last_data_pipe 
return t_rowlist pipelined
as
  v_table  varchar2(30);
  v_row    t_row;
  v_cursor sys_refcursor;
begin

  select table_name into v_table from (
    select * from user_tables where table_name like 'T%'
    order by lpad(substr(table_name,2),40,'0') desc
  )  
  where rownum = 1;

  open v_cursor for 'select t_row(n) from '|| v_table;

  loop
    fetch v_cursor into v_row;
    exit when v_cursor%notfound;
    pipe row(v_row);
  end loop;

  close v_cursor;

  return;
end;

Link to test SQLFiddle.

Upvotes: 1

Related Questions