I have a requirement where I need to get the max(primary key ) of all tables in database.
I have an option to manually write a query like:
select max(primary_key1) from table1;
select max(primary_key2) from table2;
But I want to avoid doing that.
Do we have any better way of doing it.
Also I need to create a spool file on some other machine, whose IP is known.
Generally when we spool a file we write:
How can I locate this spool file on other machine.
Here is an example procedure to generate SQL to find the maximum value for every column in all the primary keys in your current schema. If you want this for every schema replace the references to USER_ views with DBA_ . In that case you will probably want to filter out SYS owned tables etc.
So you could SET SERVEROUTPUT ON, set a spool file, then run the below procedure. Edit the spool file to get rid of the junk lines then execute it.
FOR c1_rec IN
(select * from user_constraints where constraint_type = 'P')
-- Since a primary key can be composed of
-- multiple columns, build SQL for each one
FOR c2_rec IN
(SELECT COLUMN_NAME FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = c1_rec.constraint_name
ORDER BY POSITION)
-- Now build SQL to get max value for each column and output
stmt := 'SELECT MAX(' || c2_rec.column_name || ') FROM ' || c1_rec.table_name;
The procedure was meant as an example to the original poster on how to do this, not to give them a procedure that handles every possible situation. It works fine for single column PKs which may be all they need. Generating individual selects for each column in the primary key was probably not the best example, but it showed them how to loop through the constraint column view to get each column in the PK.