Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 27 Next »

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.

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

File Extensions 

NameDescription
.seq,tbl,idx,typ,tps,tpb,sql,pkh,plb,pks,pkb,pls,pck,fnc,trg,spc,bdy,prcA file containing sql statements. Extensions may be customized using the FDEBS_SQL_EXTENSIONS project property.

Object Type Code

NameCode
DB Objects (SQLs)SQL

Object Type Attributes

NameCodeDescriptionDefault ValueSupported Values
SourceSOURCEObject Source Location Type

SCM

SCM
Target LocationTARGET_LOCATIONPath to where the file should be deployed to.$<PROD_TOP>/patch/115/sql/
TypeTYPEDB Object TypeGeneric SQL
DB User PropertyDB_USERFlexDeploy property to use as the user to connect to the database with.FDEBS_DB_USER
DB Password PropertyDB_PASSWORDFlexDeploy property to use as the password for the DB User Property.FDEBS_DB_PASSWORD
Ignore FailureIGNORE_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 SyntaxFIX_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 PermissionFILE_PERMISSIONSPermissions to apply to the file after it is deployedDefaults to the project property FDEBS_FILE_PERMISSIONS

Related Project Properties

NameCodeDescriptionDefault Value
EBS SQL Root Source DirectoryFDEBS_SQL_ROOT_SOURCE_DIRSource folder for sql and pls files (e.g. db).sql
EBS SQL Root Destination DirectoryFDEBS_SQL_ROOT_DESTINATION_DIRTarget directory for SQL and PLS files (e.g. XXHR_TOP/admin/sql).$<PROD_TOP>/patch/115/sql
EBS SQL ExtensionsFDEBS_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 ListFDEBS_IGNORE_ERRORS_FILE_LISTList of files to ignore errors (e.g. DropHRTables.sql,*_ddl.sql).
EBS SQL Ignore Compile ErrorsFDEBS_SQL_IGNORE_COMPILE_ERRORSIgnore SQL compilation errors for all files in the projectfalse
EBS SQL Retry CountFDEBS_SQL_RETRY_COUNTRetry count for SQLs and PL/SQLs.5
File PermissionsFDEBS_FILE_PERMISSIONSTarget 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;
/
  • No labels