10

I have a problem with DBMS_DATA_MINING.CREATE_MODEL on version 11.2. On 10g this code below works OK, and I'm quite sure that on 11.1 it works too.

CREATE OR REPLACE VIEW "SH"."ITEMS" AS SELECT PROD_ID AS item FROM SALES GROUP BY PROD_ID;
CREATE OR REPLACE VIEW "SH"."TRANSACTIONS" AS SELECT "SH"."SALES"."PROD_ID" AS item , "SH"."SALES"."CUST_ID" tid FROM "SH"."SALES" where cust_id between 100001 AND 104500 GROUP BY cust_id, prod_id;
CREATE TABLE "SH"."AR_SETTINGS" ( "SETTING_NAME" VARCHAR2(30 BYTE), "SETTING_VALUE" VARCHAR2(128 BYTE) );
INSERT INTO SH.AR_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES ('ASSO_MAX_RULE_LENGTH', '6' );
INSERT INTO SH.AR_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES( 'ASSO_MIN_CONFIDENCE', TO_CHAR(0.7));
INSERT INTO SH.AR_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES( 'ASSO_MIN_SUPPORT', TO_CHAR(0.1));

BEGIN DBMS_DATA_MINING.CREATE_MODEL( model_name => 'AR_sh', mining_function => DBMS_DATA_MINING.ASSOCIATION, data_schema_name => 'sh', data_table_name => 'transactions', case_id_column_name => 'tid', settings_schema_name => 'sh', settings_table_name => 'ar_settings'); END;

causes:

ORA-40103: invalid case-id column: TID
ORA-06512: at "SYS.DBMS_DATA_MINING", line 1779
ORA-06512: at line 1
40103. 00000 - "invalid case-id column: %s"
*Cause: The column designated as case-id is not of one of CHAR, VARCHAR2,
NUMBER data type. Case-id columns of type CHAR and VARCHAR2 must
be of length less than or equal to 128 bytes.
*Action: Change the schema of your input data to supply a case-id column
of appropriate data type and/or length.

to be sure:

describe "SH"."TRANSACTIONS"
Name Null Type 
--
ITEM NOT NULL NUMBER 
TID NOT NULL NUMBER

and

select * from v$version;

returns:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
PL/SQL Release 11.2.0.1.0 - Production 
CORE    11.2.0.1.0  Production 
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production 
NLSRTL Version 11.2.0.1.0 - Production

The sample code from dmardemo.sql causes the same error. I don't know what is wrong. Please help.

Vladimir Vaschenko
  • 508
  • 3
  • 19
zacheusz
  • 8,750
  • 3
  • 36
  • 60

4 Answers4

4

Your code and the samples both work for me. I'm also using 11.2.0.1.0, except I'm using 32-bit instead of 64-bit.

I'm not sure what this means. Maybe there was a problem with your installation? You might want to look at Verifying Your Data Mining Installation.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • 1
    I doubt Vista/7 that makes a difference. Once thing Oracle seems to do very well is keep their software consistent between operating systems. I'm surprised that a difference in `cust_id` would make a difference either; your example still worked for me even when I changed the range to return no results. Can you reproduce your problem on another database, one from a fresh 11g install? If your database was upgraded from 10g it looks like there are some additional steps that you must take for data mining: http://docs.oracle.com/cd/E18283_01/datamine.112/e16807/upgrade_odm.htm#sthref285 – Jon Heller Dec 28 '11 at 23:16
  • it happens on fresh 11.2 installation (without upgrade) – zacheusz Dec 29 '11 at 14:44
2

or maybe the range where cust_id between 100001 AND 104500 changed between versions?

Mike
  • 149
  • 4
2

Purely a guess, but is it possible that a synonym for TRANSACTIONS is not pointing to SH.TRANSACTIONS but some other table? I know you specify the schema name SH, but may still cause issues if this is the case (describe TRANSACTIONS to see).

tbone
  • 15,107
  • 3
  • 33
  • 40
1

Thanks for the help. After couple of system restarts it started to work. Without any reason (no configuration changes).

zacheusz
  • 8,750
  • 3
  • 36
  • 60