Home » SQL & PL/SQL » SQL & PL/SQL » How to Create user defined data types? (Oracle 9i)
How to Create user defined data types? [message #321133] Mon, 19 May 2008 03:33 Go to next message
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 #321142 is a reply to message #321133] Mon, 19 May 2008 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
CREATE TYPE

Regards
Michel
Re: How to Create user defined data types? [message #321203 is a reply to message #321133] Mon, 19 May 2008 07:25 Go to previous messageGo to next message
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 #321223 is a reply to message #321203] Mon, 19 May 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Can you help me. Is there any chance to maintain same insert statement.

No way I know.
But the principle of type is to be... typed, so you have to name the type you want to use.
Actually, what you want is not a real type but an alias for a subtype which is only supported in PL/SQL.

Regards
Michel


Re: How to Create user defined data types? [message #321229 is a reply to message #321203] Mon, 19 May 2008 08:36 Go to previous message
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?
Previous Topic: Needed help for Auditing
Next Topic: variable in a view
Goto Forum:
  


Current Time: Wed Nov 13 06:47:59 CST 2024