Oracle SQL fucntions Cheet Sheet

 Oracle SQL Functions Cheet Sheet

Broken VIM

I seem to be getting the below error when I try to use vim. vi test 

dyld[55718]: Library not loaded: /usr/local/opt/ruby/lib/libruby.3.0.dylib Referenced from: /usr/local/Cellar/macvim/8.2-171_1/MacVim.app/Contents/MacOS/Vim Reason: tried: '/usr/local/opt/ruby/lib/libruby.3.0.dylib' (no such file), '/usr/local/lib/libruby.3.0.dylib' (no such file), '/usr/lib/libruby.3.0.dylib' (no such file), '/usr/local/Cellar/ruby/3.1.2/lib/libruby.3.0.dylib' (no such file), '/usr/local/lib/libruby.3.0.dylib' (no such file), '/usr/lib/libruby.3.0.dylib' (no such file) On checking the location /usr/local/opt/ruby/lib/ I found that the file libruby.3.1.dylib is avialable instead of libruby.3.0.dylib. 

So I created a sym link using the below command. 

ln -s libruby.3.1.dylib libruby.3.0.dylib 

 This fixed my broken vim editor and I was up and running. Below links helped me fix this issue. Link1

Nice Story...

In Mahabharat, Karna asks Lord Krishna - "My mother left me the moment I was born. Is it my fault I was born an illegitimate child? I did not get the education from Dhronacharya because I was considered not a Kshatriya. Parsuraam taught me but then gave me the curse to forget everything when he came to know I was Son of Kunthi belong to Kshatriya. A cow was accidentally hit by my arrow & its owner cursed me for no fault of mine. I was disgraced in Draupadi's Swayamvar. Even Kunthi finally told me the truth only to save her other sons. Whatever I received was through Duryodhana's charity. So how am I wrong in taking his side ???" **Lord Krishna replies, "Karna, I was born in a jail. Death was waiting for me even before my birth. The night I was born I was separated from my birth parents. From childhood, you grew up hearing the noise of swords, chariots, horses, bow, and arrows. I got only cow herd's shed, dung, and multiple attempts on my life even before I could walk! No Army, No Education. I could hear people saying I am the reason for all their problems. When all of you were being appreciated for your valour by your teachers I had not even received any education. I joined Gurukula of Rishi Sandipani only at the age of 16! You are married to a girl of your choice. I didn't get the girl I loved & rather ended up marrying those who wanted me or the ones I rescued from demons. I had to move my whole community from the banks of Yamuna to far off Sea shore to save them from Jarasandh. I was called a coward for running away!! If Duryodhana wins the war you will get a lot of credit. What do I get if Dharmaraja wins the war? Only the blame for the war and all related problems... Remember one thing, Karna. Everybody has Challenges in life to face. LIFE IS NOT FAIR & EASY ON ANYBODY!!! But what is Right (Dharma) is known to your Mind (conscience). No matter how much unfairness we got, how many times we were Disgraced, how many times we Fall, what is important is how you REACTED at that time. Life's unfairness does not give you license to walk the wrong path... Always remember, Life may be tough at few points, but DESTINY is not created by the SHOES we wear but by the STEPS we take...

Collections Initialisation in Oracle

This is one new thing I learned today about initialising a collection variable. If the varaible is initialised without giving the record count, then we need to extend the collection variable to add data to it. But if the variable is initialised with null then we need not extend the variable. Below is the sample code to explain that.

DECLARE

       TYPE PSOUG_TAB IS TABLE OF NUMBER;

       PTAB PSOUG_TAB;

BEGIN

        PTAB := PSOUG_TAB();

        --PTAB.EXTEND;

        PTAB(1) := 100;

        DBMS_OUTPUT.PUT_LINE('VALUE AT INDEX(1) IS '||PTAB(1));

        PTAB.EXTEND(5,1);

        DBMS_OUTPUT.PUT_LINE('VALUE AT INDEX(4) IS '||PTAB(4));

END;

The above code would throws an error, if the PTAB variable is not extended before addiing records to it. But the below code would not throw an error.

DECLARE

       TYPE PSOUG_TAB IS TABLE OF NUMBER;

       PTAB PSOUG_TAB;

