Keeping up with new features

if there is one thing hard with learning oracle , its catching up with its pace of releasing new features & functionalities.

Recently I saw a plsql code some developer had written at a client place, to remove all unwanted characters from a varchar2 column and just to retain alphabets and numbers..

I was quick to suggest a simple "translate" function would do the trick and PLSQL call can be totally avoided..I learnt about translate function when it was Oracle version 7.3 and I should consider lucky enough that its still there in database kernel, working the same way.

here is a test table to demonstrate the functionality..

SQL> select * from ccs_test_trans ;

C1 C2
---------- ----------
1 12 34 560
2 abcdefgh
3 ABC#$%@(
4 *&^^!%%
5 12345678
6 a;p 27s
7 olka8762;;

7 rows selected.

Assume c2 is the column, which somehow has all extra characters (since varchar2 accepts everything) and needs little bit of cleansing work.

this is how I would have used translate function..
logic is..use nested translate function..
1) first translate - capture all unwanted characters
2) second translate - removes them..leaving only the wanted characters intact.

and then use a replace function to remove the extra spaces (since translate would have replaced all unwanted characters to space/any other character you specify)

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' retain_chrs
6 from dual)
7 select c1,c2,
8 replace(translate( c2,
9 translate( c2,
10 retain_chrs,
11 lpad( '*', length(retain_chrs), '*' )
12 ),
13 ' ' ), ' ', '') as new_c2
14 from ccs_test_trans ,v1

SQL> /

C1 C2 NEW_C2
---------- ---------- ----------
1 12 34 560 1234560
2 abcdefgh abcdefgh
3 ABC#$%@( ABC
4 *&^^!%%
5 12345678 12345678
6 a;p 27s ap27s
7 olka8762;; olka8762

7 rows selected.


Now coding that wasnt difficult..but understanding or explaining that to anyone new certainly is..because they need to understand how translate works..

but as shown above, it certainly works..and does the work needed..

The question is..do we really need such a complicated query ..isnt there a simple way of solving the same?

Lets see with 10g we have regular expressions..and Oracle allows all POSIX references in SQL. So all I want is to retain all characters which are either numbers or alphabets and remove the rest..

:alnum -> denotes exactly the same ; either alphabets or numbers
REGEXP_REPLACE-> does the replace functionality

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' retain_chrs
6 from dual)
7 select c1,c2,
8 replace(translate( c2,
9 translate( c2,
10 retain_chrs,
11 lpad( '*', length(retain_chrs), '*' )
12 ),
13 ' ' ), ' ', '') as new_c2,
14 REGEXP_REPLACE( c2, '[^[:alnum:]]' , '') as RE_c2
15 from ccs_test_trans ,v1

SQL> /
-- results

C1 C2 NEW_C2 RE_C2
---------- ---------- ---------- ----------
1 12 34 560 1234560 1234560
2 abcdefgh abcdefgh abcdefgh
3 ABC#$%@( ABC ABC
4 *&^^!%%
5 12345678 12345678 12345678
6 a;p 27s ap27s ap27s
7 olka8762;; olka8762 olka8762

7 rows selected.

How cool and simple is that?

Lets say I also want to allow comma and periods in the c2 column and they should be retained. (added two new rows with sample data to test)

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' ||
6 ',.' retain_chrs
7 from dual)
8 select c1,c2,
9 replace(translate( c2,
10 translate( c2,
11 retain_chrs,
12 lpad( '*', length(retain_chrs), '*' )
13 ),
14 ' ' ), ' ', '') as new_c2,
15 REGEXP_REPLACE( c2, '[^[:alnum:]{,.}]' , '') as RE_c2
16 from ccs_test_trans ,v1

SQL> /
-- results

