LIKE in SQL shell script

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

This gives me always 0. When I run from the GUI tool for count 13.
When I change the query to select count(*) from table I will get the correct result
please tell me how to change the SQL code so that it gives me correct result.

How is the column start defined (desc <table_name>)?

select count(*) from table order by start desc
It gives out the count.

Some problem I am facing when usng LIKE in SQL statement used in Shell script

Could you please post the table structure? You can obtain the details with the describe command:

desc <table_name>

It's important, because, if it's of date type, you don't need the like operator in this case:

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

Consider that you will need a FBI on trunc(start) in this case, otherwise probably a full table scan will be performed).

It should be better to use something like this to avoid the above mentioned problem:

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

Are you sure that the SELECT statement works ?
Apparently, start is reserved word and cannot be used as a column name in an Oracle table.

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>

The only way to make it work (that I can think of) is to put start within double quotes in the CREATE TABLE statement.
But then, you'll have to use double quotes in your SELECT statement as well.

tyler_durden

Well,
I suppose that the words table and start are used only in the example and are not the real table/column names.

In that case, I guess the OP incorrectly believes that the shell variable "result" holds the record count of the table. It does not.

The value of "result" will be 0 if -
(i) the "sqlplus" command was run successfully, which means that the ORACLE_HOME was set, and
(ii) all commands in the sqlplus session ran successfully

That the record count of the table in the sqlplus session is <n>, is a different matter altogether. The value <n> will not be assigned to "result".

The OP's SELECT statement works, probably because the column (start, or whatever) is a VARCHAR2 and also, the LIKE operator works with a literal string as well.

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>

Since the query works and sqlplus works in my environment, the shell script would run successfully as well, but the value of "result" will not be 2.

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

But if there's something wrong with the SELECT statement, then the error code will be non-zero:

$
$ # 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
$
$

tyler_durden

Agreed,
I stopped reading when I saw the sql statement

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

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

How can I use only one date in start rather than using 2 dates.

assuming start is defined as DATE or similar:

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