EBS Object Types - DB Objects (SQLs)

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). File content is also further analyzed by populate process.
  • 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 

NameDescription
.sqlA file containing sql statements. Extensions may be customized using the FDEBS_SQL_EXTENSIONS project property.

Attributes

NameDescriptionDefault ValueSupported Values
SourceObject Source Location Type

SCM

SCM
Target LocationPath to where the file should be deployed to.$<PROD_TOP>/patch/115/sql/
TypeDB Object TypeGeneric SQL
DB User PropertyFlexDeploy property to use as the user to connect to the database with.FDEBS_DB_USER
DB Password PropertyFlexDeploy property to use as the password for the DB User Property.FDEBS_DB_PASSWORD
Ignore Failure

Check if failure in file should be ignored.

@Since 4.6.0.3

If file contains WHENEVER SQLERROR CONTINUE, this property will default to true.
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

Related Project Properties

NameDescriptionDefault Value
EBS SQL Root Source DirectorySource folder for sql and pls files (e.g. db).sql
EBS SQL Root Destination DirectoryTarget directory for SQL and PLS files (e.g. XXHR_TOP/admin/sql).$<PROD_TOP>/patch/115/sql
EBS SQL ExtensionsSQL 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 ListList of files to ignore errors (e.g. DropHRTables.sql,*_ddl.sql).
EBS SQL Ignore Compile ErrorsIgnore SQL compilation errors.false
EBS SQL Retry CountRetry count for SQLs and PL/SQLs.5

Sample Build Commands 

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

Sample Deploy Commands 

cp $SOURCE_FILE $XXHR_TOP/patch/115/sql/;
sqlplus $FDEBS_DB_USER/$FDEBS_DB_PASSWORD <<EOF
set define off
  @$XXHR_TOP/patch/115/sql/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 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 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;
/
The following macros are not currently supported in the footer:
  • style