Updated: July 16, 2020

There are many data types to choose from when defining your table columns. The eighty percent rule tells us we can probably get away with a smaller subset of data types, 8 times out of 10.

Here is what the ‘TYPE’ control looks like in the modeler when defining a column:

Choice is great, but it shouldn’t paralyze you.

I was curious to see what data types were used most in my 11.2.0.3 database, so I fired off a quick query:

SELECT count(*), data_type FROM dba_tab_cols
WHERE data_type NOT LIKE ('%$%')
and owner not in ('SYS', 'SYSTEM')
GROUP BY data_type
order by 1 desc;
COUNT	DATA_TYPE
+++++   ++++++++++++++++++++++++++
30089	VARCHAR2
11562	NUMBER
2553	DATE
2310	RAW
509	CLOB
176	CHAR
170	BLOB
161	XMLTYPE
160	TIMESTAMP(6)
68	XMLTYPEPI
54	NVARCHAR2
50	TIMESTAMP(6) WITH TIME ZONE
39	ROWID
34	XMLTYPEEXTRA
23	SharedValueType754_T
23	SDO_GEOMETRY
22	ANYDATA
21	STRINGLIST
17	MGMT_JOB_VECTOR_PARAMS
15	FLOAT

Want to learn how to copy result sets out of SQL Developer WITH the column headers?

Instead of seeing all 100+ types when designing your model, what if you could…

Create a ‘Favorites’ list of data types

Yes you can!

In the preferences for v3.1 you can establish your ‘preferred’ data types.

A few clicks can save you a lot of scrolling!

So now when I go to create or modify my attributes, I only see this when the ‘Preferred’ check box is ticked:

Now I can blaze through my table definitions

While you’re here, let’s save you ONE Extra Click PER New Column Definition!

By default, the modeler assumes you want your new column to be defined by a domain.

Most of you aren’t using DOMAINs, yet. So in the meantime, tell the Modeler to switch you over to Logical types by default.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. Hi Jeff – this did work and I’m working on a new project and all my data types on a new model have become unknown today. I’ve gone in and I no longer have ANY data types to add in the preferred selection column above (20.3)
    HELP!

  2. Hello to every one, it’s actually a nice for me to pay a quick visit this website, it
    contains precious Information.

  3. Glen Robbins Reply

    In data modeler is it possible to change the Data Type of multiple columns at once? I.e. From a table high light 10 columns and change all of the to varchar2(50) (from varchar2(25))?

Write A Comment