Home » SQL & PL/SQL » SQL & PL/SQL » How to select the MAX(PK_ID) value in a procedure (Oracle 10.2.0.3)
How to select the MAX(PK_ID) value in a procedure [message #382299] Wed, 21 January 2009 17:53 Go to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
Hi everyone- this seems like a perhaps silly question, but I'm racking my brain to figure it out...

What I'm trying to do is to write a procedure that will select the highest numerical value used as a primary key for each table in my schema, increment this value by 1, and then use this value in order to synch up my sequences.

Keep in mind that this value is different from the number of rows in a table.

So, say for example, you have a table with 3 rows, and the primary key values are 1, 3, and 800.

...As I iterate over all of the tables in my schema, when it gets to the table described above, the procedure I want will be sure that the next sequence value will be reset to 801. (Not 3, which is the number of rows in the table).

...Currently the problem is that as we've been moving data around, sometimes the sequences will go out of synch, and I have to either recreate them or increment them each manually/individually.

Any help would be greatly appreciated.

Thanks!!
Re: How to select the MAX(PK_ID) value in a procedure [message #382302 is a reply to message #382299] Wed, 21 January 2009 18:22 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
select MAX(PK_ID) into max_pk from TABLEA;
Re: How to select the MAX(PK_ID) value in a procedure and synchronize sequences [message #382304 is a reply to message #382299] Wed, 21 January 2009 19:56 Go to previous messageGo to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
No- duh. ...I know that.
The point here is to synchronize all of the sequences for a particular schema in one shot (or procedure run).
Otherwise, I would just manually
select max(pk_column_id) from table;

and then manually
alter sequence <owner>.<sequence_name> increment by <max_pk_val - current_val>

--------------
In any case- I think I've got it figured out.
The example presumes that all of your sequences are named the same as your tables, except with a '_SQ' at the end of it.
So, for example, the EMPLOYEE table would have a sequence named EMPLOYEE_SQ.
It also presumes that your sequences are monotonically incrementing from the value of 1 upward.

Here's the code:

CREATE OR REPLACE PROCEDURE SYS.P_SYNCHRONIZE_SEQUENCES (schema_owner dba_users.username%TYPE default 'SCOTT') AS
 v_max_val  number default null;
 v_next_val number default null;
 v_count    number default 0;
BEGIN
 for the_tables in 
 (select c.table_name as table_name, c.owner as owner, i.column_name as column_name, c.table_name||'_SQ' as sequence_name
  from dba_constraints c, dba_ind_columns i
 where c.index_name = i.index_name AND c.owner = schema_owner AND c.constraint_type = 'P')
 loop
  execute immediate 'select max('||the_tables.column_name||') from ' || the_tables.table_name INTO v_max_val;

  if v_max_val < 1 then
  v_max_val := 1;
  end if;
  
  execute immediate 'select '||the_tables.owner||'.'||the_tables.sequence_name||'.nextval from dual' INTO v_next_val;

  if v_next_val < v_max_val then
   v_next_val := (v_max_val - v_next_val);
   execute immediate 'alter sequence '||the_tables.owner||'.'||the_tables.sequence_name||' increment by '||v_next_val;
   v_count := v_count + 1;
  end if;
   
 end loop;
 
  dbms_output.put_line('Synchronized '||v_count||' sequences for schema owner: '||schema_owner);
  
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 
END;
/
Re: How to select the MAX(PK_ID) value in a procedure [message #382305 is a reply to message #382299] Wed, 21 January 2009 20:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>The point here is to synchronize all of the sequences for a particular schema in one shot (or procedure run).


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

You did not post expected/desired results.
I still do not understand what you mean/meant by "synchronize";
I do not understand the business purpose for doing so.

Not all tables will have the same number of rows, so I suspect that after some brief period of time that the sequences will no longer be "synchronized".

If you are satisfied with your solution, then that is all that matters.

Never confuse movement with progress.
Going around in circles is movement;
but many folks would not consider it to be progress.

I'll concede your code accomplishes movement.
Time will tell if progress exists in 30 - 60 days.


Re: How to select the MAX(PK_ID) value in a procedure [message #382309 is a reply to message #382299] Wed, 21 January 2009 22:18 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi stevekerver,

Similar type of question is asked in this blog, and it has been described beautifully in

http://www.orafaq.com/forum/t/138600/0/

Thanks
Trivendra
Previous Topic: Create table with email body
Next Topic: Prasing a text field (merged)
Goto Forum:
  


Current Time: Sat Dec 03 06:06:50 CST 2016

Total time taken to generate the page: 0.10322 seconds