Re: Can a Table be Invalidated?

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: Tue, 19 Jul 2005 18:12:47 +0100
Message-ID: <fgXDO4CPST3CFw7A@jimsmith.demon.co.uk>

In message <1121730352.811437.134990_at_f14g2000cwb.googlegroups.com>, joel-garry_at_home.com writes
>Andreas Sheriff wrote:
>
>>Wow!
>
>>A table *can* be invalidated after-all.
>
>>Interesting...
>
>But not for long:
>

It looks like any access to the table revalidates it.

OTOH, the scenario I first thought of doesn't invalidate the table.

SQL> create or replace function validate_ssn(ssn in varchar2)

   2 return boolean
   3 is
   4 begin
   5
   6 IF TRANSLATE(ssn, 'A0123456789', 'BAAAAAAAAAA') =

   7      'AAA-AA-AAAA' THEN
   8          return true;
   9      else
  10          return false;

  11 end if ;
  12
  13 end;
  14
  15 /

Function created.

SQL>
SQL> CREATE OR REPLACE TYPE ssn AS OBJECT (

   2 n_ CHAR(11),
   3 CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)    4 RETURN self AS result,
   5 MEMBER FUNCTION get_ssn RETURN CHAR);    6 /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY ssn IS

   2
   3 CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)    4 RETURN self AS RESULT IS
   5
   6 BEGIN
   7 IF validate_ssn(ssn_in) THEN

   8      n_ := ssn_in;
   9      RETURN;
  10    ELSE
  11      RAISE_APPLICATION_ERROR(-20001, 'INVALID SSN');

  12 END IF;
  13 END;
  14
  15 MEMBER FUNCTION get_ssn RETURN CHAR IS   16
  17 BEGIN
  18 RETURN n_;
  19 END;
  20
  21 END;
  22 /

Type body created.

SQL>
SQL> CREATE TABLE person (

2 per_name VARCHAR2(20),
3 per_ssn SSN);

Table created.

SQL>
SQL> DECLARE
   2
   3 myssn ssn;
   4
   5 BEGIN

   6     myssn := ssn(ssn_in=>'232-22-5678');
   7     INSERT INTO person VALUES ('Morgan', myssn);

8
9 -- myssn := ssn(n=>'444=55-6789'); 10 -- INSERT INTO person VALUES ('Morgan', myssn); 11

  12     myssn := ssn(ssn_in=>'123-45-6789');
  13     INSERT INTO person VALUES ('Cline', myssn);
  14     COMMIT;

15 END;
16 /

PL/SQL procedure successfully completed.

SQL>
SQL> select object_name,object_type,status from user_objects;

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SSN                            TYPE BODY           VALID
SSN                            TYPE                VALID
PERSON                         TABLE               VALID
VALIDATE_SSN                   FUNCTION            VALID

SQL>
SQL> create or replace function validate_ssn(ssn in varchar2)

   2 return boolean
   3 is
   4 begin
   5 begin
   6 IF TRANSLATE(ssn, 'A0123456789', 'BAAAAAAAAAA') =

   7      'AAA-AA-AAAA' THEN
   8          return true;
   9      else
  10          return false;

  11 end if ;
  12
  13 end;
  14
  15 /

Warning: Function created with compilation errors.

SQL>
SQL> select object_name,object_type,status from user_objects;

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SSN                            TYPE BODY           INVALID
SSN                            TYPE                VALID
PERSON                         TABLE               VALID
VALIDATE_SSN                   FUNCTION            INVALID

SQL>
SQL> select * from person;

PER_NAME

PER_SSN(N_)

Morgan
SSN('232-22-5678') Cline
SSN('123-45-6789') Interesting side note. Testing this, I put a drop table person at the top of the script, but I still get

SQL>
SQL> CREATE OR REPLACE TYPE ssn AS OBJECT (

   2 n_ CHAR(11),
   3 CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)    4 RETURN self AS result,
   5 MEMBER FUNCTION get_ssn RETURN CHAR);    6 /
CREATE OR REPLACE TYPE ssn AS OBJECT (
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

This is 10gR2 and person is still in the recycle bin. If I purge it, this goes away.

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>

Received on Tue Jul 19 2005 - 12:12:47 CDT