 | |
user defined data type: does they exist ?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: user defined data type: does they exist ? -
01-26-2008
, 07:00 PM
|
On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference -- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- |
Mark,
I believe the TYPE could also represent objects with a single definition:
CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20));
CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1);
This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You
can create a domain of a specified name, size and default values... for
example:
SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL;
SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM);
SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM);
And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and
default value from domain address_data2_dom.
Any where you need a 20 character data type you can use this domain.
Cool thing is that when you then alter the domain to be - say
varchar2(30) - any where you have used this domain, the column
automatically gets modified - so you don't have to find all the places
where you used it and alter that table.
#22
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference -- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table. |
In answer to the original question, no, you cannot create scalar types
in Oracle.
#23
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference -- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table. |
In answer to the original question, no, you cannot create scalar types
in Oracle.
#24
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference -- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table. |
In answer to the original question, no, you cannot create scalar types
in Oracle.
#25
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference -- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table. |
In answer to the original question, no, you cannot create scalar types
in Oracle.
#26
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference *-- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table.- Hide quoted text - - Show quoted text - |
definitions do not belong being used as column definitions in
permanent tables. If the type is varchar2(20) just use varchar2(20).
Adhoc query tool, ODBC, etc... just do not deal well with stored
objects in realtional databases.
HTH -- Mark D Powell --
#27
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference *-- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table.- Hide quoted text - - Show quoted text - |
definitions do not belong being used as column definitions in
permanent tables. If the type is varchar2(20) just use varchar2(20).
Adhoc query tool, ODBC, etc... just do not deal well with stored
objects in realtional databases.
HTH -- Mark D Powell --
#28
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference *-- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table.- Hide quoted text - - Show quoted text - |
definitions do not belong being used as column definitions in
permanent tables. If the type is varchar2(20) just use varchar2(20).
Adhoc query tool, ODBC, etc... just do not deal well with stored
objects in realtional databases.
HTH -- Mark D Powell --
#29
| |||
| |||
|
|
Mark D Powell wrote: On Jan 18, 5:39 am, alberto.rol... (AT) gmail (DOT) com wrote: SQL Manual entry CREATE TYPE PL/SQL User's Guide and Reference *-- contains many examples of using object types hello, thanks. I've already tried it, but it seems to me that it can "only" create complex types, something like struct in C++. And to use them, I have to use a "constructor" in the insert statement. I need to be able to do only something like insert into table AA (column1) values ('this is a string with maximum 50 chars') and not something like insert into table AA (column1) values (CODE('this is a string with maximum 50 chars')) Maybe I missed something on the create type syntax ? thank you very much! If you object type consists of a single varchar2 value why do you need an object at all? My professional opinion based on experience and common sense is that objects do not belong stored in a relational database but should be created and manipulated in the application layer. Adhoc query tools and end users do not relate to stored objects. You can however sometimes use objects to advantage in PL/SQL code that manipulates data. HTH -- Mark D Powell -- Mark, I believe the TYPE could also represent objects with a single definition: CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20)); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example: SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM); And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom. Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table.- Hide quoted text - - Show quoted text - |
definitions do not belong being used as column definitions in
permanent tables. If the type is varchar2(20) just use varchar2(20).
Adhoc query tool, ODBC, etc... just do not deal well with stored
objects in realtional databases.
HTH -- Mark D Powell --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.