Wednesday, March 18, 2009
Tuesday, October 07, 2008
Filler
Friends,
I am in Boston on a client visit and i am liking my journey immensely. Apparently, i am at the right time at the right corner of the world. The first thing that hit me about Boston was its greenery. And then the sites to watch, the ancient culture, the friendly people around all add up to the pleasure.
Cape Cod was the best beach I have ever visited, considering the number of beaches i have been to. I have been to beaches in India (Pondy and Chennai) but none impressed me more than this.
I had been to Niagara Falls too. What an amazing falls!
I have good friends to give me company here and I made some real good friends here; friends i can count on!
All in all, the trip is peaceful so far.
I am in Boston on a client visit and i am liking my journey immensely. Apparently, i am at the right time at the right corner of the world. The first thing that hit me about Boston was its greenery. And then the sites to watch, the ancient culture, the friendly people around all add up to the pleasure.
Cape Cod was the best beach I have ever visited, considering the number of beaches i have been to. I have been to beaches in India (Pondy and Chennai) but none impressed me more than this.
I had been to Niagara Falls too. What an amazing falls!
I have good friends to give me company here and I made some real good friends here; friends i can count on!
All in all, the trip is peaceful so far.
Saturday, January 19, 2008
Monday, May 22, 2006
Death...
Death is nothing at all
I have only slipped away into the next room
I am I and you are you
Whatever we were to each other
That we are still
Call me by my old familiar name
Speak to me in the easy way you always used
Put no difference into your tone
Wear no forced air of solemnity or sorrow
Laugh as we always laughed
At the little jokes we always enjoyed together
Play, smile, think of me, pray for me
Let my name be ever the household word that it always was
Let it be spoken without effort
Without the ghost of a shadow in it
Life means all that it ever meant
It is the same as it ever was
There is absolute unbroken continuity
What is death but a negligible accident?
Why should I be out of mind
Because I am out of sight?
I am waiting for you for an interval
Somewhere very near
Just around the corner
All is well.
Nothing is past; nothing is lost
One brief moment and all will be as it was before
How we shall laugh at the trouble of parting when we meet again!
--Canon Henry Scott-Holland,(1847-1918)
Canon of St Paul's Cathedral
'The King of Terrors', a sermon on death delivered in St Paul's Cathedral on Whitsunday 1910, while the body of King Edward VII was lying in state at Westminster:
published in Facts of the Faith, 1919 Saturday, May 20, 2006
To You...My Orange Evenings
Leisure
by William Henry Davies
What is this life if, full of care,
We have no time to stand and stare.
No time to stand beneath the boughs
And stare as long as sheep or cows.
No time to see, when woods we pass,
Where squirrels hide their nuts in grass.
No time to see, in broad daylight,
Streams full of stars like skies at night.
No time to turn at Beauty's glance,
And watch her feet, how they can dance.
No time to wait till her mouth can
Enrich that smile her eyes began.
A poor life this if, full of care,
We have no time to stand and stare.
Load Excel Sheet data into Oracle Table using SQLLOADER
Question of the Day
Q) Is it possible to load data from an Excel sheet into an Oracle table?A)
First, lets make clear that such a thing is possible. Data can be loaded from an Excel sheet into an oracle table. I will list out one way how to do it, and point out other ways through which the same can be achieved, but those i haven't tried myself yet.
Method 1) We can save the spreadsheet data with a '.CSV' extension. This will save the records in a CSV file with each record in a separate line, each field delimited by a comma. CSV here stands for 'Comma Separated Values'.
This method contains three steps:
1) create a data file.
2) create a control file
3) run SQL* LOADER.
Step 1) creating a data file. Consider we have the following data in the Excel spreadsheet that has been saved in a CSV format. We will name it emp.dat. This will be our data file.
1,vinod,palle
2,pavan,keesara
3,sarath,sura
4,bill,gates
5,larry,ellison
6,larry,wall
Now, this file is specified in the control file. We will see how. But first, lets ensure there is an 'emp' table in the database. Else, we will create it.
$ sqlplus vikram/singh
SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 18 02:37:19 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
VIK TABLE
SQL> create table emp(eno number,
ename varchar2(20),
lastname varchar2(20));
Table created.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ---------------------------- ENO NUMBER
ENAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
SQL> select * from emp;
no rows selected
One can see that there are no records in the emp table. We can insert our data from the .CSV file into the table. We can also append the data into the table using the APPEND keyword.
Step 2) creating a control file.
Our control file may look like the one below. We will call it emp.ctl:
LOAD DATA
INFILE emp.dat
INSERT INTO TABLE emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(eno, ename, lastname)
Each line in the control file is as follows:
LOAD DATA: This loads data
INFILE emp.dat: This is the place to specify our datafile. It can also contain '*' which means the data is not in any data file but in the same control file. In the latter case, the data should be specified at the end of the control file.
INSERT INTO TABLE emp: This says to 'sqlldr' to actually insert data into table 'emp'. The other options can be APPEND, and TRUNCATE.FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' This says to the sqlldr utility how to read/parse the data file. In our case, which is CSV, the fields in each line(record) are delimited by a comma. However, there may be any character to delimit the fields. An optional character can also be specified.
(eno, ename, lastname) These are the fields in the 'emp' table.
Step 3) run SQL* Loader. This is the actual invocation of the SQL*LOADER. This can be as follows:
$sqlldr vikram/singh control=emp.ctl log=emp.log
SQL*Loader: Release 10.1.0.3.0 - Production on Thu May 18 04:17:16 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 6
Thats it! there are a lot many other options that can be given at the command level.
For more details, refer Oracle Documentation.
Verification:
Now is the time to verify if our data is loaded properly.
$ sqlplus vikram/singh
SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 18 03:10:50 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
select * from emp;
ENO ENAME LASTNAME
---------- -------------------- --------------------
1 vinod palle
2 pavan keesara
3 sarath sura
4 bill gates
5 larry ellison
6 larry wall
6 rows selected.
That verifies it!
Now we will see other methods of doing the same. I shall only list the method of doing it. I dont know the procedure.
Method 2) Use the perl module DBI and DBD:Excel modules. Then, perl will read Excel sheet data for you, write it to a named pipe, which would be an input to the SQL* Loader.
The modules can be obtained from the CPAN site.
DBD:Excel http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm
DBI http://search.cpan.org/~timb/DBI-1.50/DBI.pm
Check for the latest releases before downloading any of these modules. They come with good documentation.
Method 3) There is a product available in the market, that does exactly the same what we are looking for, and more. However, it costs. more details can be obtained from: http://www.oraxcel.com/projects/sqlxl
There may be other products but i am not aware of it.
Thus ends our journey!
This is surely not an end. If any of you folks out there know a better way of achieving the same thing, or any of you find flaws with the above explanation, feel free to mail me at "vikramsingh120@gmail.com". Your suggestions, complaints are always welcome.
Thank You.
Q) Is it possible to load data from an Excel sheet into an Oracle table?A)
First, lets make clear that such a thing is possible. Data can be loaded from an Excel sheet into an oracle table. I will list out one way how to do it, and point out other ways through which the same can be achieved, but those i haven't tried myself yet.
Method 1) We can save the spreadsheet data with a '.CSV' extension. This will save the records in a CSV file with each record in a separate line, each field delimited by a comma. CSV here stands for 'Comma Separated Values'.
This method contains three steps:
1) create a data file.
2) create a control file
3) run SQL* LOADER.
Step 1) creating a data file. Consider we have the following data in the Excel spreadsheet that has been saved in a CSV format. We will name it emp.dat. This will be our data file.
1,vinod,palle
2,pavan,keesara
3,sarath,sura
4,bill,gates
5,larry,ellison
6,larry,wall
Now, this file is specified in the control file. We will see how. But first, lets ensure there is an 'emp' table in the database. Else, we will create it.
$ sqlplus vikram/singh
SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 18 02:37:19 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
VIK TABLE
SQL> create table emp(eno number,
ename varchar2(20),
lastname varchar2(20));
Table created.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ---------------------------- ENO NUMBER
ENAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
SQL> select * from emp;
no rows selected
One can see that there are no records in the emp table. We can insert our data from the .CSV file into the table. We can also append the data into the table using the APPEND keyword.
Step 2) creating a control file.
Our control file may look like the one below. We will call it emp.ctl:
LOAD DATA
INFILE emp.dat
INSERT INTO TABLE emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(eno, ename, lastname)
Each line in the control file is as follows:
LOAD DATA: This loads data
INFILE emp.dat: This is the place to specify our datafile. It can also contain '*' which means the data is not in any data file but in the same control file. In the latter case, the data should be specified at the end of the control file.
INSERT INTO TABLE emp: This says to 'sqlldr' to actually insert data into table 'emp'. The other options can be APPEND, and TRUNCATE.FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' This says to the sqlldr utility how to read/parse the data file. In our case, which is CSV, the fields in each line(record) are delimited by a comma. However, there may be any character to delimit the fields. An optional character can also be specified.
(eno, ename, lastname) These are the fields in the 'emp' table.
Step 3) run SQL* Loader. This is the actual invocation of the SQL*LOADER. This can be as follows:
$sqlldr vikram/singh control=emp.ctl log=emp.log
SQL*Loader: Release 10.1.0.3.0 - Production on Thu May 18 04:17:16 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 6
Thats it! there are a lot many other options that can be given at the command level.
For more details, refer Oracle Documentation.
Verification:
Now is the time to verify if our data is loaded properly.
$ sqlplus vikram/singh
SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 18 03:10:50 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
select * from emp;
ENO ENAME LASTNAME
---------- -------------------- --------------------
1 vinod palle
2 pavan keesara
3 sarath sura
4 bill gates
5 larry ellison
6 larry wall
6 rows selected.
That verifies it!
Now we will see other methods of doing the same. I shall only list the method of doing it. I dont know the procedure.
Method 2) Use the perl module DBI and DBD:Excel modules. Then, perl will read Excel sheet data for you, write it to a named pipe, which would be an input to the SQL* Loader.
The modules can be obtained from the CPAN site.
DBD:Excel http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm
DBI http://search.cpan.org/~timb/DBI-1.50/DBI.pm
Check for the latest releases before downloading any of these modules. They come with good documentation.
Method 3) There is a product available in the market, that does exactly the same what we are looking for, and more. However, it costs. more details can be obtained from: http://www.oraxcel.com/projects/sqlxl
There may be other products but i am not aware of it.
Thus ends our journey!
This is surely not an end. If any of you folks out there know a better way of achieving the same thing, or any of you find flaws with the above explanation, feel free to mail me at "vikramsingh120@gmail.com". Your suggestions, complaints are always welcome.
Thank You.
Hell by Connor McDonald
Hi friends,
let me start the blog with this funny article. This is written by Connor McDonald, the author of the books "Mastering Oracle PL/SQL:Practical Solutions", and "Oracle Insights: Tales of the Oak Table". I have gathered the following write-up from his website www.oracledba.co.uk. I suggest everyone have a look into his website. Its a good collection of his experiences with the Oracle databases. Well, read the article...
Hell story
My own personal hell story was moving database from one server to another, but we were recycling some of the hardware (disks and memory), so it was a unload-to-tape, reload-from-tape job.
The servers were in Port Hedland (a rat-infested dusty 110-degrees-plus 98% humidity hell hole... Hi to anyone in Port Hedland ... ). I was not physically present - don't you love it when IT companies think "remote login" is always the best way...Anyway, because of the dust issues, I unloaded three copies of the database to three tapes, and then did a verification read of all them. An on-site non-techie took care of unloading/reloading tapes for me.
He chucks the disk drives and memory from the old server plus the three tapes into a truck and drive 20 km's across town (where the new server is). Then I spend an hour on the phone trying to explain blind to him how to plug all this stuff in to the new server.
He turns it on... smoke starts billowing out of the box. Emergency shutdown (ie, rip out power cord). He's plugged some of the memory in wrong so its munched one of the boards. So (over the phone again) we talk through removing all the memory from just that board but leaving the other memory in.
Finally, the machine does in fact boot nicely. I log in as root and mount the new disks and start restoring from tape.
30mins in... first tape dies with a read error. About half the database restored. "No problem" I think... we load the second tape.
Another 30 mins, second tapes dies with a read error. Everything restored except SYSTEM tablespace. "Luckily we've got that third tape..." I think.
Third tape goes in... 'ufsrestore' command just hangs... Phone rings. On-site techie says "I can see tape spewing out of the Exabyte drive...."
So all tapes used, and no SYSTEM tablespace... As they say in the classics,
"Thats when you discover that adrenalin is brown and sticky".
I'm thinking about career moves etc, when the phone rings again. Its my wife.
"How's thing going at work?" she asks.
"Don't ask!..." I say and give her the run-down on what's happened..."Anyway, how's things at home?"
There's a long pause... followed by her bursting into tears....
"My pet budgerigar got out of the cage and has flown away....(sob) (sob)... he's in the tree in the back yard...(sob) (sob) can you please come home and try catch him..."
Not really what I needed at that point in time...
So I tell the techie I'm off to get a coffee... I jump in the car and scoot home to console the wife..."Where is the little bird?"
"That tree there..." says my wife as she points to our 100 year old citradora, around 150ft high. With the binoculars I can just see the little yellow budgie about 149.5ft up...
wife - "Can you climb up and get him?"
me (mentally) - "Are you out of your frickin mind ?!?!?!"
me (verbally) - "Of course dear, fetch me the ladder..."
So I start climbing this stupid tree, knowing full well that I'll never get this stupid bird, and that even if I did, I would wring its stupid little neck....30 mins later, I'm teetering at about 80ft and the branches won't support my weight anymore. So I climb back down, covered in sweat and scratches, but the effort seems to have appeased my wife...
So I spend another 10 mins crapping on about how "its better now that the the little bird is free" etc etc, knowing full well the neighbour's cat will have him for dinner within a matter of hours.
I scoot back to work...its amazing how teetering on the brink at 80ft gives you some clarity. I have a sneaking suspicion that the SYSTEM tablespace was on one of the disks that was NOT recycled from the old server. I get the techie to drive back to the old server, get all the remaining disks.
Another hour to get them all plugged in correctly (remember the good old days of SCSI terminators....) and voila! I find the SYSTEM tablespace on the old disks.
So at the end of all this drama, I naturally send my status email to management:
"Server moved, no errors encountered"
That ends the story.
Well friends, thats it. Keep looking for more funny, insightful articles, and information on this blog. While I will try to keep this blog as interesting as possible, i request everyone out there to contribute to this blog as well.
If you have any questions, feel free to ask me. My mailID is vikramsingh120@gmail.com. Thanks friends for reading this!
let me start the blog with this funny article. This is written by Connor McDonald, the author of the books "Mastering Oracle PL/SQL:Practical Solutions", and "Oracle Insights: Tales of the Oak Table". I have gathered the following write-up from his website www.oracledba.co.uk. I suggest everyone have a look into his website. Its a good collection of his experiences with the Oracle databases. Well, read the article...
Hell story
My own personal hell story was moving database from one server to another, but we were recycling some of the hardware (disks and memory), so it was a unload-to-tape, reload-from-tape job.
The servers were in Port Hedland (a rat-infested dusty 110-degrees-plus 98% humidity hell hole... Hi to anyone in Port Hedland ... ). I was not physically present - don't you love it when IT companies think "remote login" is always the best way...Anyway, because of the dust issues, I unloaded three copies of the database to three tapes, and then did a verification read of all them. An on-site non-techie took care of unloading/reloading tapes for me.
He chucks the disk drives and memory from the old server plus the three tapes into a truck and drive 20 km's across town (where the new server is). Then I spend an hour on the phone trying to explain blind to him how to plug all this stuff in to the new server.
He turns it on... smoke starts billowing out of the box. Emergency shutdown (ie, rip out power cord). He's plugged some of the memory in wrong so its munched one of the boards. So (over the phone again) we talk through removing all the memory from just that board but leaving the other memory in.
Finally, the machine does in fact boot nicely. I log in as root and mount the new disks and start restoring from tape.
30mins in... first tape dies with a read error. About half the database restored. "No problem" I think... we load the second tape.
Another 30 mins, second tapes dies with a read error. Everything restored except SYSTEM tablespace. "Luckily we've got that third tape..." I think.
Third tape goes in... 'ufsrestore' command just hangs... Phone rings. On-site techie says "I can see tape spewing out of the Exabyte drive...."
So all tapes used, and no SYSTEM tablespace... As they say in the classics,
"Thats when you discover that adrenalin is brown and sticky".
I'm thinking about career moves etc, when the phone rings again. Its my wife.
"How's thing going at work?" she asks.
"Don't ask!..." I say and give her the run-down on what's happened..."Anyway, how's things at home?"
There's a long pause... followed by her bursting into tears....
"My pet budgerigar got out of the cage and has flown away....(sob) (sob)... he's in the tree in the back yard...(sob) (sob) can you please come home and try catch him..."
Not really what I needed at that point in time...
So I tell the techie I'm off to get a coffee... I jump in the car and scoot home to console the wife..."Where is the little bird?"
"That tree there..." says my wife as she points to our 100 year old citradora, around 150ft high. With the binoculars I can just see the little yellow budgie about 149.5ft up...
wife - "Can you climb up and get him?"
me (mentally) - "Are you out of your frickin mind ?!?!?!"
me (verbally) - "Of course dear, fetch me the ladder..."
So I start climbing this stupid tree, knowing full well that I'll never get this stupid bird, and that even if I did, I would wring its stupid little neck....30 mins later, I'm teetering at about 80ft and the branches won't support my weight anymore. So I climb back down, covered in sweat and scratches, but the effort seems to have appeased my wife...
So I spend another 10 mins crapping on about how "its better now that the the little bird is free" etc etc, knowing full well the neighbour's cat will have him for dinner within a matter of hours.
I scoot back to work...its amazing how teetering on the brink at 80ft gives you some clarity. I have a sneaking suspicion that the SYSTEM tablespace was on one of the disks that was NOT recycled from the old server. I get the techie to drive back to the old server, get all the remaining disks.
Another hour to get them all plugged in correctly (remember the good old days of SCSI terminators....) and voila! I find the SYSTEM tablespace on the old disks.
So at the end of all this drama, I naturally send my status email to management:
"Server moved, no errors encountered"
That ends the story.
Well friends, thats it. Keep looking for more funny, insightful articles, and information on this blog. While I will try to keep this blog as interesting as possible, i request everyone out there to contribute to this blog as well.
If you have any questions, feel free to ask me. My mailID is vikramsingh120@gmail.com. Thanks friends for reading this!
Hello World!
This is a beginning, of a journey towards perfection. This is a beginning, of a search towards better way of doing things. This is a beginning, of new things, good and bad. This is a beginning!
Friends, read this blog...and
Suggest changes They will be considered if found feasible, or rational.
Learn things The entries will be posted here after thorough homework.
Share knowledge This is everybody's space. If you have anything useful, do not hesitate to mail it to me. My mailid: vikramsingh120@gmail.com .
This is a beginning, of a journey towards perfection. This is a beginning, of a search towards better way of doing things. This is a beginning, of new things, good and bad. This is a beginning!
Friends, read this blog...and
Suggest changes They will be considered if found feasible, or rational.
Learn things The entries will be posted here after thorough homework.
Share knowledge This is everybody's space. If you have anything useful, do not hesitate to mail it to me. My mailid: vikramsingh120@gmail.com .
Subscribe to:
Posts (Atom)