Problem running oracle script from command line using sqlplus

Question

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.

Answer 1

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.

Answer 2

The Simple answer

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

It will run with ease.

Answer 3

You need to either put an exit at 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.

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

Answer 4

The command line

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

mentioned above means that sqlplus should execute the script Setup.sql and wait further commands interactively (if script does not do exit explicitly). This is a normal behavior of sqlplus.

sqlplus terminates in three cases:

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

Either from interactive mode or from script you can send ^Z character to softly terminate input flow. Interactively you just press 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:

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

In both cases sqlplus will terminate after the script execution because of EOF in the input flow.