Shell Programming and Scripting

sqlplus -s <<__END__
${USER}/${PASS}@${DB}
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
SET ECHO ON
SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000
select count(*) from table where start LIKE '10-JUN-10';
EXIT
__END__
result=$?
echo $result

desc <table_name>

select count(*) from table where trunc(start) = to_date('10-JUN-10', 'dd-mon-yy');

select count(*) from table where start 
between to_date('09-JUN-10', 'dd-mon-yy')
and to_date('11-JUN-10', 'dd-mon-yy');

test@ORA11G>
test@ORA11G> create table t ( start varchar2(10) );
create table t ( start varchar2(10) )
                 *
ERROR at line 1:
ORA-00904: : invalid identifier

test@ORA11G>

test@ORA11G>
test@ORA11G> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(10)

test@ORA11G>
test@ORA11G>
test@ORA11G>
test@ORA11G> select * from t;
X
----------
10-JUN-10
11-JUN-10
10-JUN-10

3 rows selected.

test@ORA11G>
test@ORA11G> select count(*) from t where x like '10-JUN-10';

  COUNT(*)
----------
         2

1 row selected.

test@ORA11G>
test@ORA11G>

$
$ cat f3.sh
sqlplus -s test/test <<__END__
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
SET ECHO ON
SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000
select count(*) from t where x LIKE '10-JUN-10';
EXIT
__END__
result=$?
echo "Result = $result"
$
$
$ . f3.sh
  COUNT(*)
----------
         2
1 row selected.
Elapsed: 00:00:00.00
Result = 0
$
$

$
$ # Note that column xy does not exist in table t
$ cat f3.sh
sqlplus -s test/test <<__END__
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
SET ECHO ON
SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000
select count(*) from t where xy LIKE '10-JUN-10';
EXIT
__END__
result=$?
echo "Result = $result"
$
$ . f3.sh
select count(*) from t where xy LIKE '10-JUN-10'
                             *
ERROR at line 1:
ORA-00904: "XY": invalid identifier

Result = 136
$
$