This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,552 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

Why are my bind variable values dissappearing?

Paul Simmons
Paul Simmons Solutions ArchitectMember Posts: 17
edited May 8, 2016 6:59PM in SQLcl

Hi All,

I have the following script that executes beautifully in sqlplus,

lup\hrb.object.lup_countries.sql

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;

/

That gives me the following output

SQLPLUS Output

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.

I execute the same script is SQLcl and I get some weird errors due to the bind variable being unset

SQLcl Output

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.

I have bolded the print OBJ_NAME output, what has happened to be variable value?

For the record this script works fine in SQL Developer (4.1.3.20.78)

Paul Simmons

Answers