My _VIMRC file for PL/SQL Development


I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.

http://stembits.in/my-vimrc-file-for-plsql-development/

Finding Details of Oracle Errors.

I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.

http://stembits.in/finding-details-of-oracle-errors/

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 sanjeev.blog@gmail.com

Regards
Sanjeev

Create Excel Workbook by PL/SQL

I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.

http://stembits.in/create-excel-workbook-by-plsql/

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.

Regards
Sanjeev

VIM and PL/SQL Programming
I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.

http://stembits.in/vim-and-plsql-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 sanjeev.blog@gmail.com


Regards
Sanjeev