Thursday, November 29, 2012

Adding Wrap This Code context Menu to files

I was working on some packages and I wanted to wrap this code before I could deliver, the parameters that we need to pass to the wrap utility are not something easy to remember.

So I decided to make it easy. What I finally achieved is this..
The right click menu of PKS and PKG files now show a nice option called "Wrap This Code".

If you think this is handy, save following registry entries in .reg files and import them into registry. 
You might want to do some minor changes , for example if you are using some different extensions or you have different path for the wrap.exe etc.

Tuesday, November 06, 2012

Oracle APEX Reporting options

I am currently learning APEX the new (or not-new)development tool from Oracle.

By now I know how to create basic forms and simple reports etc. To make the learning better I started developing a software for school management.

With Oracle XE and APEX together you can develop a software that will be completely free of cost. There is a cache, however the reports that you can develop are limited to banded data only. If you want complex reports or if you want to generate PDF or DOC you need to look elsewhere. After searching on web I found following options are available.

1. Use Oracle BI Publisher. - NOT Free.
2. Use OC4J for PDF generation - not very clear on the license of this. I suppose this is not free.
4. JasperReports.

Currently I am checking BIRT.

Thanks to Vis Naidu for his detailed write up here

I will share my experiences with BIRT in my next posting.


Friday, July 06, 2007

My _VIMRC file for PL/SQL Development

Please find the file here

I tried to paste the contents here but somehow all words enclosed in the <> gets removed. So I uploaded it as file.

Note :
whenever we save file in VIM , a copy is created with filename~,
by default it is in the same directory from where original file is opened. To put all such backup files in specific dir create desired dir and set it as below

set bdir=c:\\vim\\temp
I have commented this line in the file cause if this line is not commented and this directory is not present, VIM can not save files.
So if you want to enable this feature first you need to create this dirctory.

Please do leave your comments or suggestions here or you may mail me at


Sunday, July 01, 2007

Finding Details of Oracle Errors.


When we get any oracle error and we want to know more details about it we have to search through oracle documentation. That is time consuming most of the times. Here is an attempt to make that task simple.

I have created a Excel file with a database of most ORA/PLS errors. Just enter the type of error (ORA, PLS etc) and the error number and click on GO. You will get the Error, Cause, and Action.

You will see a dialog box for entering the error type and code like this.

For example I entered this….

Error Type : ORA

Error number : 01234

You will get the Error message, its cause and action like this.

Please do leave your comments or suggestions here or you may mail me at



Generate Excel workbook with multiple worksheets from PL/SQL

Version 0.62

I am in apps environment and so assumed the presence of fnd_file package.

I am uploading a version for non-apps environment.

Please find the code Non-apps_gen_xl_xml_package.

The Apps version is here

Please do leave your comments or suggestions here or you may mail me at


Thursday, June 28, 2007

Create Excel Workbook by PL/SQL

Further to my previous post..

I have uploaded the actual package code here
Compile the code on 9i or above and it should compile w/o error.

Package Name : gen_xl_xml
Version : 0.62

You can download samples for demo of how to create a simple and multi-sheet workbook from here

Here I explain how to use this package...

1. First step is to open the file. You do this by using the procedure

If you not using the package in Oracle Applications then use
create_excel - You need to pass the directory object and the file name to create.

If you are under Oracle Applications then use
create_excel_apps - This does not have any parameter as it writes to the out file.
You can not call both procedures at a time.

2. Then you can create styles , worksheet by using the respective procedures.

3. To Write the cells you call write_cell_char and write_Cell_num procedures
4. If you just want to apply style but no contents then use write_cell_null
5. Use set_row_height and set_column_width to set the height and width of row and column respectively.
6. At then end you call close file method. At this point the package will actually write to the file.

Debugging :

There is a package level variable debug_flag which is false by default.

If your excel file is not opening or there are any errors you may set it on and try running from sqlplus to see all the steps that the package executes like creating worksheet , setting row height etc.

Known Limitations.

1. As of now Rows have to be inserted in ascending order.
2. Not all attributes in style are available now. For Example : Borders.
3. Currently formulas are not supported. You may try passing formula strings , I have not checked this but excel's XML structure does not write the formulas that way.
4. Date format is not supported as of now.
5. Column height changes has to be inserted in the ascending order. For example you can not call height change for column c after column e.

