Who has locked my package?

This question I have frequently faced while developing packages/procedures in PL/SQL in Oracle. 


Question: How will I identify who has locked a procedure or a package. I need to identify this because it should not hang after the compilation is issued?


Answer: You may see the details from v$sql with the search text (Your package name). If the query fetches results then join it with v$session to get the session information.


select * 
from v$sql, v$session
where sql_address=address
and upper(sql_text) like '%PACKAGE_NAME%';


Hope this simple tip saves you some time.

Getting file size with PL/SQL

UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length.

CREATE OR REPLACE FUNCTION flength (
   location_in   IN   VARCHAR2,
   file_in       IN   VARCHAR2
)
   RETURN PLS_INTEGER
IS 
   TYPE fgetattr_t IS RECORD (
      fexists       BOOLEAN,
      file_length   PLS_INTEGER,
      block_size    PLS_INTEGER
   );

   fgetattr_rec   fgetattr_t;
BEGIN
   UTL_FILE.fgetattr (
      location         => location_in,
      filename         => file_in,
      fexists          => fgetattr_rec.fexists,
      file_length      => fgetattr_rec.file_length,
      block_size       => fgetattr_rec.block_size
   );
   RETURN fgetattr_rec.file_length;
END flength; 

Pass a directory structure name as first parameter and the file name as the second parameter.

Create directory TEST as '/tmp';


select flength('TEST','test.txt') size_in_bytes from dual;


size_in_bytes
-------------
        18568

CAUTION: If you are using 9.2.0.6 then there is a database bug which might not return the exact file size. This bug has been fixed in 9.2.0.7 version.

For more information visit here.