Char detected as byte on production server.

mcnamaragio · June 10, 2015 7:21AM

I have the same table on two different servers but char columns are detected as byte on the production server. Here is how it looks:

It would be nice if there was an option to at least ignore the error. It would be better if the error didn't happen at all.

richardjm · June 10, 2015 7:24AM

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

mcnamaragio · June 10, 2015 7:27AM

What details do you need apart from table schema definition?

richardjm · June 10, 2015 7:29AM

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

mcnamaragio · June 10, 2015 7:57AM

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?

richardjm · June 10, 2015 9:44AM

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

mcnamaragio · June 10, 2015 10:24AM

So can we have an option to ignore CHAR_USED ?

richardjm · June 10, 2015 10:35AM

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.

mcnamaragio · June 10, 2015 10:44AM

Screenshot of what?

richardjm · June 10, 2015 10:48AM

I think you tried attaching a screenshot with your original post.

mcnamaragio · June 10, 2015 10:53AM

Just sent it to support email.

richardjm · June 10, 2015 11:03AM

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?

mcnamaragio · June 10, 2015 11:08AM

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.

richardjm · June 10, 2015 11:16AM

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.

mcnamaragio · June 10, 2015 11:45AM

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

richardjm · June 10, 2015 11:47AM

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

mcnamaragio · June 10, 2015 11:58AM

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

richardjm · June 10, 2015 12:02PM

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:09PM

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.

mcnamaragio · June 11, 2015 5:28AM

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

mcnamaragio · July 13, 2015 3:58AM

Any decision about this topic?

richardjm · July 16, 2015 2:42AM

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.

mcnamaragio · July 16, 2015 2:49AM

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.

richardjm · July 16, 2015 11:24AM

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!

mcnamaragio · July 23, 2015 1:42PM

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

richardjm · July 27, 2015 3:41AM

Enjoy your holidays

mcnamaragio · August 3, 2015 9:49AM

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

richardjm · August 3, 2015 10:42AM

Sure try...

http://download.red-gate.com/Oracle/Sch ... cad9f2.exe

mcnamaragio · August 5, 2015 3:38AM

I tried it but the result is same.

richardjm · August 5, 2015 3:48AM

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

mcnamaragio · August 5, 2015 4:35AM

Bingo! It works great.

Char detected as byte on production server.

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk