?
Solved

How to find the last record of each tables in a schema - oracle database

Posted on 2011-10-28
31
Medium Priority
?
414 Views
Last Modified: 2012-05-12
Hi,

can you tell me, how to find the last records of every tables in schema, as a single list

E+g:

Table 1 - rsult1
Table 2 - rsult2
Table 3 - rsult3

I am writing query like this,


SELECT ADDRESS_RN, UPDATED
FROM MAISY.ADDRESS
WHERE ADDRESS_RN =(SELECT MAX(ADDRESS_RN) FROM MAISY.ADDRESS)

SELECT ALERTG_RN , LAST_UPDATED
FROM MAISY.ALERTG
WHERE ALERTG_RN =(SELECT MAX(ALERTG_RN) FROM MAISY.ALERTG)

SELECT ALERTGU_RN
FROM MAISY.ALERTGU
WHERE ALERTGU_RN =(SELECT MAX(ALERTGU_RN) FROM MAISY.ALERTGU)

but I do not know, how to integrate results a single list, through one sql script or function in ORACLE 9I.
the tables do not have any common column, but their primary key is TableName_RN.

Any Suggestion?

Many Thanks
0
Comment
Question by:kevincox29
  • 13
  • 10
  • 8
31 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044222
Add a union all between them.


SELECT ADDRESS_RN, UPDATED
FROM MAISY.ADDRESS
WHERE ADDRESS_RN =(SELECT MAX(ADDRESS_RN) FROM MAISY.ADDRESS)
union all
SELECT ALERTG_RN , LAST_UPDATED
FROM MAISY.ALERTG
WHERE ALERTG_RN =(SELECT MAX(ALERTG_RN) FROM MAISY.ALERTG)
union all
SELECT ALERTGU_RN
FROM MAISY.ALERTGU
WHERE ALERTGU_RN =(SELECT MAX(ALERTGU_RN) FROM MAISY.ALERTGU)
0
 

Author Comment

by:kevincox29
ID: 37044247
slightwv,

IT GIVES A - ORA-01789: query block has incorrect number of result columns, ERROR MESSAGE. THE TABLES DON'T HAVE ANY COMMON COLUMN

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044281
>>ORA-01789: query block has incorrect number of result columns

I missed the last query.

For a union all, all the queries need the same number of results and data types.

The last query only has one column.  You need to add a column to that qyery.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044284
Looking just at the columns selected, see the last one is missing a second column:

SELECT ADDRESS_RN, UPDATED
SELECT ALERTG_RN , LAST_UPDATED
SELECT ALERTGU_RN
0
 

Author Comment

by:kevincox29
ID: 37044443
the table does not have any 'DATE' COLUMN- IS there any other way to union it.
how can I get the table name form the  Select query?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044453
Just use null:


SELECT ADDRESS_RN, UPDATED
...
SELECT ALERTG_RN , LAST_UPDATED
...
SELECT ALERTGU_RN, null
...
0
 

Author Comment

by:kevincox29
ID: 37044486
HOW TO GET A table name in a select query?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37044506
also,  should be more efficient to use rank analytic than double query each table  with MAX subquery

SELECT *
  FROM (SELECT address_rn, updated, RANK() OVER (ORDER BY address_rn DESC) r FROM maisy.address)
 WHERE r = 1
UNION ALL
SELECT *
  FROM (SELECT alertg_rn, last_updated, RANK() OVER (ORDER BY alertg_rn DESC) r FROM maisy.alertg)
 WHERE r = 1
UNION ALL
SELECT *
  FROM (SELECT alertgu_rn, NULL, RANK() OVER (ORDER BY alertgu_rn DESC) r FROM maisy.alertgu)
 WHERE r = 1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37044509
>>> HOW TO GET A table name in a select query?

is this a new question? - and in any case, what do you mean?
0
 

Author Comment

by:kevincox29
ID: 37044510
I want the results like this,

Table-name   RN value   updated Date

Address         adrres_rn               10/10/2011
Alert                aletr_rn                   11/10/2011
--
---

How can I  Modify the sql query?  
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37044521
ah I understand now - thanks for the example


SELECT *
  FROM (SELECT 'Address' table_name,
               address_rn rn_value,
               updated updated_date,
               RANK() OVER (ORDER BY address_rn DESC) r
          FROM maisy.address)
 WHERE r = 1
