如何使用SQLPLUS在命令行上将参数传递给PL/SQL脚本? [英] How do I pass arguments to a PL/SQL script on command line with SQLPLUS?

问题描述

如何使用SQLPLUS在命令行上将参数传递给PL/SQL脚本?我可以这样调用我的PL/SQL脚本，但是该脚本需要参数才能使其成功.如何运行sqlplus.exe，以便可以将参数传递给脚本?

How do I pass arguments to a PL/SQL script on command line with SQLPLUS? I can call my PL/SQL script like so, but the script requires arguments in order for it to succeed. How can I run sqlplus.exe so that I can pass arguments to the script?

@ECHO off
// where HOST030 is a tnsnames alias to a machine, port, and instance    
sqlplus.exe MYUSER/mypassword@HOST030 < refreshDataOnOracle.sql    
pause

我试图搜索答案，但是在任何地方都找不到SQLPLUS的参数示例".我怀疑这是使用SQL * Plus"START"命令的类似方法吗?

I tried to search for the answer but couldn't find an "argument example" anywhere for SQLPLUS. I suspect it would be a similar method of using the SQL*Plus "START" command?

推荐答案

首先，您需要像这样调用脚本:

Firstly, you will need to invoke your script like so:

sqlplus.exe MYUSER/mypassword@HOST030 @refreshDataOnOracle.sql foo bar  

您将使用"@"符号代替要执行的操作系统重定向，以指示要执行的文件名.您还将在命令行上提供脚本参数.在脚本中，您将使用&1，&2等引用参数.

Instead of the OS redirection you will use the "@" symbol to indicate the file name to execute. You will also supply the script parameters on the command line. In the script you will refer to the parameters using &1, &2 etc.

update mytable set mycol = '&2' where myid = '&1';

将翻译为

update mytable set mycol = 'bar' where myid = 'foo';

这篇关于如何使用SQLPLUS在命令行上将参数传递给PL/SQL脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！