|
From: Robert M. <rob...@gm...> - 2013-03-08 04:18:28
|
To clarify, the work-around that John describes was the only way to correct the problem in the past. However, the Oracle plugin has a query tokenizer that now recognizes "/" as an "end-of-statement operator", much like SQL-Plus. So after installing the Oracle plugin, and without changing the statement separator, you should be able to execute SQL statements that have embedded semi-colons when the whole statement ends with a "/" on its own line. My goal with these more recent changes to the plugin was to give SQuirreL a way to mimic the behaviour of SQL-Plus as much as possible. Rob On Thu, Mar 7, 2013 at 5:29 PM, John Hardin <jh...@im...> wrote: > 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 > > > ------------------------------------------------------------------------------ > Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester > Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the > endpoint security space. For insight on selecting the right partner to > tackle endpoint security challenges, access the full report. > http://p.sf.net/sfu/symantec-dev2dev > _______________________________________________ > Squirrel-sql-users mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > |
