Toad World® Forums

cannot generate identity tables

CREATE TABLE t
(
id NUMBER GENERATED AS IDENTITY (ORDER NOCACHE),
x NUMBER
);

I can run as script, but if I type CTRL-ENTER in my Toad 12.0.0.55, I am getting ORA-00922: missing or invalid option

Hmmm, it CTRL+Enter’ed fine for me.

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Friday, June 28, 2013 8:27 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

cannot generate identity tables

Thread created by laurentc.schneider_609

CREATE TABLE t

(

id NUMBER GENERATED AS IDENTITY (ORDER NOCACHE),

x NUMBER

);

I can run as script, but if I type CTRL-ENTER in my Toad 12.0.0.55, I am getting ORA-00922: missing or invalid option

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

Okay, can I help you further with debug traces / print screen etc?

Try turning on spool sql to see if the full statement is making it to the database.

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Friday, June 28, 2013 8:50 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

RE: cannot generate
identity tables

Reply by laurentc.schneider_609

Okay, can I help you further with debug traces / print screen etc?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

You should be able to use the execute statement at caret button (of F9).

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Friday, June 28, 2013 9:50 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

RE: cannot generate
identity tables

Reply by laurentc.schneider_609

Okay, can I help you further with debug traces / print screen etc?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

@Michael : no, I am getting [Error] Execution (4: 74): ORA-00922: missing or invalid option

@John : i turnout out sql trace on the server, but did not get useful info

*** MODULE NAME:(TOAD Beta 12.0.0.55) 2013-06-28 15:53:38.299

*** ACTION NAME:() 2013-06-28 15:53:38.299

=====================

PARSE ERROR #140733363939136:len=75 dep=0 uid=8 oct=1 lid=8 tim=1372427618298269 err=922

CREATE TABLE t(id NUMBER …

Whta is SPOOL SQL option?

Using F9 or CTRL+Enter? They are completely different beasts.

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Friday, June 28, 2013 10:04 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

RE: cannot generate
identity tables

Reply by laurentc.schneider_609

@Michael : no, I am getting [Error] Execution (4: 74): ORA-00922: missing or invalid option

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

In Toad, from the main menu: Database -> Spool SQL -> Spool to Screen.

Now, every query that Toad executes will appear in the output window at the bottom.

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Friday, June 28, 2013 9:09 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

RE: cannot generate
identity tables

Reply by laurentc.schneider_609

@John : i turnout out sql trace on the server, but did not get useful info

*** MODULE NAME:(TOAD Beta 12.0.0.55) 2013-06-28 15:53:38.299

*** ACTION NAME:() 2013-06-28 15:53:38.299

=====================

PARSE ERROR #140733363939136:len=75 dep=0 uid=8 oct=1 lid=8 tim=1372427618298269 err=922

CREATE TABLE t(id NUMBER …

Whta is SPOOL SQL option?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

okay,

CREATE TABLE t(id NUMBER GENERATED AS IDENTITY (ORDER NOCACHE), x NUMBER);

does not work with CTRL ENTER

but

CREATE TABLE t(id NUMBER GENERATED AS IDENTITY (ORDER NOCACHE), x NUMBER)

works with CTRL ENTER (no trailiing semicolumn)

F9 does not work when I select the trailing semi-column

@john


Session: SYSTEM@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SRP***LX)(PORT=))(CONNECT_DATA=(SID=)))

Timestamp: 16:17:03.127

CREATE TABLE t(id NUMBER GENERATED AS IDENTITY (ORDER NOCACHE), x NUMBER);

Error: ORA-00922: missing or invalid option

not related to identity I figured out.

even DROP TABLE T does failed when followed by ; in Database 12c

DROP TABLE T;

Error: ORA-00933: SQL command not properly ended

John and I have been chatting offline. In short, I don’t have anything to offer to you yet as I have more questions than solutions.

Is this 12c the commercial one that was just released? I’ve tried on our 12c, but it’s still the beta 12c and your statement(s) are working fine using all available
execution methods. The Editor still strip statement terminators behind the scene before execution for all statements other than CREATE statements. DML statements will raise an ORA-911 error when we execute with the terminator, PL/SQL will error without it.
When a statement containing a terminator is executed and an ORA-911 is raised Toad hides this from you, strips the terminator if it exists, and re-executes the statement. John proposed that the ORA error has changed which sounds like the most likely cause,
but I’ll need to get a commercial copy of 12c to test on first.

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Friday, June 28, 2013 10:23 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

RE: cannot generate
identity tables

Reply by laurentc.schneider_609

not related to identity I figured out.

even DROP TABLE T does failed when followed by ; in Database 12c

DROP TABLE T;

Error: ORA-00933: SQL command not properly ended

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

I am using the commerical version

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

It is very bizarre that DROP TABLE T; is usable on 11.2.0.3 but not 12.1.0.1

Strange you talk about ORA-911, since I mentioned ORA-922 and ORA-933. I suppose it depends on the statement.

If I try the following 4 statements, SELECT and INSERT works like a charm with CTRL-END, DROP TABLE gerenates ORA-933 and CREATE TABLE generates ORA-922

select * from v$version;

DROP TABLE T;

CREATE TABLE T(X NUMBER);

insert into t(x) values (1);

If I select (highlight) the “DROP TABLE T” without the semi-column, it works too

All 4 statements work like a charm in 11.2.0.3

it is a major annoyance and it also reproduces in Toad 11.6 Production

DROP TABLE T;

cannot be control-enter’ed in 12c

and your ora-911 makes perfect sense

in sqlplus 11.2 db 11.2

SQL> select * from v$version;

BANNER


Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL> drop table t;;

drop table t;

ERROR at line 1:

ORA-00911: invalid character

in sqlplus 12.1 db 12.1

SQL> select * from v$version;

BANNER CON_ID


Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

PL/SQL Release 12.1.0.1.0 - Production 0

CORE 12.1.0.1.0 Production 0

TNS for Linux: Version 12.1.0.1.0 - Production 0

NLSRTL Version 12.1.0.1.0 - Production 0

SQL> drop table t;;

drop table t;

ERROR at line 1:

ORA-00933: SQL command not properly ended

Indeed not an ora-911

This is fixed for the first beta.

From: laurentc.schneider_609 [mailto:bounce-laurentcschneider_609@toadworld.com]

Sent: Monday, July 01, 2013 3:57 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] cannot generate identity tables

RE: cannot generate
identity tables

Reply by laurentc.schneider_609

and your ora-911 makes perfect sense

in sqlplus 11.2 db 11.2

SQL> select * from v$version;

BANNER


Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL> drop table t;;

drop table t;

       *

ERROR at line 1:

ORA-00911: invalid character

in sqlplus 12.1 db 12.1

SQL> select * from v$version;

BANNER CON_ID