使用 sqlplus 从命令行运行 oracle 脚本时出现问题
我在尝试使用 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;
/
当我使用 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
我正在使用此命令行运行该工具,这适用于其他脚本:
sqlplus username/password@server/dbname @Setup.sql
有什么想法吗?谢谢。
I'm having a problem trying to run my sql script into oracle using sqlplus. The script just populates some dummy data:
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:
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
您需要在脚本末尾添加
exit
,或者以sqlplus username/password@server/dbname
运行它。 Setup.sql
(即重定向输入,<
而不是@
)。您只需在挂起会话中输入“exit”即可检查这是否是问题所在。如果确实挂起,您是否已从 Developer 提交或回滚了执行?
You need to either put an
exit
at the end of the script, or run it assqlplus 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.If it is really hanging, have you committed or rolled back the execution from Developer?
我也看到了这个问题,某些脚本可以在像 TOAD 这样的客户端中正常执行,但是当使用 @script 指令通过 SQLPlus 执行而不是挂起时,SQLPlus 客户端会返回一个带有数字的提示,该数字对应于正在执行的脚本中的行数 (+1)。
例如,如果我们执行一个名为“doit.sql”的脚本,该脚本有 70 行,我们将使用适当的命令启动 SQLPlus 并输入:
> @doit.sql
然后我们会看到:
71:
此时按 Enter 键将返回
72:
我们可以通过输入
来执行这些脚本>/
出现提示并按 Enter 键。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).
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
Then we would see:
71:
Pressing enter at this point would return
72:
We were able to get these scripts executed by entering
/
at the prompt and pressing enter.简单的答案
确保你把两个都写成“end;”并在最后一行放'/'
它将轻松运行。
The Simple answer
Make sure you put both 'end;' and in final line put '/'
It will run with ease.
上面提到的命令行
意味着
sqlplus
应该执行脚本Setup.sql
并以交互方式等待进一步的命令(如果脚本没有明确执行exit
) 。这是sqlplus
的正常行为。sqlplus
在三种情况下终止:WHENEVER
plsql-command)exit
(交互和脚本)无论是从交互模式还是从脚本,您都可以发送
^Z
字符来软终止输入流。以交互方式,您只需按Ctrl+Z,Enter
即可。当然,您可以重定向 STDIN,并从文件而不是键盘中获取它。有两种类似的方法可以执行此操作:
在这两种情况下,
sqlplus
都会在脚本执行后终止,因为输入流中存在EOF
。The command line
mentioned above means that
sqlplus
should execute the scriptSetup.sql
and wait further commands interactively (if script does not doexit
explicitly). This is a normal behavior ofsqlplus
.sqlplus
terminates in three cases:WHENEVER
plsql-command)exit
(both interactive and sript)Either from interactive mode or from script you can send
^Z
character to softly terminate input flow. Interactively you just pressCtrl+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:
In both cases
sqlplus
will terminate after the script execution because ofEOF
in the input flow.请确保 doit.sql 中的查询以分号结尾,而不是在提示符下使用
/
。Instead of using the
/
at the prompt, ensure that your query in the doit.sql ends with a semicolon.问题是因为 SQLplus 是从 Oracle 客户端主目录运行的,并且在运行启动升级命令时崩溃了。
从非客户端 oracle 主目录运行 sqlplus 并尝试命令,然后它按预期工作。
因此解决方案是从主 oracle 主目录而不是 oracle 客户端主目录运行 sqlplus。
Issue is because of SQLplus was running from the oracle client home and it was crashing on running the startup upgrade command.
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.