Update Oracle sequences

Previous Next

This action is now deprecated. USoft 9.1 import routines deal with database sequence updates (both on Oracle and SQL Server) automatically. You only ever need this action if, for some reason, you plan to perform INSERTs of records directly against the RDBMS.

This action applies only to the Oracle RDBMS. You must have Oracle software installed that allows you to call sqlplus from the command line. Delivery Manager does not offer this facility for SQL Server.

1.Creates an Oracle SQL script for resetting the current value of Oracle sequences used by USoft applications in "Database account" for generating unique numbers. This Oracle SQL script is named:

sequences-exec.500.post.rdbms.sql

 

and is placed in the folder indicated by the required "Working directory" parameter.
2.Executes this script in "Database account" if "Execute script" is set to Yes.

 

The purpose of resetting the sequences is to avoid problems with INSERTs after data have changed as a result of data import actions. If a SEQ_PERSON sequence is used for generating unique person ID numbers, and the highest person ID in the current data is 1500, the current value of the sequence is reset to 1501:

drop sequence SEQ_PERSON;                                                                                                                                                                                                                  

create sequence SEQ_PERSON start with 1501;

 

This type of problem is automatically prevented if you perform the INSERT through USoft.

The operation applies to all Oracle sequences in "Database account", not only for unique numbers for end user application tables, but also for unique numbers for tables of USoft applications such as USoft Definer.

Parameters

Name

Type

Optional?

Database account

Database account

No

Working directory

Folder or file

No

Execute script

Option flag

Yes

Script files

The operation uses the required "Working directory". When the action is run, 4 SQL scripts with suffix "500.post.rdbms.sql" are written to this folder, overwriting earlier versions if they exist:

Short name

File Name

Type

make

sequences-make.500.post.rdbms.sql

Input file

use

sequences-use.500.post.rdbms.sql

Input file

temp

sequences-temp.500.post.rdbms.sql

Interim spool file

exec

sequences-exec.500.post.rdbms.sql

Executable script

 

The make and use input files contain the instructions necessary to execute the operation.

The temp file is an interim spool file.

The exec file contains the SQL commands for updating the Oracle sequences.

The temp and exec files are generated each time the make file is run.

Running from the command line

You can (re)generate the exec file, away from USoft Delivery Manager, in Oracle SQL*Plus by running the make script from the command line. Pass the working directory as an additional parameter:

sqlplus username/password@connection working-dir/sequences-make.500.post-rdbms.sql working-dir
 

To execute immediately, uncomment the following code line in the make file:

/* start &&1\sequences-exec.500.post.rdbms.sql */

 

Or you can execute separately, by giving the command:

sqlplus username/password@connection working-dir/sequences-exec.500.post-rdbms.sql