Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more Continue reading

Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’)
UNION
SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’)
UNION
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,’VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%’)
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ‘User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.

SQL Developer Connection Tip

I have recently been playing with SQLdeveloper and came around an interesting feature: you can group connections in folder. This will prevent you from having a very big list of connections.

To group your connections you must do the next thing:
1. Select two or more connections
2. Press your right mouse button
3. Choose “Add To Folder” -> “New Folder”

The result will look like this:

Nothing very special but it’s a very handy feature!

Oracle for Newbies, Power Day

In December you have probably read on this blog about our “Oracle for Newbies” seminar. As posted there we wanted to convince newbies that Oracle is not expensive, nor difficult. With free Oracle products (Oracle XE database, Application Express and SQL Developer) we showed how fast you can set up flexible database structures and professional web applications.

As previously announced there will be a follow-up on this seminar: on Monday, February 9th, iAdvise will organize a Power Day. It will be a very practical and interactive day on which participants will get the opportunity to try out everything. We will guide the participants when they are installing the Oracle software and we will help them while they are creating and deploying their first applications. After this session the attendees will be able to develop their own powerful and professional web applications.

Our objective for this Power Day is that newbies can learn Oracle on a pleasant way! We will keep you informed!

For more information about our Power Day, please contact us at info@iadvise.be.

Oracle for Newbies

On Wednesday iAdvise gave an Oracle seminar at the headquarters of Oracle Belgium in Brussels. That’s not THE big news of course, but for this seminar our audience was different. This time we focused the “newbies”, people who‘ve never or hardly heard about Oracle.


With this seminar we wanted to debunk the myths that:

  • Oracle is expensive
  • Oracle is only for the “big” companies
  • Oracle is difficult to install and has steap lurning curve

With life installations and life demo’s we convinced our public. Therefore we only used FREE Oracle products, like Oracle XE Database, Oracle Application Express (Apex) and SQL Developer. We installed the Oracle software life during the seminar. We created a database for a fleet of cars and developed a complete web application in less than no time to manage cars, traffic offences and car accidents. We showed the most important and powerful functionalities of Apex and we even included security.

During the seminar we showed:

  • How easily and quickly you can install the Oracle XE Database and Apex
  • How fast and flexible you can set up a database structure with SQL Developer or Apex
  • How you can create a powerful and professional web application with Apex in no time
  • How Apex is based on a extremely simple architecture
  • How fast you can deploy the application using SQL Developer or Apex
  • How dirt-cheap all these topics are thanks to the free Oracle products

Oracle users already knew this, but at the end of the seminar the “newbies” were also convinced: Oracle is fun !


With the motto “When you hear you forget, when you see you remember, when you do you understand” iAdvise will organize a “Power Day”. During that day we will guide “newbies” while they try this out by theirself.
Are you interested in the “Oracle for Newbies” presentation slides or would you like to have more information about our Power Day? Please contact us at info@iadvise.be.

SQLDeveloper + Vista Trick

Recently I switched from Windows XP to Windows Vista, ignoring all pro’s and contra’s for upgrading my OS, I had a problem when I used SQLdeveloper.

When you edit a package and try to scroll between the code then it became unreadable:

After some research I came across an excellent tip on the java2go blogspot.

I’ll give the short version here under but please visit them for more info:

- Download the latest java jdk

- Edit the file (C:\sqldeveloper\ide\bin\ide.conf) and add the following:
# setting maximum heap to 256 MB
AddVMOption -Xmx256M

#setting keepWorkingSetOnMinimize
AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true

- Edit the file (C:\sqldeveloper\ide\bin\jdk.conf) and add the following:
SetJavaHome C:\Program Files\Java\jdk1.5.0_16
(refering to the jdk you downloaded

I restarted my SQLdeveloper and all my problems were gone!

SQL Developer and BLOBs

SQL Developer is a very complementary tool to Apex.
Although, you can do all basic SQL stuff via the SQL Workshop in Application Express, sometimes you need a more powerful tool like SQL Developer, especially when you really need to ‘develop’ in SQL and PL/SQL.

One of the SQL features I like about SQL Developer is the possibility to look at the content of a BLOB column.

This feature was very useful during my quest on the content of the flows-files table. And I don’t think other tools like TOAD, SQL Navigator, PL/SQL Developer has those possibilities …

Just by clicking on the blob-column in the result grid, a popup window is opened where you can specify that you want to see the content as text or image.

When this column contains plain text, there is no problem at all to have a look at the content. The following example opens a tab-delimited file.

SQL Developer can even visualize binary images (gif, jpeg, bmp). Very handy …