Tag: plsql

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…