C1 C2 NEW_C2 RE_C2
---------- ---------- ---------- ----------
1 12 34 560 1234560 1234560
2 abcdefgh abcdefgh abcdefgh
3 ABC#$%@( ABC ABC
4 *&^^!%%
5 12345678 12345678 12345678
6 a;p 27s ap27s ap27s
7 olka8762;; olka8762 olka8762
8 12,34,56.0 12,34,56.0 12,34,56.0
9 abc, def.g abc,def.g abc,def.g

9 rows selected.

I havent dealt a lot with REGULAR expressions..but it certainly looks an interesting area..if NOT performance, atleast its worth a try - for its readability and flexibility for future modifications.
I am sure you would agree the following SQL reads way too easy that the previous one (using translate) and running for 10+ lines.

SQL> select c1,c2,REGEXP_REPLACE( c2, '[^[:alnum:]{,.}]' , '') as RE_c2
2 from ccs_test_trans
3 /

C1 C2 RE_C2
---------- ---------- ----------
1 12 34 560 1234560
2 abcdefgh abcdefgh
3 ABC#$%@( ABC
4 *&^^!%%
5 12345678 12345678
6 a;p 27s ap27s
7 olka8762;; olka8762
8 12,34,56.0 12,34,56.0
9 abc, def.g abc,def.g

9 rows selected.

Happy SQL-ing until next..

ANSI & ORACLE MV - still a long way to go..

Anything new is always interesting and so is the my current project at a client place - which is sort of hybrid between Transactional and reporting system. Thanks to this project, I got a break from traditional TUNING tasks and am over-working on catching up with datawarehousing (DW) techniques & implementing them.

