Adding an SQL Script to a Project

MS SQL Client is required on all cluster nodes in order to allow deployment of packages with SQL script items. See Requirements/Prerequisites.

The SQL script added to the Project will be included in the future Package in order to be executed on the Destination database after deployment.

Adding an SQL Script requires specifying its SQL script name (used to label the script in the Project), Target database (the destination database the script should be executed on: DELMIA Apriso database or Framework database), Target database type (enables determining the type of the target database, SQL, Oracle or both of them, on which the script can be run. If the target database type is different from the selected, the script will not be executed), and SQL script content.

The Script can either be loaded from a file or built with the help of an SQL Script Editor.

Option	Description
Load from file	If selected, the SQL script will be loaded from file. You can choose one of the options: Reload on each package generation – the script will be reloaded each time a package is created basing on the current Project. Load once – the script contents will be loaded once and then reused on each package generation.
Create new	If selected, you can enter the SQL Script in the SQL Script Editor (use the SQL Script Editor button. For details, see Item Properties).

Script Validation

The script will not run if it is not validated and properly formatted. Depending on the format of the database (Oracle or SQL) it should be validated in the SQL*Plus or sqlcmd.

Additional Spaces

If there are additional spaces in the script and the script is not ended with "/" character, it will not run properly.

"/"- slash mark (on Oracle only) shows a command line SQL interpreter where the script ends. It should be added after procedure's execution or anonymous blocks. If the user puts it after a single UPDATE/INSERT/SELECT/DELETE statement, it gets executed twice, e.g.:

One row is created

INSERT INTO MyTable(....) VALUES(....);

Two rows are created

INSERT INTO MyTable(....) VALUES(....);
/

Non-ascii Characters in Scripts

In case of using non-ascii characters in scripts:

MS SQL: make sure the script is saved in Unicode format (UTF-8 with signature), Codepage 65001 (all Unicode strings should be preceded with an "N" prefix).
Oracle: it is possible to use explicit non-ascii characters but the script has to be saved in UTF-8 without BOM encoding. If you use ASCII encoding, it is recommended to use UNISTR function for this purpose.
For example: UNISTR('Fran\00E7ais') provides 'Français', where \00E7 is the hexadecimal value of a character 'ç' in UTF-16/UCS-2 encoding format.

Use of HTML entities (such as  )

When used in stored procedures, can cause timeout errors. be avoided. The problem stems from the limitation of the sqlplus tool.