2009-11-21

Invalid procedure

Wonder what is in there. Self invalidating procedure. Executable although invalid.


SQL> drop table i purge;

Table dropped.

SQL> create table i(n number);

Table created.

SQL> create or replace procedure INVALID_EXECUTABLE wrapped
2 a000000
3 b2
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 7
20 133 148
21 /GKO5fy/wMAULSQ3EONeuteOeyYwgzLINa5qfHQCrcHqNAWjUjjq5aQl2wunqxfypGG6DTUC
22 yHBZPTO1oTh2rTHCnRckEhNR70+2N0Mxn4pbg7fZJwVKX9dFOCicXmazqP6JcBIEQF1QCSdZ
23 DK5WOcSpQXvAnj5qIawt3H+JSwVpk8gG4oVLFcJ/Du2MO8CJvj7IVzAKn1TGVhx7nATQ1Xoh
24 FtjOUd1kLeAhNpt+DGEwkLz/U3ZKLO8ZUrzcIIYGLJsBqyicCNA73FdMJNCGw7WlVkNl7Vji
25 GmA6ougclJqWHoi4fzY81Efcubz7O35pxg==
26 /

Procedure created.

SQL> select * from i;

no rows selected

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
VALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

N
----------
1

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

SQL> alter procedure INVALID_EXECUTABLE compile;

Procedure altered.

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
VALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

N
----------
2

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

N
----------
3

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

2 comments:

  1. Well, this will do it:

    CREATE TABLE TEST (col1 VARCHAR2(1));

    CREATE TABLE i (n NUMBER);

    CREATE OR REPLACE PROCEDURE testinvalid
    IS
    l_dummy NUMBER (1);
    BEGIN
    SELECT COUNT (1)
    INTO l_dummy
    FROM TEST;

    INSERT INTO i
    VALUES (1);

    COMMIT;

    EXECUTE IMMEDIATE 'DROP TABLE test';

    EXECUTE IMMEDIATE 'CREATE TABLE test (col1 varchar2(1))';
    END;
    /

    SELECT status
    FROM user_objects
    WHERE object_name = 'TESTINVALID';

    BEGIN
    testinvalid;
    END;
    /

    SELECT COUNT (1)
    FROM i;

    ----------------
    Every execution of the procedure will invalidate the procedure since it references a table that is dropped and created by itself, but it compiles automatically when executed again...
    (I know, I made such a crappy procedure)

    ReplyDelete
  2. You got the point, the wrapped procedure does it with only one table.

    ReplyDelete

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.