Is something like this an acceptable compromise for the users:ĭisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2. Note that this time, I do not have to re-define 1 as it has already been done from inside the script. The closest I could get the script to work the way I wanted it to work is to add DEFINE 1 at the end of the script: Please see the outputs below:īut here's the problem - it still picks up the last value, which I'd like to avoid, when there's no input. Id like to be able to run a script in SQLPlus with an input parameter (i.e.
I'd like to keep the "process" as simple as possible as it'll be run by a user who will forget how to do it in 3 days and then wonder why the script doesn't work )Īnyways, as you have suggested, I've pre-defined 1 before running the script. User will log into the database via SQLPlus and run the script (i.e. The first and last PROMPT commands simply space the output a bit better by adding a blank line above and below the description. The script is to be run directly from SQLPlus prompt. PROMPT PROMPT This script will first DESCRIBE a table, then PROMPT it will list the definitions for all indexes PROMPT on that table. As I've mentioned before, there's no process that calls the script. New 5: dbms_output.put_line ( 'You entered: ' || 'abc' ) Once you've done this it will keep the value until you end the session or undefine it: New 5: dbms_output.put_line ( 'You entered: ' || '' ) ĭefine the variable before calling it. Old 5: dbms_output.put_line ( 'You entered: ' || '&1' ) Pass an empty string as arguments to the script:ĭbms_output.put_line ( '#1 cannot be null' ) ĭbms_output.put_line ( 'You entered: ' || '&1' ) To overcome this there are a couple of methods you could use: How does the accept command work to accept a variable into PL/SQL Answer: To accept a value into SQLPlus you use the combination of the 'accept' and 'prompt' commands. and in PL/SQL you can use this notation to make PL/SQL ignore special.
If they're not already define it'll prompt you. Dear Sql community, Which special characters need escaping in a solr query. To use them Oracle needs to know their values.
When you "set define on", Oracle interprets ampersands as substitution variables.