Alter Type Syntax Not Recognized

Toad for Oracle
1

Hello,

I’m using Toad 10.0. When I have a CREATE TYPE DDL statement with an
embedded Alter Type, The Toad editor gives a syntax check error. For example:

CREATE OR REPLACE TYPE CATEGORYLINKROW IS OBJECT

( category_id INTEGER , fund_id INTEGER , sort_order INTEGER , fund_number
VARCHAR2 ( 16 ), short_name VARCHAR2 ( 64 ), record_type INTEGER , class_name
VARCHAR2 ( 32 ) )

alter type CATEGORYLINKROW modify attribute CLASS_NAME VARCHAR2 ( 64 ) cascade

/

ERROR line 165, col 2, ending_line 165, ending_col 6, Found ‘alter’, Expecting:
end of input -or- ; -or- FINAL INSTANTIABLE NOT

Yet this is syntactically valid. I can run it from SQL*Plus no problem.

Dan Clamage

Oracle Database Developer

C O N F L U E N C E

412.246.1806 direct

412.802.8632 main

412.802.8647 fax

THE MATERIAL CONTAINED IN THIS MESSAGE IS CONFIDENTIAL AND IS SUBJECT TO
RESTRICTIONS ON ITS DISCLOSURE. The recipient acknowledges that the information
contained herein is the exclusive, proprietary and confidential property of
Confluence Technologies, Inc. and shall be at all times regarded, treated and
protected as such by the recipient. The use and disclosure of this information
is subject to the restrictions contained in the Software License Agreement
between the recipient (or the recipient’s employer or its affiliates) and
Confluence Technologies, Inc.

2

You need a ‘;’ before the ALTER – those are 2 different
statements, Toad is reading it as one statement.

3

This is called an ‘evolved type’. It’s one that was created
and later altered. Oracle sort of tacks the DDL together in DBA_SOURCE.

This is valid DDL, and Toad should allow it to run.

4

Ahhh. I got a syntax error in Toad. I figured it was legit since I’ve
never seen an ALTER inside of a CREATE OR REPLACE before.

5

Yeah it’s kinda wacky if you ask me, but that’s how Oracle does it.

6

I should also mention that the code I displayed was in fact generated by Toad
during a schema export.

It sure would be nice (hint, hint) if the schema export feature had a checkbox
to control whether the output is the evolved type or as a pristine type (with
the evolved attributes listed as if they were part of the original create type
DDL).

Dan Clamage

Oracle Database Developer

C O N F L U E N C E

412.246.1806 direct

412.802.8632 main

412.802.8647 fax

THE MATERIAL CONTAINED IN THIS MESSAGE IS CONFIDENTIAL AND IS SUBJECT TO
RESTRICTIONS ON ITS DISCLOSURE. The recipient acknowledges that the information
contained herein is the exclusive, proprietary and confidential property of
Confluence Technologies, Inc. and shall be at all times regarded, treated and
protected as such by the recipient. The use and disclosure of this information
is subject to the restrictions contained in the Software License Agreement
between the recipient (or the recipient’s employer or its affiliates) and
Confluence Technologies, Inc.

7

I’ve logged this.

Michael

8

Hi Dan,

I don’t see any view in the data dictionary which tells if the type or its
attributes is evolved or not. If you know of one, let me know.

-John

9

The last time I used the Import Data Table wizard I had opened a very large
recordset. Now, when I try to open the wizard again, it is trying to open that
table with all those records. I just want it to open blank. Is that possible?

As long as this has been taking, I am wondering if it is completely locked up.

Thanks,
Eva

10

We have to open a query to the table to perform the import…but it
doesn’t have to load the whole thing – just the first few records
(whatever number your OCI_ARRAY_BUFFER_SIZE setting is set to), so it
shouldn’t take long.

Maybe Toad is trying to access the file you last used to import from, and
it’s taking a long time to open the file. Could that be it? If so, you
could prevent the delay by moving the file so Toad doesn’t see it.

11

I do appreciate that Toad remembers many of the things I did “last
time”, But at the point I choose to import data into a table, I’d
rather that Toad waited to learn into which table I intended to import before it
does the query you mention. In this case I wish Toad’s memory
weren’t so good. As Eva says, it can take a long time, and it likely as
not, is not the table I’m planning to load this time.

Is there an option to tell Toad not to do that query when the user chooses the
Data Import tool?

Thanks much,

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg

12

Hm, yeah, I can see your point. Well, one way around it is instead of going to
Database -> Import -> Import Table Data, just right-click the table you
want to import into from the schema browser, then choose “Import table
data” from there.
image001.jpeg

13

Thanks for all the replies. I am going to do the right click from now on as John suggests. Maybe put this on the pile of potential changes though as Dan hit the nail 100% on the head!

Thanks for all the replies. I am going to do the right click from now on as John
suggests. Maybe put this on the pile of potential changes though as Dan hit the
nail 100% on the head!
image001.jpeg

14

The next beta will just open that screen fresh, without reloading the prior
action.
image001.jpeg

15

Thanks, John, both for the work-around and for the change. J

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg

16

John,

Try this query:

SELECT * FROM USER_TYPE_VERSIONS

ORDER BY TYPE_NAME , VERSION# , LINE ;

The entire history of each evolved type can be seen, including previous
versions. Really I’d be interested only in the max version#. The alter
type commands are embedded in the Text column.

I think the dbms_metadata package extracts DDL from this view (or at least
something like it).

When I Describe an object type and click the Attributes & Methods tab, I see
all attributes listed, as of the latest version. Since Toad is building this
list somehow, it’d be nice if there was an option to generate DDL as if it
weren’t evolved.

Dan Clamage

Oracle Database Developer

C O N F L U E N C E

412.246.1806 direct

412.802.8632 main

412.802.8647 fax

THE MATERIAL CONTAINED IN THIS MESSAGE IS CONFIDENTIAL AND IS SUBJECT TO
RESTRICTIONS ON ITS DISCLOSURE. The recipient acknowledges that the information
contained herein is the exclusive, proprietary and confidential property of
Confluence Technologies, Inc. and shall be at all times regarded, treated and
protected as such by the recipient. The use and disclosure of this information
is subject to the restrictions contained in the Software License Agreement
between the recipient (or the recipient’s employer or its affiliates) and
Confluence Technologies, Inc.

17

Ditto on the thanks!

Ditto on the thanks!
image001.jpeg