I recently discovered that under certain circumstances, for now it looks to be environment setting depended, that an ORA-25330 is encountered when running dbms_predictive_analytics.predict procedure. I found out of this problem while preparing a demo and certainly the error was comming up in TOAD (I am not a TOAD fan, but it is handy searching through the very big amount of factor columns in a GAPP analysis). When the same command below was executed directly on the server via sqlplus, the error was not encountered.

To investigate I tried to create a errorstack on the error, but without luck. So I started to do a sqltrace and found out that the ORA-25330 is actually on the server translated as ORA-40206. After I found that I created the following small test in TOAD:

alter session set sql_trace=true;

alter session set events ’40206 trace name ERRORSTACK level 3′;

select ‘REPRODUCING ORA-25330 BY GERWIN HENDRIKSEN’ from dual;

DECLARE
v_accuracy NUMBER(10,9);
BEGIN
DBMS_PREDICTIVE_ANALYTICS.PREDICT(
accuracy => v_accuracy,
data_table_name => ‘GHH_RESULT’,
case_id_column_name => ‘TIME’,
target_column_name => ‘ELAT’,
result_table_name => ‘GHH_PREDICT_ORA25330′);
DBMS_OUTPUT.PUT_LINE(‘Accuracy = ‘ || v_accuracy);
END;
/

select ‘REPRODUCED ORA-25330 BY GERWIN HENDRIKSEN’ from dual;

exit TOAD

Then the following information could be found in the trace file (use 11.1.0.6 by the way):

