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). 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.
| 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_EXTENSIONSthe 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. |
|
| 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 | 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.
...
| Code Block |
|---|
|
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 |
Example File
| Tip |
|---|
- 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.
|
| Code Block |
|---|
|
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;
/ |
...