PL/SQL: Sleep without using DBMS_LOCK

If you want to wait in your PL/SQL program (“sleep”) you may want to use the procedure “SLEEP” from the package “DBMS_LOCK“. While this function does exactly what you want, your DBA may not grant you access to this package, because it contains some other mighty and dangerous procedures.

But there is help! Here are some valid alternatives:

Option 1: APEX_UTIL.sleep

If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.

Example – “Wait 5 seconds”:

    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

Option 2: java.lang.Thread.sleep

An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:

AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';

Note: Please remember, that “Thread.sleep” uses milli seconds!

Example – “Wait 5 seconds”:

    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    SLEEP(5 * 1000);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));



I will speak at the “DOAG 2015 Conference + Exhibition” (“DOAG 2015 Konferenz + Ausstellung”)

I will be on the “DOAG 2015 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 17th to 19th.

I will speak at November 18th, at 10 am, room “Konferenzraum EG”:
Was Sie bei modernen Datenbank-Systemen anders machen müssen
(English: “What you need to do differently in modern database systems”)

The DOAG is the largest German Oracle user group with over 6000 members.

DOAG 2015 Konferenz + Ausstellung

DOAG 2015 Conference + Exhibition

SQL-Developer: Create External Tool dialog – Macros (“variables”)

You can use the folloeing commands in “Arguments” and “Run Directory” sections of the “Create External Tool” dialog:

Macro Meaning
${sqldev.conn} DB Connection String
${sqldev.dbuser} DB UserName
${env:var=} Environment Variable
${file.dir} File Directory
${file.ext} File Extension
${} File Name
${} File Name Without Extension
${file.path} File Path
${file.url} File URL
${} IDE Application Name
${ide.classpath} IDE Classpath
${ide.dir} IDE Install Directory
${} IDE Oracle Home Directory
${ide.user.dir} IDE User Directory
${prompt} Prompt
${promptl:=} Prompt with Label
${prop:name=} System Property

SQL-Developer: Activate logging

In case you want to activate logging in SQL-Developer – here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Deactivate the following line by adding a “#” at the beginning:
    #IncludeConfFile sqldeveloper-nondebug.conf
  5. Add a new line with the following text to the file:
    IncludeConfFile sqldeveloper-debug.conf
    AddVMOption -Dcom.methodr.loglevel=all

  6. Save the file.