Page Comparison

A collection of reusable code, programs, and database objects that provides common functionality across all E-Business products.

Object Type Identification

...

Files ending in file extensions added to the EBS SQL Extensions (`FDEBS_SQL_EXTENSIONS)` project property are also added.
EBS SQL Extensions property defaults to seq,tbl,idx,typ,tps,tpb,sql,pkh,plb,pks,pkb,pls,pck,fnc,trg,spc,bdy,prc.
Tip
Files with .sql extension are identified as this type only AFTER excluding them as Concurrent Program SQLs (by source folder).
Files that start with "SCHEMA" will automatically update the user and password properties accordingly. See SQL File Considerations for more information.

File Extensions

Name	Description
.seq,tbl,idx,typ,tps,tpb,sql,pkh,plb,pks,pkb,pls,pck,fnc,trg,spc,bdy,prc	A file containing sql statements. Extensions may be customized using

the FDEBS_SQL_EXTENSIONS

the SQL Extensions project property.

Object Type

Name	Code
DB Objects (SQLs)	`SQL`

Object Type Attributes

Name	Code	Description	Default Value	Supported Values
Source	`SOURCE`	Object Source Location Type	SCM	SCM
Target Location	`TARGET_LOCATION`	Path to where the file should be deployed to.	$<PROD_TOP>/patch/115/sql/
Type	`TYPE`	DB Object Type	Generic SQL
DB User Property	`DB_USER`	FlexDeploy property to use as the user to connect to the database with.	`FDEBS_DB_USER` You can use file name like SCHEMASCHEMANAME_FILENAME.sql. For example, SCHEMAPOS_registertrans.tbl. In this case this will default to `FDEBS_DB_SCHEMAPOS_USER`.
DB Password Property	`DB_PASSWORD`	FlexDeploy property to use as the password for the DB User Property.	`FDEBS_DB_PASSWORD` You can use file name like SCHEMASCHEMANAME_FILENAME.sql. For example, SCHEMAPOS_registertrans.tbl. In this case this will default to `FDEBS_DB_SCHEMAPOS_PASSWORD`.
Ignore Failure	`IGNORE_FAILURE`	Check if failure in file should be ignored

.@Since 4.6

.

0.3

	If file contains WHENEVER SQLERROR CONTINUE case insensitively, this property will default to true, and ignore failures even if box is unchecked.
Fix SQL Syntax	`FIX_SQL_SYNTAX`	If there is need to add / at the end of file, FlexDeploy will add it at deploy time. For example, if there is program block as last statement, we need / and new line after it.

@Since 4.6.0.3

True

	True
Target File Permission	`FILE_PERMISSIONS`	Permissions to apply to the file after it is deployed	Defaults to the project property FDEBS_FILE_PERMISSIONS

Related Project Properties

Name	Code	Description	Default Value

EBS

SQL Root Source Directory

FDEBS_SQL_ROOT_SOURCE_DIR

Source folder for sql and pls files (e.g. db).

sql

EBS

SQL Root Destination Directory

FDEBS_SQL_ROOT_DESTINATION_DIR

Target directory for SQL and PLS files (e.g. XXHR_TOP/admin/sql).

$<PROD_TOP>/patch/115/sql

EBS

SQL Extensions

FDEBS_SQL_EXTENSIONS

SQL file extensions in their order to process.

seq,tbl,idx,typ,tps,tpb,sql,pkh,plb,pks,pkb,pls,pck,fnc,trg,spc,bdy,prc

See Database SQL file extensions and their order of process.

EBS

SQL Ignore Compile Errors

File ListList of files to ignore errors (e.g. DropHRTables.sql,*_ddl.sql).EBS SQL Ignore Compile Errors

FDEBS_SQL_IGNORE_COMPILE_ERRORS

Ignore SQL compilation errors

.

for all files in the project

false

EBS

SQL Retry Count	`FDEBS_SQL_RETRY_COUNT`	Retry count for SQLs and PL/SQLs.	5
File Permissions	`FDEBS_FILE_PERMISSIONS`	Target File Permission. Will be set to target file after deployment using chmod. eg: 755
Ignore Errors File List	`FDEBS_IGNORE_ERRORS_FILE_LIST`	List of files to ignore errors (e.g. DropHRTables.sql,*_ddl.sql).

Sample Build Commands

N/A - Build commands not supported for this type.

Sample Deploy Commands

Code Block

theme

language	bash		RDark

cp "$SOURCE_FILE" "$XXHR_TOP/patch/115/sql/";
cd "$XXHR_TOP/patch/115/sql/"
sqlplus $FDEBS_DB_USER/$FDEBS_DB_PASSWORD <<EOF
set define off
  @$XXHR_TOP/patch/115/sql/XXHR_@XXHR_BIPUB_REP_PKG.seq
.
SHOW ERRORS
exit sql.sqlcode
EOF

...

Info

Tip

Make sure to end file with forward slash /, if last statement is FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY.

Simple statements can be delimited by ; at end of statement but FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY needs / after it

Notice

"SHOW ERRORS" and "exit sql.sqlcode" are dynamically added or removed from the deployment command at runtime. If the file contents of this sql file contain "EXIT", then "SHOW ERRORS" and "exit sql.sqlcode" are added to the deployment command, if they don't already exist. On the otherhand, if the file contents does not contain "EXIT", then those same commands are removed from the deployment command if they exist.

Special Considerations

If execution of DROP TABLE, DROP VIEW, DROP INDEX, DROP SEQUENCE, DROP TYPE, DROP SYNONYM, DROP PUBLIC SYNONYM fails with particular error, it will be ignored. This would help with files where DROP TABLE then CREATE TABLE format is used as it is possible that DROP TABLE may fail if table does not exist yet. Specific errors as defined below are ignored.
- DROP TABLE - ORA-00942
- DROP VIEW - ORA-00942
- DROP INDEX - ORA-01418
- DROP SEQUENCE - ORA-02289
- DROP TYPE - ORA-04043
- DROP SYNONYM - ORA-01432 or ORA-01434
- DROP PUBLIC SYNONYM - ORA-01432 or ORA-01434
Program blocks should be terminated by use of / no matter where it is added in SQL file. For example, Package Specification, Package Body, Trigger etc.
- FlexDeploy will automatically add / at the end of file if last statement is a program block.
Statements that are not program blocks should be terminated using ; no matter where it is added in SQL file. For example create or replace view, insert etc.
- FlexDeploy will automatically add / at the end of file if last statement is View, Insert, Update, Delete and does not end with ;
Single or multiple line comments are also supported in input files.
See SQL File Considerations for details on file format and execution of files using user other than apps.
Consider adding compileInvalidSqls operation in Deploy workflow if SQL files are being deployed. This operation can take longer if there are many invalid objects in Schema.

Example File