Category Archives: PL/SQL

PLSQL: Exception Types

Knowing how and when to handle exceptions is crutial when writing PLSQL (as in most other programming languges). This does not mean that you always should handle your exceptions in your PLSQL code. Most probably you shouldn’t, or at least not all of it. Handling exceptions in PLSQL is more about “securing a consistent state […]

PLSQL: Naming convention

I really appreciate PLSQL code with some kind of naming convention. I try to stick to these conventions by Steven Feuerstein.

PLSQL: Inserting And Updating Using Records

One feature I very seldom see in use is the possibility to insert and update data using Oracle records. A record is a composite datatype having the capability of holding more than one single value all with possible different datatypes. If the number of attributes within a record is the same as in a table […]

10g SQL: MEMBER OF statement … but watch out

In 10g Oracle introduced the MEMBER OFF statement. The following article shows how using this feature handling binding in-list may introduce performance degradiation. The article also show an example where the feature increase performance. Once again: There is very few universal rules regarding SQL tuning.

PL/SQL: Missing ELSE in CASE-statement

What happens if we miss out the ELSE in a CASE statement? If your answer is “nothing”, then you might be up for a surprise. If we don´t include an ELSE-statement in a IF-statement, and the boolean expression evolves to false, then you´re completly right – nothing happens! The code will continue running after the […]

“It might be better to actually not handle exceptions”

What? Is it better to not handle EXCEPTIONS??? Your first thought might be: “He must be kidding!” Well – these are actually the words of Tom Kyte in an article in Oracle Magazine (Nov 2011). The problem actually starts when developers catch execptions and don´t propagate (raise) this any furter. Tom states: “The fact is […]

Dynamisk WHERE klausul m/bind

Gjennom tuning arbeid og SQL/PLSQL reviews får jeg innblikk i mye av den koden som skrives i dag mot Oracle databaser. Når det gjelder bind variabler er generelt utviklere blitt mye flinkere. Men fremdeles ser jeg tilfeller der utviklere bygger opp spørringer dynamisk uten bind. Det er desverre ikke sånn at hvis vi bruker bind […]

Oracle Bulk collect

En kollega hadde et problem med en pl/sql blokk. Hun ønsket å gjøre en bulk collect inn i en array av records. Det er svært vanlig å tenke på denne måten, men dette fungerer ikke med hensyn til bulk collect. Her må vi i stedet snu ting litt på hodet (se beskrivelse under). Vedkommende ønsket […]