UNION ALL
SELECT *
  FROM (SELECT 'Alertg',
               alertg_rn,
               last_updated,
               RANK() OVER (ORDER BY alertg_rn DESC) r
          FROM maisy.alertg)
 WHERE r = 1
UNION ALL
SELECT *
  FROM (SELECT 'Alertgu',
               alertgu_rn,
               NULL,
               RANK() OVER (ORDER BY alertgu_rn DESC) r
          FROM maisy.alertgu)
 WHERE r = 1
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044524
For this example probably the best way is to hard code it.

SELECT 'ADDRESS' table_name, ADDRESS_RN, UPDATED
...


There are views you can query but since you already know what tables you are selecting from, use a literal string.


I should probably mention it now:
You can get tables and columsn form the databsae views looking for all tables with a column ending in '_RN', then write some SQL to get the MAX of that column all dynamically but that is a LOT of work for something that can easily be done brute force.

0
 

Author Comment

by:kevincox29
ID: 37044596

Table-name     RN value                 updated Date

Address         1111112                10/10/2011
Alert                1234568                11/10/2011

 I wan the table name in the first column of the result.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37044608
Yes,  http:#37044521  does that and also eliminates the extra io of the MAX subqueries
0
 

Author Comment

by:kevincox29
ID: 37045105
Well. sdstuber, slightwv:

your ideas are fine, but the problem is I have more than 500 tables in the schema. My sql script is going to be a ver BIG one.

Is there  anyway to achieve this through ,  SYS.DBA_TABLES or any other SYS properties or something?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 37045472
USER_TAB_COLUMNS has the tables and columns.

Below are some samples.  The 'easy' way is 10g and above.  The 9i is a little more complex.  I used pipelined functions.

The examples do not have the 'DATE' column because it would make things more difficult since there is no naming convention.  

you can probably tweak the code by looking for a column like '%UPDATE%' that is a DATE datatype but I don't have the time for that example right now.
create table test_table_1(col1_rn  number);
create table test_table_2(col1  number);
create table test_table_3(col1_rn  number);

insert into test_table_1 values(1);
insert into test_table_1 values(2);
insert into test_table_2 values(9);
insert into test_table_3 values(3);
insert into test_table_3 values(4);
commit;

drop type myTab;
drop type myRow;

create or replace TYPE myRow IS object ( 
      table_name varchar2(50),
      max_rn_value VARCHAR2(50)
      );
/

show errors

create or replace type myTab is table of myRow;
/

show errors


