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

问题描述

我要提示符后输入数据库名。

但它没有，它仍然连接。什么数据库做它连接到？它说：连接到

下面是该批次code。

 关闭@echoSQLPLUS＆安培;用户名/＆放大器;密码@＆安培;分贝ECHO关于退出。超时吨/ 30：暂停
 

解决方案

号（＆安培; ）

用于在SQL * Plus替换变量。您在Windows命令行，它意味着别的东西上提供它;当您从客户端退出，你会看到类似这样的：

 用户名不被识别为一个内部或外部命令，
可运行的程序或批处理文件。
密码@不被识别为一个内部或外部命令，
可运行的程序或批处理文件。
'的dbname不被识别为内部或外部的命令，
可运行的程序或批处理文件。
 

该批处理文件将其当作一个命令分隔符，所以它没有传递到SQL * Plus，只着眼于一旦程序完成后有什么。

用户名/密码的提示是完全indpendent您的命令行的值。这将在本地连接到任何你ORACLE_SID环境变量设置为，或者如果你的TWO_TASK环境变量设置那么它将被使用，作为一个TNS连接。

您可以提示一个SQL脚本的三条信息，你在您的previous问题未遂，或从批处理文件，提示他们，并通过他们在命令行上，在一个循环中，如果你想处理错误，因为渔业部显示;但正如我在评论中提到的那个答案，把凭据在命令行上也不是很安全的。你可以使用一个定界符直接在code段使用批处理变量，而不是使用SQL脚本。

例如，只是模仿你做之前，你可以替换与渔业部的 SQLPLUS.EXE 行：

  @（
每当呼应SQLERROR退出失败
呼应连接％SqlUserName％/％SQLPASSWORD％@％SqlDatabase％
从呼应双SELECT *;
回声退出
）| SQLPLUS.EXE -s / NOLOG
 

或从脚本文件运行命令：

  @（
每当呼应SQLERROR退出失败
呼应连接％SqlUserName％/％SQLPASSWORD％@％SqlDatabase％
回声@ myscript.sql
回声退出
）| SQLPLUS.EXE -s / NOLOG
 

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

作为要与更多的回声语句，和/或其他语句不脚本可以运行尽可能多的脚本。但是，如果任何脚本包括退出，或会导致退出的错误，这会终止整个会话，而不仅仅是该脚本。

---

由于在SQL任何错误都将导致一个出口，不只是一个凭证问题，您周围的循环，当你不想要，因为你在评论中指出的。你有两个选择：

• 您可以添加每当SQLERROR继续的连接后声明;但如果你打一个错误，将继续，并尝试仍要运行后续命令，并根据你在做什么可能最终奇怪的结果。但是，如果你创建对象，那么你可能有你不介意扔你想忽略错误保护滴。


• 您可以后使用每当SQLERROR出口2 前的连接和每当SQLERROR出口1 。那么你的批处理脚本可以决定是否要循环，以提示基础上再次返回值，1或2。（我不是真正熟悉Windows退出codeS等等其他值可能更合适，以避免混淆）。

或者你可以混合两者有不同的处理特定命令的失败，让你忽略了一个错误降说，但别的退出脚本。取决于你在做什么和你的详细要求。

了解更多关于每当行为。

I need to prompt ENTER DATABASE NAME.

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

Here's the batch code.

@echo off

sqlplus &username/&password@&db

ECHO About to exit.

timeout t/ 30

:pause

解决方案

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.

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.

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.

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.

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

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.

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.

---

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:

• 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).

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屋！