How to Pass Variable from Shell Script to SQLplus

how to pass variable from shell script to sqlplus

You appear to have a heredoc containing a single SQL*Plus command, though it doesn't look right as noted in the comments. You can either pass a value in the heredoc:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql BUILDING
exit;
EOF

or if BUILDING is $2 in your script:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql $2
exit;
EOF

If your file.sql had an exit at the end then it would be even simpler as you wouldn't need the heredoc:

sqlplus -S user/pass@localhost @/opt/D2RQ/file.sql $2

In your SQL you can then refer to the position parameters using substitution variables:

...
}',SEM_Models('&1'),NULL,
...

The &1 will be replaced with the first value passed to the SQL script, BUILDING; because that is a string it still needs to be enclosed in quotes. You might want to set verify off to stop if showing you the substitutions in the output.

---

You can pass multiple values, and refer to them sequentially just as you would positional parameters in a shell script - the first passed parameter is &1, the second is &2, etc. You can use substitution variables anywhere in the SQL script, so they can be used as column aliases with no problem - you just have to be careful adding an extra parameter that you either add it to the end of the list (which makes the numbering out of order in the script, potentially) or adjust everything to match:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql total_count BUILDING
exit;
EOF

or:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql total_count $2
exit;
EOF

If total_count is being passed to your shell script then just use its positional parameter, $4 or whatever. And your SQL would then be:

SELECT COUNT(*) as &1
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('&2'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

If you pass a lot of values you may find it clearer to use the positional parameters to define named parameters, so any ordering issues are all dealt with at the start of the script, where they are easier to maintain:

define MY_ALIAS = &1
define MY_MODEL = &2

SELECT COUNT(*) as &MY_ALIAS
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('&MY_MODEL'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

---

From your separate question, maybe you just wanted:

SELECT COUNT(*) as &1
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('&1'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

... so the alias will be the same value you're querying on (the value in $2, or BUILDING in the original part of the answer). You can refer to a substitution variable as many times as you want.

That might not be easy to use if you're running it multiple times, as it will appear as a header above the count value in each bit of output. Maybe this would be more parsable later:

select '&1' as QUERIED_VALUE, COUNT(*) as TOTAL_COUNT

If you set pages 0 and set heading off, your repeated calls might appear in a neat list. You might also need to set tab off and possibly use rpad('&1', 20) or similar to make that column always the same width. Or get the results as CSV with:

select '&1' ||','|| COUNT(*)

Depends what you're using the results for...

Passing variables from bash script into Oracle SQL*Plus

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

How to Pass a sqlplus parameter from shell script to sql file

After searching in the internet for almost a week, could arrive at a working code. Below is the working code.

cat extract.sh

#!/bin/ksh
   var_Date=`sqlplus -s $DB_USER/$DB_PASS@$DB_HOST:$DB_PORT/$DB_SID << EOF
   SET HEADING OFF
   SELECT ''''||TRIM(MAX(SAMPLE_DATE)-1)||'''' FROM SAMPLE_CASES WHERE KEY IN ('ab','bc');
   EXIT;
   EOF`
   export var_Date
   echo $var_Date

   sqlplus -s $DB_USER/$DB_PASS@$DB_HOST:$DB_PORT/$DB_SID @data_extract.sql $var_Date

cat extract.sql

set echo off
set trimspool on
set pagesize 0
set colsep ~

spool extract.csv
SELECT CASE_ID FROM SAMPLE_CASE1 WHERE to_char(TIME_START,'DD-MON-YYYY') >='&1'
spool off

Firstly, we to need to pass the argument $var_Date to sqlplus within single quotes which is done within the variable declaration itself by concatenating ''''. We also need to use SET HEADING OFF to make the variable var_Date hold only the date value and not the header value.

Secondly, there was a ora error: "ORA-01858: a non-numeric character was found where a numeric was expected". To mitigate this, I had type-casted the field time_start to to_char post which the filter is applied and stored in the csv file.

Hope this helps others who had trouble like me.

How to pass parameter of oracle sql script file to sqlplus from shell prompt?

Example 1

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
host=10.x.xx.xxx
port=1521
service=esmd
ezconnect=$host:$port/$service

echo username:  $username
echo password:  $password
echo host:      $host
echo port:      $port
echo service:   $servive
echo ezconnect  $ezconnect

echo -e 'show user \n  select 1 from dual; \n select sysdate from dual; \nexit;' |  sqlplus -s  $username/$password@$ezconnect

Output:

oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
host: 10.x.xx.xxx
port: 1521
service:
ezconnect 10.x.xx.xxx:1521/esmd
USER is "Scott"

         1
----------
         1

SYSDATE
---------
19-FEB-20

oracle@esmd:~>

Example 2

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"

echo username:  $username
echo password:  $password

testoutput=$(sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual;
@ulcase1.sql
exit;
EOF
)

echo $testoutput

Output:

oracle@esmd:~> ./test_Upper_case.sh
username: "Scott"
password: "@T!ger"
USER is "Scott" 19-02-2020 15:08 Test passed
oracle@esmd:~>

Example 3 test.sh

#!/bin/bash

sudo  -H -E  -u oracle  -s "/opt/oracle/test_Upper_case.sh"

Output

esmd:~ # ./test.sh
username: "Scott"
password: "@T!ger"
USER is "Scott" 19-02-2020 15:50 Test passed

/opt/oracle/test_Upper_case.sh

!/bin/sh

username=\"Scott\"
password=\"@T!ger\"

echo username:  $username
echo password:  $password

testoutput=$($ORACLE_HOME/bin/sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual;
@/opt/oracle/ulcase1.sql
exit;
EOF
)

echo $testoutput

---

Related Topics