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

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)

slightwv,

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

>>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.

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

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?

Just use null:


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

HOW TO GET A table name in a select query?

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

>>> HOW TO GET A table name in a select query?

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

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?  

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

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.

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.

Yes,  http:#37044521  does that and also eliminates the extra io of the MAX subqueries

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?

Hi ,
Does this work with oracle 9.2?

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.

we use oracle 9, and above function only find 6 columns- but didn't give the  maximum values

Can you add examples to my test case to show where it fails?

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



 

>>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.

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?

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

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?

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?

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

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

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

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.

The code does not support with oracle 9.2 , but author's idea is fine.