This discussion is archived
10 Replies Latest reply: Feb 2, 2013 1:52 AM by Shahrokh RSS

Unable to create cache groups from CASE-SENSITIVE oracle table's name

Shahrokh Newbie
Currently Being Moderated
Hello All

I have some case-sensitive tables in a oracle database and their columns are the same too. I've tried to cache these tables into TimesTen under a read-only cache group. I think timesten cannot find

case-sensitive tables because as soon as I changed name of the tables, the creation could succeeded. What can I do to overcome this issue? I don't want lose case-sensitive feature. Is it because of

I'm using an old version of TimesTen(11.2.1.4.0)
  • 1. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    ChrisJenkins Guru
    Currently Being Moderated
    I would certainly not start any new project using an old release. If this does not have to use 11.2.1 for soem specific reason then you should use the latest 11.2.2 release.

    How are you creating the cache groups? Are you using SQL Developer or writing your own scripts?

    As in Oracle DB, any mixed case object names where the case must be preserved have to be quoted in TimesTen. For example:

    CREATE TABLE "MixedCase"
    (
    "Column1" VARCHAR2(20) ...

    Same for cache groups.

    Perhaps you can provide a specific example of an Oracle tabel definition and the cache group definition that you tried to create...

    Chris
  • 2. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    Shahrokh Newbie
    Currently Being Moderated
    Hi Chris

    Thanks for your answer. I'm using SQL Developer(both graphical and by command) to manage Timesten db. When I'm about to select root table for cache group i can see the table and when I

    select on it, the caching procedures can not be done and it says your table does not have Primary Key; you can see below that this is not true and the table has two primary key. When I'm

    trying to create the cache group via command in work sheet the error is "TT5140: could not find HLR.SUBSCRIBER. may not have privileges"

    in Oracle:
    CREATE TABLE "HLR"."Subscriber"
    (
    "SSI" NUMBER(10,0) NOT NULL ENABLE,
    "CCNC" VARCHAR2(50 BYTE) NOT NULL ENABLE,
    "Code" VARCHAR2(128 BYTE) DEFAULT NULL NOT NULL ENABLE,
    "Account" NVARCHAR2(32),
    "Mnemonic" NVARCHAR2(15),
    "Region" NVARCHAR2(32),
    "UserAddress" NVARCHAR2(32),
    "Name" NVARCHAR2(32) NOT NULL ENABLE,
    "VPNCode" NUMBER(10,0),
    "VPNCCNC" VARCHAR2(50 BYTE),
    "SubOrgId" NUMBER(10,0),
    "SubscriberTypeId" NUMBER(2,0) DEFAULT 5 NOT NULL ENABLE,
    "StatusId" NUMBER(2,0) DEFAULT 1 NOT NULL ENABLE,
    "SubscriberClass" NUMBER(2,0),
    "DefinedIpAddressId" NUMBER(10,0),
    CONSTRAINT "Subscriber_PK" PRIMARY KEY ("SSI", "CCNC") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE,
    CONSTRAINT "FK_DefinedIpAdd_Subscriber" FOREIGN KEY ("DefinedIpAddressId") REFERENCES "HLR"."DefinedIPAddress" ("Id") ENABLE,
    CONSTRAINT "Fk_Status_Subscriber" FOREIGN KEY ("StatusId") REFERENCES "HLR"."Status" ("Id") ENABLE,
    CONSTRAINT "Fk_SubOrg_Subscriber" FOREIGN KEY ("SubOrgId") REFERENCES "HLR"."SubOrganization" ("Id") ENABLE,
    CONSTRAINT "Fk_SubscriberType_Subscriber" FOREIGN KEY ("SubscriberTypeId") REFERENCES "HLR"."SubscriberType" ("Id") ENABLE,
    CONSTRAINT "Fk_VPN_Subscriber" FOREIGN KEY ("VPNCode", "VPNCCNC") REFERENCES "HLR"."VPN" ("SSI", "CCNC") ENABLE
    )

    in TimesTen:
    CREATE READONLY CACHE GROUP "PRO1"
    AUTOREFRESH MODE INCREMENTAL INTERVAL 5 MINUTES
    STATE PAUSED
    FROM "HLR"."Subscriber"
    (
    "SSI" NUMBER(10,0) NOT NULL ,
    "CCNC" VARCHAR2(50 BYTE) NOT NULL ,
    "Code" VARCHAR2(128 BYTE) NOT NULL ,
    "Account" NVARCHAR2(32),
    "Mnemonic" NVARCHAR2(15),
    "Region" NVARCHAR2(32),
    "UserAddress" NVARCHAR2(32),
    "Name" NVARCHAR2(32) NOT NULL ,
    "VPNCode" NUMBER(10,0),
    "VPNCCNC" VARCHAR2(50 BYTE),
    "SubOrgId" NUMBER(10,0),
    "SubscriberTypeId" NUMBER(2,0) DEFAULT 5 NOT NULL ,
    "StatusId" NUMBER(2,0) DEFAULT 1 NOT NULL ,
    "SubscriberClass" NUMBER(2,0),
    "DefinedIpAddressId" NUMBER(10,0),
    PRIMARY KEY("CCNC","SSI")
    )
  • 3. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    ChrisJenkins Guru
    Currently Being Moderated
    In order to try and isolate the problem can we remove SQL Developer from the loop...

    Can you try executing the CREATE CACHE GROUP statement directly from ttIsql. You will need to connect to the DSN providing all the necessary credentials. For example if your DSN is 'MYHLR' then it would look somehting like:


    ttIsql "DSN=MYHLR;UID=HLR;PWD=ttpwd;OraclepWD=orapwd"

    Where 'ttpwd' is the TimesTen password for the 'HLR' user and 'orapwd' is the Oracle password for the HLR user.

    Once connected just paste in the CREATE CACHE GROUP command and see if it works. If you get an error proceed to troubleshoot it. If it works okay in ttIsql then the problem is specific to SQL Developer. If it doesn't work in ttIsql then you have soem other issues that need to be resolved first.

    Chris
  • 4. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    Shahrokh Newbie
    Currently Being Moderated
    I have the same error using ttIsql. What do you think about privileges error?? I grant all the permission to cacheuser user in Oracle.
  • 5. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    ChrisJenkins Guru
    Currently Being Moderated
    Okay, this is a bug. I just tried it myself and I see the same issue. The problem is due to the mixed case table name. I will log a bug on this but for now the only workaround is to not use mixed case table names.

    Chris
  • 6. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    Shahrokh Newbie
    Currently Being Moderated
    Ok thanks anyway for your reply. I searched the net and it was surprised me that nobody got this issue; I think using mixed case in Oracle is rare.

    When do you think this bug is solved?? Are you in TimesTen developing team??

    Edited by: Shahrokh on Jan 31, 2013 12:03 AM
  • 7. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    ChrisJenkins Guru
    Currently Being Moderated
    We can't say yet when this will be fixed as it has only just been logged. I would suspect that it will be at least a few months before a release that includes a fix but it is only a guess at this stage.

    Chris
  • 8. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    Shahrokh Newbie
    Currently Being Moderated
    I got an idea for solving this issue; what do you think using Materialized View for each table? I mean we can use this as a wrapper. Does this slow

    down transactions dramatically?

    Edited by: Shahrokh on Jan 31, 2013 2:53 AM
  • 9. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    ChrisJenkins Guru
    Currently Being Moderated
    Not sure how an MV will help (you cannot cache an MV, only base tables). And an MV dor sure will slow thinsg down significantly anyway.

    Chris
  • 10. Re: Unable to create cache groups from CASE-SENSITIVE oracle table's name
    Shahrokh Newbie
    Currently Being Moderated
    About speed, you're right but TimesTen can cache a MV; you can test it yourself.

    Thanks anyway for your respond.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points