-
How To get the max(Primary key)
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
Last edited by cool_tas; 01-27-2006 at 07:49 AM.
-
procedure
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;
-
-- 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
-
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
-
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
-
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
-
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
-
Originally Posted by 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
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
"What is past is PROLOGUE"
-
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
-
set SERVEROUTPUT on;
set feedback off;
declare
max_cnt number;
begin
for TAB_CUR in (select C.TABLE_NAME,CC.COLUMN_NAME
from ALL_TAB_COLUMNS TC,ALL_CONSTRAINTS C,ALL_CONS_COLUMNS CC
where tc.owner=C.OWNER
and C.OWNER=CC.OWNER
and tc.table_name=c.table_name
and C.TABLE_NAME=CC.TABLE_NAME
and tc.COLUMN_NAME=cc.COLUMN_NAME
and C.OWNER='USER'
and C.CONSTRAINT_TYPE='P'
and tc.DATA_TYPE='NUMBER'
and rownum<=10)
LOOP
max_cnt:=0;
execute immediate 'select max('||tab_cur.column_name||') from '|| tab_cur.table_name into max_cnt;
DBMS_OUTPUT.PUT_LINE(TAB_CUR.TABLE_NAME||'-'||MAX_CNT);
end LOOP;
end;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|