How to pass variable to a query?

Shell Programming and Scripting
1

Hi All,

How to pass date variable to a query?

I have tried the below one , but it's not working.

ost.ksh

#!/bin/ksh

v_date=$1
var=$(sqlplus -s $ORACON <<ENDOFSQL
SELECT TO_DATE('$v_date','DD-MON-YYYY'),-1) FROM DUAL;
exit;
ENDOFSQL
)
#End

I have executed as below.

ksh ost.ksh 20150612

Please help me.

Thanks.

2

The single quotes will make that a literal `$v_date'
Change it for double quotes to preserve the special meaning of the `$'

3

Hi,

I have changed single quote to double quote.

I got the below error.

var SELECT get_dat_function(TO_DATE("20150612","DD-MON-YYYY"),-1) FROM DUAL;
                                                             *
ERROR at line 4:
ORA-06550: line 4, column 62:
PL/SQL: ORA-00904: "DD-MON-YYYY": invalid identifier
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

#!/bin/ksh

v_date=$1
var=$(sqlplus -s $ORACON <<ENDOFSQL
SELECT get_dat_function(TO_DATE("$v_date",'DD-MON-YYYY'),-1)) FROM DUAL;
exit;
ENDOFSQL
)
#End

Thanks.

4

The single qutotes are not the problem.
You pass the date in format YYYYMMDD but the statement expects it to be in format DD-MON-YYYY. And there is a surplus brace.

v_date=$1
var=$(sqlplus -s $ORACON <<ENDOFSQL
SELECT get_dat_function(TO_DATE('$v_date','YYYYMMDD'),-1) FROM DUAL;
exit;
ENDOFSQL
)
#End
5

Hi,

I have tried this still same error.

Thanks

6

Please post the exact errormessage. When I execute this after setting the ORACON variable it works for me:

$ sqlplus $ORACON

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 18 13:28:27 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create or replace function get_dat_function(v_dat date, v_offset number) return date
  2  is
  3  begin
  4     return v_dat-v_offset;
  5  end get_dat_function;
  6  /

Function created.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
$ 
$ cat x.sh
v_date=$1
var=$(sqlplus -s $ORACON <<ENDOFSQL
SELECT get_dat_function(TO_DATE('$v_date','YYYYMMDD'),-1) FROM DUAL;
exit;
ENDOFSQL
)
#End
echo "$var"
$ 
$ ./x.sh 20150612

GET_DAT_FUNCTION(T
------------------
13-JUN-15
$
7

Hi,

The error is

ORA-00904: "YYYYMMDD": invalid identifier

Thanks.

8

Double quotes in sqlplus have a different meaning than in the shell. There they say that the identifier they surround has to be treated case sensitive. Put the date format specifier in single quotes like in my example.