Type synonym in oracle

Question

Is it possible to create a "type synonym" in oracle? Something like:

CREATE PUBLIC SYNONYM EmailType FOR VARCHAR2(120);

But what´s the syntax? I´ve tried this CREATE TYPE EmailType AS VARCHAR2(120);, but I got a ORA-24344 success with compilation error. — Adilson de Almeida Jr, Sep 08 '11 at 17:33
`CREATE OR REPLACE TYPE EmailType AS OBJECT( EMail VARCHAR2(120));` — Chandu, Sep 08 '11 at 17:56

score 4 · Accepted Answer · answered Sep 14 '11 at 06:30

4

You cannot do that, but you might want to use SUBTYPE. Place SUBTYPE declaration inside of package and use that.

CREATE OR REPLACE PACKAGE SUBTYPES_PKG IS
  SUBTYPE EMAIL IS VARCHAR2(120);
END;

answered Sep 14 '11 at 06:30

Jokke Heikkilä

918
1
8
17

So could then use subtype: l_email_variable SUBTYPES_PKG.EMAIL; – Jokke Heikkilä Sep 14 '11 at 07:19

score 2 · Answer 2 · answered Sep 08 '11 at 18:06

2

What you're looking for is known in other databases as a "domain" - and there ain't no domains in Oracle. Sorry for the bad news. To use a TYPE in Oracle you'll need something like

CREATE OR REPLACE TYPE EMAILTYPE AS OBJECT
  (
  strEmail  VARCHAR2(120)
  )

and then you'll probably need to define constructors and methods to operate on your type.

Share and enjoy.

answered Sep 08 '11 at 18:06

Bob Jarvis - Слава Україні

48,992
9
77
110

@Tegiri: I respectfully disagree. Oracle's type system, in my experience, is a heavyweight solution to the problem of creating meaningful names for 'kinds' of data which brings with it an entire pile of undesireable baggage. I've used both and *for my uses* I find domains a better solution. Others will doubtless disagree. I'd be happy if I could use PL/SQL-ish syntax like "ALTER TABLE X ADD (Y DOMAIN_NAME.TABLE_NAME.COLUMN_NAME%TYPE)" when adding a new column but this is not allowed. YMMV. – Bob Jarvis - Слава Україні Oct 19 '11 at 12:05

Ben · Answer 3 · 2011-09-09T06:43:37.977

0

I don't have access to a database at the moment, so I'll check in the morning but I'm fairly sure you can do this. Although you can't create an object as a public synonym, you can create a synonym on almost any object.

CREATE OR REPLACE TYPE EmailType AS OBJECT( EMail VARCHAR2(120));
create or replace public synonym emailtype  for emailtype;

edited Sep 09 '11 at 06:43

answered Sep 08 '11 at 21:29

Ben

51,770
36
127
149

I think this is ok, but I would like to use the "EmailType" as a synonym for VARCHAR2(120) – Adilson de Almeida Jr Sep 08 '11 at 22:25
This will create `emailtype` as a synonym for `varchar2(120)`, people will still be able to type varchar2(120) though. You shouldn't really stop them. What happens if someone needs create another type with varchar2(120) in and can't? – Ben Sep 09 '11 at 07:50

Type synonym in oracle

3 Answers3