Disappearing Columns

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

At the site where I am currently working Oracle Designer is used. The version they have here, Designer 10, does not supprt XMLType and this posed a
problem at first.
Table script could not be generated because it is not possible to define a column of XMLType. What we did was create a user defined type called XMLType and use this fake-XMLType
for the table definitions. When you generate a table-creation script, it will look like this:

CREATE TABLE XML_TEST<br /> (ID NUMBER(10,0) NOT NULL<br /> ,FAKE_XML XMLTYPE<br /> )<br />/<br />


But that is not all, the generator also creates a script for the XMLType itself:

PROMPT Creating Object Type 'XMLTYPE'<br />CREATE OR REPLACE TYPE XMLTYPE AS OBJECT<br /> (X INT<br /> )<br />/<br />

If you are not careful this fake XMLType is created in the database and none of your XMLType methods will work properly, if at all.

And guess what happened yesterday? We forgot to remove the "create-type" script, so the fake-XMLType was created and all fell down. Removing the fake-XMLType was the only option, but
you are not allowed to do so when dependents exist, like a column in a table.

Where did it go?

To remove the fake-XMLType I issued

SQL&gt; drop type xmltype<br />  2  /<br />drop type xmltype<br />*<br />ERROR at line 1:<br />ORA-02303: cannot drop or replace a type with type or table dependents<br />

That obviously didn’t work, so I force dropped it:

SQL&gt; drop type xmltype force;<br /><br />Type dropped.<br /><br />SQL&gt;<br />

And that did the job…. and more. It also removed the columns in the table that were of the XMLType. So the table above changed to:

SQL&gt; desc xml_test<br /> Name       Null?    Type<br /> ---------- -------- ----------<br /> ID         NOT NULL NUMBER(10)<br /><br />SQL&gt;<br />

Actually the columns was not dropped, but marked as UNUSED and this columns become inaccessible.
(more info in the documentation)
To find out how many columns are marked as UNUSED, you can query the datadictionary view USER_UNUSED_COL_TABS. It is also
possible to explicitly mark a column as UNUSED explicitly:

alter table table_name set unused (column_name);<br />

It is not possible reverse this action, you can not mark the column as USED.

Live and learn

Instead of being smart and fix the problem with a single statement, the problem only got worse. Instead of getting rid of the fake XMLtype by dropping it, I had
to recreate the tables as well. If only I had paid a little more attention when I executed the create-scripts…

Oh well, you live and learn. I won’t do that again… until the next time.

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author


  1. Remco Blaakmeer on

    I just thought of a solution to this problem, with this trigger:
    create or replace trigger trg_prevent_create_xmltype
    before create
    on database
    if ora_dict_obj_owner != ‘SYS’
    and upper(ora_dict_obj_name) = ‘XMLTYPE’
    raise_application_error(-20000, ‘Will not create XMLTYPE (ignore this error)’);
    end if;
    This generates an error when you forget to strip the CREATE TYPE statement. This might not be what you want if you execute the DDL from the Design Editor, but I don’t do that anyway.

  2. Alex Nuijten on

    Hi Rachana,
    No that is not possible. I can see any praticle use for it either….

    SQL> set serveroutput on
    SQL> begin
    2 dbms_output.put_line (sqlerrm (-12983));
    3 end;
    4 /
    ORA-12983: cannot drop all columns in a table

    PL/SQL procedure successfully completed.


  3. Bug, SR has been accepted by Oracle on all levels (not only the one shown here). I (but you triggered me, Alex) refer to this one, as my newly discovered (SQL) “Database Injection” bug (because it HAS some security implications, if you think about it…), and is not only data corruption related.

    Alex, Harm, follow up can be read the SR via the BetaCSI

  4. Although not predefined in Designer, it HAS support for xmltype. You have to create the type yourself as TYPE object. After that you can reference to it in your tables and views and program units.

  5. Ah, good point…. you were miles ahead of me, now I understand what you mean. Thank you.

  6. My problem is that it should behave as the following DATE contra XMLTYPE example… (in which the DATE behavior is the recommended one…)

    To show that it shouldn’t be happening or it is at least inconsistent behavior, two examples: one with “XMLTYPE” and one with “DATE”…

    2 (X INT
    3 )
    4 /

    Type created.

    SQL> drop type mydate;

    Type dropped.

    2 (X INT
    3 )
    4 /
    ERROR at line 1:
    ORA-02302: invalid or missing type name

    2 (X INT
    3 )
    4 /

    Type created.

    SQL> show user
    USER is “SYSTEM”

  7. The real problem is that Oracle is not doing any further development with Designer.

    Designer should support built in types and new data types like Intervals, etc.

    It doesn’t.

    As we move on to 11g and onward it will become less and less feasible to build systems using it.

  8. @Nigel:
    Unfortunately it is not possible in Designer to set a flag to prevent generation of Types, you can for packages, procedures and the like but not for Types.
    I am having trouble to see your workaround though, how is it possible to define a column in a table which has the XMLType as it’s datatype?
    Good point on revoking the CREATE TYPE privilege, too bad we need it for other Types.

    Do you ever sleep? 😉
    The XMLType that was created erroneously by the Designer Generator was the one I wanted to get rid of. Not the “real” XMLType which would destroy your XML DB…
    Talking about reserved words, consider this:

    SQL> declare
    2 xmltype varchar2(10);
    3 begin
    4 xmltype := ‘Hello’;
    5 dbms_output.put_line (xmltype);
    6 end;
    7 /

    PL/SQL procedure successfully completed.

    SQL> ed
    Wrote file afiedt.buf

    1 declare
    2 date varchar2(10);
    3 begin
    4 date := ‘Hello’;
    5 dbms_output.put_line (date);
    6* end;
    SQL> /

    PL/SQL procedure successfully completed.


  9. Marco Gralike on

    Hell of a way to destroy your XML DB environment in ONE statement. Tested it on EE and will log a SR for this.

  10. Alex,

    Can’t you mark the user type definition in Designer to prevent DDL generation? or does that stop you generating the columns themselves?

    If not, you could try fooling Designer; make a variant of USER_TYPES that includes XMLTYPES – something like:

    create view USER_TYPES as
    select * from sys.user_types
    select ‘XMLTYPE’, …
    from DUAL where not exists (
    select 1 from sys.user_types
    where type_name=’XMLTYPE’

    Of course it’s horrible – but it might be worth it if you think this mistake might happen again…

    Finally, if you don’t normally use user-defined types, try revoking CREATE TYPE privilege.

    Regards Nigel