SQL Developer mismatch between generated DDL and Code field of custom types

Question

I'm using SQL Developer to manage an Oracle database. My project is quite old and the DDL to create the db from scratch was missing so I've used SQL Developer's "Database Export" tool to extract the DDL of the db. I'm also using some custom types in some tables to have vector data saved in some columns.

Now, I've noticed that in the sql file generated by SQL Developer, the DDL for the creation of a custom type is:

CREATE OR REPLACE EDITIONABLE TYPE "MY_TYPE" AS TABLE OF  NUMBER;

while after restoring the DB from that SQL file the "Code" field of that type in SQL Developer is:

create or replace TYPE             "MY_TYPE" AS TABLE OF  NUMBER;

First of all, why do all these formatting mistakes (whitespaces and lowercase letters) exist there? And why did the EDITIONABLE keyword disappear?

I am also currently adding documentation to this type, should I use the first or second version to add comments?

Answer 1

There's two things in play here, and one of them you would not normally see.

For example, consider a table. Your script might be:

SQL> create table t ( x int );

Table created.

but if you ask for the DDL for that table from the database, you get this:

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-----------------------------------------------------------
CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER(*,0)
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

There's a lot more stuff, because the database is building the DDL from the attributes stored in the database. It is giving the complete definition including all of the default clauses etc.

Of course, unless you had saved your original script, you would simply assume that the DDL that came from the database is the script.

With a TYPE (or any PL/SQL code), unlike the TABLE example above, the database does indeed saves your original code (because its source code).

Thus when I do something like:

SQL> create or replace
  2  type mytype as TABLE of number;
  3  /

Type created.

it was saved that text exactly as you typed it. We can see that here:

SQL> select text
  2  from user_source
  3  where name = 'MYTYPE';

TEXT
--------------------------------
type mytype as TABLE of number;

So now (unlike the table) we have two options when it comes to giving you the DDL for your type object.

We can build the DDL from scratch, just like we did for the table above:

SQL> select dbms_metadata.get_ddl('TYPE','MYTYPE') from dual;

DBMS_METADATA.GET_DDL('TYPE','MYTYPE')
-------------------------------------------
CREATE OR REPLACE EDITIONABLE TYPE "MCDONAC"."MYTYPE" as TABLE of number;

or we can just grab your code from USER_SOURCE and put "create or replace" in front of it:

SQL> select 'create or replace '||text
  2  from user_source
  3  where name = 'MYTYPE';

'CREATEORREPLACE'||TEXT
--------------------------------------------------
create or replace type mytype as TABLE of number;

Hence the two results you're seeing.

You could argue that the "full" DDL from DBMS_METADATA is the "most" correct, but that's really a subjective debate rather than a cast-iron one.