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.