What you want is most likely coded and available already

Agreed, developers have this passion to code..anything and everything..

I have been to many meetings with Java folks (fun..always fun!!:) ) wherein they start the meeting with "we can do this with really cool JMS implementation ..and built a hash table for this UI and a entity bean here ...another public class with business logic here.." and every other possible term which would make me wonder "Am I really in the same software field as these folks?"..And then after everyone sort of settles down I usually start with - "everything sounds really cool..but what are we trying to do here..Can I know the problem first?" :)

Okay now, back to the topic what I wanted to Blog..lets forget Java developers ..How abt Oracle backend Programmers? Time and again I see they love to code and implement something which is already there..

Here is something I recently encountered at a client place.."We wanted to dump this table data into csv format so that we can send it to xyz department..so this is our PL/SQL program to do that"

The program looks like

- open a file in write mode
- declare a cursor
- open the cursor
- fetch one row at a time to variable
- exit no more data to fetch
- write last the row fetched to file
- move the pointer to next line in file
- go back to fetching step and fetch the next row
- close the cursor
- catch exceptions etc..

the cursor was returning a resultset of 200K rows and this was taking about 2 hours to complete, when it was shown to me..

so whats wrong in the logic? Nothing ..absolutely..except that the programmer didnt realize there is much simpler way and that comes part of Oracle software - which could saved all the coding efforts..

okay ..here is my test table (testtab) and consider if I want to simply dump the data to csv format..

SQL> l
1* select * from test_tab
SQL> /

ID C DATA
--- - -----
1 B 1xxxx
2 C 2xxxx
3 A 3xxxx
4 B 4xxxx
5 C 5xxxx
6 A 6xxxx
7 B 7xxxx
8 C 8xxxx
9 A 9xxxx

Thats how the data looks..
All I have to do is change my sql setting to have a unique way to differenciate column seperator( which is by default set to space) other than data.

SQL> set colsep '","' <-- that does what I wanted

SQL> l
1* select '"'||t.id id,t.code,t.data||'"' from test_tab t

<-- change the query a little bit to add the double quotes to front and end of every row


SQL> spool c:\Venkat\blog\csv_output.xyz <-- redirects the output to file

SQL> /

ID ","C","T.DATA
---","-","------
"1 ","B","1xxxx"
"2 ","C","2xxxx"
"3 ","A","3xxxx"
"4 ","B","4xxxx"
"5 ","C","5xxxx"
"6 ","A","6xxxx"
"7 ","B","7xxxx"
"8 ","C","8xxxx"
"9 ","A","9xxxx"
SQL> spool off

-- now open the file and trim off the unnecessary lines from top and bottom (or you can use the sqlplus commands to suppress them)


Open the file (output.xyz) in Excel and it prompts you with Delimited or Fixed space question.. Pick delimited option and click on next, it would ask for delimiter/seperator used.. and I clicked on Others and entered a comma and also the text qualifier is set to double quotes




and thats all about whats needed..next step , Excel converts the data and shows data in Spreadsheet format..

Done!!


You want to talk performance..?
- here its plain SQL..dumping the text
- NO PLSQL involved
- no row by row FILE operation

What the plsql program was doing in 2 hours was done in less than 5 minutes with simple and straight sql..

:)


Check out sqlplus reporting functionality.. sometimes a simple "break on column skip 1" and TTITLE combination can generate better reports than what a crystal report or BI product out there is doing with repeated queries..

Sometimes its not all about just solving..solving it the efficient way too

Okay time to sign off now

( I had an interesting assignment to work with hibernate last week..and I am confident that Oracle tuning is nowhere close to dying..The options hibernate allows and the mess it creates - Gotto love that product for the job security it creates for Database Tuners :) )