Here is something interesting what I figured last week (thanks to a question from client's dev team)..

-- Create 3 tables with 10 rows each
-- & add Primary keys

SQL> Create table test_tb1 as
2 select rownum c1, owner c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb1 add (
2 constraint test_tb1_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb2 as
2 select rownum c1, object_name c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb2 add (
2 constraint test_tb2_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb3 as
2 select rownum c1, object_type c2,created from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb3 add (
2 constraint test_tb3_pk primary key(c1)
3 )
4 /

Table altered.

-- this exercise is about MATERIALIZED VIEWS..so to have FAST REFRESHING (aka incremental refreshing) Materialized view implemented , we should first have MV LOG created on base tables.

--So here we create them

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb1
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb2
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb3
2 with primary key,ROWID,sequence
3* (c2,created) including new values
SQL> /

Materialized view log created.

-- Now that we have base tables and MV logs ready, lets try creating a Materialized view..(using ANSI syntax)

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from test_tb1 t1
13 JOIN test_tb2 t2
14 ON t1.c1 = t2.c1
15 JOIN test_tb3 t3
16* on t1.c1 = t3.c1
SQL> /
JOIN test_tb3 t3
*
ERROR at line 15:
ORA-12015: cannot create a fast refresh materialized view from a complex query

-- that fails ..because optimizer thinks its a complex query (I am not good as ANSI..and not sure if there is any way to rewrite within ANSI standard itself )..but the point is , I didnt expect that to be considered as complex query


-- that was the problem reported from Dev team to me..and I just thought to test if it was ANSI SQL which was creating the problem. So here I rewrite the same SQL using traditional joins

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18* and t1.c1 = t3.c1
SQL> /

Materialized view created.

-- WORKS!! Thats surprising..


SQL> drop materialized view test_mv1;

Materialized view dropped.

-- Lets try the same with outer joins too

SQL> ed
Wrote file afiedt.buf

1* drop materialized view test_mv1
SQL> Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18 and t1.c1 = t3.c1(+)
19 /

Materialized view created.

-- Selecting from MV

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS

10 rows selected.

-- Testing MV functionality by adding more data to base tables and
-- seeing if MV works fine.

SQL> insert into test_tb1 select 10+rownum c1 , 'NEW DATA' c2 from test_tb1
2 /

10 rows created.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.

SQL> commit;

Commit complete.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> insert into test_tb2 select 10+rownum c1, 'New data' c2 from test_tb2
2 /

10 rows created.

SQL> select * from test_tb2;

C1 C2
---------- -------------------------
1 ICOL$
2 I_USER1
3 CON$
4 UNDO$
5 C_COBJ#
6 I_OBJ#
7 PROXY_ROLE_DATA$
8 I_IND1
9 I_CDEF2
10 I_PROXY_ROLE_DATA$_1
11 New data
12 New data
13 New data
14 New data
15 New data
16 New data
17 New data
18 New data
19 New data
20 New data

20 rows selected.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.


-- works fine!!!

Moral: Though ANSI standard has been introduced in Oracle since 9.1 version, its always better to check back to traditional join syntax when sometimes you get unexpected error with sql/optimizer.

PLSQL rocks..

Most of us know PLSQL is an extension of SQL meant for procedural needs and computations which cannot be done using SQL/"set theory"... It takes a while for programmers coming from procedural programming world (Cobol, Java, C etc) to switch to SQL because thinking in sets needs some time and practise before you can start enjoying the fun.

But fortunately or unfortunately, people who dont find SQL solution easily still somehow manage to solve everything in Oracle just by writing lots of procedural code using PL/SQL and call it within SQL..One classic example (funny too) is illustrated here.

But check out the PLSQL package guide and you will be amazed how many useful APIs there are..actually its been tough to catch up with the speed of new packages getting introduced with every new release.

I just happened to read something really cool and was amazed..Would you believe Oracle's engine has the capability of solving Linear algebra problems? Read on ..to believe..


From PLSQL documentation:

UTL_NAV.LAPACK_GESV Procedures
This procedure computes the solution to a real system of linear equations
a * x = b
where a is an n by n matrix and x and b are n by nrhs matrices.
The LU decomposition with partial pivoting and row interchanges is used to factor A
as
a = P * L * U
where P is a permutation matrix, L is unit lower triangular, and U is upper triangular.
The factored form of a is then used to solve the system of equations
a * x = b

Syntax
UTL_NLA.LAPACK_GESV (
n IN POSITIVEN,
nrhs IN POSITIVEN,
a IN OUT UTL_NLA_ARRAY_DBL,
lda IN POSITIVEN,
ipiv IN OUT UTL_NLA_ARRAY_INT,
b IN OUT UTL_NLA_ARRAY_DBL,
ldb IN POSITIVEN,
info OUT INTEGER,
pack IN flag DEFAULT 'C');

Solving Linear equation with PL/SQL :


I am taking a practical problem from this site :
http://mathforum.org/library/drmath/view/57278.html

Problem:
(1) A has three times as many sweets as B. If he gives B six sweets,
he will then have twice as many as B then has. How many sweets did
they each have to start with?

Solution:

Let x = number of sweets that B has initially; then 3x is the number
that A has. If now A gives 6 sweets to B then A has 3x-6 sweets and B
has x+6 sweets. Now we are told that after this transfer, A has twice
as many sweets as B, so we can write down an equation to represent
this fact, i.e.

3x-6 = 2(x+6)
3x-6 = 2x + 12
3x-2x = 12 + 6
x = 18

So initially B had 18 sweets and A had 3*18 = 54 sweets.

Check: After transfer B has 18+6 = 24, A has 54-6 = 48, and 48 is
twice 24.

Now, lets solve this using PL/SQL

Lets represent the data in Matrix form first..

Lets say 'a' is the number of sweets A has & 'b' is the number of sweets with B

i) Before Transfer..

a-3b=0 (since a exactly is 3 times of b)

ii) After transfer (A gives 6 sweets to B)

a-6 = 2 (b+6) (after transfer A has double the amount of what B has)
a-6 = 2b +12
a-2b = 18


So we have two equations..
a - 3b = 0 -- Equation 1
a - 2b = 18 -- Equation 2

Representing the same by Matrix theory..


| 1 -3 | | a | __ | 0 |
| 1 -2 | | b | -- | 18 |

<---A--> <-X-> -- <-B--> <== Matrix Notation

Now all we have to do is find values of Matrix X

Lets feed the same value into PLSQL and save ourselves some time...



SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 A utl_nla_array_dbl := utl_nla_array_dbl(1,1,-3,-2);
3 B utl_nla_array_dbl := utl_nla_array_dbl( 0,18);
4 X utl_nla_array_int := utl_nla_array_int(0,0);
5 result integer;
6 BEGIN
7 UTL_NLA.LAPACK_GESV (
8 n => 2, /* MATRIX A is 2*2 */
9 nrhs => 1, /* MATRIX B has 1 column */
10 a => A, /* PASS MATRIX A */
11 lda => 2, /* max(1,2)=2 */
12 ipiv => X, /******** THIS IS WHAT WE WANT TO FIND */
13 b => B, /* PASS MATRIX B */
14 ldb => 2, /* max(1,2)=2 */
15 info => result, /* Code execution status */
16 pack => 'C' /* default column-wise */
17 );
18 IF result=0 THEN
19 dbms_output.put_line('PLSQL call was successfully executed');
20 dbms_output.put_line(' Results');
21 dbms_output.put_line(' -------------');
22 FOR i IN 1..B.count LOOP
23 dbms_output.put_line('- '||chr(64+i) ||' = ' || TO_CHAR(B(i),'999'));
24 END LOOP;
25 ELSE
26 dbms_output.put_line('Error during PLSQL call..Troubleshooting reqd');
27 END IF;
28* END;

SQL> /
PLSQL call was successfully executed
Results
-------------
- A = 54
- B = 18

PL/SQL procedure successfully completed.

How cool is that? There are lots of subprograms in the same UTL_NAV package to solve other situations (say if matrix A is not n*n matrix but instead happens to be m*n size)..


Have more fun..by changing the data and see how fast can your PL/SQL program actually respond..I tried this site to try couple of more linear equation problems and its working great

Something **NEW** learnt for the day :)

