ORA-06502: PL/SQL: numeric or value error during installation of FTLDB Demo an a 12.1.0.2 PDB

[PhilippSalvisberg]

I've done the following:

• run "dba_install.bat phspdb2 sys xxx ftldb ftldb"
• run "dba_switch_java_permissions.bat phspdb2 sys xxx grant public"

both steps completed succesfully.

Afterwards I

• ran "install.bat phspdb2 sys xxx ftldb ftldbdemo ftldbdemo".

This failed. Here the exerpt of the consle output:

Run UT_FTLDB_API tests.
begin
*
ERROR at line 1:
ORA-20000: FTLDB initialization failed
ORA-06512: at "FTLDB.FTLDB_API", line 270
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FTLDBDEMO.UT_FTLDB_API", line 13
ORA-06512: at "FTLDBDEMO.UT_FTLDB_API", line 39
ORA-06512: at line 2

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!! INSTALLATION FAILED !!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Do you have an idea what I'm doing wrong?

[voidunit]

Philipp, thanks for informing us.

The issue looks mysteriously. We run CI tests on Oracle 10.2.0.5 and 11.2.0.4 and it works ok. We don't have Oracle 12c installed on the testing server, but Sergey has just run the demo on his 12c and everything still looks good.

Could you please provide your NLS params, especially your characterset?

Also, I will fix error handling in the init block in the upcoming version and make it more verbose with the full backtrace. But for now could you please comment the whole 'exception when others then' section at the very end of ftldb_api package and rerun the demo? I need the error backtrace for investigation.

[PhilippSalvisberg]

Hello Victor,

Thank you for your fast response. Here are the NLS settings:

SQL> SELECT * FROM nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION              12.1.0.2.0
NLS_NCHAR_CONV_EXCP            FALSE
NLS_LENGTH_SEMANTICS           BYTE
NLS_COMP                       BINARY
NLS_DUAL_CURRENCY              $
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_SORT                       BINARY
NLS_DATE_LANGUAGE              AMERICAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_CALENDAR                   GREGORIAN
NLS_NUMERIC_CHARACTERS         .,
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               AL32UTF8
NLS_ISO_CURRENCY               AMERICA
NLS_CURRENCY                   $
NLS_TERRITORY                  AMERICA
NLS_LANGUAGE                   AMERICAN

SQL> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

and the error stack looks as follows after commenting out the exception handler in the package initialisation code of FTLDB_API:

Run UT_FTLDB_API tests.
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FTLDB.FTLDB_API", line 166
ORA-06512: at "FTLDB.FTLDB_API", line 177
ORA-06512: at "FTLDB.FTLDB_API", line 266
ORA-06512: at "FTLDBDEMO.UT_FTLDB_API", line 13
ORA-06512: at "FTLDBDEMO.UT_FTLDB_API", line 39
ORA-06512: at line 2

HTH, Regards
Philipp

[voidunit]

Philipp, @sns777 has found the buggy place in code. The issue appears only in Oracle 12c. In a few hours, I'll look at it myself.

As a temporary workaround please edit the ftldb_api.pkb file and replace line 183:

'call ' || dbms_assert.sql_object_name(in_config_func_name) || '() into :1'

with the following

'begin :1 := ' || dbms_assert.sql_object_name(in_config_func_name) || '(); end;'

[voidunit]

P.S. It's not the first time the CALL statement plays a dirty trick on me.

[PhilippSalvisberg]

Victor, thanks again. I've change line 167 as suggested and it worked.

-------------------------------------------
--- INSTALLATION COMPLETED SUCCESSFULLY ---
-------------------------------------------

[voidunit]

Ok. The issue is not about charset as I thought before. It repeats on different 12c databases. Who would've thought that code tested on 11.2 wouldn't work on 12.1?

We should improve our testing environment and install 12c.

I'll release v1.4.1 soon with the fix.

[voidunit]

Done. Tested on 10.2.0.5, 11.2.0.4, 12.1.0.2.