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

[Drav S. <hol...@re...>]

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.