Friday, July 10, 2009

Smart Choice for Cursor Processing

It's nice article desribing when to use
CURSOR FOR Loops
Avoid using it
SELECT INTO
Use when query can return atmost one row. Also put SELECT statements in seperate PROCEDURES/FUNCTION. Which can be optimized/cached in Oracle 11g.
CURSOR BULK COLLECT VARRAY [Fixed number of rows or less]
Use when SELECT query will return multiple rows but you know upper limit. If upper limit is very high say 10000, you may want to go for next approach. As it will consume lots of memory.
CURSOR BULK COLLECT NESTED ARRAY with LIMITS
Use when SELECT query will fetch multiple rows and you don't know the upper limit or you know the upper limit but it is very high.

More Detail with examples at:
http://www.oracle.com/technology/oramag/oracle/08-nov/o68plsql.html

Monday, July 6, 2009

Unix to Dos [How to write Control Character in Unix File]

Suppose following is your file. If you move the file from Windows to Unix, you will see Control M (^M) character at the end of each line.
File in Windows:
export USERNAME
export PASSWORD
export CTLPATH

Same File when you move it to Unix
export USERNAME^M
export PASSWORD^M
export CTLPATH^M
^M

You can see the special characters because Windows has [Carriage Return Line Feed] CRLF (\r\n) as line terminating sequence. Where as Unix has only [Line Feed] LF (\n) as line terminator.

So, when we move file from Dos (Windows) to Unix, we need some kind of special processing which will convert CRLF into LF.

One is to open the file and replace Control M character visible in file to blank. To replace Control M character one should know how to create Control M character in Unix.

Folloing is the way to create Control M character in Unix.

Open File in vi editor.

Come in Instert mode by pressing Esc i
Press Control V
Then Press Control M
Then Esc.

It will generate Control M.

Whatever control character you want to generate, first press Control + V and then Control +

So convert file from Dos to Unix follow the steps mentioned below:

Open file in vi.
Type following command.
:0,$s/^M//

The above command will remove Control M (^M) character from entire file.

I will mention other utilities to convert from Dos to Unix sometime later.