Why are my bind variable values dissappearing?

Paul Simmons

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)

thatJeffSmith-Oracle

reproduced, definitely a bug

thanks for the report!

Paul Simmons

I would mark as correct answer, but that hardly seems true.  Thanks for response ... I am looking forward to this working.