Re: Script not executed, although Oracle SQLPlus could do it

Hi LeO,

This is a common question. I think this answer from Maury Hammel should help
you out:

"I found that the problem was that the code had ';' characters inside of it.
SQuirreL doesn't know anything about procedures or code blocks, so it scans
the single block of code for the ';' character, and breaks the block into
multiple SQL statements.  It then tries to execute each statement
separately, which the database engine chokes on.

Try changing the Session SQL property 'Statement Separator' from ';' to
something else ('/' for example), and then try your procedure again like so:

create procedure test1
language sql
begin
 <sql statements>;
end;
/

Of course, you'll have to change the property back to ';', after creating
your procedure if you want the ';' to remain the termination character for
single SQL statements.

Maury"

Regards,
Matt 

-----Original Message-----
From: squirrel-sql-users-bounces@xxxxxxxxxxxxxxxxxxxxx
[mailto:squirrel-sql-users-bounces@xxxxxxxxxxxxxxxxxxxxx] On Behalf Of LeO
Welsch
Sent: Wednesday, November 15, 2006 5:57 AM
To: squirrel-sql-users@xxxxxxxxxxxxxxxxxxxxx
Subject: [Squirrel-sql-users] Script not executed,although Oracle SQLPlus
could do it

I am surprised that I cannot execute a script on the SQL although I can do
it on the SQL-Plus from Oracle. Has anybody an idea, why that's so?

The included script which fails with the very first line. If I delete it,
then I get other ORA-Errors, e.g. below.

Since the ORA-Errors do not refer to any kind of invalid table, I guess
there is something wrong with the transfer-process (Java => Oracle).

Has anybody an idea how to make it the right way, so the parsing itself from
Oracle does not fail any longer?

:)

LeO




Error: java.sql.SQLException: ORA-06550: line 3, column 24:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:

   := . ( @ % ; not null range with default character , SQL State: 65000,
Error Code: 6550 Error occured in:
begin
             declare
                  v_comparetime                 date

or

Error:   ORA-00905: missing keyword

SQLState:  42000
ErrorCode: 905

or

Error: java.sql.SQLException: ORA-00900: invalid SQL statement , SQL State:
42000, Error Code: 900

or

Error: java.sql.SQLException: ORA-00900: invalid SQL statement , SQL State:
42000, Error Code: 900 Error occured in:
LOOP
                                  v_comparetime := v_comparetime + 1/48

--
"Ein Herz für Kinder" - Ihre Spende hilft! Aktion: www.deutschlandsegelt.de
Unser Dankeschön: Ihr Name auf dem Segel der 1. deutschen America's
Cup-Yacht!


-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV