Regular expression to split comma separated values

with test as
(select '5/10/2007,36352737,,"first_name, last_name Jr.",211001,7204,-75.00' str from dual)
Select regexp_substr(str,'".*"|[^,]+', 1, level)
from test
connect by regexp_substr(str,'".*"|[^,]+', 1, level) is not null

Oracle: Spool to Excel from SQLPlus

Spooling the output from SQLPlus to log file is often in unreadable format.

To Spool SQLPLus output to Excel (.xls) file, the trick is to turn On "MARKUP HTML"

SQL>set markup html on
SQL>set feedback off

SQL>spool c:\test.xls

SQL>select * from user_objects where rownum <= 20;
SQL>spool off

SQL>set markup html off

GPS

Forgot SYS password in Oracle

If you forget or lost both SYS and SYSTEM password, you can reset them without logging in to the database.

Here is a cool utility to reset SYS and SYSTEM password, if you ever loose or forget the passwords for these account.

This is tested in Windows box only ( orapwd should work in Unix too)
---------------------------------------------------------------------

1) Go to services ( start -> Run->Services.msc)
2) Stop all Oracle services.
3) Find PWDxxxx.ora file. It is usually located in the Oracle\product\10.x\db_x\database. xxxx stands for your Oracle SID. For example, if you database is called ORCL, then the file will be PWDorcl.ora
4) Back up the existing file (just rename the file)
5) Generate new PWDxxxx.ora file using orapwd utility.
6) Go to command prompt, type
ORAPWD file=path_to_the_PWDxxx.ora_file password=zzzz
zzzz will the new password for both SYS and SYSTEM account
7) Now you will see a new PWDxxx.ora file generated.
8) Restart your Oracle service
9) You should be able to connect to the database with the new password.