Direct Oracle Connection without Tnsnames.ora

sqlplus username/xxxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=machine_name)(Port=1521))(CONNECT_DATA=(SID=service_name)))

or

sqlplus username/xxxxx@machine_name:port/sid

Convert a stats input date to Edward date_sid

Old way of doing it:
select to_char(to_date('09/01/2007', 'MM/DD/YYYY'), 'YYYYMMDD') from dual;

New way with regexp
select regexp_replace('09/01/2007', '(\d{2})/(\d{2})/(\d{4})', '\3\2\1') from dual

reduced function call to 1

Oracle 11g password and Toad (connection issue)

One of the new features of 11g is case-sensitive password.

It causes problem for Toad because Toad makes the password captial silently.

If you are using lower case password, you will get
ORA-01017: invalid username/password; logon denied.

How to disable this new featuer:
alter system set sec_case_sensitive_logon=FALSE;

A new column PASSWORD_VERSIONS has been added in dba_users may be related to this.
Not quite sure on this.
The values (10G 11G) remine same before/after changing the system parameter sec_case_sensitive_logon.

Unix Shell Integer Comparison

In Unix shell,

Use -ne -eq -lt -gt to compare numbers.

== and != are used for string comparison.

Perl does the things on the opposite.

Initcap

An interesting function:

SQL> select initcap('abc XYZ') from dual;

INITCAP('ABCXY
--------------
Abc Xyz

Scalar query and parallel query

If you have a scalar query in the select list, the whole query will not work in parallel mode even if you give a parallel hint

(Unix/Linux Shell) How many lines in a file?

Linux (FC6)

wc -l tx.txt | cut -f 1 -d " "

Solaris

wc -l tx.txt | sed -e 's/^ *//' | cut -f 1 -d " "
OR
wc -l txt.txt | awk '{print $1}'