----- Error Stack Dump -----
ORA-40206: ongeldige instellingswaarde voor instellingsnaam SVMS_CONV_TOLERANCE
----- Current SQL Statement for this session (sql_id=394st4vxujtxa) -----
SELECT dm_mod_build ('DM$TANNWBZBBEUCBGVRNYNRTE',CURSOR(SELECT "HDISKPOWER10_HOT600", "RLFREE_HOT720", "HDISK35_HOT600", "HOT737_CPU", "HDISK48_HOT600", "HDISK0_HOT600", "HDISK40_HOT600", "HDISK23_HOT600", "RUNABLE_HOT720", "PGIN_HOT720", "WAIT_HOT720", "HDISK2_HOT720", "HOT600_CPU", "HDISK28_HOT600", "HDISK6_HOT600", "CYCLES_HOT720", "RT_HOT720", "SYS_HOT730", "HDISK54_HOT600", "PGSIN_HOT730", "HDISK51_HOT600", "HOT606_CPU", "RT_HOT730", "HDISK11_HOT600", "HDISK10_HOT600", "SYS_HOT720", "HDISK0_HOT730", "USER_HOT600", "PRLFREE_HOT720", "FAULTS_HOT720", "HDISK8_HOT600", "RT_HOT600", "PGSOUT_HOT600", "HOT604_CPU", "VTFREE_HOT730", "HDISK64_HOT600", "HDISK46_HOT600", "WAIT_HOT730", "HDISK29_HOT600", "CYCLES_HOT600", "HDISK66_HOT600", "HDISK12_HOT600", "HDISK56_HOT600", "HDISK14_HOT600", "PRLFREE_HOT730", "VT_HOT720", "HDISKPOWER7_HOT600", "HOT601_CPU", "VTFREE_HOT720", "RLFREE_HOT730", "HDISK60_HOT600", "HDISK15_HOT600", "HDISK19_HOT600", "HDISK59_HOT600", "VT_HOT600", "HDISK22_HOT600", "PGOUT_HOT720", "RUNABLE_HOT600", "HDISK47_HOT600", "HDISK37_HOT600", "HOT730_CPU", "HDISK30_HOT600", "HDISK13_HOT600", "HDISK4_HOT720", "HOT705_CPU", "HDISK61_HOT600", "PGSOUT_HOT730", "HDISK5_HOT600", "HDISK63_HOT600", "HOT727_CPU", "HDISK42_HOT600", "HOT721_CPU", "WAIT_HOT600", "PGOUT_HOT730", "HDISKPOWER1_HOT600", "HDISK55_HOT600", "HDISK9_HOT600", "RECLAIMS_HOT600", "HDISK65_HOT600", "HDISK57_HOT600", "HDISK2_HOT730", "PVTFREE_HOT730", "HDISK21_HOT600", "HDISK62_HOT600", "RECLAIMS_HOT730", "PVTFREE_HOT600", "HDISKPOWER9_HOT600", "HDISKPOWER2_HOT600", "HDISK67_HOT600", "HDISK0_HOT720", "PGSIN_HOT720", "HDISK3_HOT600", "HDISK17_HOT600", "HDISK3_HOT730", "HDISK1_HOT600", "HDISK3_HOT720", "PGIN_HOT730", "HDISK34_HOT600", "HDISK24_HOT600", "RLFREE_HOT600", "HDISK20_HOT600", "HDISK53_HOT600", "RECLAIMS_HOT720", "HDISK26_HOT600", "HOT602_CPU", "VTFREE_HOT600", "HDISK27_HOT600", "HDISK32_HOT600", "HOT603_CPU", "HDISK4_HOT600", "HDISK2_HOT600", "HDISK31_HOT600", "HDISK1_HOT720", "FAULTS_HOT730", "PVTFREE_HOT720", "PGSOUT_HOT720", "CYCLES_HOT730", "PRLFREE_HOT600", "ELAT", "HDISKPOWER0_HOT600", "HOT738_CPU", "HDISK50_HOT600", "HDISKPOWER3_HOT600", "HDISK36_HOT600", "HOT722_CPU", "HDISK41_HOT600", "HDISK43_HOT600", "HDISK16_HOT600", "SCANS_HOT730", "USER_HOT720", "HDISK1_HOT730", "SYS_HOT600", "SCANS_HOT600", "HDISK25_HOT600", "HDISK52_HOT600", "HDISK44_HOT600", "HDISKPOWER8_HOT600", "PGIN_HOT600", "HOT707_CPU", "HOT720_CPU", "PGSIN_HOT600", "HDISKPOWER6_HOT600", "HDISK49_HOT600", "RUNABLE_HOT730", "HDISK18_HOT600", "HDISK38_HOT600", "PGOUT_HOT600", "HDISK39_HOT600", "HDISK45_HOT600", "HDISK7_HOT600", "VT_HOT730", "FAULTS_HOT600", "SCANS_HOT720", "HDISK33_HOT600", "HDISKPOWER4_HOT600", "USER_HOT730", "HDISK58_HOT600", "HDISKPOWER5_HOT600" FROM "HOTSOS"."DM$TXFM_FGLJPVFEXMNNHTQRDVJDBV" ORDER BY ora_hash("TIME"),"TIME"),CURSOR(SELECT setting_name, setting_value FROM TABLE(CAST(:1 AS DM_Model_Settings))), 2) FROM dual
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x2d168b34 61 SYS.DMMODBIMP
0x2d168b34 58 SYS.DMMODBIMP
0x2d1d2374 941 package body SYS.DBMS_DATA_MINING_INTERNAL
0x2d1d2374 2729 package body SYS.DBMS_DATA_MINING_INTERNAL
0x2d1c8bac 1627 package body SYS.DBMS_DATA_MINING
0x2d1daef0 524 package body SYS.DBMS_PREDICTIVE_ANALYTICS
0x2d1daef0 1292 package body SYS.DBMS_PREDICTIVE_ANALYTICS
0x2a65c2e8 4 anonymous block

