Versions Compared

Old Version 38

changes.mady.by.user Jay-Ar Brouillard

Saved on

compared with

New Version Current

changes.mady.by.user Jay-Ar Brouillard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

A collection of reusable code, programs, and database objects that provides common functionality across all E-Business products.

Object Type Identification

...

NameCodeDescriptionDefault Value
SQL Root Source DirectoryFDEBS_SQL_ROOT_SOURCE_DIRSource folder for sql and pls files (e.g. db).sql
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
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.

SQL Ignore Compile ErrorsFDEBS_SQL_IGNORE_COMPILE_ERRORSIgnore SQL compilation errors for all files in the projectfalse
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
Ignore Errors File ListFDEBS_IGNORE_ERRORS_FILE_LISTList of files to ignore errors (e.g. DropHRTables.sql,*_ddl.sql).

...

Code Block
languagebash
themeRDark
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


Info
titleNotice

@since 5.5.0.1 "SHOW ERRORS" and "exit sql.sqlcode" are dynamically added or removed from the deployment command at runtime. If the file contents of this sql file contain "EXIT", then "SHOW ERRORS" and "exit sql.sqlcode" are added to the deployment command, if they don't already exist. On the otherhand, if the file contents does not contain "EXIT", then those same commands are removed from the deployment command if they exist.

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
  • Program blocks should be terminated by use of / no matter where it is added in SQL file. For example, Package Specification, Package Body, Trigger etc.
    • FlexDeploy will automatically add / at the end of file if last statement is a program block.
  • Statements that are not program blocks should be terminated using ; no matter where it is added in SQL file. For example create or replace view, insert etc.
    • 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 details on file format and execution of files using user other than apps.
  • 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.@since 5.5.0.1 "SHOW ERRORS" and "exit sql.sqlcode" are dynamically added or removed from the deployment command at runtime. If the file contents of this sql file contain "EXIT", then "SHOW ERRORS" and "exit sql.sqlcode" are added to the deployment command, if they don't already exist. On the otherhand, if the file contents does not contain "EXIT", then those same commands are removed from the deployment command if they exist.

Example File

Code Block
languagesql
themeRDark
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;
/

...