How To get the max(Primary key)

[cool_tas]

Hi all,

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:
spool c:/Records_Count/STAGING_TABLE_COUNT

How can I locate this spool file on other machine.

Any help is appreciated,
Thanx

[jrich1]

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.

JR



DECLARE
stmt varchar2(200);
c1_rec user_constraints%ROWTYPE;
c2_rec user_cons_columns%ROWTYPE;

BEGIN
FOR c1_rec IN
(select * from user_constraints where constraint_type = 'P')
LOOP
-- 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)
LOOP

-- Now build SQL to get max value for each column and output
stmt := 'SELECT MAX(' || c2_rec.column_name || ') FROM ' || c1_rec.table_name;
dbms_output.put_line(stmt);

END LOOP;

END LOOP;
END;

[tamilselvan]

-- Since a primary key can be composed of
-- multiple columns, build SQL for each one

Who told you ?
Or
Where did you read it?

Tamil

[jrich1]

Lots of examples in Oracle Doc on creating a multicolumn primary key constraint, just check the master index.

Here is an example:

SQL> create table test1 (col1 number, col2 number);

Table created.

SQL> alter table test1 ADD (CONSTRAINT test1_pk PRIMARY KEY (col1,col2));

Table altered.

SQL> select table_name, constraint_type from user_constraints where constraint_name = 'TEST1_PK';

TABLE_NAME C
------------------------------ -
TEST1 P

SQL> select * from user_cons_columns where constraint_name = 'TEST1_PK';

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- ------------------ ---------- ------------ ----------
SCOTT TEST1_PK TEST1 COL1 1
SCOTT TEST1_PK TEST1 COL2 2

SQL>

JR

[tamilselvan]

And how this code helps?
-- Now build SQL to get max value for each column and output
stmt := 'SELECT MAX(' || c2_rec.column_name || ') FROM ' || c1_rec.table_name;
dbms_output.put_line(stmt);

You consider only one column.

Tamil

[jrich1]

Using table created in example above, this is what I get when I run the code...

SQL> set serveroutput on
SQL> /
SELECT MAX(DEPTNO) FROM DEPT
SELECT MAX(EMPNO) FROM EMP
SELECT MAX(COL1) FROM TEST1
SELECT MAX(COL2) FROM TEST1

PL/SQL procedure successfully completed.

SQL>

There are two loops in the procedure, one to step through the PK constraints, and using the constraint name found we then loop through all the columns in the constraint generating the SQL he wants.

JR

[tamilselvan]

You did not get what I am indirectly saying.
When a table has a PK that has 2 columns, your SQL will not work or will give wrong result.

Let us say PK is on 2 columns - both are varchar2. The SQL I expected:
Select max(pk_col_1), max(pk_col_2) from my_table.
OR
select max(pk_col_1||pk_col_2) from my_table.

And so on ....

Tamil

[dbasan]

You did not get what I am indirectly saying.
When a table has a PK that has 2 columns, your SQL will not work or will give wrong result.

Let us say PK is on 2 columns - both are varchar2. The SQL I expected:
Select max(pk_col_1), max(pk_col_2) from my_table.
OR
select max(pk_col_1||pk_col_2) from my_table.

And so on ....

Tamil

It will not create as you have listed but it will do as:

select max(pk1) table1
select max(pk2) table1
select max(pk1) table2
select max(pk2) table2
select max(pk3) table2

[jrich1]

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.

JR