length of number column as a char in view
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: length of number column as a char in view

  1. #1
    Join Date
    Feb 2001
    Posts
    20
    I have a table with a field of data type number of lenght 2. But when I create a view on this by converting number field to char the lenght of the column in the view becomes 40.

    create table try ( col1 number(2));

    create view try_view as select to_char(col1)'xy' from try;

    Here the lenght of column becomes varchar2(40).

    Any Idea.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    By default the NUMBER datatype is converted to VARCHAR2(40).

  3. #3
    Join Date
    Feb 2001
    Posts
    20
    That is quite obvious but is there any way to retain the column size in the view

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by stecal
    By default the NUMBER datatype is converted to VARCHAR2(40).
    It depend from db version.
    In 9i NUMBER datatype is converted to VARCHAR2 with length
    as is:
    select length(to_char(87687687.654654)) from dual;
    select length(to_char(8768646546567687.654654)) from dual;
    select vsize(to_char(87687687.654654)) from dual;

    LENGTH(TO_CHAR(87687687.654654))
    --------------------------------
    15
    1 row selected.

    LENGTH(TO_CHAR(8768646546567687.654654))
    ----------------------------------------
    23
    1 row selected.

    VSIZE(TO_CHAR(87687687.654654))
    -------------------------------
    15
    1 row selected.

    create or replace view vs
    ( a )
    as
    select to_char(object_id)
    from all_objects
    where rownum <= 1;

    -- where OBJECT_ID - NOT NULL NUMBER

    select length(a), vsize(a) from vs;
    View created.
    LENGTH(A) VSIZE(A)
    ---------- ----------
    5 5
    1 row selected.





  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You should get used to using the CAST() function to deal with this.

    Select CAST(to_char(my_number) AS CHAR(7)) my_char
    from whatever

    You can be ure of what you are going to represent the function as when you CAST() it.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Shestakov
    It depend from db version.
    In 9i NUMBER datatype is converted to VARCHAR2 with length
    as is:
    Shestakov,

    The isue here is not the length of numbers converted from varchar2 to number, but the implicit declared maximum length of a varchar2 column when converted from number to varchar2 in a view. Describe your view VS and you'll see that its column A is declared as VARCHAR2(40). This hasn't change in 9i either.

    Anyway, slimdave allready gave us the proper sollution...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by jmodic
    Describe your view VS and you'll see that its column A is declared as VARCHAR2(40). This hasn't change in 9i either.
    Anyway, slimdave allready gave us the proper sollution...
    Juriy how u can describe this result, if i specified format in to_char function:

    SQL> create or replace view v_rb (a)
    as
    select to_char(object_id,
    '999999999999999999999999999999999999999')
    from all_objects where rownum <=1;
    desc v_rb;

    View created.

    SQL> Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A VARCHAR2(40)

    SQL> create or replace view v_rb (a)
    as
    select to_char(object_id,
    '9999999999999999999999999999999999999999')
    from all_objects where rownum <=1;
    desc v_rb;

    View created.

    SQL> Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A VARCHAR2(41)

    SQL> create or replace view v_rb (a)
    as
    select to_char(object_id,
    '99999999999999999999999999999999999999')
    from all_objects where rownum <=1;
    desc v_rb;

    View created.

    SQL> Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A VARCHAR2(39)

    View created.

    Oracle create varchar2(xx) variable with length = format_length + 1 (for sign as i gess)

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by danisha
    That is quite obvious but is there any way to retain the column size in the view
    Well, thanks for the pissy answer. You're the one asking the ***simple*** question, so who is to know what is or isn't obvious to you? Your question ("Any idea") sure looks like you DIDN'T know the default behavior. You want a specific answer, then write a specific question. Is "Any idea" related to you not knowing WHY varchar2(40) appears, or HOW to set/change it to something else?


  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Shestakov
    Juriy how u can describe this result, if i specified format in to_char function:

    SQL> create or replace view v_rb (a)
    as
    select to_char(object_id,
    '99999999999999999999999999999999999999')
    from all_objects where rownum <=1;
    desc v_rb;

    View created.

    SQL> Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A VARCHAR2(39)

    View created.
    Yes, that would be another workaround for the original poster's problem.

    I like slimdave's sollution more as it is more elegant, but I'm not sure if CAST() was available prior to 9i. So in case of being unable to use CAST your workaround is a good one.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I currently use CAST() for this in 8.1.7

    I believe there were big enhancements to CAST() in 9i, though they don't affect this prupose.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width