The SQL itself is bad. Double-quotes are used for identifiers, single-quotes are used for data. Sqlplus should have barfed with a very clear error message:

ERROR at line 1:
ORA-00904: "yyyymmdd": invalid identifier
[download]

So, instead of "yyyymmdd", use 'yyyymmdd'. Furthermore, '88888888' is confusing, as without significant change, the highest date in year 8888 would be 12/31.

But, that's not the best way to do it anyway. In general, you should avoid placing a function on a column in a where clause, both because it negates the use of (a non-function based) index, and because the function must be executed for every value. That a lot of wasted CPU time. Instead, place the functions on the values, so that the functions run only once:

Fwiw, unless you specifically want to exclude all dates BCE, the between itself is redundant, and can be expressed:

sql query in the code was just for test purpose. Below is the sql I expect to run(using the STARTDATE & ENDDATE values user provides) select date_time_out,order_id,error_message,customer_id from ECS_SCHEMA.cob_mcom_log where to_char(end_date_time,'yyyymmdd') between $STARTDATE and $ENDDATE and error_code<>0;

Put this sql in a file called query.sql and put it in the cgi-bin.

 
set linesize 100
set verify off
select date_time_out,order_id,error_message,customer_id 
from ECS_SCHEMA.cob_mcom_log
where end_date_time 
   between to_date(&1,'YYYYMMDD') 
   and     to_date(&2,'YYYYMMDD') 
and error_code <> 0;
exit;

together with this script

#!/usr/bin/perl
use strict;
use warnings;
use CGI;
use Time::Piece;

my $sqlfile  = '/var/www/cgi-bin/query.sql';

my $q = new CGI;
my $today = Time::Piece->new->ymd('');
my $startdate = $q->param('startdate') || $today;
my $enddate   = $q->param('enddate') || $today;

my $msg;
if ( ($startdate =~ /^20\d{6}$/) 
  && ($enddate   =~ /^20\d{6}$/) ){
  my $connstr = 'USER/PASWORD@DB';
  $msg = qx"sqlplus -s $connstr \@$sqlfile $startdate $enddate";
} else {
  $msg = "ERROR : Dates must be 20YYMMDD";
}

print $q->header,$q->start_html;
print qq!
<h3>Dates</h3>
<form>
Start date : <input type="text" maxlength="8" name="startdate" value="
+$startdate"/> yyyymmdd <br/>
End date   : <input type="text" maxlength="8" name="enddate"  value="$
+enddate"/> yyyymmdd</br>
<input type="submit"/>
</form>
</hr>
<h3>Result for $startdate to $enddate</h3>
<pre> $msg </pre>!;
print $q->end_html;
[download]

Open script with browser, enter dates and press submit

update yy-mm-dd corrected to yyyymmdd

poj

That uses dynamic sql, which is generally considered bad. Please do not use dynamic sql. Instead, convert $STARTDATE and $ENDDATE to placeholders. Then execute the query by passing the values. If you want to use sqlplus, "var" will set a local variable and :variable can be used as the placeholder.

It still stands that to_char() on the column is not optional and should instead be moved to enclose the variables. That would also, likely, produce clearer error messages on any passed data.

How is the sqlplus part failing?

Are you sure that blindly mixing shell scripts and Perl code is a good approach?

Maybe you want to read perlop and perlsyn on how to construct strings in Perl.

Personally, I suggest simply using Querylet::CGI to run SQL queries from a CGI environment.

Hi, Not all the perl modules are installed on my system. And I cant get it installed too, it is in my office. So, this Querylet::CGI would not help. Any other solution? Regards

You can manually install Querylet::CGI, see Yes, even you can use CPAN.

I simply want to use "$STARTDATE" and "$ENDDATE" I parsed in my script (in above) in my sql query to retrieve the data from DB and output it on the web page, when the user clicks on "submit" button. Please help, this is very urgent!

Have you read the links to the Perl documentation I provided?

These links discuss how and when variables interpolate.

Maybe eliminate CGI from the problem space and first get your Perl script running outside of the webserver.

What errors do you get?

connstr='user/password@DB';
$account='sqlplus -s $connstr <<END
...
[download]

• connstr should be $connstr (Missing dollar sign), and preceding "my"
• Setting $account uses SINGLE QUOTES, which will not interpolate $connstr.
• Other variables inside the "heredoc" will not be interpolated either.
• The "heredoc" does not appear to be correctly formatted