I came across another example today where I was positive I knew how something worked, but was proven wrong. This is why it is so critical to be able to make quality test cases. This specific case had to do with definer rights in PL/SQL. Let me set up an example to explain. First as… Read More »
Instrumentation (when referring to code) is code that is put in place to allow you to monitor the execution of the code. Instrumentation in PL/SQL code is something that is often missing. Worse, a lot of people don’t allow instrumentation because they worry that adding the extra code to do the instrumentation will slow down… Read More »
Lewis Cunningham just posted about timing differences between NVL and COALESCE. It reminded me of a very important difference that I didn’t know about until a couple years ago that can make the timing differences even greater. NVL will evaluate both inputs, where COALESCE will stop as soon as it finds a non-null value. The… Read More »
In putting together the test script for my last post, I noticed something I didn’t expect while using the RPAD function. My original script to populate my test table looked like: insert into t1(c1, c2) select rownum, rpad(‘test’, 5000, ‘!’) from dual connect by level
Today I was asked by a colleague how to solve this problem, so I thought I would share. While the LONG datatype should no longer be used going forward, on occassion you do still run into it from time to time. My colleague was given the task of creating an archive process for a table,… Read More »
A continuation of a my previous post, I am looking more into the capabilities of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This time I will look more at what FORMAT_ERROR_BACKTRACE can do. These tests are on a 10.2.0.4 database. First, I do a simple test to see what FORMAT_ERROR_BACKTRACE even returns: BEGIN raise_application_error(-20001, ‘Test Error Message’); EXCEPTION WHEN… Read More »
I have been aware of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for some time now, but until recently have never found myself needing to know much about them. For my uses SQLERRM has always worked just fine. However, in a project I am currently working on, we are looking to have better error reporting, so it was finally… Read More »
A handy little function for quickly grabbing information about where code is running / who is running it. Common Uses: Current User Name: sys_context(‘USERENV’, ‘CURRENT_USER’); Current User SID: sys_context(‘USERENV’, ‘CURRENT_USERID’); Database Name (from Init Param): sys_context(‘USERENV’, ‘DB_NAME’); More info can be found in the documentation