Create View and Set Datatypes

[GROKING]

Is it possible to create a view and setting the datatypes and sizes you need rather than using the source table defaults.

This code did not work.

CREATE OR REPLACE FORCE VIEW "SYS"."NICHECOM_SITES3_VW" ("site_id" NUMBER(15), "site_name" VARCHAR2(55), "status" VARCHAR2(15)) AS
select "site_id", "site_name", "status"
from sites@nichecom2;

Or do I need to create an object type and call it from the view to accomplish this?

Thanks for the help!!

 

[djbjr]

I imagine the following will work

CREATE OR REPLACE FORCE VIEW "SYS"."NICHECOM_SITES3_VW" 
AS 
select 
to_number(substr(to_char("site_id"), 1, 15)) "site_id", 
substr("site_name", 1, 55) "site_name", 
substr("status", 1, 15) "status"
from sites@nichecom2;

EXAMPLE

SQL> create table sites ("site_id" NUMBER(30),
  2  "site_name" VARCHAR2(100),
  3  "status" VARCHAR2(100));

Table created.

SQL> desc sites
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 site_id                                            NUMBER(30)
 site_name                                          VARCHAR2(100)
 status                                             VARCHAR2(100)



SQL> CREATE VIEW view_sites AS 
  2  select 
  3  to_number(substr(to_char("site_id"), 1, 15)) "site_id", 
  4  substr("site_name", 1, 55) "site_name", 
  5  substr("status", 1, 15) "status"
  6  from sites;

View created.

SQL> desc view_sites
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 site_id                                            NUMBER
 site_name                                          VARCHAR2(55)
 status                                             VARCHAR2(15)

 

[SantaMufasa]

Groking,

If you wish to completely change a VIEW's columns' data types, check out the CAST function.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]