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

[Blair M. <cra...@gm...>]

That still gives the same error. How about the semi-colons--is that correct?

On 3/7/13 11:06 PM, 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;
> /
>
> Regards
>
> D.
>