SQL 使用 sqlplus 从命令行运行 oracle 脚本时出现问题

Problem running oracle script from command line using sqlplus

提问by Charlie

I'm having a problem trying to run my sql script into oracle using sqlplus. The script just populates some dummy data:

我在尝试使用 sqlplus 将我的 sql 脚本运行到 oracle 时遇到问题。该脚本只是填充了一些虚拟数据：

DECLARE 
  role1Id NUMBER;
  user1Id NUMBER;
  role2Id NUMBER;
  user2Id NUMBER;
  role3Id NUMBER;
  user3Id NUMBER;
  perm1Id NUMBER;
  perm2Id NUMBER;
  perm3Id NUMBER;
  perm4Id NUMBER;
  perm5Id NUMBER;
BEGIN
  INSERT INTO PB_USER(USER_ID,USER_NAME, USER_EMAIL, USER_ACTIVEYN)
  VALUES(PB_USER_ID_SEQ.nextval, 'RoleDataManagerTests_Username', '[email protected]',' ');

  INSERT INTO ROLES(ROLE_ID, ROLE_NAME)
  VALUES(PB_ROLE_ID_SEQ.nextval, 'Test role 1');
  INSERT INTO ROLES(ROLE_ID, ROLE_NAME)
  VALUES(PB_ROLE_ID_SEQ.nextval, 'Test role 2');
  INSERT INTO ROLES(ROLE_ID, ROLE_NAME)
  VALUES(PB_ROLE_ID_SEQ.nextval, 'Test role 3');

  SELECT ROLE_ID INTO role1Id FROM ROLES WHERE ROLE_NAME = 'Test role 1';
  SELECT USER_ID INTO user1Id FROM PB_USER WHERE USER_NAME = 'RoleDataManagerTests_Username';
  INSERT INTO USERS_ROLES(USER_ID, ROLE_ID) VALUES(user1Id, role1Id);

  SELECT ROLE_ID INTO role2Id FROM ROLES WHERE ROLE_NAME = 'Test role 2';
  SELECT USER_ID INTO user2Id FROM PB_USER WHERE USER_NAME = 'RoleDataManagerTests_Username';
  INSERT INTO USERS_ROLES(USER_ID, ROLE_ID) VALUES(user2Id, role2Id);

  SELECT ROLE_ID INTO role3Id FROM ROLES WHERE ROLE_NAME = 'Test role 3';
  SELECT USER_ID INTO user3Id FROM PB_USER WHERE USER_NAME = 'RoleDataManagerTests_Username';
  INSERT INTO USERS_ROLES(USER_ID, ROLE_ID) VALUES(user3Id, role3Id);

  INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION)
  VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm1', 'permission 1');
  INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION)
  VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm2', 'permission 2');
  INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION)
  VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm3', 'permission 3');
  INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION)
  VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm4', 'permission 4');
  INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION)
  VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm5', 'permission 5');

  SELECT PERMISSION_ID INTO perm1Id FROM PERMISSIONS WHERE KEY = 'perm1';
  SELECT PERMISSION_ID INTO perm2Id FROM PERMISSIONS WHERE KEY = 'perm2';
  SELECT PERMISSION_ID INTO perm3Id FROM PERMISSIONS WHERE KEY = 'perm3';
  SELECT PERMISSION_ID INTO perm4Id FROM PERMISSIONS WHERE KEY = 'perm4';
  SELECT PERMISSION_ID INTO perm5Id FROM PERMISSIONS WHERE KEY = 'perm5';

  INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID)
  VALUES(role1Id, perm1Id);
  INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID)
  VALUES(role1Id, perm2Id);
  INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID)
  VALUES(role1Id, perm3Id);

  INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID)
  VALUES(role2Id, perm3Id);

  INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID)
  VALUES(role3Id, perm4Id);
  INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID)
  VALUES(role3Id, perm5Id);
END;
/

My script works fine when I run it using Oracle SQL Developer but when I use the sqlplus command line tool this is what's outputted and then it just hangs:

当我使用 Oracle SQL Developer 运行它时，我的脚本工作正常，但是当我使用 sqlplus 命令行工具时，这是输出的内容，然后它就挂了：

SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 11 09:49:34 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

I'm running the tool using this command line, which works fine for other scripts:

我正在使用此命令行运行该工具，它适用于其他脚本：

sqlplus username/password@server/dbname @Setup.sql

