Re: [Squirrel-sql-users] Can't create Oracle Procedure in Squirrel

[John H. <jh...@im...>]

On Thu, 7 Mar 2013, Drav Sloan wrote:

> urbanmojo wrote:
>> Hi:
>>
>> I am using Squirrel as the client tool using JDBC RAC driver against Oracle
>> 11.1. I am trying to replace an existing Oracle procedure with:
>>
>> CREATE OR REPLACE PROCEDURE MY_PROC
>> AS
>> BEGIN
>> DELETE FROM table1 WHERE IEX_DATE >= (select max(idate) from table1) - 20;
>> DELETE FROM table2 WHERE IEX_DATE >= (select max(idate) from table2) - 20;
>> DELETE FROM table3 WHERE IEX_DATE >= (select max(idate) from table3) - 20;
>> DELETE FROM table4 WHERE IEX_DATE >= (select max(idate) from table4) - 20;
>> END;
>>
>> It fails with the error:
>> Query 1 of 5, Rows read: 0, Elapsed time (seconds) - Total: 0.047, SQL
>> query: 0.047, Building output: 0
>> 6,326 Row(s) Deleted
>> Query 2 of 5, Rows read: 0, Elapsed time (seconds) - Total: 0.672, SQL
>> query: 0.672, Building output: 0
>> 12,097 Row(s) Deleted
>> Query 3 of 5, Rows read: 0, Elapsed time (seconds) - Total: 1.25, SQL query:
>> 1.25, Building output: 0
>> 5,040 Row(s) Deleted
>> Query 4 of 5, Rows read: 0, Elapsed time (seconds) - Total: 0.531, SQL
>> query: 0.531, Building output: 0
>> Error: ORA-00900: invalid SQL statement
>>
>> SQLState: 42000
>> ErrorCode: 900
>> Error occured in:
>> END
>> What am I doing wrong? I've tried different things but nothing seems to give
>> me an executable procedure.
>
> Terminate the declaration with a trailing / as thus:
>
> CREATE OR REPLACE PROCEDURE MY_PROC
> AS
> BEGIN
> DELETE FROM table1 WHERE IEX_DATE >= (select max(idate) from table1) - 20;
> DELETE FROM table2 WHERE IEX_DATE >= (select max(idate) from table2) - 20;
> DELETE FROM table3 WHERE IEX_DATE >= (select max(idate) from table3) - 20;
> DELETE FROM table4 WHERE IEX_DATE >= (select max(idate) from table4) - 20;
> END;
> /

The trailing slash is (IIRC) specific to the Oracle client tools, not part 
of the base Oracle SQL syntax.

The problem is that Squirrel is interpreting the ";" as a query separation 
marker and sending the individual pieces of the above to Oracle as 
separate query commands.

To fix:

Open up the Session Properties dialog in Squirrel, go to the SQL tab, 
locate the "Statement Separator" option and change it to something that 
doesn't appear in the code you're trying to run to generate the SP, 
perhaps "|||".

Then you should be able to create your SP.

You'll want to switch the statement separator back to ";" for normal 
interactive use.

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Failure to plan ahead on someone else's part does not constitute
   an emergency on my part.                 -- David W. Barts in a.s.r
-----------------------------------------------------------------------
  3 days until Daylight Saving Time begins in U.S. - Spring Forward