Oracle Listener service is not starting

When you try to start the listener service and it gives you the below message and stops automatically.

The OracleOraDb10g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.


Solution which worked for me:

1) listener.ora file was missing under the NETWORK/ADMIN folder so I have added the file to this folder with the below content.

            LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST =  <IPADDRESS>)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

2) Made sure the sqlnet.ora file has the below entry.



    SQLNET.AUTHENTICATION_SERVICES= (NTS)

     NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


3) Made sure the tnsnames.ora has the proper entry as shown below


  orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IPADDRESS>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )



4) Restart the machine and manually start the oracle service. This time it will start properly and you can able to connect to it via any oracle developer tools.

Note: Even-though the listener is not running you will be able to connect to database via sqlplus available under oracle home application development utility.

Displaying all dates between two given dates

Displaying all dates between two dates. 


Table "tab_dates" contains the below data

START_DATE  |   END_DATE   

---------------------------------
01-JAN-12          07-JAN-12            

08-JAN-12         16-JAN-12            




Required Output:



DAY_DATE                  START_DATE           END_DATE                  
------------------------- ------------------------- ------------------------- 
01-JAN-12                 01-JAN-12                 07-JAN-12                 
02-JAN-12                 01-JAN-12                 07-JAN-12                 
03-JAN-12                 01-JAN-12                 07-JAN-12                 
04-JAN-12                 01-JAN-12                 07-JAN-12                 
05-JAN-12                 01-JAN-12                 07-JAN-12                 
06-JAN-12                 01-JAN-12                 07-JAN-12                 
07-JAN-12                 01-JAN-12                 07-JAN-12                 
08-JAN-12                 08-JAN-12                 16-JAN-12                 
09-JAN-12                 08-JAN-12                 16-JAN-12                 
10-JAN-12                 08-JAN-12                 16-JAN-12                 
11-JAN-12                 08-JAN-12                 16-JAN-12                 
12-JAN-12                 08-JAN-12                 16-JAN-12                 
13-JAN-12                 08-JAN-12                 16-JAN-12                 
14-JAN-12                 08-JAN-12                 16-JAN-12                 
15-JAN-12                 08-JAN-12                 16-JAN-12                 
16-JAN-12                 08-JAN-12                 16-JAN-12 


{code} -- Applies only to Oracle 11g database

with tab_dates as 
      (select to_date('01-JAN-12','dd-mon-yy') start_date,to_date('07-JAN-12','dd-mon-yy') end_date from dual
       union all
        select to_date('08-JAN-12','dd-mon-yy') start_date,to_date('14-JAN-12','dd-mon-yy') end_date  from dual)


    select start_date + i day_date, start_date, end_date from tab_dates,
     xmltable('for $i in 0 to xs:int(D)-1 return $i' passing 
     xmlelement(D,  (end_date-start_date)+1 ) columns i integer path '.') ;
{/code}

Explanation:


Using XMLTable() generate the list of numbers (ex: 0..6 for dates 6 days apart)   


(end_date-start_date)+1  = number of days in-between and the result is substituted in the place of "D" using the XMLElement() function.


The temporary XMLTable now contains a column named "i".