Any ideas? Thanks.

有任何想法吗？谢谢。

回答by Alex Poole

You need to either put an exitat the end of the script, or run it as sqlplus username/password@server/dbname < Setup.sql(i.e. redirected input, <instead of @). You can check if that's the issue by just typing 'exit' in the hanging session.

您需要将 anexit放在脚本的末尾，或者将其作为sqlplus username/password@server/dbname < Setup.sql（即重定向输入，<而不是@）运行。您可以通过在挂起会话中输入“退出”来检查这是否是问题。

If it is really hanging, have you committed or rolled back the execution from Developer?

如果它真的挂了，你是否从 Developer 提交或回滚了执行？

回答by cwash

I was seeing this problem as well with certain scripts that would execute fine in a client like TOAD, but when being executed via SQLPlus with the @script directive instead of hanging, the SQLPlus client would return a prompt with a number on it which corresponded to the number of lines in the script being executed (+1).

我在某些脚本中也看到了这个问题，这些脚本在像 TOAD 这样的客户端中可以正常执行，但是当通过带有 @script 指令而不是挂起的 SQLPlus 执行时，SQLPlus 客户端会返回一个提示，上面有一个数字，对应于正在执行的脚本中的行数 (+1)。

For example, if we executed a script named 'doit.sql' that had 70 lines we would start SQLPlus with the appropriate command and enter:

例如，如果我们执行一个名为“doit.sql”的脚本，它有 70 行，我们将使用适当的命令启动 SQLPlus 并输入：

> @doit.sql

> @doit.sql

Then we would see:

然后我们会看到：

71:

71:

Pressing enter at this point would return

此时按回车将返回

72:

72:

We were able to get these scripts executed by entering /at the prompt and pressing enter.

我们能够通过/在提示符下输入并按 Enter 来执行这些脚本。

回答by Viren

The Simple answer

简单的答案

Make sure you put both 'end;' and in final line put '/'

确保你把两个 'end;' 并在最后一行放'/'

It will run with ease.

它将轻松运行。

回答by Naeel Maqsudov

The command line

命令行

sqlplus username/password@server/dbname @Setup.sql

mentioned above means that sqlplusshould execute the script Setup.sqland wait further commands interactively (if script does not do exitexplicitly). This is a normal behavior of sqlplus.

上面提到的意思是sqlplus应该执行脚本Setup.sql并以交互方式等待进一步的命令（如果脚本没有exit明确执行）。这是 的正常行为sqlplus。

sqlplusterminates in three cases:

sqlplus在三种情况下终止：

• Failure (for some errors you can change if it terminates or not. See WHENEVERplsql-command)
• Explicit exit(both interactive and sript)
• End of STDIN (EOF)

• 失败（对于某些错误，您可以更改是否终止。参见WHENEVERplsql-command）
• 显式exit（交互式和 sript）
• STDIN (EOF) 结束

Either from interactive mode or from script you can send ^Zcharacter to softly terminate input flow. Interactively you just press Ctrl+Z,Enter.

无论是从交互模式还是从脚本，您都可以发送^Z字符以软终止输入流。您只需按交互方式即可Ctrl+Z,Enter。

And, of course, you may redirect STDIN, and take it from file, not from keyboard. There are two similar ways to do this:

而且，当然，您可以重定向 STDIN，并从文件中获取它，而不是从键盘中获取。有两种类似的方法可以做到这一点：

1) sqlplus username/password@server/dbname<Setup.sql
2) echo @Setup.sql|sqlplus username/password@server/dbname

In both cases sqlpluswill terminate after the script execution because of EOFin the input flow.

sqlplus由于EOF在输入流中，这两种情况都将在脚本执行后终止。

回答by PranavF

Issue is because of SQLplus was running from the oracle client home and it was crashing on running the startup upgrade command.

问题是因为 SQLplus 从 oracle 客户端主目录运行，并且在运行启动升级命令时崩溃。

Run the sqlplus from the non-client oracle home and tried the commands then it works as expected. so resolution is to run the sqlplus from the main oracle home instead of oracle client home.

从非客户端 oracle home 运行 sqlplus 并尝试命令，然后它按预期工作。所以解决方案是从主 oracle home 而不是 oracle client home 运行 sqlplus。

回答by sandrows

Instead of using the /at the prompt, ensure that your query in the doit.sql ends with a semicolon.

不要/在提示符下使用 ，而是确保 doit.sql 中的查询以分号结尾。