Re: Oracle DBA Studio, almost useful

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1069481773.698748_at_yasure...
| Jarmo wrote:
|
| > I'm using Oracle DBA Studio to create my first table. It has a
| > useful-looking table wizard into which I've entered the details of my
new
| > table. The wizard then proceeds to generate the (alleged) SQL
equivalent of
| > my request. Problem is that when I click 'Finish' it rejects the
| > auto-generated SQL claiming ORA-01741: illegal zero-length identifier.
| >
| > The auto-generated SQL looks like this:
| >
| > CREATE TABLE "SYSTEM"."EMPLOYEE"("EMPLOYEEID" NUMBER(4) NOT NULL,
| > "NAME" VARCHAR2(128) NOT NULL,
| > CONSTRAINT "" PRIMARY KEY("EMPLOYEEID"), UNIQUE("EMPLOYEEID"))
| >
|
| You've made at least two critical mistakes.
|
| First ... no table should ever, for any reason, be built as SYSTEM
| unless it is built as part of the Oracle installation.
|
| Second ... you named a column 'name' which is a reserved word in
| Oracle.
|
| See SELECT * FROM v_$reserved_words WHERE LOWER(keyword) LIKE 'n%';
|
| Beyond that you should not be buiding in-line constraints and as far as
| I am concerned as an educator you shouldn't be touching or building
| anything in DBA Studio until you can first do it flawlessly in SQL*Plus.
|
| So close DBA Studio. Open SQL*Plus, log on as SYSTEM and create a user
| account and grant it appropriate privileges. Then log on as that user
| and start learning Oracle.
|
| And if my above instructions seem a bit cryptic ... that is with intent.
| The answers to the questions you will have can be found at
| http://tahiti.oracle.com.
|
| Learn to fish.
| --
| Daniel Morgan
| http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
| http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
| damorgan_at_x.washington.edu
| (replace 'x' with a 'u' to reply)
|

simple answer -- the empty (zero-length) constraint name for the primary key is causing the problem

if you execute the command in SQL*Plus it will highlight the twin double-quotes as the error location -- so apparently DBA Studio is generating bad code

[Quoted] regarding reserved words -- V$RESERVED_WORDS is the public synonym used for accessing the list -- unless you're logged in as SYS, selecting from V_$RESERVED_WORDS will not work unless you prefix it with the owner (i.e. SYS.V_$...) [Quoted] but by default not all users have access to it -- they need the SELECT_CATALOG_ROLE privilege, which is too broad a set of privs for developers. so, you'll likely need to be connected as SYSTEM or a more privileged account to access the list

[Quoted] when you do, don't bother with the LOWER(keyword) syntax -- all keywords are in upper case, and it's a bad habit to get into to unnecessarily wrap columns in functions within where clause predicates

[Quoted] more to the point -- in 8.1.7 NAME is not a reserved word, but NAMED is (perhaps NAME is a reserved word in 9i?). however, since V$RESERVED_WORDS lists PL/SQL reserved words, you will find that Oracle will let you use some of the listed words to name a table or a column -- it will apparently not cause an error unless the reserved word is a SQL reserved word -- but you could run into problems down the road with PL/SQL code that references the table (check out the appendix to the SQL Reference manual for a list of SQL reserved words) nevertheless, good advise, but not actually the problem here

do research as suggested, but feel free to keep posting questions -- not everyone will whap you with the fishing rod ;-)

• mcs