Re: [Squirrel-sql-users] Script not executed, although Oracle SQLPlus could do it

[Gerd W. <bir...@t-...>]

LeO Welsch wrote:
> 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?
I suspect you didn't see the buttons. They are far on the left side of the main 
frame tool bar. If your screen solution isn't high enough and/or you are using a 
look and feel that paints big buttons they where probably hidden. The handling 
of those windows are a mess anyway. I just completely worked them over. See 
change log:
http://squirrel-sql.cvs.sourceforge.net/*checkout*/squirrel-sql/sql12/doc/changes.txt

@Rob: Would it be possible to create a snapshot of the main branch for Leo? 
Thanks in advance.

What should happen is: If you open the DB Output window and execute the for 
example the following PLSQL code:

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello PLSQL world');
END;

After hitting the refresh button of th DB Output window you should see "Hello 
PLSQL world".

Gerd


> 
> :)
> 
> LeO
> 
> -------- Original-Nachricht --------
> Datum: Wed, 15 Nov 2006 08:56:40 -0800
> Von: "Matthew Dahlman" <mda...@su...>
> An: "\'LeO Welsch\'" <le...@gm...>, squ...@li...
> 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: squ...@li...
>> [mailto:squ...@li...] On Behalf Of LeO
>> Welsch
>> Sent: Wednesday, November 15, 2006 5:57 AM
>> To: squ...@li...
>> 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!
>