Oracle Magazine, July/August 2017
Database Application Developer and DBA BEYOND SQL 101 sand values within your statements or scripts In such cases you might not want to disable substitution variables The database script variables_ and_ ampersands sql sets the SQL Plus environment back to accepting and replacing substitution variables and uses string concatenation to ensure that any literal ampersand is correctly read as a literal string value Note that if you run the SET DEFINE OFF SQL Plus command any attempt to access your substitution variable as originally written in your script will result in an error message similar to the following ORACLE MAGAZINE JULY AUGUST 2017 116 SQL @ variables_ and_ ampersands sql SP2 0552 Bind variable V_ TABLE not declared One of the ways to handle this error is to ensure that you run the SET DEFINE ON SQL Plus command the first line of the variables_ and_ ampersands sql script Occasionally you might want to use a named substitution variable more than once in your database script If your input to the variable is the same for the duration of the script run you can avoid being prompted for a value each time the variable is encountered by employing double ampersand notation Listing 10 shows a script with a substitution variable used in multiple statements where the input is potentially the same for each variable invocation Listing 11 demonstrates a similar script that uses double ampersand notation with the substitution variable Code Listing 10 Substitution variables requiring value input each time they are invoked Create a database script called resubstitute sql select table_ name num_ rows to_ char last_ analyzed DD MON YYYY HH24 MI SS last_ analyzed from user_ tables
You must have JavaScript enabled to view digital editions.
