Query using Oracle substitution variables

substitution variable in oracle sql developer
oracle sql ampersand substitution
oracle substitution variable escape
how to pass parameter in oracle sql query
sqlplus substitution variables
using variables in oracle sql developer
substitution variable in sql server
how to use bind variables in oracle stored procedure

I have a example SQL code like below

select *
  from Engg_Studens
 where Student_Name = '&Student_Name'
   And Department = 'computer science & Engineering' ;

My question here is: while running the SQL, it is asking for the substitution variable for '& engineering' which is in department name. I should only be asked for '&student_name'.

You can escape the second ampersand (&) but honestly I never remember how to do that off the top of my head. I usually do something like this:

SELECT *
  FROM engg_studens
 WHERE student_Name = '&Student_Name'
   AND department = 'Computer Science &' || ' Engineering';

Using the concatenation (||) operator here avoids the replacement variable.

Oracle SQL*Plus Substitution Variables, https://docs.oracle.com › database › oracle › oracle-database › sqpug › usin Using Substitution Variables for Strings and Dates. Now, if I want to use a substitution variable for a string (varchar), I have to make sure the value is enclosed inside single quotes, as usual, so, for example, if I have this query to filter employees by job_id: SELECT * FROM employee WHERE job_id = &job;

You can not write a statement which includes the "&" character.

Try like below:

select * from Engg_Studens where Student_Name = '&Student_Name' 
and Department = 'computer science '|| chr(38) ||' Engineering' ;

Using Substitution Variables, 6.3.3 Storing a Query Column Value in a Substitution Variable. Data stored in the database can be put into substitution variables: SQL> column last_name  Substitution variables are the workhorse of SQL*Plus scripts. They give you a place to store user input, and they give you a way to use that input in SQL queries, PL/SQL code blocks, and other SQL*Plus commands.

Another approach is to change the substitution character to something you are sure will not be used elsewhere:

SQL> set define ^

SQ> select *
  from Engg_Studens
 where Student_Name = '^Student_Name'
   And Department = 'computer science & Engineering'

Enter value for student_name: Arif
old   3:  where Student_Name = '^Student_Name'
new   3:  where Student_Name = 'Arif'

Only the ^Student_Name is now treated as a substitution variable so you only get a prompt for that.

You could also potentially separate the prompt from the query and switch to using an actual bind variable, but that seems like overkill here.

Using Substitution Variables, Substitution variables are set off in the text of a script by preceding them with either one or two ampersand characters. Say, for example, that you had this query  A more practical use of substitution variables is to prompt for a value before referencing the variable: SQL> accept myv char prompt 'Enter a last name: ' SQL> select employee_id from employees where last_name = '&myv'; If these two commands are stored in a SQL*Plus script, a different last name can be entered each time the script is run.

Literals, Substitution Variables and Bind Variables, Then two queries are issued, both specifying literal values in the WHERE clause. Finally the contents of the shared pool is displayed by querying the V$SQL view. So, in summary, Substitution variables are variables that the user interface detects and prompts for text to substitute into the code before submitting it to the database, and Bind variables are placeholders in queries that allow SQL queries to be soft parsed rather than hard parsed when the query is re-used, help prevent SQL injection, and allow for the values to be supplied easily and seamlessly within the code issuing it.

Substitution Variables, In this lesson you will learn about substitution variables, which are a feature of SQL*Plus and SQL Developer that helps you write more "generic" queries. Literals, Substitution Variables and Bind Variables If you've read anything about writing OLTP applications that talk to Oracle databases, you will know that bind variables are very important. Each time a SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool.

Query using Oracle substitution variables, You can escape the second ampersand ( & ) but honestly I never remember how to do that off the top of my head. I usually do something like  APP_SESSION is the most commonly used built-in substitution strings. You can use this substitution string to create hypertext links between application pages that maintain a session state by passing the session number. Note that you can also use the substitution string SESSION in place of APP_SESSION.

Comments
  • This happens because you have not properly escaped the & in 'computer science & Engineering' and that it consider it as a bind variable. Read this for solutions
  • Not really relevant to the question, but that is a substitution variable, not a bind parameter. They are different things.
  • I suggested the question title change to "Query using Oracle substitution variables".
  • tried with the syntax , and it worked . thanks for the script .
  • Actually you can. You would either have to escape the character properly, change the setting for substitution variables character (to use a different character) or turn off the substitution variables at all (and use parameterized queries instead).
  • It worked when I tested it so not sure what to tell you. Depends what "did not work" means.