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.

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.

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

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

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.

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?

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;

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;