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 |
EBS SQL Extensions project property.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
Code Block |
---|
language | bash |
---|
theme | EclipseRDark |
---|
|
cp "$SOURCE_FILE" "$XXHR_TOP/patch/115/sql/";
cd "$XXHR_TOP/patch/115/sql/"
sqlplus $FDEBS_DB_USER/$FDEBS_DB_PASSWORD @$XXHR_TOP/patch/115/sql/XXHR <<EOF
set define off
@XXHR_BIPUB_REP_PKG.seq
<<EOF.
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
|
...
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;
/ |