Can't create Oracle Procedure in Squirrel

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't create Oracle Procedure in Squirrel

urbanmojo
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.
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

Drav Sloan-4
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.

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

urbanmojo
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.
>


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

Dennis Benzinger
In reply to this post by urbanmojo
Hello!

How are you executing the command? Looks like each DELETE gets send
individually to your database.

I don't have access to a Squirrel installation at the moment but IIRC
there is a command to run all selected code in one piece.


HTH and best regards,
Dennis Benzinger

Am 07.03.2013 16:01, schrieb urbanmojo:

> 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.
>
>
>
> --
> View this message in context: http://squirrel-sql-client.10976.n7.nabble.com/Can-t-create-Oracle-Procedure-in-Squirrel-tp3082.html
> Sent from the Users mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> 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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

Robert Manning
In reply to this post by urbanmojo
You need the Oracle plugin for SQuirreL. Go to Help -> Software Update -> Check(button) -> Ok(button)
Then find the artifact called oracle-assembly.zip and click on the action value of "NONE" and change it to install.

After its downloaded you will need to restart and agree to install the update.

Rob

On Thu, Mar 7, 2013 at 10:01 AM, urbanmojo <[hidden email]> 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.



--
View this message in context: http://squirrel-sql-client.10976.n7.nabble.com/Can-t-create-Oracle-Procedure-in-Squirrel-tp3082.html
Sent from the Users mailing list archive at Nabble.com.

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

John Hardin
In reply to this post by Drav Sloan-4
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/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

Robert Manning
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 <[hidden email]> 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/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't create Oracle Procedure in Squirrel

John Hardin
On Thu, 7 Mar 2013, Robert Manning wrote:

> 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.

Thanks for the clarification. I plead blissful ignorance of the current
state of the plugin through not having needed to talk to 'Orrible (via
Squirrel or any other direct query tool) for the last 7-ish years.

--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users