How to pass variable from shell script to sqlplus

Question

I have a shell script that calls file.sql

I am looking for a way to pass some parameters to file.sql file.

If I do not pass a variable with some value to the sql script, I will have to create several .sql files with a SELECT statement, and all that would change would be a few words.

My shell script calls file.sql:

sqlplus -S user/ pass@localhost echo " Processing triples" ./load_triples.sh BUILDING/Mapping_File BUILDING Y >> load_semantic.log @/opt/D2RQ/file.sql exit; EOF 

And here is what file.sql looks like:

 SET ECHO ON; SPOOL count.log SELECT COUNT(*) as total_count FROM TABLE(SEM_MATCH( '{ ?s rdf:type :ProcessSpec . ?s ?p ?o }',SEM_Models('BUILDING'),NULL, SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL)); SPOOL OFF; 

Is it possible to change my shell script to pass variable names?

Ie: model = "BUILDING" and pass this to file.sql?

Is there anything similar?

Answer 1

It seems you have a heredoc containing one SQL * Plus command, although it doesn’t look like the comment says. You can pass the value to heredoc :

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

or if BUILDING $2 in a script:

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

If your file.sql had exit at the end, then that would be even easier since you didn't need heredoc :

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

In your SQL, you can reference position parameters using replacement variables :

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

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

---

You can pass multiple values ​​and refer to them sequentially in the same way as positional parameters in the shell script - the first parameter passed &1 , the second &2 , etc. You can use substitution variables somewhere in the SQL script, so you can use them as column aliases without problems - you just need to be careful when adding an additional parameter that you either add to the end of the list (which makes the numbering out of order in the script, maybe ) or configure 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 passed into your shell script, just use its positional parameter $4 or something else. And your SQL will 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 in a lot of values, you can more clearly use positional parameters to define named parameters, so all problems with order are addressed at the beginning 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 are requesting for (the value is $2 or BUILDING in the original part of the response). You can refer to a replacement variable as many times as you want.

This may not be easy to use if you run it several times, as it will appear as a header above the count value in each bit of output. Perhaps this would be more clear:

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

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

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

Depends on what you use for the results ...