EBS Object Types - DB Objects (SQLs)
A collection of reusable code, programs, and database objects that provides common functionality across all E-Business products.
File Extensions
If file is not Concurrent program (indicated by parent folder), then it is considered DB Objects (SQLs). File content is also further analyzed by populate process.
| Name | Description |
|---|---|
| .sql | A file containing sql statements. Extensions may be customized using EBS SQL Extensions project property. |
Attributes
| Name | Description | Default Value | Supported Values |
|---|---|---|---|
| Source | Object Source Location Type | SCM | SCM |
| Target Location | Path to where the file should be deployed to. | $<PROD_TOP>/patch/115/sql/ | |
| Type | DB Object Type | Generic SQL | |
| DB User Property | FlexDeploy property to use as the user to connect to the database with. | FDEBS_DB_USER | |
| DB Password Property | FlexDeploy property to use as the password for the DB User Property. | FDEBS_DB_PASSWORD |
Related Project Properties
| Name | Description | Default Value |
|---|---|---|
| EBS SQL Root Source Directory | Source folder for sql and pls files (e.g. db). | sql |
| EBS SQL Root Destination Directory | Target directory for SQL and PLS files (e.g. XXHR_TOP/admin/sql). | $<PROD_TOP>/patch/115/sql |
| EBS 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 | List of files to ignore errors (e.g. DropHRTables.sql,*_ddl.sql). | |
| EBS SQL Ignore Compile Errors | Ignore SQL compilation errors. | false |
| EBS SQL Retry Count | Retry count for SQLs and PL/SQLs. | 5 |
Sample Build Commands
N/A - Build commands not supported for this type.
Sample Deploy Commands
cp $SOURCE_FILE $XXHR_TOP/patch/115/sql/; sqlplus $FDEBS_DB_USER/$FDEBS_DB_PASSWORD <<EOF set define off @$XXHR_TOP/patch/115/sql/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.
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.
Single or multiple line comments are also supported in input files.
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;
/
- style