[PAETEC Communications]

Using the Oracle Enhanced Connector I cannot execute CREATE OR REPLACE statements for procedures OR packages. The following SQL put into a SQL editor gives me an “ORA-00900 Invalid SQL statement” error.

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE( in_param IN VARCHAR2 )
AS
BEGIN
DELETE MYSCHEMA.TABLE_A a WHERE a.ID = in_param;
COMMIT;
END;
/

If I remove the first two semi-colons (at the end of in_param and COMMIT), it works. But then looking in TOAD, its not valid and doesn’t compile. But if I open a valid procedure FROM THE DATABASE in MyEclipse, I can successfully run it. When it fails, the error in the logs is:

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2005-11-08 15:50:10.088
!MESSAGE Error processing query: ‘END’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

Apparently, myEclipse is having trouble with multiple semi-colons, or better said, multiple nested statements.

I’d like to Right-Click on a .sql file in my eclipse project, select “Open with MyEclipse SQL Editor”, select a DB connection and click RUN and have it install. My setup is Eclipse 3.1, MyEclipse 4.0, Win2000, 1GB RAM.

[Riyad Kalla]

Can you provide a c-reate table script for an example table you are using this query against? NOTE you will need to break up the sql keyword because our security script stop people from posting real sql. We will then test this internally and see what is going on here.

[PAETEC Communications]

Test Table:

C-REATE TABLE TEST_TABLE
(
  COLUMN_A  VARCHAR2(200),
  COLUMN_B  VARCHAR2(300)                       NOT NULL
);

Test Insert Statement (note, if you execute the sql below all in one sql editor at the same time, the error I describe above occurs due to the “COMMIT;” statement for the same reason mentioned above: multiple statements):

IN-SERT INTO TEST_TABLE( COLUMN_A, COLUMN_B ) VALUES ( 'foo', 'bar'); 
COMMIT;

Test Prodecure (this fails when executed in a MyEclipse SQL editor):

C-REATE OR RE-PLACE PROCEDURE TEST_PROCEDURE ( in_param IN VARCHAR2 ) IS
BEGIN
   D-ELETE FROM TEST_TABLE
   WHERE COLUMN_A = in_param;
   COMMIT;
EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END TEST_PROCEDURE; 

– System Setup ——————————-
Operating System and version: Microsoft Windows 2000
Eclipse version: 3.1.0
Eclipse build id: I20050627-1435
Fresh Eclipse install (y/n): y
If not, was it upgraded to its current version using the update manager?
Other installed external plugins: nope
Number of plugins in the <eclipse>/plugins directory that begin with org.eclipse.pde.*: 8
MyEclipse version: 4.0.1 (buildId: 20050930-4.0.1-GA )
Eclipse JDK version: 1.4.2_05
Application Server JDK version: n/a
Are there any exceptions in the Eclipse log file? nope

If this is a DB related question please answer the following:

RDBMS vendor and version: Oracle 9i (9.2.0.5)
JDBC driver vendor and version, and access type (thin, type-2, etc): Oracle JDBC 1.4 Thin
Connection URL: jdbc:oracle:thin:@dbServer.paetec.com:1521:dbSID

[Riyad Kalla]

I just got notice that someone from the DB team will reply to this today, sorry for the delay.

[PAETEC Communications]

Whats the word? Have they been able to test and reproduce the problem?

[support-michael]

Thanks for this report. Yesterday we replicated the problem in our test environment and have entered a problem report for the dev team to track down. We’ll update this thread once we no when a resolution will be available.

Michael

[PAETEC Communications]

Ok thank you! Do you think this will be fixed in a minor release (4.0.4) or make it into 4.1?

[jae]

does the fix for this issue relate to the this problem as well?

https://www.genuitec.com/forums/topic/closed-advanced-oracle-options/&highlight=

i know this topic was quite similar to what i posted about in above

http://www.myeclipseide.com/PNphpBB2+file-viewtopic-t-9671.html

[PAETEC Communications]

Yes, it sounds like the same problem. It appears to be a problem with the semi-colons. If you remove all but the last semi-colon from your script, it’ll probably work (by work I mean run to completion in myEclipse with no errors) but obviously in your database, the procedure or package will not compile.

To MyEclipse support: any progress on this? An ETA?

Thanks guys!

[Riyad Kalla]

To MyEclipse support: any progress on this? An ETA?

Not yet, items for 4.1 were already committed in the pipeline and we weren’t able to get this in, but I am adding your comments to it so the developer that it gets assigned to has a better idea of what is going on. Sorry for the delay in this guys.

[PAETEC Communications]

This is a pretty big bug, if you ask me. The biggest thing I need this for is we have SQL and DDL statements sourced in our CVS repository, and we’d like to be able to Right-Click Open With.. SQL Editor and then run the script against a connection. What good is a SQL editor if it can only execute single statements?

On a good note, if I open up an EXISTING procedure using the MyEclipse database explorer, I can add a column to a select statement or add procedure variables, add new SQL statements, etc. If I open an EXISTING package, I can add things, procedures, etc. Anything really. So it appears that it CAN work. But if I take that code and copy it into a brand new SQL editor, an error occurs. I can’t see it being too big of a code change to fix considering it works elsewhere.

[Brian Fernandes]

You’ll be glad to know that we ran this this bug into the ground and you should see the fix in our next release which should be out shortly.

We’ll be grateful if you could give it a test run then and let us know if it runs fine for you.

Best regards and thank you for your patience – much appreciated,
Brian.

[PAETEC Communications]

wonderful! I just downloaded the lastest milestone (didnt work). So when I see 4.1 released in a couple days, I will give it a try! Thanks for the quick response!

[PAETEC Communications]

This appears to be fixed in MyEclipse 4.1 M2. I was able to successfully run nested SQL statements (PACKAGE BODY, PROCEDURES, etc) in a new MyEclipse SQL editor.

Thank you!!!

[Brian Fernandes]

Very glad it’s working 🙂

Thank you for hanging in there with us!

Best regards,
Brian.

[Author]

Posts