Note : This code is not using any version specific features but if you are using 8i I think directory object is not there rather you have to use path set by utl_dir.

I request you to use this code and pass on to whoever requires it. If you get any errors please write to me and I will try to sort them out. I plan to fix the above limitations as I go ahead.


Wednesday, January 17, 2007

VIM and PL/SQL Programming

I have been using VIM since last 5 years for doing most of my PL/SQL programming tasks and over this period I found some tweaks /scripts that will help me speed up my work and do it effectively.

Before I could go ahead I must thank VIM and the great mailing list of VIM which is very responsive and helpful. Most of the settings in vimrc I did were with the help of people on VIM list.

All these tweaks and scripts can be divided into three broad categories.
1. Changes to _VIMRC
2. Scripts
3. Extrernal settings.

1. Changes to _VIMRC

Not in any specific order.

First of all I will list down what functionality I achieved and then I will show you the steps.

1. All files with extensions PLS, PLB, BUF, SQL, CODE, PKG should use same syntax coloring as SQL. Reason is straight forward, as some projects use their own extensions for packages etc. These are the files extensions I chose for myself you can add your own extensions.

2. For all above type of files when ever I will do some foldings in VIM , those folds shall be remembered when I re-open those files.

3. As PL/SQL is case in sensitive I want search mechanism also to be case in-sensitive too.

4. Added my own custom dictionary where I can add all my frequently used table names and columns etc. With VIM's powerful word completion feature this will help me to improve my typing speed and also reduce number of errors/typo too.

5. Added some shortcuts

F9 : quit w/o saving and
F12 : quit with save. This is useful when you are working in sqlplus. After ed , doing changes simply press F12 and you are back to SQLplus.

F3 : Change case under cursor, I am not 100% happy with this , Will further modify it.
F4 : Copy word under cursor to clipboard.
F8 : Copy full path of file to clipboard. This is useful when you open file in VIM and frequently do changes and test it. Simply press F8 and go to SQLplus. Type @ and control-V.

6. Personally I like that VIM window is opened maximized. So added one line for that.

7. I dont use VIM's built in File navigator frequently but in case I use I have set it to show full details rather than file list.

8. A small visual change, I have set some colors for the SELECTION text.

9. By default VIM searches word under cursor when you press * key. A very useful feature.
I found one setting in tips that allows to search for multiple selected words/ text to be searched. A very useful enhansement.

10. Setting to hilight current line.

11. Menus that allows me to see the files from Unix directly
There is nothing new in this for VIM users but I think I have made it more convinient for myself, I am working on Oracle Apps. and many times I need to read the LOG and out files from Unix server, and I have the request id.

I have created some menus so that I can select proper sub-menu from it and pater/type the request id there. The file will be read for me in the VIM.

12. QuickSQL Menus. - I don't write triggers everyday and so when time comes I have to look for manuals to see the sytax etc. This is one example. So I cam up with this idea of adding menus which when selected will insert the syntax text in the editor.

Please find my _vimrc here

2. Scripts

In this section I will list all the VIM scripts that use.

1. FavMenu.vim - Adds a favourites menu to VIM. I like to keep added files like _VIMRC, TNSNAMES.ora etc. and of course the code files that I am currently editing.

2. showpairs.vim - This shows matching brackets

3. MultipleSearch.vim - Allows to search multiple words in different colours.

4. filemenu.vim - This script creates a menu of files of given type. I have one script called desc.sql which actually creates .desc files when I run desc command. Then this filemenu script adds a menu called desc in VIM. So I have all .desc files available at the click of a mouse.

5. FeralToggleCommentify.vim - This allows me to comment lines by pressing ALT-C, If line is commented it will get uncommented.

6. mru.vim - This is script to create a most recently used file list.

7. sql_iabbr.vim - Converts the sql key words into capital as you type.

8. sql_indent.vim - Automatically indents the code as you type.

9. sql.vim - I have changed the default coloring to suite to my likings.

Please do leave your comments or suggestions here or you may mail me at