—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
skdstdst()+38 call kgdsdst() BFE0F18C ? 2 ?
ksedst1()+88 call skdstdst() BFE0F18C ? 0 ? 1 ? A5A89EE ?
853D37A ? A5A89EE ?
ksedst()+33 call ksedst1()+8 BFE104A8 ? B6E564 ? 2050033 ?
0 ? FF ? 2004 ?
dbkedDefDump()+1046 call ksedst() 0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksedmp()+47 call dbkedDefDump() 3 ? 0 ?
dbkdaKsdActDriver() call 00000000 3 ? 110000 ? 4048CF ? 11 ?
+798 4048CF ? 11 ?
dbgdaExecuteAction( call 00000000 B6E564 ? BFE10914 ?
)+61
dbgdaRunAction()+33 call dbgdaExecuteAction( B6E564 ? ED37DD0 ? 20C0002 ?
7 ) BFE10914 ?
dbgdRunActions()+64 call dbgdaRunAction() B6E564 ? 6FF874 ?
dbgdProcessEventAct call dbgdRunActions() D8FFC92 ? D8FFC9B ?
ions()+580
dbgdChkEventKgErr() call dbgdProcessEventAct B6E564 ? F563360 ? 6FF984 ?
+369 ions() 118 ? 6FF984 ? 1 ?
dbkdChkEventRdbmsEr call dbgdChkEventKgErr() B6E564 ? 6FFF04 ? 9D0E ?
r()+23
__PGOSF28_ksfpec()+ call dbkdChkEventRdbmsEr 9D0E ? 800 ? 0 ? 100000 ? 0 ?
183 r() 6FFF04 ?
dbgePostErrorKGE()+ call 00000000 F563360 ? 9D0E ?
825
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() F563360 ? 6D3E88 ? 9D0E ?
43
kgesev()+256 call 00000000 F563360 ? 6D3E88 ? 9D0E ?
ksesec1()+29 call kgesev() F563360 ? 6D3E88 ? 9D0E ? 1 ?
BFE11294 ?
dmsvmbLSettings()+6 call ksesec1() 9D0E ? 1 ? 13 ? EBDEB50 ?
85
dmsvm_build()+644 call dmsvmbLSettings() BFE114B4 ? BFE114E4 ?
CD175CC ? CD1759F ?
dmmodbftch()+679 call dmsvm_build() 10E429C ? BFE114E4 ? 6E4BC4 ?
6E4CE0 ?
spefcpfa()+85 call 00000000 BFE122BC ? 104729C ?
1047208 ? 6E4CC4 ? 0 ?
6E4BC4 ? 6E4CE0 ?
spefmccallstd()+734 call spefcpfa() BFE11FF4 ? 7 ? 6E4C80 ?
BFE11780 ? BFE1178C ? 0 ?
pextproc()+70 call spefmccallstd() BFE12244 ? BFE1206C ?
BFE11EEC ? BFE11FF4 ? 0 ?
__PGOSF294_peftrust call pextproc() BFE12244 ? BFE1206C ?
ed()+114 BFE11EEC ? BFE11FF4 ?
psdexsp()+186 call 00000000 BFE12244 ? BFE11848 ?
924DB1F ? F563360 ? 10470CC ?
BFE11848 ?
rpiswu2.()+404 call 00000000 BFE11E50 ? 1 ? 10F9A40 ?
F563360 ? 0 ? 36 ?
psdextp()+544 call rpiswu2() 324538D4 ? 59 ? 32453918 ?
2 ? BFE11E6C ? 59 ?
pefccal()+484 call 00000000 10E3578 ? BFE1206C ?
BFE11EEC ? 0 ? 20003 ?
BFE12244 ? 98E1266 ?
pefcal()+175 call pefccal() BFE122BC ? 0 ? 926CB7F ?
F4B2504 ? 1 ? EF483C4 ?
pevm_FCAL.()+140 call pefcal() BFE12244 ? 8 ? 35 ? 0 ? 0 ?
BFE1227C ?
pfrinstr_FCAL()+56 call pevm_FCAL() 10E3698 ? 26F94620 ?
pfrrun_no_tool()+40 call 00000000 10E3698 ? 26F93C5A ?
10E36D8 ?
pfrrun()+783 call pfrrun_no_tool() 0 ? E5A3135 ? BFE12520 ?
plsql_run()+742 call pfrrun() 10E3698 ? 0 ? 0 ? 0 ? 0 ? 0 ?
peidxr_run()+205 call plsql_run() 10E3698 ? 2 ? 101AEDC ?
peidxexe()+55 call peidxr_run() 10E3530 ? 1010002 ? 101AEDC ?
A95818 ? 6C00 ? BFE12CF4 ?
kkxdexe()+306 call peidxexe() 10E3530 ? EF483E0 ? F4B2504 ?
7A ? 11 ? 0 ?
kkxmpexe()+167 call kkxdexe() 10E3530 ? A966D8 ? A95818 ?
kgmexwi()+575 call 00000000 F563360 ? 26F3395C ? 9BAA58 ?
10E3530 ? 2D168B34 ? A95818 ?
A95838 ?
kgmexec()+1243 call kgmexwi() F563360 ? 1 ? BFE132DC ?
26F3395C ? 9BAA58 ? FD30AC ?
BFE10002 ? 2D168B34 ?
A95818 ? BFE1315C ?
evapls()+597 call kgmexec() F563360 ? 1 ? BFE132DC ?
2E68937C ? 2AE928F0 ?
A95900 ? A95818 ? 0 ?
A958EC ? 0 ? 79 ?
evaopn2()+436 call 00000000 2C2B89A8 ? BFE14024 ?
EB3352C ? A981D8 ? BFE13B74 ?
E459135 ?
qximeop_fbk.()+397 call 00000000 2C2B89A8 ? 3D9CADBD ? 0 ? 0 ?
0 ? 0 ?
qximeop()+67 call qximeop_fbk.() 2C2B89A8 ? 0 ? C0 ?
BFE13988 ? FCE010 ?
BFE1398C ?
qerocifc()+501 call qximeop() 2C2B89A8 ? 0 ? C0 ?
BFE13988 ?
qerocplFetch()+915 call qerocifc() 2AFD0B0C ? F7EA60 ? 1 ?
qerocFetch()+294 call qerocplFetch() 324538D4 ? 0 ? BFE13A30 ? 0 ?
1 ? F7ED5C ?
qerstFetch()+322 call 00000000 2AFD0B0C ? F7EA60 ? 0 ? 0 ?
1 ? BFE13A2C ?
qervwFetch()+119 call 00000000 2C2B858C ? F7ED5C ? 0 ? 0 ?
1 ? 0 ?
qerstFetch()+322 call 00000000 2C2B8540 ? F7EDD4 ? 0 ? 0 ?
1 ? 3E8 ?
kokbscl()+442 call 00000000 2C2B8228 ? F7EE04 ? 0 ? 0 ?
1 ?
evaopn3.()+1445 call 00000000 2ADCB04C ? 0 ? 0 ? 0 ?
2D0000 ? 403CAD ?
evaopn2()+382 call evaopn3.() 2ADCAFBC ? F7EEE4 ? 101D6CC ?
__PGOSF316_opifcr() call 00000000 2ADCA99C ? 0 ? 0 ? 0 ? 0 ?
+5010 0 ?
qerstRowP()+425 call 00000000 BFE14A70 ? 2 ?
qerfiFetch()+185 call 00000000 2 ? 8E86781 ?
qerstFetch()+176 call 00000000 2ADCAF64 ? F7EEEC ? B19518E ?
BFE14764 ? 2 ? C ?
opifch2()+6022 call 00000000 2ADCAF0C ? F7EF0C ? E4D5180 ?
BFE14A70 ? 2 ?
opiefn0()+523 call opifch2() 89 ? 7 ? BFE14E1C ?
opipls()+3313 call opiefn0() 4E ? 7 ? BFE151B8 ? 0 ? 0 ?
0 ? 0 ? 0 ? 0 ? 0 ?
opiodr()+991 call 00000000 66 ? 7 ? BFE161E8 ?
__PGOSF106_rpidrus( call opiodr() 66 ? 7 ? BFE161E8 ? A ?
)+163
skgmstack()+117 call 00000000 BFE15AE0 ? 0 ? BFE15FFC ?
BFE16204 ? BFE15B04 ?
BFE15AFC ?
rpidru()+95 call skgmstack() BFE15AC4 ? F563120 ? F618 ?
E480FE8 ? BFE15AE0 ?
rpiswu2.()+404 call 00000000 BFE15FFC ? 0 ? E8C1604 ?
6FEC50 ? E8C1604 ? 0 ?
rpidrv.()+1154 call rpiswu2.() 324538D4 ? 59 ? 32453918 ?
16 ? 6EF7B8 ? 59 ?
psddr0()+450 call rpidrv() A ? 66 ? BFE161E8 ? 39 ?
psdnal()+333 call psddr0() A ? 66 ? BFE161E8 ? 31 ?
pevm_EXIM()+183 call 00000000 BFE16E1C ? BFE16F3C ?
F571340 ? 9C5734 ? 2D1D2374 ?
pfrinstr_EXIM()+39 call pevm_EXIM() 713648 ? 6DCED0 ?
pfrrun_no_tool()+40 call 00000000 713648 ? 2B7BB9B8 ? 713688 ?
pfrrun()+783 call pfrrun_no_tool() 2 ? 7136F4 ? BFE16778 ?
plsql_run()+742 call pfrrun() 713648 ? 1 ? EF48A20 ?
EF48A18 ? EF48A10 ? EF489F4 ?
peicnt.()+269 call plsql_run() 713648 ? 1 ? 0 ?
kkxexe()+440 call peicnt() BFE16E1C ? 713648 ? F563360 ?
32918CBC ?
opiexe()+12530 call kkxexe() FA6D68 ? C2870CC ? 1C8CE8 ?
A56BDD7 ? C28708E ?
BFE16FE8 ?
opiall0.()+1450 call opiexe() 49 ? 3 ? BFE17D5C ?
opial7()+444 call opiall0() 3E ? 22 ? BFE18400 ?
BFE1ABF4 ? BFE181B8 ? 0 ?
opiodr()+991 call 00000000 47 ? F ? BFE1ABF4 ?
ttcpip()+1066 call 00000000 47 ? F ? BFE1ABF4 ? 8 ?
opitsk()+1275 call ttcpip() F571340 ? 47 ? BFE1ABF4 ? 0 ?
BFE1A884 ? BFE1AD1C ?
opiino()+882 call opitsk() 0 ? 0 ?
opiodr()+991 call 00000000 3C ? 4 ? BFE1BC38 ?
opidrv()+462 call opiodr() 3C ? 4 ? BFE1BC38 ? 0 ?
sou2o()+91 call opidrv() 3C ? 4 ? BFE1BC38 ?
opimai_real()+109 call sou2o() BFE1BC1C ? 3C ? 4 ?
BFE1BC38 ?
ssthrdmain()+142 call opimai_real() 2 ? BFE1BD30 ?
main()+116 call ssthrdmain() 2 ? BFE1BD30 ?
__libc_start_main() call 00000000 2 ? BFE1BDF4 ? BFE1BE00 ?
+220 BC7A20 ? 0 ? B5D4F0 ?
_start()+33 call __libc_start_main() 85399C4 ? 2 ? BFE1BDF4 ?
A56F5B0 ? A56F5A0 ? BC82D0 ?

So within the code wich ran, the error was raised:

40206, 00000, "invalid setting value for setting name %s"
// *Cause: The input value for the given setting name was invalid.
// *Action: Consult the documentation for the settings table and provide
// a valid setting value.

The involved setting name was “SVMS_CONV_TOLERANCE”. The problem is caused by the NLS setting of your seperator, so:

alter session set NLS_NUMERIC_CHARACTERS=”.,”;

does the trick, but be careful with TOAD, it can be possible that the alter session still gives issues. So a better thing is to ensure it goes well, is to change the seperator settings in TOAD itself and to put as NLS_LANG in the registry “AMERICAN_AMERICA.AL32UTF8″.

Regards, Gerwin