I've tried a few different methods (from here and elsewhere) with no success and was hoping if one of the more experienced individuals of yourselves would be able to help.
This is my shell script
read -p 'What is the ID? ' asnid
if [ -z "$asnid" ]
then
echo "ID is Empty"
exit
else
echo "ID, continuing to Date"
fi
read -p 'What is the Arrival Date? (Answer in DD-MM-YY format please!) ' date
if [[ $date =~ ^[0-9]{2}-[0-9]{2}-[0-9]{2}$ ]]
then echo "Date $date is in valid format (DD-MM-YY)"
cd /go/to/path
sqlplus data/base@srvc @./sql.sql $asnid $date
else echo "Date $date is in an invalid format (not DD-MM-YY)"
exit
fi
and then my .sql statement
define asnid = &asnid
define date = &date
update table
set date = '&date'
where asnid = '&asnid';
COMMIT;
exit;
However, this then asks me for the variables again.. such as;
Enter value for asnid:
I know it's something really simple, but what!!
Oracle 11g, Bash is RHEL 6
You are passing positional parameters, so refer to them as &1
and &2
. If you want more friendly names in the query body then just change the definition of those:
define asnid = &1
define date = "to_date('&2', 'DD-MM-RR')"
I've included (a) enclosing the second argument in quotes as it needs to be treated as a string, and (b) converting that string to a an actual date. Because it has a comma, the whole expression has to also be in double-quotes. So then you don't need the quotes in the SQL statement:
update table
set date = &date
where asnid = &asnid;
The whole to_date(...)
expression will then be substituted into your query, with the passed-in value embedded; you'll see that happen if you set verify on
, but you probably want it off except for testing. With it on, the script shows:
What is the ID? 42
ID, continuing to Date
What is the Arrival Date? (Answer in DD-MM-YY format please!) 01-09-21 Date 01-09-21 is in valid format (DD-MM-YY)
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 25 18:12:50 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Aug 25 2021 18:11:54 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options
old 2: set some_date = &date
new 2: set some_date = to_date('01-09-21', 'DD-MM-RR')
old 3: where asnid = &asnid
new 3: where asnid = 42
1 row updated.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options
(You might also want to add the -s
and maybe -l
flags to your sqlplus
call, to suppress the banners and make it exit immediately if the credentials are wrong.)
Alternatively you can keep the define
simple, and apply the to_date()
in the statement:
define asnid = &1
define date = &2
update some_table
set some_date = to_date('&date', 'DD-MM-RR') where asnid = &asnid;
and the output then appears as:
old 2: set some_date = to_date('&date', 'DD-MM-RR')
new 2: set some_date = to_date('01-09-21', 'DD-MM-RR')
old 3: where asnid = &asnid
new 3: where asnid = 42
but if you're doing that you might as well skip the define
and just refer to &1
and &2
directly in the statement.
In both cases I've left &1
alone on the assumption that will be a number. If that is actually a string then enclose that in quotes too, either in the statement or the define.
If you have the option you should probably prompt for dates with 4-digit years; possibly in ISO format - and have a matching format in the to_date()
.
You could also skip (or minimise) the shell script by having SQL*Plus prompt for the values via accept
:
accept asnid number format 99999999 prompt "What is the ID? "
accept date date format 'DD-MM-RR' prompt "What is the Arrival Date? (Answer in DD-MM-YY format please!) "
update some_table
set some_date = to_date('&date', 'DD-MM-RR')
where asnid = &asnid;
Then call it without arguments:
sqlplus data/base@srvc @/go/to/path/sql.sql
and you'll see something like this, where I've used a few non-values and invalid values just to demonstrate:
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 25 18:24:28 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Aug 25 2021 18:24:02 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options
What is the ID?
SP2-0598: "" does not match input format "99999999"
What is the ID? 42
What is the Arrival Date? (Answer in DD-MM-YY format please!)
SP2-0685: The date "" is invalid or format mismatched "DD-MM-RR"
What is the Arrival Date? (Answer in DD-MM-YY format please!) 31-09-21
SP2-0685: The date "31-09-21" is invalid or format mismatched "DD-MM-RR"
What is the Arrival Date? (Answer in DD-MM-YY format please!) 30-09-21
old 2: set some_date = to_date('&date', 'DD-MM-RR')
new 2: set some_date = to_date('30-09-21', 'DD-MM-RR')
old 3: where asnid = &asnid
new 3: where asnid = 42
1 row updated.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options
That will allow date strings that Oracle is willing to convert using that format mask, so it would allow you to enter '01-Sep-21'. If you don't want that then make the format mask 'FXDD-MM-RR'
. (But, again, think about using 4-digit years and an unambiguous format...)