create or replace function get_my_max_vals return myTab PIPELINED
  as
  out_rec myRow := myRow(null,null);
  begin

  		for i in (select table_name, column_name from user_tab_columns where column_name like '%_RN') loop
		execute immediate ' select ''' || i.table_name || ''', max(' || i.column_name || ') from ' || i.table_name
			into out_rec.table_name, out_rec.max_rn_value;

         pipe row(out_rec);
         end loop;
     return;
  end;
/

select * from table(get_my_max_vals);


--10g and above
SELECT table_name,
       TO_NUMBER(
           EXTRACTVALUE(
               xmltype(
                   DBMS_XMLGEN.getxml(
                          'select max(' || column_name || ') X from '
                       || table_name
                   )
               ),
               '/ROWSET/ROW/X'))
           max_col
  FROM user_tab_columns where column_name like '%_RN'
/

Open in new window

0
 

Author Comment

by:kevincox29
ID: 37045600
Hi ,
Does this work with oracle 9.2?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37045635
The pipelined function should.

Now that I think about it, try the XML one.  I mix up my older versions but XML was around back in the 9i days.

Now I cannot think of why it wouldn't work.  I don't have any 9i left around to test it.
0
 

Author Comment

by:kevincox29
ID: 37045729
we use oracle 9, and above function only find 6 columns- but didn't give the  maximum values
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37045742
Can you add examples to my test case to show where it fails?
0
 

Author Comment

by:kevincox29
ID: 37045875
RESULT FOR select * from table(get_my_max_vals);


TABLE_NAME                               MAX_RN_VALUE                            
---------------------------------------- ----------------------------------------
CLINIC_TEMPLATE                                                                  
LOGIN3                                                                          
LOGIN3                                                                          
MEDS_REPRINT                                                                    
MEDS_REPRINT                                                                    
MEDS_REPRINT                                                                    
MEDS_REPRINT                                                                    
MEDS_REPRINT                                                                    
MEDS_REPRINT                                                                    

9 rows selected

it's wrong- because there are more than 500 tables with RN as a column.

SELECT table_name,
       TO_NUMBER(
           EXTRACTVALUE(
               xmltype(
                   DBMS_XMLGEN.getxml(
                          'select max(' || column_name || ') X from '
                       || table_name
                   )
               ),
               '/ROWSET/ROW/X'))
           max_col
  FROM user_tab_columns where column_name like '%_RN'

results a ORA-00904: invalid column name ERROR



 
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046208
>>because there are more than 500 tables with RN as a column.

that 'end' with _RN or have RN in them?

Post the results of:
select table_name from user_tab_columns where column_name like '%_RN';

Are the tables in a different_schema?

If so, change user_tab_columns to all_tab_columns but you need to be careful here:  all_ level views are ALL objects the user can see.

>>results a ORA-00904: invalid column name ERROR

Maybe it isn't 9.2 compatible.  It ran in my 10.2 test case.  Oh well.
0
 

Author Comment

by:kevincox29
ID: 37056016
Basically, all the tables are from a schema.

when I run ,
select table_name from all_tab_columns where column_name like '%_RN';

I get all the tables, which I wanted. However, when I edit the function to

create or replace function get_my_max_vals return myTab PIPELINED
  as
  out_rec myRow := myRow(null,null);
  begin

              for i in (select table_name, column_name from all_tab_columns where column_name like '%_RN') loop
            execute immediate ' select ''' || i.table_name || ''', max(' || i.column_name || ') from ' || i.table_name
                  into out_rec.table_name, out_rec.max_rn_value;

         pipe row(out_rec);
         end loop;
     return;
  end;

still I am getting the only the 9 results, I did not get all the tables. How do I need to change the function?

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37056102
is the owner of your function the same as you?  and, do you have access to those tables via roles?

ALL_TAB_COLUMNS - returns results you have access to,  "you" in this case will be the owner of the function, not the person running the function.
privileges granted through roles don't apply inside the function
0
 

Author Comment

by:kevincox29
ID: 37056201
OK. that works fine- for the admin login. my question is,

There are more than one column has _RN in some table. but ALL THE TABLE HAS PRIMARY KEY AS tablename_RN.

As an example, Address table has the Primary key as ADDRESS_RN ,
 but it also has EPNUMBER_RN as well.
, ALERT table has it's primary as ALERT_RN.
EMPLOYEE table has it's primary key EMPLOYEE_RN and it also has ADDRESS_RN as a column

How to get only the PRIMARY key maximum RN. based on this , I would like to have the last records for all the table as a list.

how do I change the function?
0
 

Author Comment

by:kevincox29
ID: 37056321
OK. i got the MAXIMUM  RN numbers of the primary key as the list , HOW DO  i get the LAST RECORDS based on the RN value.

I want the list as,

Table name - last record

Address -   last record of ADDRESS table
Alert - last record of ALERT table

etc,

How can I?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37056353
how are you identifying the last record?  do you have a date column?  or are you assuming max RN column is last?

for previous,  you can use all_cons_columns and all_constraints to determine which columns are part of the primary key

SELECT *
  FROM all_constraints c, all_cons_columns cc
 WHERE c.constraint_type = 'P' AND c.owner = cc.owner AND c.constraint_name = cc.constraint_name
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37056397
also,  since your are using the ALL* views,  you'll be looking at multiple schemas,  but your results don't include the schema.

Either switch to USER, so you only look in a single schema (the same schema that owns the function) or include the table owners in your results
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37056401
and, last,  you can't have a single sql statement return a row from every table  unless all of those tables have identical structures.

that is to say... this doesn't make sense in SQL


Address -   last record of ADDRESS table
Alert - last record of ALERT table
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37056866
What is the 'real' requirement here?  This question has changed several times.

What is this information for?
Are you wanting to write some sort of auditing app?  

If auditing:  
Oracle has auditing capability built in.  If you do not want to use it, triggers might be a better approach.


Like I mentioned in http:#a37045472 without some formal naming convention this approach can get complicated.  You then threw in the 'multiple columns ending in _RN and I only want the PK' complication.

After that the 'last value' for some arbitrary column.  If you cannot pick out the column by data type or column name, you need to hard-code the column somewhere.

Either in some cross-reference table you dynamically build the SQL from in the procedure or just go back to the UNION ALL method and keep the script up-to-date manually.

0
 

Author Closing Comment

by:kevincox29
ID: 37083975
The code does not support with oracle 9.2 , but author's idea is fine.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question