oracle-databasesqlplusvariable-substitution

Passing variables from bash script into Oracle SQL*Plus


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


Solution

  • 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...)