Home » SQL & PL/SQL » SQL & PL/SQL » Logic to find table names and data in oracle (Oracle 10g)
Logic to find table names and data in oracle [message #576233] Fri, 01 February 2013 10:35 Go to next message
manoj12
Messages: 176
Registered: March 2008
Location: India
Senior Member
Hi ,

I have a schema DEF and I have a column_name CREATE_DATE.

I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.

Can you explain me what would be the logic for it.

Please help me sir as I have been stuck?

Thanks & Regards
Manoj
Re: Logic to find table names and data in oracle [message #576234 is a reply to message #576233] Fri, 01 February 2013 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Logic to find table names and data in oracle [message #576237 is a reply to message #576233] Fri, 01 February 2013 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Generate a script (from user_tab_columns) that will contain a SELECT for each table containing this table and execute it.

Regards
Michel

[Updated on: Fri, 01 February 2013 11:33]

Report message to a moderator

Re: Logic to find table names and data in oracle [message #576238 is a reply to message #576237] Fri, 01 February 2013 12:09 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Have a look at this example; I'm searching for number of records in every table whose HIREDATE column's value is before 01.04.1982 (dd.mm.yyyy).
SQL> select hiredate from emp order by 1;

HIREDATE
----------
17.12.1980    *  these records
20.02.1981    *  satisfy
22.02.1981    *  the condition
02.04.1981
01.05.1981
09.06.1981
08.09.1981
28.09.1981
17.11.1981
03.12.1981
03.12.1981
09.12.1982
23.12.1982
12.01.1983

14 rows selected.

SQL> DECLARE
  2    l_str VARCHAR2(500);
  3    l_cnt NUMBER := 0;
  4  BEGIN
  5    FOR cur_r IN (SELECT u.table_name, u.column_name
  6                  FROM user_tab_columns u
  7                  WHERE u.column_name = 'HIREDATE'
  8                 )
  9    LOOP
 10      l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
 11              ' WHERE ' || cur_r.column_name || ' < date ''1981-04-01''';
 12
 13     EXECUTE IMMEDIATE (l_str) INTO l_cnt;
 14
 15      IF l_cnt > 0 THEN
 16        dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
 17      END IF;
 18    END LOOP;
 19  END;
 20  /
3 : EMP

PL/SQL procedure successfully completed.

SQL>

So: 3 records in EMP table satisfy that condition.

See if you can adjust it according to your needs.

[Updated on: Fri, 01 February 2013 12:12]

Report message to a moderator

Re: Logic to find table names and data in oracle [message #576284 is a reply to message #576238] Sat, 02 February 2013 07:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Querying user_tab_columns will check not just tables but also views. Also, you would need to exclude MV container tables, MV log tables, IOT overflow tables, etc. Anyway, below is same without PL/SQl:

with t as (
           SELECT  u.table_name,
                   u.column_name,
                   extractvalue(
                                xmltype(
                                        dbms_xmlquery.getxml(
                                                             'select count(*) cnt from "' || u.table_name ||
                                                             '" where "' || u.column_name ||
                                                             '" < date ''2009-01-01'' and rownum = 1'
                                                            )
                                       ),
                               '/ROWSET/ROW/CNT'
                              ) cnt
             FROM  user_tab_columns u
             WHERE u.column_name = 'HIREDATE'
          )
select  table_name,
        column_name
  from  t
  where cnt = '1'
/

TABLE_NAME                     COLUMN_NAME
------------------------------ -------------
EMP                            HIREDATE
EMP1                           HIREDATE

SQL>


SY.
Re: Logic to find table names and data in oracle [message #576287 is a reply to message #576284] Sat, 02 February 2013 08:54 Go to previous message
manoj12
Messages: 176
Registered: March 2008
Location: India
Senior Member
Thanks a lot sir.I got a very good solution sir from you
Previous Topic: Re: ORA-06530: Reference to uninitialized composite (split from hijacked thread by bb)
Next Topic: Cursor
Goto Forum:
  


Current Time: Fri Oct 24 19:50:17 CDT 2014

Total time taken to generate the page: 0.10002 seconds