Home » SQL & PL/SQL » SQL & PL/SQL » Query to find relevant Table name (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
Query to find relevant Table name [message #574835] Wed, 16 January 2013 01:11 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Hi i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:

EMP
EMP_1
EMP_2
EMP_3
EMP_4

i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'.
But here object_name i will passing dynamically.
Re: Query to find relevant Table name [message #574836 is a reply to message #574835] Wed, 16 January 2013 01:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Try WHERE TABLE_NAME LIKE '&TBL_NAME%'

regards,
Delna
Re: Query to find relevant Table name [message #574840 is a reply to message #574835] Wed, 16 January 2013 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have to use dynamic SQL.
Either in PL/SQL using EXECUTE IMMEDIATE statement or DBMS_SQL package.
Either in SQL*Plus generating a script from a query on USER_OBJECTS and executing this generated script.

Regards
Michel
Re: Query to find relevant Table name [message #574847 is a reply to message #574840] Wed, 16 January 2013 01:56 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Actually my main perpose is to create backup tables i.e. EMP is main table and EMP_1,EMP_2,EMP_3 .. EMP_100 is backup table.Now whenever i am making the any changes in EMP table, then i should take the backup of exiting EMP table and backup data should go to EMP_101 (as last table with sequence is EMP_100).

Above my post would help me out the such tables i.e. EMP_1 to EMP_100.if i got the that result then i can manipulate and figured out my requirement.

Any idea in SQL , PL/SQL i would appreciate Razz .

Thanks
Re: Query to find relevant Table name [message #574850 is a reply to message #574847] Wed, 16 January 2013 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table emp_1 as select * from emp where 1=2;

Table created.

SQL> create table emp_2 as select * from emp where 1=2;

Table created.

SQL> select table_name from user_tables where table_name like 'EMP\_%' escape '\' order by 1;
TABLE_NAME
------------------------------
EMP_1
EMP_2

2 rows selected.

SQL> col tb_num new_value tb_num
SQL> select to_char(max(to_number(substr(table_name,instr(table_name,'_')+1)))+1) tb_num
  2  from user_tables where table_name like 'EMP\_%' escape '\'
  3  /
TB_NUM
----------------------------------------
3

1 row selected.

SQL> create table emp_&tb_num as select * from emp;

Table created.

SQL> select table_name from user_tables where table_name like 'EMP\_%' escape '\' order by 1;
TABLE_NAME
------------------------------
EMP_1
EMP_2
EMP_3

3 rows selected.

Regards
Michel
Re: Query to find relevant Table name [message #574853 is a reply to message #574850] Wed, 16 January 2013 02:57 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Thanks a lot Michel. But i found in net other way also.

DECLARE
TAB_NAME VARCHAR2(30) := 'EMP';
NEW_TAB_NAME VARCHAR2(30);
BEGIN
  FOR I IN (
        SELECT * FROM (
            SELECT T.*, 
                    ROW_NUMBER() OVER(ORDER BY T.TABLE_NAME DESC) AS ROW_NR ,
                    MIN(TABLE_NAME) OVER () AS ORIGINIALL_TABLE
                    FROM USER_TABLES T 
            WHERE TABLE_NAME LIKE TAB_NAME||'%'
        ) WHERE ROW_NR = 1
  ) LOOP
   DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME);  
    NEW_TAB_NAME := SUBSTR(I.TABLE_NAME, 1, INSTR(I.TABLE_NAME, '_') - 1 ) 
                    ||'_' 
                    || (TO_NUMBER(SUBSTR(I.TABLE_NAME, INSTR(I.TABLE_NAME, '_') + 1 )) + 1 ) ;
                    
    DBMS_OUTPUT.PUT_LINE(NEW_TAB_NAME);    
   
  END LOOP;
END;


In below post
https://forums.oracle.com/forums/thread.jspa?threadID=2486818&tstart=0
Re: Query to find relevant Table name [message #574855 is a reply to message #574853] Wed, 16 January 2013 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This code is wrong.
'99' is greater than '100', '101'... , so as soon as you have '99' the answer will always be '100', even if it exists and the next ones too.

Actually you will never reach '99', as soon as you created '9' the next one will always be '10'.

Regards
Michel
Re: Query to find relevant Table name [message #574856 is a reply to message #574847] Wed, 16 January 2013 03:13 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
chandan.rattan wrote on Wed, 16 January 2013 08:56
Actually my main perpose is to create backup tables i.e. EMP is main table and EMP_1,EMP_2,EMP_3 .. EMP_100 is backup table.Now whenever i am making the any changes in EMP table, then i should take the backup of exiting EMP table and backup data should go to EMP_101 (as last table with sequence is EMP_100).


Huh, what a strange requirement! Suppose that your EMP table contains several millions of records (not likely for employees, but - generally speaking ...). You update one column value in one of these records and you want to create a backup table for ALL records, millions of them, just because one tiny information changed?

Why wouldn't you, rather, backup only records that were changed? Besides, you don't create any number of tables in order to do that; a single table, along with a database trigger, could handle it (I suppose).
Re: Query to find relevant Table name [message #574858 is a reply to message #574855] Wed, 16 January 2013 03:27 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Michel, Though we can achieve it using below.
SELECT SUBSTR(TABLE_NAME,
                          DECODE(INSTR(TABLE_NAME, '_'),
                                 0,
                                 LENGTH(TABLE_NAME),
                                 INSTR(TABLE_NAME, '_')) + 1),
                   A.*
              FROM (SELECT T.TABLE_NAME,
                           ROW_NUMBER() OVER(ORDER BY T.TABLE_NAME DESC) AS ROW_NR,
                           MIN(TABLE_NAME) OVER() AS ORIGINIALL_TABLE
                      FROM USER_TABLES T
                     WHERE TABLE_NAME LIKE --TAB_NAME||
                           'EMP%') A --WHERE ROW_NR = 1
             ORDER BY TO_NUMBER(NVL(SUBSTR(TABLE_NAME,
                                           DECODE(INSTR(TABLE_NAME, '_'),
                                                  0,
                                                  LENGTH(TABLE_NAME),
                                                  INSTR(TABLE_NAME, '_')) + 1),
                                    0))
Re: Query to find relevant Table name [message #574859 is a reply to message #574856] Wed, 16 January 2013 03:33 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Hi , Yes are correct.But we will keep these backup tables only for 1 month.Then we may take take physically backup and then remove later on point permanently.We are actually doing migeration of data from a legacy (Old system) to our product.So after after completing the activities, we may be dropping the tables permanently.
Re: Query to find relevant Table name [message #574876 is a reply to message #574858] Wed, 16 January 2013 08:37 Go to previous message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel, Though we can achieve it using below.


See my query, isn't it much smaller?

Regards
Michel
Previous Topic: Sql for previous day/ day before previous day
Next Topic: need to find out db name for the indexes
Goto Forum:
  


Current Time: Tue Oct 21 11:43:12 CDT 2014

Total time taken to generate the page: 0.17762 seconds