Oracle Tips and Tricks

While I generally prefer to build solutions that are database agnostic, sometimes it’s necessary to move some code into stored procedures or triggers in the interests of performance. This reduces the interaction between the application and the database but also gives the database more control over optimizations. This blog is about some small tricks that you can do in Oracle. I will follow up with a later one on SQL Server tips.

Joining onto or querying Cursors

Cursors are both a blessing and a curse. They provide a lot of flexibility but one part where they really fall short is when you want to join against them with a query or extract a single row out of the cursor using a query. They don’t work that way so you end up scratching your head. Well, there are a few ways around it. The quickest (not so quick from Oracle’s perspective but rather from a development standpoint) and dirtiest way to get this working is to use the Oracle XML operations to convert it to an xml table.

Let’s assume that you have a cursor with the following fields:

Client varchar2(100)
Invoice_Number Number(16)
Amount Number(16,2)

The idea is to get all of the data into a “table” that we can use for joins or queries. The trick is to do something like the following:

select I.*
  from xmltable('/ROWSET/ROW' passing xmltype(MyCursor) columns
    Client varchar2(100) path 'Client',
    Invoice_Number Number(16) path 'Invoice_Number',
    Amount Number(16,2) path 'Amount') I;

I wouldn’t use this for large cursors or for operations that require really high performance but it’s really useful to use on the odd occasion. Here is a full set of code to test it out:

-- Create the test table
CREATE TABLE TestInvoiceTable
(
Client varchar2(100),
Invoice_Number Number(16),
Amount Number(16,2)
);

-- Add some test data
INSERT INTO TestInvoiceTable (Client, Invoice_Number, Amount) VALUES ('Client A', 1231, 55.50);
INSERT INTO TestInvoiceTable (Client, Invoice_Number, Amount) VALUES ('Client B', 1232, 122.96);
INSERT INTO TestInvoiceTable (Client, Invoice_Number, Amount) VALUES ('Client A', 1233, 232.00);
INSERT INTO TestInvoiceTable (Client, Invoice_Number, Amount) VALUES ('Client C', 1234, 85.14);

-- A Block of code to test our query
declare
  example_cursor SYS_REFCURSOR;
  ExtractInvNo Number(16);
Begin
  -- Build a dummy cursor
  OPEN example_cursor FOR
  SELECT * FROM TestInvoiceTable;

  -- Use the cursor in a traditional query
  SELECT I.Invoice_Number INTO ExtractInvNo
  FROM XMLTABLE('/ROWSET/ROW' PASSING XMLTYPE(example_cursor) COLUMNS
    Client varchar2(100) path 'CLIENT',
    Invoice_Number Number(16) path 'INVOICE_NUMBER',
    Amount Number(16,2) path 'AMOUNT') I WHERE I.Amount > 100 AND ROWNUM = 1;

  -- Output the value
  DBMS_OUTPUT.PUT_LINE(ExtractInvNo);
end;

This will output 1232 to the output window.

Finding Blockers

This can be done really quickly by querying the v$session_blockers table. It will quickly return which sessions are being blocked and by who. Not very difficult to find but it certainly helps if you find your oracle session just hanging.

select * from v$session_blockers

Running Totals in a Query

Getting a running total can be really useful in queries. We may want to find out how many line items on an invoice are still outstanding if we have only received part of the payment on that invoice or we may just want to have a carrying value for a report. Oracle has a way to do this. We can even have a running total for a particular subset of our data. So, using the table that I added earlier, we could do something like this:

SELECT Client, Invoice_Number, Amount,
  SUM(Amount) OVER 
    (ORDER BY Client, Invoice_Number) AS Running_Total,
  SUM(Amount) OVER 
    (PARTITION BY Client ORDER BY Client, Invoice_Number) AS Client_Total
FROM TestInvoiceTable ORDER BY Client, Invoice_Number

This will return the following data:

CLIENT	INV_NUMBER	AMOUNT	RUNNING_TOTAL	CLIENT_TOTAL
Client A	1231	55.50	55.5	55.5
Client A	1233	232.00	287.5	287.5
Client B	1232	122.96	410.46	122.96
Client C	1234	85.14	495.6	85.14

As you can see, we have two running totals. The running total for the whole query; and the running total for each client courtesy of PARTITION BY.

Conclusion

Databases might not be your cup of tea but it helps to know what you can do with them. A few tricks can go a long way to making your life easier and ultimately improving the performance and robustness of your solution.