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.

what happens when you connect to your db via sqlplus, and then at the prompt call the script? — akf
– akf, Commented May 11, 2010 at 9:17

Alex Poole · Accepted Answer · 2010-05-11 09:35:51Z

15

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?

answered May 11, 2010 at 9:35

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

akf Over a year ago

even without the exit, it should do something, ie complete and display a prompt.

Alex Poole Over a year ago

true, I'm usually running with -s when I make this mistake

Charlie Over a year ago

Yeah sorry my fault, it's the transaction which isn't commited. First time I've used oracle or any of these tools!

JonnyRaa Over a year ago

for me this just seems to dump loads of numbers to the screen. Any idea what that's about?! They basically increment up to the line number but it writes them out in a matrix

Alex Poole Over a year ago

@JonnyLeeds - if you don't have -s onthe command line, you see the script line numbers. You'll be left at a numbered prompt; if hitting return generates a new number, you're writing a PL/SQL block, otherwise you'll get an sql> prompt. Either way you haven't told it to execute. You need a / on its own on the line after the block's final end.

|

2 revs · Accepted Answer · 2013-09-06 19:21:01Z

15

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.

edited Sep 6, 2013 at 19:21

community wiki

2 revs
cwash

1 Comment

Big Rich Over a year ago

I had similar, hanging with just a number I could increment by pressing enter. I redirected my script into my SQLPlus command, using < instead of @ and the script ended and returned to the command prompt, as per the suggestion in @alex-poole's post, above. Unfortunately, I couldn't just add EXIT, as my import led to a number of 3rd party scripts, shared around the business in our code repository.

Viren · Accepted Answer · 2013-04-09 20:50:31Z

8

The Simple answer

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

It will run with ease.

answered Apr 9, 2013 at 20:50

Viren

811 silver badge1 bronze badge

2 Comments

Yoav24 Over a year ago

fixed it for me.

RonJohn Over a year ago

@Yoav24 also for me. Oracle is... beyond aggravating.

Naeel Maqsudov · Accepted Answer · 2017-12-12 12:28:22Z

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.

sandrows · Accepted Answer · 2012-07-10 12:35:56Z

0

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

answered Jul 10, 2012 at 12:35

sandrows

11

1 Comment

Alex Poole Over a year ago

This should have been a comment to cwash's answer, but is wrong anyway; getting the line number prompts indicates it was a PL/SQL block, which would require the / (as well as the ;, which must already be there or it still wouldn't work).

PranavF · Accepted Answer · 2018-05-10 10:53:28Z

0

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.

answered May 10, 2018 at 10:53

PranavF

1

Collectives™ on Stack Overflow

Problem running oracle script from command line using sqlplus

6 Answers 6

6 Comments

1 Comment

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

1 Comment

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related