CODEWITHSUNDEEP
sqloracle-database
Di Zou
Di Zou

Reputation: 4609

Is there a way to auto-input sql script prompts?

I have a sqlscript that contains statements like this:

prompt Enter 'html' for an HTML report, or 'text' for plain text
prompt  Defaults to 'html'

column report_type new_value report_type;
set heading off;
select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual;

So when I run the script it prompts me to enter in values. I want to automate this script. How would I pass to the script parameters that are then used for the prompts? I cannot modify the script at all by removing the prompts.

I am using Windows Server 2008 and Windows 7.

Upvotes: 1

Views: 1607

Answers (2)

Ben
Ben

Reputation: 52863

Assuming you're passing values in from the command line or a batch file etc you can pass in parameters by using &1, &2, .. &n, where the number is the position of the parameter when calling

column report_type new_value report_type
set heading off

select 'Type Specified: ',lower(nvl('&1','html')) report_type from dual;

This would be called by

[call sqlplus schema/pw@db @] my_script.sql html

Incidentally you don't need semi-colons after SQL*Plus commands.

Upvotes: 0

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

On Unix you can use here documents:

#>sqlplus un @script <<EOF
yourpassword
parameter#1
parameter#2
EOF

Upvotes: 2

Related Questions