Friday, June 22, 2012

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))
    )
  )

Tuesday, June 12, 2012

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);