如何在命令行上提示输入数据库？ [英] How do I prompt input for database on command line?

问题描述

我需要提示输入DATABASE NAME。

I need to prompt ENTER DATABASE NAME.

但它不会，它仍然连接。它连接到什么数据库？它说CONNECTED TO

But It doesn't and it still connects. What database does it connects to? It says "CONNECTED TO "

这是批处理代码。

@echo off

sqlplus &username/&password@&db

ECHO About to exit.

timeout t/ 30

:pause

推荐答案

&符号（& ）用于SQL * Plus替换变量。您正在Windows命令行上提供它，这意味着其他的;当你从客户端退出时，你会看到类似：

Ampersand (&) is used for SQL*Plus substitution variables. You are supplying it on the Windows command line, where it means something else; when you exit from the client you'll see something like:

'username' is not recognized as an internal or external command,
operable program or batch file.
'password@' is not recognized as an internal or external command,
operable program or batch file.
'dbname' is not recognized as an internal or external command,
operable program or batch file.

批处理文件将其视为命令分隔符，因此不会将任何内容传递给SQL * Plus，

The batch file will treat it as a command separator, so it passes nothing to SQL*Plus, and only looks at what's after it once that program has completed.

用户名/密码提示完全不依赖于您的命令行值。它将在本地连接到您设置的任何ORACLE_SID环境变量，或者如果您的TWO_TASK环境变量已设置，那么它将使用它作为TNS连接。

The username/password prompts are completely indpendent of your command line values. It will be connecting locally to whatever your ORACLE_SID environment variable is set to, or if your TWO_TASK environment variable is set then it will be using that as a TNS connection.

可以在您尝试您的上一个问题时提示SQL脚本中的三条信息，或者从批处理文件并传递他们在命令行，在循环中，如果你想处理错误Mofi显示;但是正如我在对这个答案的评论中提到的，将凭据放在命令行上不是很安全。您可以使用heredoc在代码段中直接使用批量变量，而不是使用SQL脚本。

You can prompt for the three pieces of information in a SQL script as you attempted in your previous question, or prompt for them from the batch file and pass them on the command line, in a loop if you want to handle mistakes as Mofi showed; but as I mentioned in a comment on that answer, putting the credentials on the command line isn't very secure. You could use a 'heredoc' to use the batch variables directly in a code snippet, rather than using a SQL script.

例如，只是为了模仿您在做什么之前，你可以替换Mofi的 sqlplus.exe 行：

For example, just to mimic what you were doing before, you could replace Mofi's sqlplus.exe line with:

@(
echo whenever sqlerror exit failure
echo connect %SqlUserName%/%SqlPassword%@%SqlDatabase%
echo select * from dual;
echo exit 
) | sqlplus.exe -s /nolog

或从脚本文件运行命令：

Or to run commands from a script file:

@(
echo whenever sqlerror exit failure
echo connect %SqlUserName%/%SqlPassword%@%SqlDatabase%
echo @myscript.sql
echo exit 
) | sqlplus.exe -s /nolog

注意，因为你正在做 connect 使用您提供的凭证调用脚本之前，脚本本身不应该有 connect 语句，它将无法看到你Windows％变量。 （这可能使他们可见，但我不认为你需要）。另请注意，每当语句意味着如果连接失败，它不会尝试执行脚本。

Note that since you are doing the connect with your supplied credentials before calling the script, the script itself should not have a connect statement, and it won't be be able to see you Windows % variables. (It's possible to make them visible but I don't think you need to). Also note that the whenever statement means it won't attempt to execute the script if the connect fails.

可以使用更多 echo 语句和/或不在脚本中的其他语句运行任意多的脚本。但是如果任何脚本包括 exit ，或者导致一个错误退出，那将会终止整个会话，而不只是该脚本。

You can run as many scripts as you want with more echo statements, and/or other statements not in scripts. But if any script includes an exit, or causes an error that exits, that will terminate the whole session and not just that script.

由于您的SQL中的任何错误都会导致退出，而不仅仅是凭据问题，所以当您不想使用时，评论。您有几个选项：

As any error in your SQL will cause an exit, not just a credential problem, you'll loop around when you don't want to, as you noted in a comment. You have a couple of options:

• 每当sqlerror continue 时，您可以添加 connect 语句;但是如果你碰到一个错误，它会继续，并尝试运行后续命令反正，根据你在做什么，可能会得到奇怪的结果。


• 每当sqlerror退出2时，你可以使用，但是如果你正在创建对象，那么你可能有保护滴，你不介意抛出你想忽略的错误。 之前的连接和每当sqlerror退出1 之后。然后你的批处理脚本可以根据返回值1或2决定是否循环再次提示。（我不太熟悉Windows退出代码，所以其他值可能更适合避免混淆）。

• You could add whenever sqlerror continue after the connect statement; but then if you hit an error it will continue and try to run subsequent commands anyway, and depending on what you're doing that might end up with strange results. But if you're creating objects then you might have protective drops that you don't mind throwing errors you want to ignore.
• You could use whenever sqlerror exit 2 before the connect and whenever sqlerror exit 1 after it. Then your batch script can decide whether to loop to prompt again based on the return value, 1 or 2. (I'm not really familiar with Windows exit codes so other values might be more appropriate to avoid confusion).

或者你可以混合使用不同处理特定命令的失败，因此你可以忽略 drop 说，但任何其他退出脚本。取决于你在做什么以及你的详细要求。

Or you could mix both to have the failure of specific commands handled differently, so you ignore an error on a drop say, but anything else exits the script. Depends what you're doing and what your detailed requirements.

阅读关于每当行为的更多信息。

Read more about the whenever behaviour.

这篇关于如何在命令行上提示输入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！