Tag: plsql

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:…

RETURNING BULK COLLECT and database links

Looks like the nice PL/SQL facility for returning a set of updated rows is restricted when it comes to database links (This tested on 12.1.0.1) SQL> declare 2 type int_list is table of number(12) index by pls_integer; 3 l_results int_list; 4 5 begin 6…

ORA-4068 and CONSTANT keyword…good and bad

Anyone that has ever coded PLSQL will be familiar with the error ORA-4068, where you had some state persisted in a session due to a package variable, and then when you change the package, the state is cleared along with an ORA-4068.  Here’s a…

12c – Nested tables vs Associative arrays

This was going to the be the immediate follow up to my previous post, but 12.1.0.2 came out and I got all excited about that and forgot to post this one 🙂 Anyway, the previous post showed how easy it is to convert between…

Associative arrays and Nested tables

A common criticism of PLSQL is that the “original” array datatype, now called associative arrays are perfect for passing stuff back and forth to 3GL environments (for example .Net), but canno be used within SQL natively, for example: SQL> create or replace 2 package…