How to Create user defined data types? [message #321133] |
Mon, 19 May 2008 03:33 |
ramora
Messages: 16 Registered: November 2006 Location: hyderabad
|
Junior Member |
|
|
Hi Expets,
Please help me. how to create user defined data types.
example :
I am declaring a column with datatype varchar2, after some time i need to increase size of that data type.
Create table test1(x varchar2(200));
alter table test1 modify x varchar2(400);
Instead of altering the table. I will create a user defined data type, there i will change the size of the data type.
Thanks in Advance
Regards,
Ram.
|
|
|
|
Re: How to Create user defined data types? [message #321203 is a reply to message #321133] |
Mon, 19 May 2008 07:25 |
ramora
Messages: 16 Registered: November 2006 Location: hyderabad
|
Junior Member |
|
|
Hi michel,
Thanks for reply.
Actually we are migrating database from MSSQL to Oracle.
In MSSQL,
we can create userdefined datatypes easily, no need to change insert statements also. Please see below example
sp_addtype checktype, 'varchar(11)', null -- Creating user defined data type
create table table01(column1 int, column2 checktype)
insert into table01 values(1,'Microsoft')
But in Oracle,
create or replace type varchar_type as object(
b varchar2(500));
create table t1(c1 int,c2 varchar_type);
insert into t1 values(1,varchar_type('asdf')); -- Insert statement will be changed.
But in my requirement, we need to maintain the same in both DB.
Can you help me. Is there any chance to maintain same insert statement.
Thanks in Advance.
Regards,
RAM.
|
|
|
|
Re: How to Create user defined data types? [message #321229 is a reply to message #321203] |
Mon, 19 May 2008 08:36 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What does MSSQL do if your user-defined type isn't just a subtype of a pre-existing type?
I'm guessing that there is some sort of type constructor that you have to use to populate the field.
If I'm right, the problem isn't that Oracle is forcing you to use constructor functions, but that MSSQL lets you not use them sometimes.
Can you change your MSSQL scripts?
|
|
|