A collection of reusable code, programs, and database objects that provides common functionality across all E-Business products.
Object Type Identification
...
| Name | Code | Description | Default Value |
|---|---|---|---|
| SQL Root Source Directory | FDEBS_SQL_ROOT_SOURCE_DIR | Source folder for sql and pls files (e.g. db). | sql |
| 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 |
| 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. |
| SQL Ignore Compile Errors | FDEBS_SQL_IGNORE_COMPILE_ERRORS | Ignore SQL compilation errors for all files in the project | false |
| 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). |
...
| 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_BIPUB_REP_PKG.seq . SHOW ERRORS exit sql.sqlcode EOF |
Special Considerations
...
- If execution of DROP TABLE, DROP VIEW, DROP INDEX, DROP SEQUENCE, DROP TYPE, DROP SYNONYM, DROP PUBLIC SYNONYM fails with particular error, it will be ignored. This would help with files where DROP TABLE then CREATE TABLE format is used as it is possible that DROP TABLE may fail if table does not exist yet. Specific errors as defined below are ignored.
- DROP TABLE - ORA-00942
- DROP VIEW - ORA-00942
- DROP INDEX - ORA-01418
- DROP SEQUENCE - ORA-02289
- DROP TYPE - ORA-04043
- DROP SYNONYM - ORA-01432 or ORA-01434
- DROP PUBLIC SYNONYM - ORA-01432 or ORA-01434
- Make sure to end file with forward slash /, if last statement is FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY.
- 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.
- 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.
...
- Consider adding compileInvalidSqls operation in Deploy workflow if SQL files are being deployed. This operation can take longer if there are many invalid objects in Schema.
- See SQL File Considerations for details on file format and execution of files using user other than apps.
Example File
| 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;
/ |
...