TNS-01189: The listener could not authenticate the user

I was working on restoring database to a new VM lab environment. So the TNS, Listener , PFILe everything was copied over as usual as I'll be doing RMAN restore. So the restoration went fine, but interestingly listener didn't like my user to run it. Error was a bit confusing to me. Here is what I got.

[oracle@lab]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-JUN-2012 09:55:32

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=production.com)(PORT=1521)))

TNS-01189: The listener could not authenticate the user

I was so stupid that I was only looking into the error. Then I suddenly looked into the HOST name. It was picking up my source database as hostname; as I just copied the listener.ora file but didn't modified!

Solution is simple. Just change the listener HOST entry according to the server you are restoring the DB.

vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lab)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

Oracle Number to Word conversion

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||

       decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )

from dual

/

First one has a limitation of number of digits. Below one is good in terms of number of digits.

Set Serveroutput On 1000000

Declare

  V_Input NUMBER := &TESTED;

  Function Numbertowords(P_Number In Out Number) Return Varchar2 Is

    V_Words Varchar2(32767) := ' ';

    V_Temp Number;   

    Type Unitsmap Is Table Of Varchar2(250) Index By Binary_Integer;

    Type Tensmap Is Table Of Varchar2(250) Index By Binary_Integer;   

    V_Unitsmap Unitsmap ;

    V_Tensmap Tensmap   ;

  Begin

    V_Unitsmap(0) := 'Zero';

    V_Unitsmap(1) := 'One';

    V_Unitsmap(2) := 'Two';

    V_Unitsmap(3) := 'Three';

    V_Unitsmap(4) := 'Four';

    V_Unitsmap(5) := 'Five';

    V_Unitsmap(6) := 'Six';

    V_Unitsmap(7) := 'Seven';

    V_Unitsmap(8) := 'Eight';

    V_Unitsmap(9) := 'Nine';

    V_Unitsmap(10) := 'Ten';

    V_Unitsmap(11) := 'Eleven';

    V_Unitsmap(12) := 'Twelve';

    V_Unitsmap(13) := 'Thirteen';

    V_Unitsmap(14) := 'Fourteen';

    V_Unitsmap(15) := 'Fifteen';

    V_Unitsmap(16) := 'Sixteen';

    V_Unitsmap(17) := 'Seventeen';

    V_Unitsmap(18) := 'Eighteen';

    V_Unitsmap(19) := 'Nineteen';

    V_Tensmap(2)  := 'Twenty';

    V_Tensmap(3)  := 'Thirty';

    V_Tensmap(4)  := 'Forty';

    V_Tensmap(5)  := 'Fifty';

    V_Tensmap(6)  := 'Sixty';

    V_Tensmap(7)  := 'Seventy';

    V_Tensmap(8)  := 'Eighty';

    V_Tensmap(9)  := 'Ninety';

    If (P_Number = 0) Then

      Return 'Zero';

    End If;

    If (P_Number < 0) Then

      V_Temp := Abs(P_Number);     

      Return 'Minus ' || Numbertowords(V_Temp);

    End If;

    V_Temp := TRUNC(P_Number / 1000000);

    If ( V_Temp > 0) Then

      V_Words := V_WORDS || Numbertowords(V_Temp) || ' Million';

      P_Number := Mod(P_Number,1000000);

    End If;

    V_Temp := TRUNC(P_Number / 1000);

    If ( V_Temp > 0)Then

      V_Words := V_Words || Numbertowords(V_Temp) || ' Thousand';

      P_Number := Mod(P_Number,1000);

    End If;

    V_Temp := Trunc(P_Number / 100);

    If ( V_Temp > 0) Then

      V_Words := V_Words ||  Numbertowords(V_Temp) || ' Hundred ';

      P_Number := Mod(P_Number,100);

    End If;

    V_Temp := P_Number;

    If (V_Temp > 0) Then

      If (V_Words != ' ') Then

        V_Words := V_Words || 'And ';

       End If;

      If (V_Temp < 20) Then

        V_Words := V_Words || V_Unitsmap(V_Temp);

        Return V_Words;

      Else

       V_Temp := TRUNC(P_Number/ 10);

        V_Words := V_Words || V_Tensmap(V_Temp);

        If ((Mod(P_Number ,10)) > 0) Then

          V_Words :=V_Words|| '-' ||V_Unitsmap(Mod(P_Number,10));

        End If;

      End If;       

    End If;

    Return V_Words;

  End;

Begin   

  Dbms_Output.Put_Line(&TESTED||' ='||Numbertowords(V_Input));

End;

Calender with single sql statement

Found this cool script from a oracle blog.

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",

"Wed", "Thu", "Fri", "Sat"

FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, week,

MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",

MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",

MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",

MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",

MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",

MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",

MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"

FROM ( select dt, (case when dt >=

to_date(to_char(dt, 'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else wk end) week

from(

SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk

FROM all_objects

WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))

)

GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week)

ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);