Here are some considerations for SQL File format and deployment.
Table of Contents |
---|
SQL File Format
Program blocks
Program blocks should be terminated by use of /. See examples below. For example, Package Specification, Package Body, Trigger etc. FlexDeploy will add / at the end of the file is last statement is program block.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
BEGIN EXECUTE IMMEDIATE 'DROP TABLE XXHR.XXHR_WF1'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP SYNONYM XXHR_WF1'; EXCEPTION WHEN OTHERS THEN NULL; END; / CREATE OR REPLACE PACKAGE xxhr_bipub_rep_pkg AS PROCEDURE generate_xml(p_errbuf OUT VARCHAR2 ,p_retcode OUT NUMBER); END xxhr_bipub_rep_pkg; / |
Non program blocks
Statements that are not program blocks should be terminated using ;. For example create or replace view, insert etc.
Code Block | ||||
---|---|---|---|---|
| ||||
CREATE SYNONYM apps.XXHR_WF1 FOR xxhr.XXHR_WF1; CREATE OR REPLACE FORCE VIEW "APPS"."DUMMY_V1" ("COUNT") AS (SELECT 1 from dual); |
Comments
Comments in SQL file. See below for supported formats.
...
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
/*Added comment by chandresh to test flexdeploy*/ |
Recompiling invalids
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.
Custom objects in non apps schema
Custom objects in non APPS schema with grants to APPS user. It is recommended by Oracle to create custom objects in custom schema. You need to provide Grants to APPS user to these custom objects. You can achieve this in two different ways in FlexDeploy.
...