Tag: plsql

PL/SQL arrays–the index datatype

You get some interesting (but perhaps not unexpected) results when playing with the speed of array functions in PL/SQL.  This is a series of tests comparing “BY PLS_INTEGER” arrays with “BY VARCHAR2” arrays.  In all the cases, their speed is pretty much blindingly fast,…

Where did my triggers go ?

You need to be careful when coding and using triggers when it comes to Datapump (or anything that transposes triggers between schemas).  A lot of people make assumptions about what will happen with their triggers, and often get a nasty shock when they see…

A new line on NEWLINE

Recently I was doing a simple external table load using a CSV file, but was getting an interesting error. My file looked simple enough “ID”,”EMAIL”,”TIMESTAMP”,”SUBJECT”,”STATUS”,”STATUS_TS” “2012348048”,”john@anon.com”,”05/02/2000″,”Subject 1″,”5″,”09/04/2007″ “2412348048”,”mike@anon.com”,”05/02/2000″,”Subject 1″,”5″,”09/16/2002″ “348543169051”,”sue@anon.com”,”03/10/2001″,”Subject 1″,”5″,”03/24/2008″ “348396029762”,”mary@anon.com”,”03/10/2001″,”Subject 1″,”5″,”03/10/2001″ “1212348047”,”sam@anon.com”,”05/02/2000″,”Subject 1″,”5″,”05/02/2000″ “1612348048”,”vincent@anon.com”,”05/02/2000″,”Subject 1,”5″,”06/02/2006″ … … So it should have been…

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…

Edition based redefinition – an apology

In April 2008 (wow, does time fly!) I used the following picture in my “11g features for Developers” presentation at the Australian Oracle User Group conference. I think the picture is from the movie “Indiana Jones and the Last Crusade”… where they sought the…

WHEN OTHERS … sometimes just not enough

We currently are working on a bug with Oracle Support with dbms_stats in 12.1.0.2. That may be the subject of a latter post, but in a nutshell, here’s the issue SQL> exec dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); BEGIN dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); END; * ERROR at line 1: ORA-21700:…