I was answering this question on plsqlchannel.com yesterday. I was way too confident that my answer was correct that I clicked the answer without giving it even a second thought. It was only when I saw my rank drop a whopping 30 points that I noticed the blunder that I did. The first blunder is that I should have cross checked my result once before submitting. Cause getting it right is more important that getting it faster. This is one thing that I have to relearn and hopefull teach my daughter. And the second is that I did'nt know that TRANSLATE always needs all the three arguments as not nulls. If anyone of them is a null then the result would be a null.
I was under the impression that TRANSLATE('ABCD','BCD','') would simply replace the 'BCD' with the '' and the reuslt would be 'A' . But I was utterly wrong. TRANSLATE would not do this. There is another function for this kind of a thing and we need to use REPLACE for that. REPLACE('ABCD','BCD','') would give the result of 'A'.
One thing to learn today. Actually two things. One in Oracle and one in Life. Get it right not just fast.
Tuesday, May 25, 2010
Monday, May 10, 2010
The Audacity of Hope - Book Review
Today I officially completed the book "The Audacity of Hope " by Barak Obama. I brought this book while I was traveling to Pune in Hyderabad airport. I was pleasantly surprised to see that book in Hyderabad. The book was priced at Rs.250 which I thought was a pretty good deal for a book in an airport.
The book definitely is a good read and a very pleasant one for that. I think for me it was my first introduction to American history and the amazing things that this country has in store for itself and for the whole world. Being an immigrant in this country and that too an unwilling one I had trouble adjusting to American way of living and the way things work in here But once I started working I think I have started realizing the good sides of it. And reading "The Audacity of Hope" really made me appreciate the american way even more. What is more endearing to me is the realization of the inherent flaws of humans and still striving to make this world a better place. And the way Obama writes his thought, it seems like its a no brainer. What is to be appreciated is the blatant frankness of the author in admitting the flaws that American ways have inflicted on foreign soils.
Reading the way in which Obama explains some of the points from the American Constitution, I envied all those students who got to listen to him really teach about American Constitution in his classes. Of course I know I would be worst fit in such a setting, but even then what is wrong in dreaming..
What is more interesting to me personally was the last chapter in which he talks about Family and his Family in particular. The relationship he has with Mitchel and how she manages and juggles as a working woman and a mom. Being a working mom myself I did identify very much with many of the feelings Obama says Mitchell Obama goes through, like feeling not good enough as a mom and at work. I am sure there is no reason for Mitchel Obama to feel that way but I definitely for one feel that way so many times. But reading this book has given me a new perspective of looking at things and made me realize that I am not alone and there are hundred of people who feel like me. And what I am doing is the best for my kids and my family,
Obama is a great writer and thinker. He has a uncanny way of connecting with people through his books. I can'nt wait to read his first book "The dreams of my father".
The book definitely is a good read and a very pleasant one for that. I think for me it was my first introduction to American history and the amazing things that this country has in store for itself and for the whole world. Being an immigrant in this country and that too an unwilling one I had trouble adjusting to American way of living and the way things work in here But once I started working I think I have started realizing the good sides of it. And reading "The Audacity of Hope" really made me appreciate the american way even more. What is more endearing to me is the realization of the inherent flaws of humans and still striving to make this world a better place. And the way Obama writes his thought, it seems like its a no brainer. What is to be appreciated is the blatant frankness of the author in admitting the flaws that American ways have inflicted on foreign soils.
Reading the way in which Obama explains some of the points from the American Constitution, I envied all those students who got to listen to him really teach about American Constitution in his classes. Of course I know I would be worst fit in such a setting, but even then what is wrong in dreaming..
What is more interesting to me personally was the last chapter in which he talks about Family and his Family in particular. The relationship he has with Mitchel and how she manages and juggles as a working woman and a mom. Being a working mom myself I did identify very much with many of the feelings Obama says Mitchell Obama goes through, like feeling not good enough as a mom and at work. I am sure there is no reason for Mitchel Obama to feel that way but I definitely for one feel that way so many times. But reading this book has given me a new perspective of looking at things and made me realize that I am not alone and there are hundred of people who feel like me. And what I am doing is the best for my kids and my family,
Obama is a great writer and thinker. He has a uncanny way of connecting with people through his books. I can'nt wait to read his first book "The dreams of my father".
Tuesday, May 04, 2010
Wednesday, March 24, 2010
dbms_utility.comma_to_table
This is the name of the proc which made my life much easier today. I had a requirement to update a table based on two parameters passed to a procedure. The update would have been simple had those parameters been tow columns corresponding to the two columns in a table. But those two parameters would be a comma separated strings. I need to derive the values by parsing the strings.
Below is a sample program used to solve this issue.
create or replace procedure emp_upd_prc
(ip_ename varchar2,
ip_ename_upd varchar2) as
l_ename_upd_array dbms_utility.uncl_array;
l_count binary_integer;
l_ename_array dbms_utility.uncl_array;
begin
dbms_utility.comma_to_table(ip_ename,l_count,l_ename_array);
dbms_utility.comma_to_table(ip_ename_upd,l_count,l_ename_upd_array);
for i in 1..l_count
loop
update emp
set ename = l_ename_upd_array(i)
where ename = l_ename_array(i);
end loop;
end emp_upd_prc;
/
But the limitations with using dbms_utility.comma_to_tabs procedure is that it can not take number string. Like in my case i tried to send a list of empno as the first parameter and a list of names as the second parameter. But i am getting the following error.
ERROR at line 1:
ORA-20001: comma-separated list invalid near 4
ORA-06512: at "SYS.DBMS_UTILITY", line 238
ORA-06512: at "SYS.DBMS_UTILITY", line 255
ORA-06512: at "SCOTT.EMP_UPD_PRC", line 12
ORA-06512: at line 1
After some googling I found that this procedure have some limitations. It does not accept numbers or special characters in strings. I guess there should be some way to overcome these issues. But as of now my requirement luckily has character data as both the parameters and not numeric, in which case I would have spent more time of finding the alternatives to the problem.
Below is a sample program used to solve this issue.
create or replace procedure emp_upd_prc
(ip_ename varchar2,
ip_ename_upd varchar2) as
l_ename_upd_array dbms_utility.uncl_array;
l_count binary_integer;
l_ename_array dbms_utility.uncl_array;
begin
dbms_utility.comma_to_table(ip_ename,l_count,l_ename_array);
dbms_utility.comma_to_table(ip_ename_upd,l_count,l_ename_upd_array);
for i in 1..l_count
loop
update emp
set ename = l_ename_upd_array(i)
where ename = l_ename_array(i);
end loop;
end emp_upd_prc;
/
But the limitations with using dbms_utility.comma_to_tabs procedure is that it can not take number string. Like in my case i tried to send a list of empno as the first parameter and a list of names as the second parameter. But i am getting the following error.
ERROR at line 1:
ORA-20001: comma-separated list invalid near 4
ORA-06512: at "SYS.DBMS_UTILITY", line 238
ORA-06512: at "SYS.DBMS_UTILITY", line 255
ORA-06512: at "SCOTT.EMP_UPD_PRC", line 12
ORA-06512: at line 1
After some googling I found that this procedure have some limitations. It does not accept numbers or special characters in strings. I guess there should be some way to overcome these issues. But as of now my requirement luckily has character data as both the parameters and not numeric, in which case I would have spent more time of finding the alternatives to the problem.
Friday, February 12, 2010
Inserting into a CLOB column using JDBC.
Below is a sample program to insert data into a table which has a clob column. The insert is pre compiled in the database in a procedure which takes in parameters for the columns. One of the parameters is the of type clob. The clob parameter is read from a file set as a parameter.
import java.io.*;
import java.sql.*;
import java.lang.reflect.*;
import oracle.jdbc.*;
import java.util.*;
public class CallProc {
public static void main(String args[]) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
//intg6i
//Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = godzilla)(PORT = 1541))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = DI55)))", "mars", "mars");
String url = "jdbc:oracle:thin:@godzilla:1541:DI55";
Properties props = new Properties();
props.put("user", "mars");
props.put("password", "mars");
props.put("SetBigStringTryClob", "true");
Connection conn = DriverManager.getConnection(url, props);
CallableStatement cstmt = null;
ResultSet rs = null;
System.out.println("Here");
cstmt = conn.prepareCall("{ call MS_2262_TRAN_EMAIL_STG_I(?,?,?,?,?,?,?,?,?,?,?,?,?,? )}");
cstmt.setString(1,"SKamble@phxa.com");
cstmt.setString(2,"VSavvich@phxa.com");
cstmt.setString(3,"ARavindran@phxa.com;NSarmadzhiev@phxa.com;NGodavarthi@phxa.com");
cstmt.setString(4,"");
cstmt.setString(5,"FW: Natixis WO for web services F/O/R and fulfillment");
cstmt.setString(6,"
cstmt.setString(7,"01/25/2010 16:44:25");
cstmt.setString(8,"Y");
cstmt.setString(9,"00000000A49F721FD035D511808D009027A83B5007003937B623EDADD3118071009027A83B50000000AA28D70000499E0AB23BD25A45AAE078142A9F1989000003B2D8CC0000");
String str =readFileAsString(args[0]);
cstmt.setString(10, str);
cstmt.setString(11,"KMILLER");
cstmt.setString(12,"KMILLER");
cstmt.setString(13,"KMILLER");
cstmt.registerOutParameter(14,OracleTypes.CURSOR);
System.out.println("Here");
cstmt.executeQuery();
rs = (ResultSet)cstmt.getObject(14);
}
private static String readFileAsString(String filePath)
throws java.io.IOException{
StringBuffer fileData = new StringBuffer(1000);
BufferedReader reader = new BufferedReader(
new FileReader(filePath));
char[] buf = new char[1024];
int numRead=0;
while((numRead=reader.read(buf)) != -1){
String readData = String.valueOf(buf, 0, numRead);
fileData.append(readData);
buf = new char[1024];
}
reader.close();
return fileData.toString();
}
}
import java.io.*;
import java.sql.*;
import java.lang.reflect.*;
import oracle.jdbc.*;
import java.util.*;
public class CallProc {
public static void main(String args[]) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
//intg6i
//Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = godzilla)(PORT = 1541))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = DI55)))", "mars", "mars");
String url = "jdbc:oracle:thin:@godzilla:1541:DI55";
Properties props = new Properties();
props.put("user", "mars");
props.put("password", "mars");
props.put("SetBigStringTryClob", "true");
Connection conn = DriverManager.getConnection(url, props);
CallableStatement cstmt = null;
ResultSet rs = null;
System.out.println("Here");
cstmt = conn.prepareCall("{ call MS_2262_TRAN_EMAIL_STG_I(?,?,?,?,?,?,?,?,?,?,?,?,?,? )}");
cstmt.setString(1,"SKamble@phxa.com");
cstmt.setString(2,"VSavvich@phxa.com");
cstmt.setString(3,"ARavindran@phxa.com;NSarmadzhiev@phxa.com;NGodavarthi@phxa.com");
cstmt.setString(4,"");
cstmt.setString(5,"FW: Natixis WO for web services F/O/R and fulfillment");
cstmt.setString(6,"
cstmt.setString(7,"01/25/2010 16:44:25");
cstmt.setString(8,"Y");
cstmt.setString(9,"00000000A49F721FD035D511808D009027A83B5007003937B623EDADD3118071009027A83B50000000AA28D70000499E0AB23BD25A45AAE078142A9F1989000003B2D8CC0000");
String str =readFileAsString(args[0]);
cstmt.setString(10, str);
cstmt.setString(11,"KMILLER");
cstmt.setString(12,"KMILLER");
cstmt.setString(13,"KMILLER");
cstmt.registerOutParameter(14,OracleTypes.CURSOR);
System.out.println("Here");
cstmt.executeQuery();
rs = (ResultSet)cstmt.getObject(14);
}
private static String readFileAsString(String filePath)
throws java.io.IOException{
StringBuffer fileData = new StringBuffer(1000);
BufferedReader reader = new BufferedReader(
new FileReader(filePath));
char[] buf = new char[1024];
int numRead=0;
while((numRead=reader.read(buf)) != -1){
String readData = String.valueOf(buf, 0, numRead);
fileData.append(readData);
buf = new char[1024];
}
reader.close();
return fileData.toString();
}
}
Sunday, January 31, 2010
Sudo
This is what I learned on sudo today. I hope i will update this post at a later time with more information but i am typing this down so that i don't forget and so that I can reference it at a later point of time.
We can sudo into a unix/linux box using the command sudo -u dba -i. (dba is the user whose permissions you will have ). After sudo ing into the machine we can connect using sysdba and access the all the database features.
I was doing some execises and came across this problem of creating a view in a schema. All I had to do was grant the create view previlage to this user but i did'nt know how to connect as dba or any other user in the database using which i can grant the premission to this view. So what i did was sudoed into the machine using sudo -u dba -i and connected to the database as sysdba. Then granted the permission. I never thought it could be so easy. But i guess when you have complete peace(@3 am in the morning) ideas strike you fast.
We can sudo into a unix/linux box using the command sudo -u dba -i. (dba is the user whose permissions you will have ). After sudo ing into the machine we can connect using sysdba and access the all the database features.
I was doing some execises and came across this problem of creating a view in a schema. All I had to do was grant the create view previlage to this user but i did'nt know how to connect as dba or any other user in the database using which i can grant the premission to this view. So what i did was sudoed into the machine using sudo -u dba -i and connected to the database as sysdba. Then granted the permission. I never thought it could be so easy. But i guess when you have complete peace(@3 am in the morning) ideas strike you fast.
Friday, January 15, 2010
Quotes
18-Feb-2010
------------
You would not bother about what other people think of you if you know how seldom they do.
01-Mar-2010
------------
Our doubts are traitors, and make us lose the good we oft might win, by fearing to attempt.
12-Feb-2010
------------
Prefer self-restraint to self-denial.(Shashi Tharoor on fasting).
19-jan-2010
------------
The inherent vice of capitalism is the unequal sharing of blessings. The inherent blessing of socialism is the equal sharing of misery
15-jan-2010
------------
Don't just read the easy stuff. You may be entertained by it, but you will never grow from it.
------------
You would not bother about what other people think of you if you know how seldom they do.
01-Mar-2010
------------
Our doubts are traitors, and make us lose the good we oft might win, by fearing to attempt.
12-Feb-2010
------------
Prefer self-restraint to self-denial.(Shashi Tharoor on fasting).
19-jan-2010
------------
The inherent vice of capitalism is the unequal sharing of blessings. The inherent blessing of socialism is the equal sharing of misery
15-jan-2010
------------
Don't just read the easy stuff. You may be entertained by it, but you will never grow from it.
Tuesday, December 29, 2009
Advanced Queuing
I would like to read and compile my learnings of the Advanced Queuing document download from Oracle. I downloaded it and would like to document my progress on it daily. I am writing it here so that i can review them at a later point in time and access my progress.
Thursday, October 29, 2009
Word Power Made Easy
I would like to blog the new words that i find on a day to day basis and keep adding them to this blog post.
dog-ear
:a corner of a page folded over like a dog's ear, as by careless use, or to mark a place
:to fold down the corner of (a page in a book).
tykes
:any small child.
:a small kids esp a small boy
dissident
:disagreeing or dissenting, as in opinion or attitude: a ban on dissident magazines.
bon mot
:a witty remark or comment;clever saying;witticism.
serendipity
:an aptitude for making desirable discoveries by accident.
:good fortune; luck: the serendipity of getting the first job she applied for.
moppet
:a young child.
patina
:a surface calcification of implements, usu. indicating great age.
:a similar film or coloring appearing gradually on some other substance.
oktoberfest
:an autumn festival that involves merrymaking and drinking beer
gadfly
:a person who persistently annoys or provokes others with criticism, schemes, ideas, demands, requests, etc
rolodex
: a small desktop file containing cards for names, addresses, and phone numbers.
ameliorate
:to make or become better, more bearable, or more satisfactory; improve; meliorate.
dissonance
:inharmonious or harsh sound; discord; cacophony.
hubris
:excessive pride or self-confidence; arrogance.
goo
:a thick or sticky substance.
:maudlin sentimentality.
shod
:a pt. and pp. of shoe.
slush
:partly melted snow.
:a mixture of grease and other materials for lubricating.
prattle
:to talk in a foolish or simple-minded way; chatter; babble.
misogynistic
: having or showing a hatred and distrust of women.
trenches
: a long, narrow excavation in the ground, the earth from which is thrown up in front to serve as a shelter from enemy fire or attack.
codger
:an eccentric man, esp who is old.
squalor
:the condition of being squalid, filth and misery
gork
:to understand thoroughly and intuitively.
confetti
: small bits of paper, usually colored, thrown or dropped from a height to enhance the gaiety of a festive event, as a parade, wedding, or New Year's Eve party.
Heffalump : Stuffed Animal.
grub
:an unkempt person.
:any remaining roots or stumps after cutting vegetation to clear land for farming.
synapses
:to join together
tempeh
:a fermented soybean cake.
snarky
:testy or irritable; short.
randy
:rude and aggressive
slimeball
:thin, glutinous mud.
:a viscous secretion of animal or vegetable origin.
loopy
:eccentric or crazy
:befuddled or confused, esp. due to intoxication.
rake
:an agricultural implement with teeth or tines for gathering cut grass, hay, or the like or for smoothing the surface of the ground.
ennui
:a feeling of utter weariness and discontent resulting from satiety or lack of interest; boredom
hag
:an ugly woman.
parole
:the conditional release of a person from prison prior to the end of the maximum sentence imposed.
salacious
: lustful or lecherous
heinrich
:a male given name, Germanic form of Henry.
bonnet
:a hat, usually tying under the chin and often framing the face, formerly much worn by women but now worn mostly by children.
clogs
:a shoe or sandal with a thick sole of wood, cork, rubber, or the like.
duvet
:a usually down-filled quilt, often with a removable cover; comforter.
garret
:an attic, usually a small, wretched one.
encore
:again; once more (used by an audience in calling for an additional number or piece).
:to call for a repetition of.
coffers
: a box or chest esp. for valuables
: a treasury, fund
bot
: the larva of a bot fly.
zeus
:The principal god of the Greek pantheon, ruler of the heavens, and father of other gods and mortal heroes.
strut
:to walk with a vain, pompous bearing, as with head erect and chest thrown out, as if expecting to impress observers.
dossier
:a collection of files or documents on the same subject. esp. a complete file containing detailed information about a person or topic.
critters
:a domestic animal
:any creature
risque
:daringly close to indelicacy or impropriety; off-color: a risqué story.
:indecent
schmaltz
:exaggerated sentimentalism, as in music or soap operas.
:fat or grease, esp. of a chicken.
heist
:a robbery or holdup
helmsmanship
:a man who steers a ship.
bard
:any poet
:a person who composed and recited epic and heroic poems, often while playing the harp, lyre or the like.
sobriquet
:a nick name
protean
:readily assuming different forms or characters; extremely variable.
:versatile; able to play many kinds of roles.
portmanteau
:a case or bag to carry clothing in especially while traveling, esp. a leather trunk or suitcase that opens into two halves.
pegging
:to drive or insert a peg into.
:to fasten with or with pegs
:to stike or pierce with or as with a peg.
viscera
:the organs in the cavities of the body, esp. those in the abdominal cavity.
:the intestines; bowels.
ubiquitous
:existing or being everywhere, esp. at the same time;omnipresent.
puerile
:of or pertaining to a child or to childhood.
:childishly foolish; immature or trivial
pilfer
:to steal especially in small quantities
charette
:a final, intensive effort to finish a project, esp. an architectural design project, before a deadline.
neanderthal
:primitive, unenlightened, or reactionary; culturally or intellectually backward.
:a reactionary; a person with very old-fashioned ideas.
pedants
:a person who makes an excessive or inappropriate display of learning.
:a person who overemphasizes rules or minor details.
:a person who adheres rigidly to book knowledge without regard to common sense.
pharaoh
:a title of an ancient Egyptian king.
:any person who uses power or authority to oppress others; tyrant.
incest
:sexual intercourse between closely related persons
:the crime of sexual intercourse, cohabitation, or marriage between persons within the degrees of consanguinity or affinity wherein marriage is legally forbidden.
labyrinth
:an intricate combination of paths or passages in which it is difficult to find one's way or to reach the exit.
:a mazelike pattern inlaid in the pavement of a church.
Limbo
:a region on the border of hell or heaven, serving as the abode after death of unbaptized infants (limbo of infants) and of the righteous who died before the coming of Christ (limbo of the fathers or limbo of the patriarchs).
:a place or state of oblivion to which persons or things are regarded as being relegated when cast aside, forgotten, past, or out of date
:an intermediate, transitional, or midway state or place
Luge
:a one- or two-person sled for coasting or racing down a chute, used esp. in Europe.
craven
:cowardly; contemptibly timid; pusillanimous.
:a coward
perseids
:any of a shower of meteors appearing in August and radiating from a point in the constellation Perseus
coven
:An assembly of 13 witches.
axletree
:a bar, fixed crosswise under an animal-drawn vehicle, with a rounded spindle at each end upon which a wheel rotates.
linchpin
:a pin inserted through the end of an axletree to keep the wheel on.
:something that holds the various elements of a complicated structure together.
bushel
:a unit of dry measure containing 4 pecks, equivalent in the U.S. (and formerly in England) to 2150.42 cubic inches or 35.24 liters (Winchester bushel), and in Great Britain to 2219.36 cubic inches or 36.38 liters (Imperial bushel). Abbreviation: bu., bush.
:a container of this capacity.
:a unit of weight equal to the weight of a bushel of a given commodity.
:a large, unspecified amount or number.
laxatives
:a medicine or agent for relieving constipation.
:of, pertaining to, or constituting a laxative; purgative.
:Stimulating evacuation of the bowels.
bulima
:abnormally voracious appetite or unnaturally constant hunger.
:a habitual disturbance in eating behavior mostly affecting young women of normal weight, characterized by frequent episodes of grossly excessive food intake followed by self-induced vomiting to avert weight gain.
troll
:to sing or utter in a full, rolling voice.
:to cause to turn round and round; roll.
:to roll; turn round and round.
:a lure used in trolling for fish.
marmite
:a metal or earthenware cooking pot with a cover, usually large and often having legs.
:A small covered earthenware casserole designed to hold an individual serving.
yak shaving
:Any apparently useless activity which, by allowing you to overcome intermediate difficulties, allows you to solve a larger problem.
amygdala
:an almond-shaped part, as a tonsil.
:a ganglion of the limbic system adjoining the temporal lobe of the brain and involved in emotions of fear and aggression.
crater
:a circular or almost circular area having a depressed floor, almost always containing a central mountain and usually completely enclosed by walls that are often higher than those of a walled plain; ring formation; ring.
jinx
:a person, thing, or influence supposed to bring bad luck
:to destroy the point of: His sudden laugh jinxed the host's joke
mulligan
: Also called mulligan stew. a stew containing meat, vegetables, etc., esp. one made of any available ingredients.
: Golf. a shot not counted against the score, permitted in unofficial play to a player whose previous shot was poor.
staid
: of settled or sedate character; not flight or capracious.
: fixed, settled or premanent.
dog-ear
:a corner of a page folded over like a dog's ear, as by careless use, or to mark a place
:to fold down the corner of (a page in a book).
tykes
:any small child.
:a small kids esp a small boy
dissident
:disagreeing or dissenting, as in opinion or attitude: a ban on dissident magazines.
bon mot
:a witty remark or comment;clever saying;witticism.
serendipity
:an aptitude for making desirable discoveries by accident.
:good fortune; luck: the serendipity of getting the first job she applied for.
moppet
:a young child.
patina
:a surface calcification of implements, usu. indicating great age.
:a similar film or coloring appearing gradually on some other substance.
oktoberfest
:an autumn festival that involves merrymaking and drinking beer
gadfly
:a person who persistently annoys or provokes others with criticism, schemes, ideas, demands, requests, etc
rolodex
: a small desktop file containing cards for names, addresses, and phone numbers.
ameliorate
:to make or become better, more bearable, or more satisfactory; improve; meliorate.
dissonance
:inharmonious or harsh sound; discord; cacophony.
hubris
:excessive pride or self-confidence; arrogance.
goo
:a thick or sticky substance.
:maudlin sentimentality.
shod
:a pt. and pp. of shoe.
slush
:partly melted snow.
:a mixture of grease and other materials for lubricating.
prattle
:to talk in a foolish or simple-minded way; chatter; babble.
misogynistic
: having or showing a hatred and distrust of women.
trenches
: a long, narrow excavation in the ground, the earth from which is thrown up in front to serve as a shelter from enemy fire or attack.
codger
:an eccentric man, esp who is old.
squalor
:the condition of being squalid, filth and misery
gork
:to understand thoroughly and intuitively.
confetti
: small bits of paper, usually colored, thrown or dropped from a height to enhance the gaiety of a festive event, as a parade, wedding, or New Year's Eve party.
Heffalump : Stuffed Animal.
grub
:an unkempt person.
:any remaining roots or stumps after cutting vegetation to clear land for farming.
synapses
:to join together
tempeh
:a fermented soybean cake.
snarky
:testy or irritable; short.
randy
:rude and aggressive
slimeball
:thin, glutinous mud.
:a viscous secretion of animal or vegetable origin.
loopy
:eccentric or crazy
:befuddled or confused, esp. due to intoxication.
rake
:an agricultural implement with teeth or tines for gathering cut grass, hay, or the like or for smoothing the surface of the ground.
ennui
:a feeling of utter weariness and discontent resulting from satiety or lack of interest; boredom
hag
:an ugly woman.
parole
:the conditional release of a person from prison prior to the end of the maximum sentence imposed.
salacious
: lustful or lecherous
heinrich
:a male given name, Germanic form of Henry.
bonnet
:a hat, usually tying under the chin and often framing the face, formerly much worn by women but now worn mostly by children.
clogs
:a shoe or sandal with a thick sole of wood, cork, rubber, or the like.
duvet
:a usually down-filled quilt, often with a removable cover; comforter.
garret
:an attic, usually a small, wretched one.
encore
:again; once more (used by an audience in calling for an additional number or piece).
:to call for a repetition of.
coffers
: a box or chest esp. for valuables
: a treasury, fund
bot
: the larva of a bot fly.
zeus
:The principal god of the Greek pantheon, ruler of the heavens, and father of other gods and mortal heroes.
strut
:to walk with a vain, pompous bearing, as with head erect and chest thrown out, as if expecting to impress observers.
dossier
:a collection of files or documents on the same subject. esp. a complete file containing detailed information about a person or topic.
critters
:a domestic animal
:any creature
risque
:daringly close to indelicacy or impropriety; off-color: a risqué story.
:indecent
schmaltz
:exaggerated sentimentalism, as in music or soap operas.
:fat or grease, esp. of a chicken.
heist
:a robbery or holdup
helmsmanship
:a man who steers a ship.
bard
:any poet
:a person who composed and recited epic and heroic poems, often while playing the harp, lyre or the like.
sobriquet
:a nick name
protean
:readily assuming different forms or characters; extremely variable.
:versatile; able to play many kinds of roles.
portmanteau
:a case or bag to carry clothing in especially while traveling, esp. a leather trunk or suitcase that opens into two halves.
pegging
:to drive or insert a peg into.
:to fasten with or with pegs
:to stike or pierce with or as with a peg.
viscera
:the organs in the cavities of the body, esp. those in the abdominal cavity.
:the intestines; bowels.
ubiquitous
:existing or being everywhere, esp. at the same time;omnipresent.
puerile
:of or pertaining to a child or to childhood.
:childishly foolish; immature or trivial
pilfer
:to steal especially in small quantities
charette
:a final, intensive effort to finish a project, esp. an architectural design project, before a deadline.
neanderthal
:primitive, unenlightened, or reactionary; culturally or intellectually backward.
:a reactionary; a person with very old-fashioned ideas.
pedants
:a person who makes an excessive or inappropriate display of learning.
:a person who overemphasizes rules or minor details.
:a person who adheres rigidly to book knowledge without regard to common sense.
pharaoh
:a title of an ancient Egyptian king.
:any person who uses power or authority to oppress others; tyrant.
incest
:sexual intercourse between closely related persons
:the crime of sexual intercourse, cohabitation, or marriage between persons within the degrees of consanguinity or affinity wherein marriage is legally forbidden.
labyrinth
:an intricate combination of paths or passages in which it is difficult to find one's way or to reach the exit.
:a mazelike pattern inlaid in the pavement of a church.
Limbo
:a region on the border of hell or heaven, serving as the abode after death of unbaptized infants (limbo of infants) and of the righteous who died before the coming of Christ (limbo of the fathers or limbo of the patriarchs).
:a place or state of oblivion to which persons or things are regarded as being relegated when cast aside, forgotten, past, or out of date
:an intermediate, transitional, or midway state or place
Luge
:a one- or two-person sled for coasting or racing down a chute, used esp. in Europe.
craven
:cowardly; contemptibly timid; pusillanimous.
:a coward
perseids
:any of a shower of meteors appearing in August and radiating from a point in the constellation Perseus
coven
:An assembly of 13 witches.
axletree
:a bar, fixed crosswise under an animal-drawn vehicle, with a rounded spindle at each end upon which a wheel rotates.
linchpin
:a pin inserted through the end of an axletree to keep the wheel on.
:something that holds the various elements of a complicated structure together.
bushel
:a unit of dry measure containing 4 pecks, equivalent in the U.S. (and formerly in England) to 2150.42 cubic inches or 35.24 liters (Winchester bushel), and in Great Britain to 2219.36 cubic inches or 36.38 liters (Imperial bushel). Abbreviation: bu., bush.
:a container of this capacity.
:a unit of weight equal to the weight of a bushel of a given commodity.
:a large, unspecified amount or number.
laxatives
:a medicine or agent for relieving constipation.
:of, pertaining to, or constituting a laxative; purgative.
:Stimulating evacuation of the bowels.
bulima
:abnormally voracious appetite or unnaturally constant hunger.
:a habitual disturbance in eating behavior mostly affecting young women of normal weight, characterized by frequent episodes of grossly excessive food intake followed by self-induced vomiting to avert weight gain.
troll
:to sing or utter in a full, rolling voice.
:to cause to turn round and round; roll.
:to roll; turn round and round.
:a lure used in trolling for fish.
marmite
:a metal or earthenware cooking pot with a cover, usually large and often having legs.
:A small covered earthenware casserole designed to hold an individual serving.
yak shaving
:Any apparently useless activity which, by allowing you to overcome intermediate difficulties, allows you to solve a larger problem.
amygdala
:an almond-shaped part, as a tonsil.
:a ganglion of the limbic system adjoining the temporal lobe of the brain and involved in emotions of fear and aggression.
crater
:a circular or almost circular area having a depressed floor, almost always containing a central mountain and usually completely enclosed by walls that are often higher than those of a walled plain; ring formation; ring.
jinx
:a person, thing, or influence supposed to bring bad luck
:to destroy the point of: His sudden laugh jinxed the host's joke
mulligan
: Also called mulligan stew. a stew containing meat, vegetables, etc., esp. one made of any available ingredients.
: Golf. a shot not counted against the score, permitted in unofficial play to a player whose previous shot was poor.
staid
: of settled or sedate character; not flight or capracious.
: fixed, settled or premanent.
Thursday, October 01, 2009
Java Stored Procedures
Its been a very long time that I have been wanting to execute some java stored procedures in the oracle database. And today finally I got to learn how to do them. This link clearly explained how we can execute java programs in the database and how we can create database procedures to call the java programs. With the help of the above link i created a sample HelloWorld program to be executed on my 11g database.
Below is a simple HelloWorld program that i wrote in java.
public class HelloWorld {
public static void printHello(String name) {
System.out.println("Hello "+name);
}
}
using the below statement I compiled the above code in the database.
loadjava -u nreddy/nreddy -v -j -t HelloWorld.java
After that i create a package with the below specification and body.
create or replace package Hello_World is
procedure Print_Hello(name varchar2);
end Hello_World;
/
create or replace package body Hello_World is
procedure Print_Hello(name varchar2) as language java
name 'HelloWorld.printHello(java.lang.String)';
end Hello_World;
/
Then i set the serveroutput on and execute the below procedure.
exec dbms_java.set_output(2000);
call hello_world.print_hello('Nirmala');
SQL> call hello_world.print_hello('Nirmala');
Hello Nirmala
Call completed.
Isn't that pretty cool. I know its very basic but I am extremely delighted to know how to execute a java program in Oracle.
Below is a simple HelloWorld program that i wrote in java.
public class HelloWorld {
public static void printHello(String name) {
System.out.println("Hello "+name);
}
}
using the below statement I compiled the above code in the database.
loadjava -u nreddy/nreddy -v -j -t HelloWorld.java
After that i create a package with the below specification and body.
create or replace package Hello_World is
procedure Print_Hello(name varchar2);
end Hello_World;
/
create or replace package body Hello_World is
procedure Print_Hello(name varchar2) as language java
name 'HelloWorld.printHello(java.lang.String)';
end Hello_World;
/
Then i set the serveroutput on and execute the below procedure.
exec dbms_java.set_output(2000);
call hello_world.print_hello('Nirmala');
SQL> call hello_world.print_hello('Nirmala');
Hello Nirmala
Call completed.
Isn't that pretty cool. I know its very basic but I am extremely delighted to know how to execute a java program in Oracle.
Wednesday, September 23, 2009
Error Logging Tables.
Error logging tables are a new feature introduced in oracle 10g release 2. They are used in places where the a unique constraint or a check constraint of a single row can result in the failure of a DML statement involving huge amount of data. For example you are importing data from a temporary table which has say a million records. The insert statement runs for about an hour and then gives a unique constraint error and rolls back all of the data inserted. In these cases wouldn't it be nice if the SQL inserted all the rows except the ones which generated the errors and logged the erroneous records in a separate table. Error logging tables do exactly that.
We can create an error table by using the procedure dbms_errlog.create_error_log.
The following syntax can be used to log the errors into the log table.
LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT {integer|UNLIMITED} ]
Below is the sample code to understand how the new error logging works.
drop table src ;
drop table tgt;
CREATE TABLE src (x,y,z)
AS
SELECT object_id
, object_type
, object_name
FROM all_objects
WHERE ROWNUM <= 5;
CREATE TABLE tgt
AS
SELECT *
FROM src
WHERE ROWNUM <= 3;
alter table tgt add constraint pky primary key (x);
insert into tgt
select * from src;
drop table error_tgt;
exec dbms_errlog.create_error_log(dml_table_name=>'tgt',err_log_table_name=>'error_tgt');
insert into tgt
select * from src
log errors into error_tgt ('log errors testing') reject limit unlimited;
select x , y ,z ,ORA_ERR_TAG$ from error_tgt;
References :
Natalka's Blog on ORAFAQ
Tim's OracleBase site
Databasejournal site
Adrian Billington's site
Mark Rittman's article in Oracle Magazine
We can create an error table by using the procedure dbms_errlog.create_error_log.
The following syntax can be used to log the errors into the log table.
LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT {integer|UNLIMITED} ]
Below is the sample code to understand how the new error logging works.
drop table src ;
drop table tgt;
CREATE TABLE src (x,y,z)
AS
SELECT object_id
, object_type
, object_name
FROM all_objects
WHERE ROWNUM <= 5;
CREATE TABLE tgt
AS
SELECT *
FROM src
WHERE ROWNUM <= 3;
alter table tgt add constraint pky primary key (x);
insert into tgt
select * from src;
drop table error_tgt;
exec dbms_errlog.create_error_log(dml_table_name=>'tgt',err_log_table_name=>'error_tgt');
insert into tgt
select * from src
log errors into error_tgt ('log errors testing') reject limit unlimited;
select x , y ,z ,ORA_ERR_TAG$ from error_tgt;
References :
Natalka's Blog on ORAFAQ
Tim's OracleBase site
Databasejournal site
Adrian Billington's site
Mark Rittman's article in Oracle Magazine
SYS.ANYDATA
Sys.anydata is a new datatype introduced in 9i. This datatype can be used to define a column which can be of any datatype. The advantage of this is that if we insert a data into a column defined as anydata datatype, it remains a date. If we insert a number, it remains a number. Below is the list of some example code provided on asktom site .
create table t(x sys.anyData);
insert into t values(sys.anyData.convertNumber(5));
insert into t values(sys.anyData.convertVarchar2('hello world'));
insert into t values(sys.anyData.convertDate(sysdate));
11:54:52 SQL> select * from t;
X()
-----------------------------------------
ANYDATA()
ANYDATA()
ANYDATA()
SQL> create or replace function getData(p_x in sys.anyData) return varchar2
2 as
3 l_varchar2 varchar2(4000);
4 l_rc number;
5 begin
6 case p_x.gettypeName
7 when 'SYS.NUMBER' then
8 l_rc := p_x.getNumber(l_varchar2);
9 when 'SYS.DATE' then
10 l_rc := p_x.getDate(l_varchar2);
11 when 'SYS.VARCHAR2' then
12 l_rc := p_x.getVarchar2(l_varchar2);
13 else
14 l_varchar2 := '** unknown **';
15 end case;
16
17 return l_varchar2;
18 end;
19 /
Function created.
SQL> select getData(x) getdata from t;
GETDATA
____________
5
19-MAR-02
hello world
create table t(x sys.anyData);
insert into t values(sys.anyData.convertNumber(5));
insert into t values(sys.anyData.convertVarchar2('hello world'));
insert into t values(sys.anyData.convertDate(sysdate));
11:54:52 SQL> select * from t;
X()
-----------------------------------------
ANYDATA()
ANYDATA()
ANYDATA()
SQL> create or replace function getData(p_x in sys.anyData) return varchar2
2 as
3 l_varchar2 varchar2(4000);
4 l_rc number;
5 begin
6 case p_x.gettypeName
7 when 'SYS.NUMBER' then
8 l_rc := p_x.getNumber(l_varchar2);
9 when 'SYS.DATE' then
10 l_rc := p_x.getDate(l_varchar2);
11 when 'SYS.VARCHAR2' then
12 l_rc := p_x.getVarchar2(l_varchar2);
13 else
14 l_varchar2 := '** unknown **';
15 end case;
16
17 return l_varchar2;
18 end;
19 /
Function created.
SQL> select getData(x) getdata from t;
GETDATA
____________
5
19-MAR-02
hello world
Tuesday, September 08, 2009
NLS Date Format.
I was reading an interesting article about SQL Injection and found that we can set the NLS_DATE_FORMAT this way too.
14:14:43 SQL> ALTER SESSION SET NLS_DATE_FORMAT = '"THIS IS A SINGLE QUOTE ''"';
Session altered.
Elapsed: 00:00:00.00
15:46:49 SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------------
THIS IS A SINGLE QUOTE '
Interesting stuff. Apparently we can use this the above setting to inject procedure which do not have any parameters passed too. Read this for me.
14:14:43 SQL> ALTER SESSION SET NLS_DATE_FORMAT = '"THIS IS A SINGLE QUOTE ''"';
Session altered.
Elapsed: 00:00:00.00
15:46:49 SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------------
THIS IS A SINGLE QUOTE '
Interesting stuff. Apparently we can use this the above setting to inject procedure which do not have any parameters passed too. Read this for me.
Friday, September 04, 2009
what is an Index Organized Table?
Over the last few days I came across this term IOT lot of times. I tried to memorize the abbreviation for it. Index Organized Table, Index Organized Table, Index Organized Table..... But it didn't sink in. Few days passed and today I was reading an article on asktom and came across a question on IOTs. And then I was trying to recollect what an IOT stood for. Well that is when I decided I need to learn about it in a better way than memorizing what IOT's full form was.
Then as anyone would do I goggled and found this wonderful white paper on Index Organized Tables and all the nitty gritty details of it. To summarize an Index Organized Table is a table which is organized based on the primary key Index of the table. This reduces storage requirements and the additional IO required during querying to access the rowid from the index and then the table. There are many added advantages to the Index Organized Tables like enabling parallel processing even tough the table is not partitioned. An IOT is no different from a conventional table in terms of the data types that can be stored except that we can not create object tables as IOTs. All the object features like Object type, VARRAYs, Nested Tables, REF cursors etc. are supported.
I could find only the advantages of using IOTs in the previous article but no disadvantages are listed there. But I found another good article which explained clearly how IOTs are physically organized and in what scenarios a IOT can be a performance drainer. It turns out that when a IOT is created physically the data will be stored in the index as well as the overflow table which contains the data of the overflowed columns. The actual table is just a logical entity which fetches data from the index and the overflow table. The only disadvantage I found was that when we need to do a full table scan on non key tables then we might have to do more physical reads than in the case where the table was a conventional table.
Having read all the articles I am left with the questions:
1) Can I convert all my existing tables into index organized table?
2) Do I have a criteria in selecting the tables which are a good fit to be defined as Index Organized Tables?
Then as anyone would do I goggled and found this wonderful white paper on Index Organized Tables and all the nitty gritty details of it. To summarize an Index Organized Table is a table which is organized based on the primary key Index of the table. This reduces storage requirements and the additional IO required during querying to access the rowid from the index and then the table. There are many added advantages to the Index Organized Tables like enabling parallel processing even tough the table is not partitioned. An IOT is no different from a conventional table in terms of the data types that can be stored except that we can not create object tables as IOTs. All the object features like Object type, VARRAYs, Nested Tables, REF cursors etc. are supported.
I could find only the advantages of using IOTs in the previous article but no disadvantages are listed there. But I found another good article which explained clearly how IOTs are physically organized and in what scenarios a IOT can be a performance drainer. It turns out that when a IOT is created physically the data will be stored in the index as well as the overflow table which contains the data of the overflowed columns. The actual table is just a logical entity which fetches data from the index and the overflow table. The only disadvantage I found was that when we need to do a full table scan on non key tables then we might have to do more physical reads than in the case where the table was a conventional table.
Having read all the articles I am left with the questions:
1) Can I convert all my existing tables into index organized table?
2) Do I have a criteria in selecting the tables which are a good fit to be defined as Index Organized Tables?
Wednesday, June 17, 2009
My Investment Plan
Its been a while since I have been thinking of investing some money in stocks. In the mean time the tough times came in and I just thanked my stars for not investing at that time. But now since I feel the market is not too high or not too low I am planning to invest in some stocks. I would like to pick some say 5 stocks and see how they perform till the end of this year. At the end of this year I would like to take out all the money and spend the profit for my sister's wedding. Well here the assumption is that I would make some profit. Even if I don't make any I think I have some saving which I can use on the occasion but I am putting that as my target because if there is a chance that I get a good profit I might get greedy and not pull out the money. Well the point is that I am investing for an occasion and I want to see if I can get anything out of my investments. I am penning this to look back and see how I perform.
Monday, May 18, 2009
Dropping Constraints.
I was working on dropping a primary key constraint on a table today and when I was recreating the constraint back my create statement was failing saying that an object already exists with that name. When I checked I found that the index on the table used for the primary key was not dropped when I dropped the constraint. This I thought was a little strange. So I tried to retest why this was happening, but the next time I created and dropped the primary key constraint both the constraint and the index were dropped. When I googled for the possible cause of this kind of phenomenon I found that when the table is created in oracle 9 and we are tying to drop the constraint in oracle 10 the indexes are not dropped. To ensure that the index is also dropped below statement can be used.
alter table X drop constaint x_pk drop index;
alter table X drop constaint x_pk drop index;
Tuesday, April 28, 2009
Updating partition key column
Recently i was working on a generating some test data into a large table which is partitioned by year. I was supposed to generate some data for the current year. And i thought i would just update the date part to current year and i would have the data. But since the table was partitioned and i was trying to update the partitioned column i got the below error and the update failed.
ORA-14402: updating partition key column would cause a partition change
Taking a look at the error it looked more like a warning and less a error. So i googled to find how i could fix the issue.
Below is the alter statement that I applied on the table.
alter table table_name enable row movement;
I did my update and restored the table back by applying the below statement.
alter table table_name disable row movement;
ORA-14402: updating partition key column would cause a partition change
Taking a look at the error it looked more like a warning and less a error. So i googled to find how i could fix the issue.
Below is the alter statement that I applied on the table.
alter table table_name enable row movement;
I did my update and restored the table back by applying the below statement.
alter table table_name disable row movement;
Tuesday, March 24, 2009
Friday, June 13, 2008
PL/SQL: numeric or value error: host bind array too small
I was getting this error today when i was debugging one bug in my code. On goggling i found that this is related to the dbms_output package that is being called in my code. The error turns out to be that when dbms_output.put_line proc is execute with a parameter whose length is more than 255 characters then the below error is generated.
SQL> exec dbms_output.put_line('123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890');
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
So when ever you execute dbms_output take care to see that the parameter is not more than 255 chanracter. I ran this on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0.
SQL> exec dbms_output.put_line('123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890');
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
So when ever you execute dbms_output take care to see that the parameter is not more than 255 chanracter. I ran this on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0.
Thursday, September 20, 2007
The Affluent Society
I happen to come across this link and found these quotes really intresting.
"Popularity is not always a test of needed intelligence"
"There remains always the possibility, even the probability, that they(Books) do more for the self-esteem of the author than for the fate of the world"
Hope fully i will get to read this book some time soon.
"Popularity is not always a test of needed intelligence"
"There remains always the possibility, even the probability, that they(Books) do more for the self-esteem of the author than for the fate of the world"
Hope fully i will get to read this book some time soon.
Subscribe to:
Posts (Atom)