EBS Object Types - DB Objects (SQLs)

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.

Files with .sql extension are identified as this type only AFTER excluding them as Concurrent Program SQLs (by source folder).

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` project property.

Object Type Code

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`
DB Password Property	DB_PASSWORD	FlexDeploy property to use as the password for the DB User Property.	`FDEBS_DB_PASSWORD`
Ignore Failure	IGNORE_FAILURE	Check if failure in file should be ignored.	If file contains WHENEVER SQLERROR CONTINUE case insensitively, this property will default to true.
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.	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 Ignore Errors File List	FDEBS_IGNORE_ERRORS_FILE_LIST	List 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

Sample Build Commands

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

Sample Deploy Commands

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_BIPUB_REP_PKG.seq
.
SHOW ERRORS
exit sql.sqlcode
EOF

Example File

Make sure to end file with forward slash /, if last statement is FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY.
- Since 4.6.0.4, FlexDeploy will automatically add / at the end of file if last statement is a program block.
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 no matter where it is added in SQL file.
- Since 4.6.0.5, 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 more details.

DROP TABLE HR.DEPARTMENTS;

CREATE TABLE HR.DEPARTMENTS
(
     DEPARTMENT_ID NUMBER (4)  NOT NULL ,
     DEPARTMENT_NAME VARCHAR2 (30 BYTE)  NOT NULL ,
     MANAGER_ID NUMBER (6) ,
     LOCATION_ID NUMBER (4)
);

DROP SEQUENCE HR.DEPARTMENTS_SEQ;

CREATE SEQUENCE HR.DEPARTMENTS_SEQ
    INCREMENT BY 10
    MAXVALUE 9990
    MINVALUE 1
    NOCACHE;

CREATE OR REPLACE TRIGGER HR.ID_DEPARTMENTS
    BEFORE INSERT ON HR.DEPARTMENTS
    FOR EACH ROW
BEGIN
  SELECT HR.DEPARTMENTS_SEQ.NEXTVAL
  INTO   :new.DEPARTMENT_ID
  FROM   dual;
END ID_DEPARTMENTS;
/