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
2009-11-21
Invalid procedure
Wonder what is in there. Self invalidating procedure. Executable although invalid.
Subscribe to:
Post Comments (Atom)
About Me
- Rafu
- I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.
Well, this will do it:
ReplyDeleteCREATE 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)
You got the point, the wrapped procedure does it with only one table.
ReplyDelete