Tag: plsql

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…

Dumb triggers part 2 – session level control

In the previous post, I pontificated about triggers that “lock you in” to having them fire, which can create dramas when it comes to doing data patching. Maybe you can design your application around this, but if you cant, the last thing you want…

Really dumb triggers

Some people hate triggers, some people love triggers… I am not that opinionated on them in either direction, but one thing I do hate, whether it be a trigger or not, is dumb code. And today’s post just happens to be about dumb code…

number of set bits

declare n number := ….; tmp number; begin tmp := n – ( bitand ((n / 2) ,3681400539)) – (bitand ((n / 4) ,1227133513)); tmp := mod ( bitand ((tmp + (tmp / 8)) , 3340530119) , 63 ); dbms_output.put_line(tmp); end; Google for “hakmem”…

Default null for collection parameter

I’ve got an existing package called DEMO as below SQL> create or replace package demo is 2 — used to pass list of numbers 3 TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 4 5 PROCEDURE p( 6 p_list1 IN t_num_list 7 );…