Job security - not looking bad

Forbes.com recently did a survey on fastest growing jobs (across all areas not just IT) and published the results here.

Database Administrator job shows up at #12..which is not bad at all and especially considering the ratio of developers-to-DBA positions are always something like 25:1 , this is not bad at all..

check out the metrics..
Number in 2004: 104,000
Number in 2014: 144,000 <-- 40000 new positions in 10 years..that is about 4000/year!!
Education required: Bachelor's degree

Number 5 looks very promising too..One more area where developers beat DBAs :)

look at the metrics
Number in 2004: 460,000
Number in 2014: 682,000 <-- 222,000 new positions in 10 years

Well well , the future looks very bright atleast for the next 5 years..Unless Oracle buys over "Pizza-hut" as part of their ongoing strategic acquisition/alliance :)

I am a developer..Why should I care about REDO?

Thats the sort of mentality most people have in Oracle Development team..The code (SQL or PLSQL or PSP or forms) should work as expected.why should a developer worry about redo? And isnt that anyways a DBA's job?

All perfect questions and assumptions..Lets see some facts abt Redo.

Redo logs are physical structures(files) of database that captures all the changes (similar to Transaction Logs in Sqlserver) and provides guarantee that even if your database crashes next minute right after your transaction is completed, you can be ensured to recover them back.

So how does it work?
Put it in very simple way..think you have a database supporting 100 applications..say 100 different schemas..you may have data stored across say 100 datafiles (just an example)..now since database doesnt which files are critical and which files are not..(all are datafiles..whether an application is used by 1 user or 1 million users..a change to data is a change to data!) ..so saving changes to all 100 files every single second is next to impossible (& unwise) task. So what does oracle do? Oracle manages changes to data (to datafiles) at its own break-time..so when the user changes a row, the data may or may not get into actual datafile.. the changed data for all you know may just be in RAM (volatile memory) and if the box crashes/reboots the contents are gone..(just like - if you have stored something in buffer by using Ctrl+C in windows and restart the machine..your buffer data is lost)

