Tag: plsql

Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic. However, not many are aware that its not just procedures and functions that can have a public (package spec) and private…

Bitwise operations

The long existing BITAND function is now within the documentation, to let you do logical AND on two numbers, and is also available from PL/SQL   If you need other bit operations, a little boolean math should suffice Just make sure you stay within…

Auto-backups of PLSQL source

I saw this on an ideas forum today and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to…

Problematic SQL ? PL/SQL is your friend.

So then… I’ve written a crappy piece of SQL. It wouldn’t be the first time I’ve done it… and it probably won’t be the last time I do it But at least I’ve done one thing right…I’ve encapsulated the SQL inside a PL/SQL procedure….

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…