#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.

Is your intent to allow dynamic redefinition of the column, i.e. to change the definition of the column in multiple tables by a single change after the table is created? I see a couple of the answers seem to be aimed at defining the attribute globally for first time creation of the table. — dpbradley
– dpbradley, Commented Jul 13, 2009 at 18:08
I only want to use the variable for the first time, when the table is created. I have lots of tables that will use the ID 'type' and if I decide halfway through creation of the schema to change the ID size, I want to be able to change it in one place. — jjnguy
– jjnguy, Commented Jul 13, 2009 at 18:10
But then won't you have half of the tables with one ID size and half with another? — dpbradley
– dpbradley, Commented Jul 13, 2009 at 18:19
I plan on writing one large script for creating all of the tables. I would like to be able to easily change my mind about the type of the ID field before creating the tables. Also, if someone wants to use my schema, it would be nice for them to be able to easily change the ID size. — jjnguy
– jjnguy, Commented Jul 13, 2009 at 18:40
OK, I understand now - in that case Tony's answer is a SQL*Plus solution. As Stephanie mentions, using a database modeling tool to generate your scripts is another approach. — dpbradley
– dpbradley, Commented Jul 13, 2009 at 19:47

Tony Andrews · Accepted Answer · 2009-07-13 17:06:35Z

4

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

DEFINE ID = "NUMBER(7)"

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

answered Jul 13, 2009 at 17:06

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan · Accepted Answer · 2009-07-13 16:30:08Z

0

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;

answered Jul 13, 2009 at 16:30

Jonathan

12k5 gold badges59 silver badges92 bronze badges

4 Comments

Jonathan Over a year ago

Note that you can define a constant like ID, but the type in the create table statement should be ID%TYPE, not just ID. If you change the type of the ID, you're also changing all the fields and vars that has a ID%TYPE, exactly as you want.

jjnguy Over a year ago

I hate to ask, but could you try to give me a specific example for my case? I am very new to sql.

Jonathan Over a year ago

of course, sorry for the later, I was in the shower. I will left you a piece of code in another answer right now.

dpbradley Over a year ago

OK, I understand now - in that case Tony's answer is a SQL*Plus solution. As Stephanie mentions, using a database modeling tool to generate your scripts is another approach.

dpbradley · Accepted Answer · 2009-07-13 16:48:37Z

0

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.

answered Jul 13, 2009 at 16:48

dpbradley

12k2 gold badges33 silver badges34 bronze badges

Comments

Stephanie Abingdon · Accepted Answer · 2009-07-13 17:31:01Z

0

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

answered Jul 13, 2009 at 17:31

Stephanie Abingdon

Comments

Jonathan · Accepted Answer · 2009-07-13 18:56:15Z

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.

Collectives™ on Stack Overflow

#define Equivalent for Oracle SQL?

5 Answers 5

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related