18 March 2009

Display Images with SQL Developer

Images can be stored as a BLOB in the database. SQL Developer can show them as well.

Step 1) Create a Table to store the image in
create table t
(img blob);

Step 2) Upload an Image

Choose an image from your file system, and commit your changes.

Step 3) On the "Data" tab, click the "..." button with the BLOB column.

Step 4) In the dialog box, check the "View as Image" box

And that's it... Looks a lot better than SQL*Plus would display images :)
SQL> select img
2 from t
3 /
SP2-0678: Column or attribute type can not be displayed by SQL*Plus


13 March 2009

Debugging Associative Arrays

My favorite development tool is PL/SQL Developer by Allround Automations. The Debugger included with this tool is great, I really like it… but… not for Associative Arrays.
Let’s take a look at some code we want to debug

type emp_rt is record
(ename emp.ename%type
,hiredate emp.hiredate%type
,sal emp.sal%type
type emp_tt is table of emp_rt
index by pls_integer;

First we declare a Record, based on some of the columns of the infamous EMP table. Next we declare the Associative Array based on the Record structure.
Then we need a (packaged) procedure, which we are going to debug. The type declaration and the procedure are both in a Package, of course.

procedure test
emps emp_tt;
idx pls_integer;
select ename
, hiredate
, sal
bulk collect
into emps
from emp
idx := emps.first;
while idx is not null
dbms_output.put_line ('Employee: '||emps(idx).ename);
idx := emps.next(idx);
end loop;
end test;

PL/SQL Developer has a Test Window, where you can run an anonymous block. It is possible to call this window whenever you feel like, no need to be in the context of a Package like in SQL Developer. Nor does the package need a breakpoint. And this is a good thing, 'cause most of time I forget to set breakpoints.

When you are stepping through the code and you want to see the content of the Associative Array, you will get the message:

And that's too bad.

SQL Developer to the Rescue

SQL Developer, Oracle's free IDE, also has a Debugger. And what is really nice about this debugger, is that you can view the content of the Associative Array.
  1. Set a Breakpoint in the procedure (this can only be done when you are in edit mode)
  2. Compile the Package in Debug (with the black compile button
  3. Choose Run --> Debug from the menu
  4. Run the procedure from the Debug window
  5. Execution halts at the Breakpoint
You can look on the "Smart Data" tab to see the content of the Associative Array.

Simply Brilliant! Great Job, Sue!

04 March 2009

Overloading Functions that Return Boolean

When you have a function that returns a Boolean, you know that you can't use it in SQL. SQL just doesn't have a Boolean datatype.

Say you have a function like the following:

function ftest (p_param1 in varchar2
,p_param2 in varchar2
return boolean;

It is easy to overload the function, provided you placed it in a package, to return a datatype which is compatible with SQL.
All too often an overloading would look like

if ftest (param1, param2)
return 1;
return 0;
end if;

Can you spot the flaw in this?
The function will return a zero when the original function returns FALSE or NULL.

A better way to overload is to use SYS.DIUTIL to do the job

return sys.diutil.bool_to_int (ftest (param1, param2));

This function will return a 1, 0 or NULL.

SQL> begin
2 dbms_output.put_line ('True : '||sys.diutil.bool_to_int (true));
3 dbms_output.put_line ('False: '||sys.diutil.bool_to_int (false));
4 dbms_output.put_line ('NULL : '||sys.diutil.bool_to_int (null));
5 end;
6 /
True : 1
False: 0

And there is also a "reverse" function, to turn an integer into a Boolean

SQL> begin
2 if sys.diutil.int_to_bool (1)
3 then
4 dbms_output.put_line ('True');
5 end if;
6 if not sys.diutil.int_to_bool (0)
7 then
8 dbms_output.put_line ('False');
9 end if;
10 if sys.diutil.int_to_bool (null) is null
11 then
12 dbms_output.put_line ('Null');
13 end if;
14 end;
15 /