Posts

Showing posts from August, 2010

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.fexi...