Home » SQL & PL/SQL » SQL & PL/SQL » sqlplus hangs when running an SQL script (SQLPlus shipped with Oracle 10g, WinXP)
sqlplus hangs when running an SQL script [message #329514] Wed, 25 June 2008 11:17 Go to next message
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

Re: sqlplus hangs when running an SQL script [message #329515 is a reply to message #329514] Wed, 25 June 2008 11:20 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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.
Re: sqlplus hangs when running an SQL script [message #329516 is a reply to message #329514] Wed, 25 June 2008 11:22 Go to previous messageGo to next message
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.
Re: sqlplus hangs when running an SQL script [message #329517 is a reply to message #329514] Wed, 25 June 2008 11:22 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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)
Re: sqlplus hangs when running an SQL script [message #329518 is a reply to message #329516] Wed, 25 June 2008 11:24 Go to previous messageGo to next message
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.
Re: sqlplus hangs when running an SQL script [message #329545 is a reply to message #329514] Wed, 25 June 2008 14:22 Go to previous message
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
Previous Topic: avoid duplicate runs on dbms jobs
Next Topic: Use of Oracle Decode function
Goto Forum:
  


Current Time: Sat Dec 03 07:45:00 CST 2016

Total time taken to generate the page: 0.10596 seconds