1 Reply Latest reply: Sep 14, 2012 10:05 AM by Jason_(A_Non) RSS

    CLOB Behaviour different on Test and Deve Environment

    961844
      Hi All,

      I'm using below oracle version

      Dev Database -
      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

      QA Database Details -

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


      I’m facing one strange issue. Please help me out.
      I’ve table with one column as CLOB

      {
      create table TEST_DEMO
      (
      DATABODY CLOB
      );

      }

      And I’m executing below query.

      {
      select * from test_demo where databody is null or databody = '';
      }

      its working on DEV database and failing on Testing Database saying inconsistent data type.

      I suspect this is chracterset issue.
        • 1. Re: CLOB Behaviour different on Test and Deve Environment
          Jason_(A_Non)
          Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
           
          SQL> 
          SQL> create table TEST_DEMO
            2  (col1  varchar(10),
            3   DATABODY CLOB
            4  );
           
          Table created
          SQL> insert into test_demo values ('r1',NULL);
           
          1 row inserted
          SQL> insert into test_demo values ('r2','');
           
          1 row inserted
          SQL> insert into test_demo values ('r3','has data');
           
          1 row inserted
          SQL> commit;
           
          Commit complete
          SQL> select * from test_demo where databody is null or databody = '';
           
          COL1       DATABODY
          ---------- --------------------------------------------------------------------------------
          r1         
          r2         
           
          SQL> SELECT value FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
           
          VALUE
          ----------------------------------------
          WE8MSWIN1252
          I suspect this is chracterset issue.
          In the first place, why do you suspect this? Are the two systems the same? If not why?

          Does it happen to only this one table?
          Does it happen to existing tables in the QA DB with CLOB columns?
          Does it happen to new tables in the QA DB, like my above example shows?
          What if you remove one of the WHERE conditions, does it work or do both cause the issue?
          What about a SELECT * FROM test_demo. Does that work?
          What exactly is the full error instead of your partial copy/paste of it?
          A copy/paste of the full set of commands would have been nice.
          What is different between Dev and QA in terms of setup?

          So many questions ... so few details.