OraFAQ Forum: SQL & PL/SQL » sqlplus hangs when running an SQL script

Home » SQL & PL/SQL » SQL & PL/SQL » sqlplus hangs when running an SQL script (SQLPlus shipped with Oracle 10g, WinXP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlplus hangs when running an SQL script [message #329514]

Wed, 25 June 2008 11:17

nmaier
Messages: 4
Registered: May 2008

Junior Member

Hi,
I am executing a 3MB sql script with SQLPlus. Using this command:
sqlplus db/pass@SN @oracleScript.oracle

Nearly all of it is writes to the database. It runs successfully for sometime printing multiple "PL/SQL procedure completed successfully" messages. And then it prints the number two on a new line and hangs, no SQLPlus prompt it just sits there doing nothing. Does that "2" mean something???

What logs can I check to see where it was at in the script when it hung or if there are any logs reporting errors?

Thanks for the help!

Nate

Report message to a moderator

Re: sqlplus hangs when running an SQL script [message #329515 is a reply to message #329514]

Wed, 25 June 2008 11:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

look familiar?

SQL> set term on echo on time on
09:19:30 SQL> !cat demo.sql
select sysdate from dual

09:19:36 SQL> @demo
09:19:40 SQL> select sysdate from dual
09:19:40   2

You have an unterminated command in the file.

Report message to a moderator

Re: sqlplus hangs when running an SQL script [message #329516 is a reply to message #329514]

Wed, 25 June 2008 11:22

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You've forgotten either a trailing ';' or a '\' from a statement.
I can't actually remember which it is, which is irritating after so long spent fixing other peoples SQL scripts.

Debugging script bugs like you've got is the reason why you put PROMPT commands in your script - it lets' you know where you've got to.

Report message to a moderator

Re: sqlplus hangs when running an SQL script [message #329517 is a reply to message #329514]

Wed, 25 June 2008 11:22

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Perhaps PL/SQL block that "hangs" misses slash character at the end; something like this:

SQL> rem This is OK:
SQL> begin
  2    null;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> rem This is WRONG:
SQL> begin
  2    null;
  3  end;
  4

(and it "hangs" on line 4)

Report message to a moderator

Re: sqlplus hangs when running an SQL script [message #329518 is a reply to message #329516]

Wed, 25 June 2008 11:24

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

It's probably a ';'.

You can get the script to continue by typing ';' and pressing return.

Report message to a moderator

Re: sqlplus hangs when running an SQL script [message #329545 is a reply to message #329514]

Wed, 25 June 2008 14:22

nmaier
Messages: 4
Registered: May 2008

Junior Member

Thanks all!!

I feel a little silly since it was such a boneheaded mistake. But it makes it simple to fix at least! Thanks again.

Nate

Report message to a moderator

Previous Topic:	avoid duplicate runs on dbms jobs
Next Topic:	Use of Oracle Decode function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 02:19:47 CDT 2025