It has been long time (almost two months) since last update of my blog. Somebody might thought that my blog is dead. Fortunatelly it is not truth. Reason for that long break was very simple. I needed to off and spent my personal time on different location than on chair behind the computer. So I splitted my personal time between ice hockey and golf. Ice hockey season started couple of days before the last post on my blog. We are playing ice hockey 3 and sometimes 4 times per week and at the begining (after the summer break) I was very tired after the game. Usually I'm playing the left defender so I'm not scoring too much. Anyway until now I scored 9 goals (one hattrick). Also I'm watching the ice hockey as fan (really big) of my city club HC Slovan Bratislava. They started very well and after the 4th round they are on 1st place of the league roster. In days when I wasn't on ice hockey stadium I was playing (or better I was learning how to play) golf. I decided to learn golf 2 year ago but there wasn't enough time to start. I spent many hours on the golf course (also many hours on driving range and other practice areas) and prepared to final exam for green card. Of course I was learning the rules and golf etiquette which is more important than technic. Now I'm green card holder and almost every weekend I'm enjoying that beautiful game... But I'm back now and I'm ready to blogging. Stay tuned!

End of Life was announced for older releases of Fedora Core (1 and 2) and RedHat (7.3 and 9). Fedora Core 6 Test 2 will be released July 26th. For more information click here. Red Hat is preparing Enterprise Linux 5 Desktop as answer to SUSE Linux Enterprise 10 desktop which was released last week. For more information click here.

Last weekend I spent with installation (or upgrade) of my "testlab" computer. I'm using this computer for personal testing, learning and also for solving or looking for right answer to some questions posted on OTN Forums. I decided to install latest version of Fedora, SuSE, Centos (and also Solaris (x86)) because in some cases versions were pretty old (RedHat 9, FC2). Another reason for reinstall was that I wanted to make update of 9i installation papers according to user comments and suggestions that I received to my mailbox or according to questions posted on OTN forums especially in Linux and Installation section. Installation went fine and it was quick. I decided to create one storage for all Linuxes where I created database and config accessible from each of Linuxes, so I will be using the same database and the same parameters independent of booted Linux distribution. So my layout of storage is as following: /storage/config - location for parameter files and Oracle Net config files. /storage/oradata - mount point for database. Each instance has it's own directory of course (TEST9I, TEST10G). /storage/redo - optional location for redo (mount point for separate RAID). I'm using it just occasionaly. After installation of OS I booted one of Linuxes, and followed (exactly) all steps from my installation paper. Some caughts from installation: - LD_ASSUME_KERNEL is not important on new distros (FC5, SuSE 10.1). Actually setting this parameter to 2.4.x will cause another problems. - There is bug (probably) in Bash 3.1.x shipped with Fedora Core 5. This bug will occur during linking phase (there is splited bash command (using sed) in $ORACLE_HOME/bin/gennttab). I wasn't looking for official fix. Much quicker solution was to use older version of Bash. So I took src.rpm package from FC4 and builded it for FC5. After installation of 9i and 10g Oracle Databases I continued with installation of Oracle Application Server (10g 2 and 3 releases) and other 3rd party tools like rlwrap. During of installation I builded rpm of latest rlwrap version for each distribution. So in case of interest you can download it here: download rlwrap rpm for Fedora Core (md5 checksum: 862847544adaee89643832020d3375d5) download rlwrap rpm for Redhat, Centos, WBL (md5 checksum: de347425bc16bef54de28ab1514cca86) download rlwrap rpm for SuSE (md5 checksum: 738cbd8c702c8b2909ad85e2c4514460) In case you don't consider me (or my server) as trusted package source then you can build your own package using sources and spec file. To build a package you need to have rpm-build and readline-devel package installed on your system. For building process use following commands:

su -
# cp rlwrap-0.24.spec /usr/src/redhat/SPECS/
# cp rlwrap-0.24.tar.gz /usr/src/redhat/SOURCES/
# cd /usr/src/redhat/SPECS
# rpmbuild -bb rlwrap-0.24.spec

Builded package you can find in /usr/src/redhat/RPMS/ (Note: For Redhat like distros the default target architecture is i386). Update rlwrap for Debian you can find here. and steps how to install rlwrap on MacOS X 10.4 you can find here. So this was my personal "Linux Installfest" (weekend). Of course all 9i papers were updated.

Last week I found this beauty:

select * from SCHEMA.USERS where ID in
(105,145,165,166,167,168,169,187,188,189,190,225,245,
246,285,305,326,345,346,347,348,366,385,386, ... ... 8407)
and 1=1
and USER_GROUP_ID='108'
and DESCRIPTION like '%'
or DESCRIPTION is NULL
order by DESCRIPTION;

So I started searching for the "guilty code" and I found this:

// description:   returns number of entries
   function get_user_list(&$ap_result, $a_id, $a_description, $a_group){
      $db =& db::get("schemaname");
      $l_query = "select * from ".USERS_TABLE." ";
      $l_query.= "where ".USERS_ID." in ($a_id) and 1=1";
      if($a_group)
         $l_query.= " and ".USER_GROUP_ID."='$a_group'";
      if ($a_description)
         $l_query .= " and ".USERS_DESCRIPTION." like '$a_description'";
      if ($a_description=="%")
         $l_query .= " or ".USERS_DESCRIPTION." is NULL";
      $l_query.= " order by ".USERS_DESCRIPTION;
      $l_res_id = $db->exec($l_query);
      if(!$l_res_id){
         api::debug("1901", "db error", $db->lasterr());
         return false;
      }
      while($l_row = $db->fetch($l_res_id)){
         $ap_result[] = $l_row;
      }
      $db->free($l_res_id);
      return count($ap_result);
   }

Dear developer, well done! You've got my attention. Now I will show you some tricks like how to write meaningful SQL queries, SELECT COUNT magic, tricky Bind variables, how to minimize transfered bytes over network, how to minimize CPU or IO operations or how to use curly braces in control structures and other interesting things. Also I'll try to tell you the terrible truth. There is no _GO_FASTER=TRUE (1=1). Why, why I didn't take the blue pill?! :-)

This post is dedicated to my favourite software that I'm using for administration of Oracle Database, debug or (occasionaly) for development.

1. Linux - I met with Linux for first time in 1999. Of course as former Windows user I had some habits and I was praying for the same features and behaviour as on Windows. After short acclimation (approx 3 months) I decided to stay on this (Linux) platform. I like the Fedora distribution (former RedHat free distribution) and I'm using it everywhere (Office Desktop PC, Notebook, Home Desktop PC and personal Notebook). Linux gives me everything what I need for free such as Open Office, Firefox browser, Evolution mail client, powerful and useful scripting languages (Bash, Perl), LDAP clients, GnuPG, local firewall and many other software and of course my favourite editor (not just) Vi(m).
2. rlwrap - If you are using SQL*Plus and you are missing functionality as you know from bash (for example) then rlwrap is the right sofware for you. rlwrap "is a 'readline wrapper' that uses the GNU readline library to allow the editing of keyboard input for any other command."rlwrap can be downloaded here.Installation is very simple. Install rlwrap from source or RPM package. Modify your .bashrc or .bash_profile and add following line:

   alias sqlplus='rlwrap sqlplus'

3. Oracle SQL Developer - If you are looking for some tool similar to TOAD and for Linux then you probably looking for Oracle SQL Developer. SQL Developer (formerly Raptor) is "a new, free graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks." More informations you could find here and in Jonathan Gennick's article. What to add? It is for free and is available for Windows, Linux and MacOSX. It supports extensions (just like Firefox) and of course it supports online upgrade function. So what are you waiting for? Download it now!
4. JDeveloper - "Oracle JDeveloper is a free integrated development environment with end-to-end support for modeling, developing, debugging, optimizing, and deploying Java applications and Web services." As SQL Developer this application support extensions (also PHP extension) and is online upgradable. You could download it from here.
5. Firefox - I'm using Firefox since 0.6 version (formerly known as Phoenix browser). Even limited functionality (basic functions only), worst stability I was used it instead of Mozilla because it was much smaller and took much less system resources than Mozilla browser. Time has changed and today Firefox is one of most used browsers and supported by many designers (skins) and programers (extensions, Oracle related extensions too).
6. vim - Vim ("Vi iMproved") is the most popular text editor on the world. Vim (or old Vi) is included almost on every existing Unix or Unix clone. And probably that is the editor what you are using when you execute "ed" command in SQL*Plus. Vim exists also for Windows platform (but I don't think that many "Win" users is using it). If you are using it on your Oracle enviroment you could read this article written by my former colleague David.

So that was my most liked software, what is yours?

I was thinking about my own blog for long time. Final decision was made after answering couple of my questions such as: - Is there something meaningful you can write about? - Will you find some free time for writing posts? and so on... Answer is pretty simple: "Don't know. I'll will be writing about Oracle, Linux or other Unix-like OS and other thoughts what crossed my mind. Basicaly I will publish anything I find as interesting..." I decided to code my own blog because I couldn't found appropriate free blog software (which met all my needs) on Freshmeat site. So "Hello Blog" and I belive you enjoy it. My apologies for my English, corrections are welcomed.