Re: Can a Table be Invalidated?

Jim Smith wrote:
> In message <1121485479.831008_at_yasure>, DA Morgan <damorgan_at_psoug.org>
> writes
>

>> Jim Smith wrote:
>>
>>> Would a table which contained an object type whose code wouldn't 
>>> compile  be classed as invalid?
>>
>>
>> Why are you asking us to tell you the answer to something you could
>> determine yourself in 15-20 seconds?

There is a reason why when you do the following:

desc user_tables

you don't see a STATUS column.

SQL> CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(40000);

   2 /

Warning: Type created with compilation errors.

SQL> CREATE TABLE department (

   2 name VARCHAR2(20),
   3 courses CourseList)
   4 NESTED TABLE courses STORE AS courses_tab; courses CourseList)

         *
ERROR at line 3:
ORA-00902: invalid datatype

or

SQL> CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(20000);

   2 /

Type created.

SQL> CREATE TABLE department (

   2 name VARCHAR2(20),
   3 courses CourseList)
   4 NESTED TABLE courses STORE AS courses_tab; CREATE TABLE department (
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column COURSES ORA-00910: specified length too long for its datatype

or

SQL> CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(2000);

   2 /

Type created.

SQL> CREATE TABLE department (

   2 name VARCHAR2(20),
   3 courses CourseList)
   4 NESTED TABLE courses STORE AS courses_tab;

Table created.

SQL> CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(90000);

   2 /
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(90000); *
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

Because you can not do it.

I was not trying to be obnoxious. I was trying to point out that in 10-15 seconds anyone with any amount of curiousity could have answered the question.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)