SQLcl: MCP Server & SQL Prompt

prompt Building OBJECT: LUP_COUNTRIES

var obj_name VARCHAR2(21);

exec :obj_name := 'LUP_COUNTRIES';

prompt Building TABLES

BEGIN

  IF NOT xxfnd.xxfnd_schema.get_object_exists('TABLE', :obj_name||'_DATA') AND

     NOT xxfnd.xxfnd_schema.get_object_exists('TABLE', :obj_name||'_ARCH') THEN

    BLD_TABLE_HELPER.BUILD_TABLE_BASE(:obj_name);

  END IF;

END;

/

DECLARE

  datum_name VARCHAR2(30);

BEGIN

  datum_name := 'COUNTRY_CODE';

  IF NOT xxfnd.xxfnd_schema.get_tab_column_exists(:obj_name||'_DATA', datum_name) THEN

    BLD_TABLE_HELPER.ADD_DATUM_COLUMN(:obj_name, datum_name, 'VARCHAR2(2)', 'NOT NULL', 'Short name of the country');

  END IF;

  datum_name := 'DESCRIPTION';

  IF NOT xxfnd.xxfnd_schema.get_tab_column_exists(:obj_name||'_DATA', datum_name) THEN

    BLD_TABLE_HELPER.ADD_DATUM_COLUMN(:obj_name, datum_name, 'VARCHAR2(50)', 'NOT NULL', 'Full name of the country');

  END IF;

  datum_name := 'IS_ORG_COUNTRY';

  IF NOT xxfnd.xxfnd_schema.get_tab_column_exists(:obj_name||'_DATA', datum_name) THEN

    BLD_TABLE_HELPER.ADD_DATUM_COLUMN(:obj_name, datum_name, 'VARCHAR2(1)', 'DEFAULT ''N'' NOT NULL', 'Is this country and organizational country in Bunnings');

  END IF;

END;

/

print obj_name

prompt Building Views

BEGIN

  BLD_TABLE_HELPER.BUILD_OBJECT_VIEWS(:obj_name);

END;

/

print obj_name

prompt Building PACKAGE

BEGIN

  BLD_PACKAGE_HELPER.BUILD_PACKAGE(:obj_name);

END;

/

print obj_name

prompt Granting to Business Layer

BEGIN

  BLD_GRANT_HELPER.GRANT_OBJECT(:obj_name, 'HRB_API', TRUE);

END;

/

prompt Registering for Archiving

BEGIN

  HRB_ARCHIVER.REGISTER_OBJECT(:obj_name, 1000);

END;

/

Prompt Validating OBJECT

BEGIN

  BLD_VALIDATION_HELPER.VALIDATE_OBJECT(:obj_name);

END;

/

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> @lup\hrb.object.lup_countries.sql

Building OBJECT: LUP_COUNTRIES

PL/SQL procedure successfully completed.

Building TABLES

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

OBJ_NAME

--------------------------------------------------------------------------------

LUP_COUNTRIES

Building Views

PL/SQL procedure successfully completed.

OBJ_NAME

--------------------------------------------------------------------------------

LUP_COUNTRIES

Building PACKAGE

PL/SQL procedure successfully completed.

OBJ_NAME

--------------------------------------------------------------------------------

LUP_COUNTRIES

Granting to Business Layer

PL/SQL procedure successfully completed.

Registering for Archiving

PL/SQL procedure successfully completed.

Validating OBJECT

PL/SQL procedure successfully completed.

SQLcl: Release 4.2.0.16.112.0616 RC on Fri May 06 09:03:27 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> @lup\\hrb.object.lup_countries.sql

Building OBJECT: LUP_COUNTRIES

PL/SQL procedure successfully completed.

Building TABLES

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

OBJ_NAME

--------------------------------------------------------------------------------

LUP_COUNTRIES

Building Views

PL/SQL procedure successfully completed.

OBJ_NAME

--------------------------------------------------------------------------------

Building PACKAGE

Error starting at line : 48 File @ file:\C:\Users\psimmons\Documents\GIT\HRB\hrb\lup\hrb.object.lup_countries.sql

In command -

BEGIN

  BLD_PACKAGE_HELPER.BUILD_PACKAGE(:obj_name);

END;

Error report -

ORA-24344: success with compilation error

ORA-06512: at "HRB.BLD_PACKAGE_HELPER", line 1272

ORA-06512: at line 2

24344. 00000 -  "success with compilation error"

*Cause:    A sql/plsql compilation error occurred.

*Action:   Return OCI_SUCCESS_WITH_INFO along with the error code

OBJ_NAME

--------------------------------------------------------------------------------

Granting to Business Layer

Error starting at line : 56 File @ file:\C:\Users\psimmons\Documents\GIT\HRB\hrb\lup\hrb.object.lup_countries.sql

In command -

BEGIN

  BLD_GRANT_HELPER.GRANT_OBJECT(:obj_name, 'HRB_API', TRUE);

END;

Error report -

ORA-00911: invalid character

ORA-06512: at "HRB.BLD_GRANT_HELPER", line 91

ORA-06512: at line 2

00911. 00000 -  "invalid character"

*Cause:    identifiers may not start with any ASCII character other than

           letters and numbers.  $#_ are also allowed after the first

           character.  Identifiers enclosed by doublequotes may contain

           any character other than a doublequote.  Alternative quotes

           (q'#...#') cannot use spaces, tabs, or carriage returns as

           delimiters.  For all other contexts, consult the SQL Language

           Reference Manual.

*Action:

Registering for Archiving

Error starting at line : 62 File @ file:\C:\Users\psimmons\Documents\GIT\HRB\hrb\lup\hrb.object.lup_countries.sql

In command -

BEGIN

  HRB_ARCHIVER.REGISTER_OBJECT(:obj_name, 1000);

END;

Error report -

ORA-01400: cannot insert NULL into ("HRB"."HRB_ARCHIVE_REGISTER"."OBJECT_NAME")

ORA-06512: at "HRB.HRB_ARCHIVER", line 53

ORA-06512: at line 2

01400. 00000 -  "cannot insert NULL into (%s)"

*Cause:    An attempt was made to insert NULL into previously listed objects.

*Action:   These objects cannot accept NULL values.

Validating OBJECT

PL/SQL procedure successfully completed.