#define Equivalent for Oracle SQL?

Question

Is there a SQL equivalent to #define?

I am using NUMBER(7) for ID's in my DB and I'd like to do something like:

#define ID NUMBER(7)

Then I could use ID in the creation of my tables:

CREATE table Person (
    PersonID ID,
    Name VARCHAR2(31)
)

This will allow me to easily change the type of all of my IDs if I realize I need more/less space later.

Answer 1

Basic definitions (similar to the C #define) are called inquiry directives. the inquiry directive cannot be used to define macros.

You can read about inquiry directives here but I think that what you need it's a simple constant.

constant_name CONSTANT datatype := VALUE;

Answer 2

No, there's no way to make this sort of dynamic definition in Oracle. Although Oracle does have the concept of user-defined column types that let you define this globally in a single object, once you create a table that has a column of this type the definition is frozen and you'll get an "ORA-02303: cannot drop or replace a type with type or table dependents" if you try to redefine the type.

Answer 3

In SQL Plus scripts you can use a substitution variable like this:

DEFINE ID = "NUMBER(7)"

CREATE table Person (
    PersonID &ID.,
    Name VARCHAR2(31)
);

Answer 4

I'd Strongly recommend getting a copy of PowerDesigner, which you let you do this through the use of domains.

Answer 5

This code declare two constants. We aren't going to use the constants itself, but their types. In the create table statement, we declare Name and Blood as "The same type of blood_type and name_type". If later we need more space, we only have to change the types of the constants and all the fields declared with %TYPE in the script will be affected. I hope it help you.

DECLARE
   blood_type CONSTANT  CHAR;
   name_type CONSTANT varchar2(10);
BEGIN
   create table patient (
        Name name_type%TYPE,
        Blood blood_type%TYPE

)
END;
/

EDITED: as dpbradley said there is a problem with this code. Oracle don't let create tables directly inside a begin-end block. The DBMS_SQL package has to be used to create a table dinamically and this would make the solution unreadable and uncomfortable. Sorry.