Home » SQL & PL/SQL » SQL & PL/SQL » View created different between instances (Varchar2 column in view created differently between instances)
View created different between instances [message #272819] Sun, 07 October 2007 17:22 Go to next message
ipentinmaki
Messages: 3
Registered: October 2007
Location: Wisconsin
Junior Member
Between the various instances I've noticed that when a function call that returns a varchar2 is used in the view creation that some instances use the expected size, while others seem to use the "largest" size.
For example, this function (o_text) returns a varchar2 but is being converted to 4 character slice. In some instances Oracle creates a view with a column of size 4, in others it uses a size of 12.
Utilizing the Oracle function User produces similar results.
** Does anyone know what is causing this ?
** Is it something I can change or is this a DB setup setting?

It makes it difficult to compare that the instances are setup correctly.


A Devel Instance
create view junk_view as select substr(o.o_text ('a', 'b'),1,4) stat_txt from dual
Name Type
--------------- --------------
STAT_TXT VARCHAR2(12)
create view junk_view as select substr(user, 1,2) two from dual;
Name Type
---------------- --------------
TWO VARCHAR2(6)
A Production Instance
create view junk_view as select substr(o.o_text ('a', 'b'),1,4) stat_txt from dual
Name Type
----------------- --------------
STAT_TXT VARCHAR2(4)
create view junk_view as select substr(user, 1,2) two from dual;
Name Type
------------------ --------------
TWO VARCHAR2(2)

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
HP-UX B.11.11 U 9000/800



Re: View created different between instances [message #272820 is a reply to message #272819] Sun, 07 October 2007 22:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That IS odd. I thought it always worked like your Prod instance.

Clearly SOMETHING is different in Dev. Initialisation params? Patch number?

You might be able to mitigate it by using the CAST() function to explicitly type the columns.

create view junk_view as 
select cast(substr(o.o_text ('a', 'b'),1,4) as varchar2(4)) stat_txt 
from dual


Ross Leishman
Re: View created different between instances [message #272847 is a reply to message #272819] Mon, 08 October 2007 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on version/patchset and on parameters like "cursor_sharing".
The correct way is to use CAST as Ross showed it.

Regards
Michel
Re: View created different between instances [message #273012 is a reply to message #272847] Mon, 08 October 2007 11:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try comparing the ouput of this in both databases:
select * from v$nls_parameters
where parameter in ('NLS_LENGTH_SEMANTICS', 'NLS_CHARACTERSET')
Re: View created different between instances [message #273022 is a reply to message #272819] Mon, 08 October 2007 13:45 Go to previous messageGo to next message
ipentinmaki
Messages: 3
Registered: October 2007
Location: Wisconsin
Junior Member
This might be it. UTF8 is wrong but sites with WE8ISO8859P1 appear to be right.

"bad view" "good view"
PARAMETER VALUE VALUE
----------------- ------------- -----------------
NLS_CHARACTERSET UTF8 WE8ISO8859P1
NLS_LENGTH_SEMANTICS BYTE BYTE


Re: View created different between instances [message #273023 is a reply to message #272820] Mon, 08 October 2007 13:46 Go to previous messageGo to next message
ipentinmaki
Messages: 3
Registered: October 2007
Location: Wisconsin
Junior Member
Thanks. Cursor sharing is the same in each instance.
NAME VALUE
------------------------------ ------------------------------
cursor_space_for_time FALSE
session_cached_cursors 0
cursor_sharing EXACT
open_cursors 500

I've not figured out a query yet that provides the patch list.
Re: View created different between instances [message #273025 is a reply to message #273023] Mon, 08 October 2007 14:02 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
the byte semantics seems to be responsible for the behavior and it makes sense. Try this before creating the views in your UTF8 database:

alter session set NLS_LENGTH_SEMANTICS = char;


Beware of the impact though of the different widths. Create some max width test data using non-7-bit ASCII characters. UTF8 uses 2 or more bytes for each non-7-bit ASCII character.
Previous Topic: user password
Next Topic: Cartesian Join
Goto Forum:
  


Current Time: Wed Dec 07 22:27:43 CST 2016

Total time taken to generate the page: 0.13391 seconds