Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003

    Unanswered: Different data type between views and its mother queries

    Hi guys.
    Is it possible to create a view and have one or more fields with different data type than what's specified in the original query structure ?


  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    What do you have in mind? I'd say that this request doesn't sound regular ... for example,

    CREATE VIEW emp_view AS
    SELECT empno, sal FROM emp;

    creates a view. Now you would like to describe the view and see datatypes different from the ones in the 'emp' table? I don't think that it is possible, UNLESS you create the view differently, for example:

    CREATE VIEW emp_view AS
    SELECT empno, TO_CHAR(sal) sal FROM emp;

    Now, describing the 'emp' table and 'emp_view' view, you'd see different datatypes of the 'sal' column.

  3. #3
    Join Date
    Jun 2004
    Liverpool, NY USA
    See the following

    SQL> desc all_objects
     Name                            Null?    Type
     ------------------------------- -------- ----
     OWNER                           NOT NULL VARCHAR2(30)
     OBJECT_NAME                     NOT NULL VARCHAR2(30)
     SUBOBJECT_NAME                           VARCHAR2(30)
     OBJECT_ID                       NOT NULL NUMBER
     DATA_OBJECT_ID                           NUMBER
     OBJECT_TYPE                              VARCHAR2(18)
     CREATED                         NOT NULL DATE
     LAST_DDL_TIME                   NOT NULL DATE
     TIMESTAMP                                VARCHAR2(19)
     STATUS                                   VARCHAR2(7)
     TEMPORARY                                VARCHAR2(1)
     GENERATED                                VARCHAR2(1)
     SECONDARY                                VARCHAR2(1)
    SQL> create view test
      2  as
      3  select owner,object_name,cast(object_id as varchar2(10)) object_id
      4  from all_objects;
    View created.
    SQL> desc test
     Name                            Null?    Type
     ------------------------------- -------- ----
     OWNER                           NOT NULL VARCHAR2(30)
     OBJECT_NAME                     NOT NULL VARCHAR2(30)
     OBJECT_ID                                VARCHAR2(10)
    SQL> select * from test where rownum < 5;
    OWNER                          OBJECT_NAME                    OBJECT_ID
    ------------------------------ ------------------------------ ----------
    SYS                            DUAL                           195
    PUBLIC                         DUAL                           196
    SYS                            SYSTEM_PRIVILEGE_MAP           228
    PUBLIC                         SYSTEM_PRIVILEGE_MAP           230
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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