so how is the data guaranteed to recover back? Thats where redo log comes in play..Oracle keeps writing all changes to data (like what row..old value..new value..etc) to an area in SGA called Log buffer..the moment a transaction is committed, instead of writing into data files, log buffer contents are written into redo log files.

okay , so why should a developer be concerned?
Well this has something to do with Oracle's internals..Say your DBA allocated 3 redo files..Oracle will write to #1..when it gets full..move to #2..when that gets full..move on to #3..and when thats full..move onto ..where?

it has to overwrite #1..but if it overwrites #1 logfile contents, how are you doing to get the data if crash happens..Are you thinking? Oracle just does the same thing!!! It starts thinking ..halting everything else going on at the database..it waits for another process called archiver, to take a copy of Redolog#1 to some safe area before it can overwrite the logfile.

Fine..enuf with internals..still why should developer be worried abt redo?

the answer to that question now is very simple..A DBA can only allocate enough number of redo log files to keep it going trouble-free..but its the statements or code which generates redo..so its the code written by developers which is root cause of redo generated..Oracle is great in parallelism..horizontal scaling ..multi-user support..all we have heard from marketing folks..but if there one place where its gets really into a bottleneck situation is "REDO".

so think about it..say a developer writes a statement which generates 2K bytes of redo data more than optimal/tuned statement..more such developers = more such statements; more users executing this application = more times the same statement will have to rexecute. potentially generating gobs of redo data at the database..more redo data = more redologs filled in at database..bringing the database to a halt each time Oracle has to wait for archiver to kick-in and back up the logfile before overwriting it..

Hmm..interesting..how do I know how much redo my statements are generating..

go back to autotrace..and run the statement with trace on..you would see a statistic showing up as "redo size"..It cant better any easier than that..

And believe me..the DBA can only see if the system is running out of log files and patch it more..DBA doesnt know which statement is causing such excessive redo (althought the session can be traced and then sql..99% that never happens..because the situation will be more of fire-fighting mode..when the database hangs, DBA cannot walk to developer's cube and ask "how dare you spoiled my day?")

So think about it this way..the less redo you (as a developer) generate..the more you are doing a favor to yourself and your DBA(s) too.. sort of like making his/her life peaceful..If you really work hard on getting keeping your code's redo size down, your DBA better realize your efforts and start treating you a free dinner or thank-you session at a bar.

thats also explains how a developer can easily mess up with DBAs weekend,if needed..write some bad code and put it as batch job to start sharp at Friday 10PM..your database will hang and DBAs get called/paged automatically..what a simple way to ruin their weekend -huh?

Whoever said Developers dont have rights to challenge DBAs - is utterly wrong. :)

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 :) )

Software development - a continuous learning process

Its interesting to read this recent post in one of my favourite sites.

I cant agree more - as I get to learn most of new stuff only when I am given a task..dont remember the last time I read something from documentation and understood on my first read.

and about the academic qualification - again a big nod from me.if someone goes back to my college and disclose that I am working as Oracle consultant, I am sure my lecturer would bet any money that you are lying..Because I hardly sat in any sessions..and lab sessions (wherein you have to program and show the results) were really allergic to me..:)..I had a big green book which was titled BDP (Business Data Processing I think)..dont remember how many times I read that..but couldn't understand anything after first page .:)

but this part really made me feel better..
"Start a blog. Pick a writing schedule and stick with it; I recommend once a week at minimum. Select a general theme for your blog and write on topics related..."

I m glad I am atleast advancing in the battlefield in the right direction..but I guess I have work on my timing schedule..

Actually I was working on posting a new blog on Index Organized tables(aka IOTs)..but I am not really convinced with my own test scripts..looking for a better/close-to real-world example to post that.

Catch you in next