Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    38

    Unanswered: Column Datatype changing in VIEW creation!!!

    Hi,
    When we create a view by selecting all the columns in a table. The view is created with some columns as number which are varchar in the table. We found that if all the record contains null value, the view creation is changing the data type. Is there any way to over come this....Advise please.

    It is really urgent!!!

    Thank you.

    avr.

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    use decode or case

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Can you show some code? Also, it's good practice to post your Oracle release number (first three digits). I am unable to reproduce after several attempts. As you can see in the below examples, Oracle always returns correct data type even when columns are null.

    create table test (col1 varchar2(10), col2 varchar2(10), col3 number);

    create or replace view test_v1 as select * from test;

    desc test_v1;

    Name Null? Type
    ----------------------------------------------------- -------- ------------
    COL1 VARCHAR2(10)
    COL2 VARCHAR2(10)
    COL3 NUMBER

    create or replace view test_v2 (colA) as select col1 || col2 from test;

    desc test_v2;

    Name Null? Type
    ----------------------------------------------------- -------- ------------
    COLA VARCHAR2(20)

    create or replace view test_v3 (colA) as select col1 || col3 from test;

    desc test_v3;

    Name Null? Type
    ----------------------------------------------------- -------- ------------
    COLA VARCHAR2(50)
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I cannot reproduce what you are saying, nor I have seen this behaivor, can you give us a concrete example of what you are actually facing ( table/view definitions, sample data ) ? .. I believe only if you are casting the values of the table as something else they might change in the view creation, otherwise they shoulnd't.
    Code:
    SQL@8i> create table t ( a varchar2(1), b number );
    
    Table created.
    
    SQL@8i> insert into t values ( 1, 2 );
    
    1 row created.
    
    SQL@8i> insert into t values ( null, 3 );
    
    1 row created.
    
    SQL@8i> insert into t values ( 3, null );
    
    1 row created.
    
    SQL@8i> insert into t values ( null, null );
    
    1 row created.
    
    SQL@8i> insert into t values ( 'A', 4 );
    
    1 row created.
    
    SQL@8i> create view t_view as select * from t;
    
    View created.
    
    SQL@8i> desc t_view;
     Name                            Null?    Type
     ------------------------------- -------- ----
     A                                        VARCHAR2(1)
     B                                        NUMBER
    
    SQL@8i>

  5. #5
    Join Date
    Jul 2003
    Posts
    38

    More info.

    Thanks for your suggestions and ideas.

    As mentioned by yourself, it works fine if less number of columns. We are creating the view with 150 columns. And also if i create the view using

    CREATE OR REPLACE VIEW VW_1 (A1,A2,..A150) AS SELECT COL1,COL2,..COL159 FROM TB_1;

    command, It is NOT working.And we are facing the above mentioned issue.
    But if we create the same using

    CREATE OR REPLACE VIEW VW_1 AS
    SELECT COL1 A1, COL2 A2 ... COL150 A150 FROM TB_1;

    command, it is creating fine. Anyway By using the other way, we proceeded. Please clarify, if anyone has more info.

  6. #6
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    If you use the CREATE FORCE VIEW command in order to create a view that otherwise has errors, then the datatype of one or more columns will be converted from VARCHAR2 to NUMBER. Here's an example,

    /* ***
    First, I try to create a view, but Oracle stops me due to aliasing errors.
    *** */

    SQL> create view test_v1 (col1, col2, col3) as select col1, col2, col3, col4 from test;
    create view test_v1 (col1, col2, col3) as select col1, col2, col3, col4 from test
    *
    ERROR at line 1:
    ORA-01730: invalid number of column names specified


    /* ***
    Second, I re-run the exact same command with the FORCE option.
    *** */


    SQL> create force view test_v1 (col1, col2, col3) as select col1, col2, col3, col4 from test;

    Warning: View created with compilation errors.

    /* ***
    The view exists, but look at the column descriptions.
    *** */


    SQL> desc test_v1;
    Name Null? Type
    ----------------------------------------------------- -------- -----------------------------
    COL1 VARCHAR2(10)
    COL2 VARCHAR2(10)
    COL3 NUMBER


    This is a known bug in Oracle 8i, 9i, and 10g. It is slated to be fixed in 10g Release 2. In the mean time "don't do that."
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  7. #7
    Join Date
    Sep 2006
    Location
    India-Bangalore
    Posts
    8

    How to change datatype for the view ?

    Hi All,

    Can we change the column data type while creating the view ? I am using the same example.


    > create table test (col1 varchar2(10), col2 varchar2(10), col3 NUMBER);

    Note: Though col1 is varchar, It stores the numeric value.

    > Create or replace view test_v2 as select * from test;

    desc test_v2;

    Name Null? Type
    ----------------------------------------------------- -------- ------------
    COL1 VARCHAR2(20)
    COL2 VARCHAR2(20)
    COL3 NUMBER

    When creating the view, I need the data type as Number for the column COL1. Appreciate any one help me in this regard.

    Thanks,
    Naren

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    SQL> create table test (col1 varchar2(10), col2 varchar2(10), col3 NUMBER);
    
    Table created.
    
    SQL> create view test_v2 as
      2    select to_number(col1) col1,
      3           col2,
      4           col3
      5    from test;
    
    View created.
    
    SQL> desc test_v2;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
    
     COL1                                               NUMBER
     COL2                                               VARCHAR2(10)
     COL3                                               NUMBER
    
    SQL>

  9. #9
    Join Date
    Sep 2006
    Location
    India-Bangalore
    Posts
    8
    g8, worked for me. Thanks a lot.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by narendran
    Note: Though col1 is varchar, It stores the numeric value.
    Why? This is begging for trouble.

Posting Permissions

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