Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 6 Next »

Here are some considerations for SQL File format and deployment.

  • 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.

    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;
    /
    
    
  • Statements that are not program blocks should be terminated using ;. For example create or replace view, insert etc.

    CREATE SYNONYM apps.XXHR_WF1 FOR xxhr.XXHR_WF1;
    
    CREATE OR REPLACE FORCE VIEW "APPS"."DUMMY_V1" ("COUNT") AS
      (SELECT 1 from dual);
  • Comments in SQL file. See below for supported formats.

    Good comment examples
    --single line comment
    /* 
     * Name : XXHR_WF1                                                        *
     */
  • Following comment is not supported as it will cause SP2-0103: Nothing in SQL buffer to run.

    Bad Comment
    /*Added comment by chandresh to test flexdeploy*/
  • 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 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.
    1. Give necessary rights to APPS user, so it can select on any other schema objects. For example, SELECT ANY TABLE privilege can be given to APPS and similarly other ANY type Grants can be given to APPS user.
    2. Use custom schema user in FlexDeploy to create objects and grant to APPS.
      1. Create custom user and password properties on Deploy workflow to be at Environment Instance Scope. For example,
      2. Defined values for these user and password in various Environment Instance pages in Topology.
      3. Use custom properties on Project Files tab for specific Files.


  • No labels