ignore the last message. I fixed that problem, but a new problem arises too. I always get deadlocks whenever I execute my patch.sql to update the database.

here is the result of the two runs


SQL> start patch
Task is complete, old version = 6
111111111111111111111111111
22222222222222222222222222222
Closing cursor, err code = -4020, err = ORA-04020: deadlock detected while trying to lock object PPDBA.TEST
cursor closed
Task is complete, new version = 7.1

PL/SQL procedure successfully completed.

7.1

7.1

SQL> start patch
Task is complete, old version = 6
111111111111111111111111111
22222222222222222222222222222
33333333333333333333333333333
44444444444444444444444444444
55555555555555555555555555555
Task is complete, new version = 7.1

PL/SQL procedure successfully completed.

7.1

7.1

SQL>
SQL>


You can see that in the first run, deadlock occurs when it try to execute the line:

DBMS_SQL.PARSE(cursor_name, ppstatement, DBMS_SQL.native);

but the second run went successfully. I don't know why there is always a deadlock for the first run. I dont' have any other sqlplus.exe open that locks the TEST table.



here is my script for creating the procedure:

CREATE OR REPLACE PROCEDURE executeDDL(ppstatement IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('1111111111111111111111111111');
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.PUT_LINE('22222222222222222222222222222');
DBMS_SQL.PARSE(cursor_name, ppstatement, DBMS_SQL.native);
DBMS_OUTPUT.PUT_LINE('33333333333333333333333333333');
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_OUTPUT.PUT_LINE('44444444444444444444444444444');
DBMS_SQL.CLOSE_CURSOR(cursor_name);
DBMS_OUTPUT.PUT_LINE('55555555555555555555555555555');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Closing cursor, err code = ' || sqlcode || ', err = ' || sqlerrm);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
DBMS_OUTPUT.PUT_LINE('cursor closed');
END;
/





--------- here is the patch.sql ---------

SET SERVEROUTPUT ON
VARIABLE cur_version VARCHAR2 (10)
VARIABLE target_version VARCHAR2 (10)
DECLARE
version VARCHAR2 (10);
BEGIN
SELECT VALUE INTO version FROM SYSFIELD WHERE SYSKEY='DB_VERS_LOAN';
:cur_version := version;
:target_version := '7.1';
IF :cur_version < :target_version THEN
DBMS_OUTPUT.PUT_LINE('Task is complete, old version = ' || :cur_version);
:cur_version := :target_version;
executeDDL('ALTER TABLE TEST ADD (QQQ VARCHAR2 (50))');
INSERT INTO TEST (AAA,BBB,CCC,DDD, EEE) VALUES('UPDATED', '','','','');
COMMIT;
DBMS_OUTPUT.PUT_LINE('Task is complete, new version = ' || :cur_version);
ELSE
INSERT INTO TEST (AAA,BBB,CCC,DDD,EEE) VALUES('NOUPDATE', '','','','');
DBMS_OUTPUT.PUT_LINE('Task is complete, current db version = ' || :cur_version || ' is already up to date');
END IF;
END;
.
/