Tag: plsql

Passing parameters

I had an interesting question from a previous work colleague. She had a need to perform a query on attribute pairs. For the sake of discussion, we’ll assume that pairs are a shopping centre name, and a checkout aisle in that shopping centre. So…

Fun with array fetch

I’ve lost track of the number of times I’ve worked with systems that didn’t run brilliantly because every database operation was the “row at a time” methodology.  Typically you see hundreds of routines (either in the database in PL/SQL) or further up the chain…

Less calls…more performance (part 2)

In the previous post, I mentioned that for a programming environment fetching rows from the database, then the method of open a ref cursor issue a fetch call close the ref cursor might not be appropriate for those situations where the result set is…

Less calls…more performance

In various programming environments, a common metaphor is to open a cursor on the database (a REF CURSOR in Oracle parlance), return that cursor handle to the calling environment, and then that cursor is used to fetch or “fill” a data object, which may…

Avoiding public embarrassment with triggers

If you create a trigger that does not compile, any part of your application that refers to the table with the invalid trigger is basically dead until that trigger becomes valid. Even if a DML on that table is not going affect any rows,…

Things that are there but you cannot use

I did a “desc” command on the STANDARD package today, the package that helps define PL/SQL, and saw the XOR function! SQL> declare 2 x boolean; 3 begin 4 x := XOR(true,true); 5 dbms_output.put_line(case when x then ‘TRUE’ else ‘FALSE’ end); 6 7 x…

From Product X to SQL Developer

I recently worked at a company that used “Product X” for all of it SQL and PL/SQL activities.  There’s no real need to reveal what “Product X” is, because this isn’t a post about whether as a product it was good, bad or somewhere…