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

Hi Matt,

Seems like I am still misunderstanding things. (Reveals that I am not the 
DB-hacker ;)

I have done so, as requested, now I have my Stored Procedure - which I did not 
want, but if that's the way, I have to handle it.

BUT when I execute it (with 'call Test();'), I get no output at all. It should 
write lines to the Output (instead of a created Table), but these are not 
displayed anyhow. 

Any hints, how I could retrieve them?

:)

LeO

-------- Original-Nachricht --------
Datum: Wed, 15 Nov 2006 08:56:40 -0800
Von: "Matthew Dahlman" <mdahlman@xxxxxxxxxxxx>
An: "\'LeO Welsch\'" <leo_zen@xxxxxx>, squirrel-sql-users@xxxxxxxxxxxxxxxxxxxxx
Betreff: RE: [Squirrel-sql-users] 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!

-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

-------------------------------------------------------------------------
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