Char detected as byte on production server.

• 
  Are the servers the same version of Oracle? Any chance you could give the SQL for the table definition on the different servers so we could try to replicate it.
  
  You can either post the information here or email to support@red-gate.com.
  
  Also your image didn't work

  richardjm June 10, 2015 07:24

  - 0 +
• 
  What details do you need apart from table schema definition?

  mcnamaragio June 10, 2015 07:27

  - 0 +
• 
  I think just the versions of Oracle used and the table definitions so that we can recreate and investigate the issue.
  
  If you're using the tool getting the SCO_Comparison.log file would be really helpful as it helps identify what we thought was different when we compared - details on how to enable logging are at http://documentation.red-gate.com/displ ... +log+files

  richardjm June 10, 2015 07:29

  - 0 +
• 
  I checked version and it's the same: 11.2.0.3.0
  
  I tried comparing it myself with all_tab_cols and found this:
  
  CHAR_USED - production has B, local C. DATA_LENGTH - production has 1, local 4.
  
  PL/SQL developer shows VARCHAR2(1) in both cases. Can this be caused by server configuration?

  mcnamaragio June 10, 2015 07:57

  - 0 +
• 
  That sounds like that may be the issue then. It looks like we short-hand a varchar2(1) to be a byte - would that be correct?
  
  A quick google came across https://community.oracle.com/thread/1029910

  richardjm June 10, 2015 09:44

  - 0 +
• 
  So can we have an option to ignore CHAR_USED ?

  mcnamaragio June 10, 2015 10:24

  - 0 +
• 
  I'd be loath to add another option for this case I'd prefer to understand what effects our behaviour is having currently.
  
  As I understand it if something is a VARCHAR2(1) in CHAR then it can store a character - always.
  However to store a single character in BYTE you would need up to a VARCHAR2(4 BYTE).
  
  Can you send in the screenshot so I can see what you're seeing to try to really understand what the problem is as I'm still struggling.

  richardjm June 10, 2015 10:35

  - 0 +
• 
  Screenshot of what?

  mcnamaragio June 10, 2015 10:44

  - 0 +
• 
  I think you tried attaching a screenshot with your original post.

  richardjm June 10, 2015 10:48

  - 0 +
• 
  Just sent it to support email.

  mcnamaragio June 10, 2015 10:53

  - 0 +
• 
  OK I think I finally understand (having seen your image) sorry it has taken so long, I blame lack of coffee today.
  
  We report the difference in the CHAR_USED for the tables - correctly. But that isn't the desired behaviour you are after. You want to consider the tables equal even if the column CHAR_USED is different - even though the capacity of a 1 BYTE is different to that of a 1 CHAR.
  
  Is that correct?

  richardjm June 10, 2015 11:03

  - 0 +
• 
  When I view the table sql in pl/sql developer I see the same script so for me the two tables are identical. I think CHAR_USED being different it's caused by server configuration differences so I want to ignore it. I found 'show parameter NLS_LENGTH_SEMANTICS' command but I can't execute it on the production server. I think that the tool should either ignore differences if NLS_LENGTH_SEMANTICS reports different result or have an option to ignore it manually.

  mcnamaragio June 10, 2015 11:08

  - 0 +
• 
  Well I think in this case the tables could behave very differently in production and non production as if you attempt to insert a unicode character in production that occupies more than one byte it wouldn't work whereas it would work fine in your development server.
  
  CHAR_USED can actually be set per column on each table independently I believe so having an option to ignore it due to the server default wouldn't work - as like you have encountered I'm not sure we can guarantee to even be able to determine the server default with non-dba credentials.
  
  I could fairly easily add an option to ignore CHAR_USED for the project but then I'd be concerned as I say above that data that would fit in a CHAR in development wouldn't fit in the BYTE in production.

  richardjm June 10, 2015 11:16

  - 0 +
• 
  You are right but why does PL/SQL developer show the same script on both servers?

  mcnamaragio June 10, 2015 11:45

  - 0 +
• 
  I can't answer that but I have found issues in the scripting by PL/SQL developer in the past - it isn't perfect.

  richardjm June 10, 2015 11:47

  - 0 +
• 
  What do you suggest? Perhaps you can add two modes of comparison? strict and not so strict ?

  mcnamaragio June 10, 2015 11:58

  - 0 +
• 
  So I take it this causes you issues as you don't want to change your production environment from BYTE - perhaps you could reverse sync to get your development environment to also use BYTE and then they would match in both. Then you could move forward modifying your development environment using BYTE to match production.
  
  Just a thought.
  
  (I'm about to head off today so won't reply quite as quickly as I have been)

  richardjm June 10, 2015 12:02

  - 0 +
• 

  richardjm wrote:

  I can't answer that but I have found issues in the scripting by PL/SQL developer in the past - it isn't perfect.

  
  Actually a quick thought is that Oracle may be scripting according to the local defaults of the server. Which makes sense..... unless you want to run it on another server where the default are different, like in your case. As a VARCHAR2(1 CHAR) has a very different meaning to a VARCHAR2(1 BYTE) even though CHAR and BYTE may be local server defaults.

  richardjm June 10, 2015 12:09

  - 0 +
• 
  I found that you can get NLS_LENGTH_SEMANTICS from nls_database_parameters and nls_instance_parameters tables.

  mcnamaragio June 11, 2015 05:28

  - 0 +
• 
  Any decision about this topic?

  mcnamaragio July 13, 2015 03:58

  - 0 +
• 

  mcnamaragio wrote:

  I found that you can get NLS_LENGTH_SEMANTICS from nls_database_parameters and nls_instance_parameters tables.

  
  What are the results are your two servers do you happen to know - and is that information available even to a restricted user?
  
  I'm still concerned that the databases are technically different and that's what we're noticing.

  richardjm July 16, 2015 02:42

  - 0 +
• 
  Yes, that information is available to a restricted user.
  
  On live server both queries return NLS_LENGTH_SEMANTICS set to BYTE while on the development server it is set to CHAR.

  mcnamaragio July 16, 2015 02:49

  - 0 +
• 
  Right I've gone and put a new option in for you that is tentatively called "Ignore alphanumeric length semantics" - doesn't that just roll off the tongue (it has yet to be string reviewed by our technical authors as you can probably tell)
  
  Can you give the following build a go and see if it behaves as you expect.
  
  ftp://support.red-gate.com/patches/Sche ... cad9f2.exe
  
  Enjoy!

  richardjm July 16, 2015 11:24

  - 0 +
• 
  Thanks. I am on vacation and will check it when I'm back in the beginning of August.

  mcnamaragio July 23, 2015 13:42

  - 0 +
• 
  Enjoy your holidays

  richardjm July 27, 2015 03:41

  - 0 +
• 
  Can you upload it to an http site? I think ftp is blocked here.

  mcnamaragio August 03, 2015 09:49

  - 0 +
• 
  Sure try...
  
  http://download.red-gate.com/Oracle/Sch ... cad9f2.exe

  richardjm August 03, 2015 10:42

  - 0 +
• 
  I tried it but the result is same.

  mcnamaragio August 05, 2015 03:38

  - 0 +
• 
  Did you select the "Ignore character length semantics" option for the project? By default it is turned off.

  richardjm August 05, 2015 03:48

  - 0 +
• 
  Bingo! It works great.

  mcnamaragio August 05, 2015 04:35

  - 0 +

Add comment

Please sign in to leave a comment.