Updating an oracle sequence via plsql
Updating an oracle sequence via plsql - gta 4 dating service
An optional output variable is currently commented out but could be enabled if you want the resulting value the sequence will assign next.Here’s the code (not sure whether formatting will come through though: — Author: Ben Martinka 8/9/13 CREATE or REPLACE PROCEDURE reset_seq( — p_nxt out number, p_tbl in varchar2, p_new in number DEFAULT -1 — p_min in number DEFAULT 0 ) IS l_new number; l_nxt number; l_cnt number; l_inc number; l_min number; l_key varchar2(256); l_seq varchar2(256); BEGIN — Derive primary key & sequence names from table name — according to database design standards.
Before doing the migration of the Oracle production database, the DBA or application developer should install a SQL Server test database.
create or replace procedure reset_sequence(p_seq in varchar2) is l_value number; begin -- Select the next value of the sequence execute immediate 'select '
I worry about how ETL tools apply updates (did you know Data Stage applys updates singly, but batches inserts in arrays? The two most common forms of Bulk Updates are: Case 1 is uninteresting.
I’ve taken your script to the next level and added parameters that allow you to reset the sequence to any specified value zero or greater, or specifying a negative value will reset the sequence to resume with the next higher integer than the maximum value in the specified table, with sequence and primary key names derived from the table name.
You can change those initializations to suit your own standard, or could add them as input variables if desired.
l_key := p_tbl