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

If a .sql file is not a Concurrent program (indicated by parent folder), then it is considered DB Objects (SQLs).
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.

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

SQL

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;
/