BEGIN

        PTAB := PSOUG_TAB(null);

        --PTAB.EXTEND;

        PTAB(1) := 100;

        DBMS_OUTPUT.PUT_LINE('VALUE AT INDEX(1) IS '||PTAB(1));

        PTAB.EXTEND(5,1);

        DBMS_OUTPUT.PUT_LINE('VALUE AT INDEX(4) IS '||PTAB(4));

END;

The above code has been taken from http://psoug.org/definition/extend.htm .

Anonymous Blocks in PL\SQL

Even Anonymous blocks in PL\SQL are compiled first and then executed. This is something new that I learned today.  The below code from yesterday's PL\SQL challenge made me realize this.

BEGIN
   EXECUTE IMMEDIATE
      'CREATE TABLE plch_stuff (stuff_id INTEGER)';

   INSERT INTO plch_stuff
        VALUES (1234);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;
/

The above code would fail not at run time but during compile time. All this while I was assuming that anonymous blocks run without being compilation.

ora_is_alter_column

System triggers cannot be based on tables or views. So this is what I relearned today after I executed the pl\sql challenge question. after alter trigger is a system trigger and it can not be created on a table.

ora_is_alter_column is a function which can be used in trigger to compare column being altered to a column name. 

Continue in 11g

In yesterdays plsqlchallenge question I learned that in 11g they have included continue statement in plsql which doesn't exist in 10g. Here is the link to the question.
.
And remember, SQL does not have any Boolean data type yet in Oracle. Only PL\SQL has it. Refer to this question.

lepidopterists

lepidopterology : the branch of zoology dealing with butterflies and moths.

sentients : having the power of perception by the senses; conscious.

Wiccan

wiccan: One who practices witch craft.

I came across this word in the euology written by Mona Simpson at her brother Steve Jobs funeral.

Wiccan

wiccan: One who practices witch craft.

I came across this word in the article written by Mona Simpson on her brother Steve Jobs.

metonymy

metonymy : change of name

Farce

farce
: a light, humorous play in which the plot depends upon a skillfully exploited situation rather than upon the development of character.
: humor of the type displayed in such works.
: foolish show; mockery; a ridiculous sham.

Tim uses this word while talking about Steve Jobs death here.

A Promise for Steve

Steve Jobs passed away yesterday! That was the first thing my husband told me when he came home. I was surprised I was unaware of it till then(it was almost 5 hrs by then). I don't know why but it felt like I lost a loved one. Like someone in the family has passed away. I have'nt known much about him and still dont know much about him. But the kind of products he made had a lot of impact on me.

I read this post from Seth Godin to pay an Euology of action to Steve. I would like to make this as my Euology to Steve. By the end of this year I would like to publish one app on iTunes and would like to become a life long iOS user, learner and developer.

Rest in Peace Steve. You are an inspiration to so many people and your legacy lives on forever.

dogfooding :

