CODEWITHSUNDEEP
sqloracle-databaseplsql
Arav
Arav

Reputation: 5247

Regarding sql script

I have the line below in a .sql script. Not sure what it does. Does it wait for input from user for dbuserid and password or does it take the values from environment? What does && denote? In test.sql file I saee the line below before creating tables:

CONNECT &&usr/&&pass@&&dbname

Upvotes: 0

Views: 423

Answers (2)

bhangm
bhangm

Reputation: 597

The '&' is used to refer to substitution variables in SQLPlus. These variables can be set using the DEFINE keyword. Alternatively, if not specified using this keyword, SQLPlus will prompt you for the value of the variable and continue prompting you every time it sees &variable

The double '&' tells SQLPlus to reuse the first defined value of the variable. For example, if you were prompted for the value of usr based on your connect code snippet, all subsequent occurrences of &&usr would be replaced with this value. In this case SQLPlus will prompt you only once to enter in the value of usr.

EDIT:
Yes you can pass in parameters from a shell script. Example:

$ cat a.sh
#!/bin/bash

# Ideally, you would retrieve the password from a secure
# location like a password safe/vault etc. Never hardcode
# the connection credentials
USER=scott
PASS=tiger
INST=orcl

sqlplus -s /nolog << EOF >> some_log_file.log
connect ${USER}/${PASS}@${INST}
set serveroutput on
select user from dual;
EOF

$ ./a.sh

$ cat some_log_file.log

USER
------------------------------
SCOTT

$

Upvotes: 0

Eddie Awad
Eddie Awad

Reputation: 3729

In this sql script you are declaring usr, pass and dbname as subsitution variables. From OTN:

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQLPlus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQLPlus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the value is discarded and the variable remains undefined. If the variable is referenced twice, even in the same command, then you are prompted twice. Different values can be entered at each prompt.

If a double ampersand reference causes SQLPlus to prompt you for a value, then SQLPlus defines the variable as that value. Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again.

And here is more about SQL*Plus Substitution Variables.

Upvotes: 1

Related Questions