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
SQL> 

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
is
emps emp_tt;
idx  pls_integer;
begin
select ename
    , hiredate
    , sal
 bulk collect
 into emps
 from emp
;
idx := emps.first;
while idx is not null
loop
  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!

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)
then
   return 1;
else
   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
NULL :

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  /
True
False
Null