When a developer uses their own code for their own daily needs. Being a user as well as a developer creates the user empathy that is the hallmark of good software.
The term seems to have originated at Microsoft. (http://www.joelonsoftware.com/items/2004/04/16.html)

I came across this word while reading Joel's blog on their new product Trello.

Pride and Prejudice

Finally I finished my first novel on Kindle. Yes its Pride and Prejudice, one of the preloaded books that come with the Kindle. My husband says that I was reading high school books. I never got to read it fully in my high school so I very much enjoyed reading it.
I guess the story is no different from any romantic novel but the way Jane Austin fill the pages with her artistic style of language and the ways she attracts her readers is amazing. I could not put down the book once I started reading it. It took me a full week to complete it though. I think this is one book every person should read it before they finish their high school. I feel too good reading this one even if it’s a decade or two later. With this I can check one book down in my list of books to read before I die.

Books to read before I die

War and Peace - Leo Tolstoy -- Reading

The Kite Runner - Khaled Hosseini

Pride and Prejudice - Jane Austen

Seven Habits of Highly Effective People

The Lord of the Rings - JRR Tolkien

Jane Eyre - Charlotte Bronte

Harry Potter series - JK Rowling

To Kill a Mockingbird - Harper Lee

Wuthering Heights - Emily Bronte

Nineteen Eighty Four - George Orwell

His Dark Materials - Philip Pullman

Great Expectations - Charles Dickens

Little Women - Louisa M Alcott

Tess of the D’Urbervilles - Thomas Hardy

Catch 22 - Joseph Heller

Complete Works of Shakespeare

Rebecca - Daphne Du Maurier

The Hobbit - JRR Tolkien

Birdsong - Sebastian Faulk

Catcher in the Rye - JD Salinger

The Time Traveler’s Wife - Audrey Niffenegger

Middlemarch - George Eliot

Gone With The Wind - Margaret Mitchell

The Great Gatsby - F Scott Fitzgerald

The Hitch Hiker’s Guide to the Galaxy - Douglas Adams

Crime and Punishment - Fyodor Dostoyevsky

Grapes of Wrath - John Steinbeck

Alice in Wonderland - Lewis Carroll

The Wind in the Willows - Kenneth Grahame

Anna Karenina - Leo Tolstoy

David Copperfield - Charles Dickens

Chronicles of Narnia - CS Lewis

Emma -Jane Austen

Persuasion - Jane Austen

The Lion, The Witch and the Wardrobe - CS Lewis

Captain Corelli’s Mandolin - Louis De Bernieres

Memoirs of a Geisha - Arthur Golden

Winnie the Pooh - A.A. Milne

Animal Farm - George Orwell

The Da Vinci Code - Dan Brown

One Hundred Years of Solitude - Gabriel Garcia Marquez

A Prayer for Owen Meaney - John Irving

The Woman in White - Wilkie Collins

Anne of Green Gables - LM Montgomery

Far From The Madding Crowd - Thomas Hardy

The Handmaid's Tale - Margaret Atwood

Lord of the Flies - William Golding

Atonement - Ian McEwan

Life of Pi - Yann Martel

Dune - Frank Herbert

Cold Comfort Farm - Stella Gibbons

Sense and Sensibility - Jane Austen

A Suitable Boy - Vikram Seth

The Shadow of the Wind - Carlos Ruiz Zafon

A Tale Of Two Cities - Charles Dickens

Brave New World - Aldous Huxley

The Curious Incident of the Dog in the Night-time - Mark Haddon

Love In The Time Of Cholera - Gabriel Garcia Marquez

Of Mice and Men - John Steinbeck

Lolita - Vladimir Nabokov

The Secret History - Donna Tartt

The Lovely Bones - Alice Sebold

Count of Monte Cristo - Alexandre Dumas

On The Road - Jack Kerouac

Jude the Obscure - Thomas Hardy

Bridget Jones’s Diary - Helen Fielding

Midnight’s Children - Salman Rushdie

Moby Dick - Herman Melville

Oliver Twist - Charles Dickens

Dracula - Bram Stoker

The Secret Garden - Frances Hodgson Burnett

Notes From A Small Island - Bill Bryson

Ulysses - James Joyce

The Inferno - Dante

Swallows and Amazons - Arthur Ransome

Germinal - Emile Zola

Vanity Fair - William Makepeace Thackeray

Possession - AS Byatt

A Christmas Carol - Charles Dickens

Cloud Atlas - David Mitchell

The Color Purple - Alice Walker

The Remains of the Day - Kazuo Ishiguro

Madame Bovary - Gustave Flaubert

A Fine Balance - Rohinton Mistry

Charlotte’s Web - E.B. White

The Five People You Meet In Heaven - Mitch Albom

Adventures of Sherlock Holmes - Sir Arthur Conan Doyle

The Faraway Tree Collection - Enid Blyton

Heart of Darkness - Joseph Conrad

The Little Prince - Antoine De Saint-Exupery

The Wasp Factory - Iain Banks

Watership Down - Richard Adams

A Confederacy of Dunces - John Kennedy Toole

A Town Like Alice - Nevil Shute

The Three Musketeers - Alexandre Dumas

Hamlet - William Shakespeare

Charlie and the Chocolate Factory - Roald Dahl


On being Certain.
http://www.rburton.com/_i_on_being_certain_i___believing_you_are_right_even_when_you_re_not_63166.htm

Command to delete files from a UNIX box.

find . -mtime +7 -exec rm{} ';'

The above command deletes files created before a week from the present day from the current directory.
I can put this command in a shell script and run it in chrontab to remove the log files on a daily basis.

Is Corruption India's biggest problem?

This was a question in one of the NDTV's forums and I typed in my view on it. Somehow the did not accept my post. So I am posting it here.

----------------------------------------------------------------------------------

No I don't think corruption is the No1 problem for India. It is just a way to blame someone else for our bad state. If we want to talk about real problems we need to address issues like
1) Primary Education
2) Drinking Water
3) Sanitation
4) Nutrition.....
I can go on. These are the real problems of India. If you really think of it how many times did you bribe a person in your life to get something done? Of my 23 years in India the only time that I had bribed a person was to a ticket collector in trains to get a berth. I don’t know if that were earth shattering. I know it would have reduced a miniscule amount for the Railways but I don’t think it is a bigger problem than any one of the ones that I stated above. Corruption is more of a intangible problem and it is going to exist in one form or the other. I think we should focus more on tangible problems than say that "India is not developing fast because of our corrupt politicians". Even the politician is one amongst us.

