Re: Trouble executing storedprocs in SQuirreL

Rob Cope wrote:
> I've never had any luck running anonymous PL/SQL blocks outside  
> Oracle's tools (though I would love info on getting it to work, if it  
> can be done).
> 
> For your example, you could try this instead:
> 
>       select DELETE_BY_LOAN('123435') from dual;

That will only work if DELETE_BY_LOAN is a function that returns a 
value.  If it is a procedure, you'll get an error.

>>      begin DELETE_BY_LOAN('123435'); end;
>>
>> And then I press Ctrl-enter.  When I do this, it says:
>>
>> ------------
>> Error: java.sql.SQLException: ORA-06550: line 1, column 44:
>> PLS-00103: Encountered the symbol "end-of-file" when expecting one of
>> the following:
>>

The problem here is that SQuirreL, by default, uses the semi-colon as a 
terminator.  So it is breaking up the begin/end block into two 
statements, which Oracle chokes on.  In order to get SQuirreL to send 
the entire begin/end block as a single statement, you need to go to the 
SQL tab in the Session Properties dialog, and change the "Statement 
Separator" to something other than an semi-colon.  What I usually did 
was change it to the / (slash) character.  Then you can run the 
procedure with the following:

begin delete_by_loan('12345'); end/

(I can't remember for sure, but you may still need the semi-colon after 
the "end" but before the slash)


Maury

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/