I have this anonymous PL/SQL block which calculates and prints a value return from a table.

DECLARE U_ID NUMBER :=39; RETAIL BINARY_FLOAT:=1; FLAG NUMBER; BEGIN SELECT NVL(RETAIL_AMOUNT,1),UNIT_ID INTO RETAIL, FLAG FROM UNITS WHERE UNIT_ID=U_ID; LOOP SELECT NVL(MAX(UNIT_ID),U_ID) INTO FLAG FROM UNITS WHERE FATHER_ID=FLAG; IF FLAG=U_ID THEN EXIT; END IF; SELECT RETAIL* RETAIL_AMOUNT INTO RETAIL FROM UNITS WHERE UNIT_ID=FLAG; EXIT WHEN FLAG=U_ID; END LOOP; DBMS_OUTPUT.PUT_LINE( RETAIL); END; 

This block work correctly, but I wanted to do the same thing using a PL/SQL Function

I wrote the function as follow:

CREATE OR REPLACE FUNCTION GET_UNIT_RETAIL(U_ID NUMBER) RETURN NUMBER IS RETAIL BINARY_FLOAT:=1; FLAG NUMBER; BEGIN SELECT NVL(RETAIL_AMOUNT,1),UNIT_ID INTO RETAIL, FLAG FROM UNITS WHERE UNIT_ID=U_ID; LOOP SELECT NVL(MAX(UNIT_ID),U_ID) INTO FLAG FROM UNITS WHERE FATHER_ID=FLAG; IF FLAG=U_ID THEN EXIT; END IF; SELECT RETAIL* RETAIL_AMOUNT INTO RETAIL FROM UNITS WHERE UNIT_ID=FLAG; EXIT WHEN FLAG=U_ID; END LOOP; RETURN NUMBER; END; / 

When I try to execute the above code to save the function to the database, the environment (SQL*PLUS) hangs for a long time and at the end returns this error:

ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

What is the problem ??? Please !

2 Answers

Sounds like ddl_lock problem

Take a look at
dba_ddl_locks to see who is "blocking" a create or replace.

Also try to create under different name - and see what happens.

The problem was because the Object GET_UNIT_RETAIL was busy by other environment Here is the answer:

https://community.oracle.com/thread/2321256

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoanJoZWaEeYSOqKmaZWBpfXN9jK2gpp2fqsFuu8KcrKuqlZl6uLTIpZxmr5Gewaq6xmarqGWcpLCsec6boZ6bpA%3D%3D