ORA-00214: Control file Version Inconsistent on Startup or Shutdown

Early in the morning to do my daily plsqlchallenge question I tried logging into my local database and bam I hit the ORA-00214 error. Well actually I hit this error when I try to start up my database. I guess the error was because I shut my machine down forcefully by plugging off my machine. I guess this was what caused the inconsistencies in the control files. The possible causes for this issue in the documentation were the following:
1) You have restored the control file from backup, but forgot to copy it onto all of the mirrored copies of the control file as listed in the "CONTROL_FILES" parameter in the initialization parameter
2) You have moved one or more copies of the control file to a different location while the database was up and running.
3) You accidentally overwrote one of the copies of the control file with an old copy.
4) The database or the system crashed while the mirrored copies of the control file were being updated, causing them to be out of sync.
5) You are restoring a database backup that was improperly taken with the database up and running ("fuzzy" backup).

In my case it was the error 4 which caused the error.


The solution was easy compared to what it could be. Below are the steps that I took to start up the database.
1) Shutdown the database
a. Shutdown immediate
2) Start up the database with nomount
a. startup nomount
3) Alter the control files to one of the control fiels.
a. ALTER SYSTEM SET CONTROL_FILES=file_name
4) Open the database.
a. Alter database open

With these commands my database was up and running and I am done for the day’s learning. I should definitely link this site for helping me out in fixing my issue.

Roots Shoots Buckets and Boots

This is the book that I finished reading this week. It was a cute little book on inspiring kids and adults to do gardening. I love gardens and my idea of a house was not complete without a garden. I always dreamt of this little cottage with a big garden as my ideal home. And my husband always dreamt of a big house I suppose. So we ended up buying a big house with a bit lot with nicely landscaped yard.

And when I bought my house I assumed that I would fill my garden with all the things that a garden can and should have. But I have to admit that I failed miserably at that. I didn’t know where to start in my huge lot. I planted some tomatoes and egg plants last spring but before I got the harvest it became too cold and all I got was a huge numbers of small green tomatoes. I enjoyed what ever little I got but I think that was shame for the mere size of my garden(say lot). So this year I plan to make a better effort and in those I efforts came across this beautiful book “Roots, Shoots, Buckets and Boots”. It has beautiful ideas on how to plant with little kids and explore the nature with your little ones. I am sure I am going to refer to this book in the future as I am really a kid starting on my gardening journey.

It has nice pictures of the ideas of pizza patches and moon light blooms and little mazes. I never new we could do so much in our gardens. It has a few instructions on how to have a compost bin and prepare the soil before panting. It has nice detailed instructions on how to plant the various plants without sounding too boring or rigorous. The list of the 20 plants to have in a garden is a must in every Californian garden I suppose. What I like more about this book is that the author is from California and so the plants she mentioned in her book are mostly suitable for our weather. I am glad I stumbled on this book and I hope I implement at least one tenth of what I read.

My first to do after reading this is to get a compost bin and list of all the 20 must have plants in my garden. Its February now and I am sure I am going to have more fun in my garden. This books did inspire me.