MyEclipse :: View topic - SQLException running CREATE OR REPLACE statements

MyEclipse: SQLException running CREATE OR REPLACE statements

PAETEC - Nov 11, 2005 - 06:11 PM
Post subject: SQLException running CREATE OR REPLACE statements

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.

support-rkalla - Nov 11, 2005 - 06:40 PM
Post subject:

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 - Nov 14, 2005 - 04:32 PM
Post subject: Test scripts and my system setup

Test Table:

Code:

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):

Code:

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

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

Code:

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

support-rkalla - Nov 16, 2005 - 02:23 PM
Post subject:

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

PAETEC - Nov 18, 2005 - 05:55 PM
Post subject:

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

support-michael - Nov 18, 2005 - 09:53 PM
Post subject:

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 - Nov 22, 2005 - 03:38 PM
Post subject:

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

jgangemi - Nov 29, 2005 - 03:21 PM
Post subject:

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

http://www.myeclipseide.com/index.php?name=PNphpBB2&file=viewtopic&t=10033&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 - Dec 02, 2005 - 02:32 PM
Post subject:

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!

support-rkalla - Dec 02, 2005 - 04:14 PM
Post subject:

Quote:

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 - Dec 14, 2005 - 05:22 PM
Post subject:

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.

Support-Brian - Dec 14, 2005 - 05:37 PM
Post subject:

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 - Dec 14, 2005 - 06:30 PM
Post subject:

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 - Dec 21, 2005 - 05:49 AM
Post subject:

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

Support-Brian - Dec 21, 2005 - 08:02 AM
Post subject:

Very glad it's working :)

Thank you for hanging in there with us!

Best regards,
Brian.

PAETEC - Jan 11, 2006 - 09:22 PM
Post subject:

Ok, maybe this should be re-opened...we have a couple more problems with the SQL Editor. First, I have pretty much the same set up as I mentioned before, except now I am on Windows XP. Still Eclipse 3.1 and but now I am using the 4.1 M2 release, because it was patched.

First problem, when I have some code that contains comments using the /* and */ notation, they are completely removed from the code when it gets into the database. Although comments beginning with "--" still make it through. My example below does this (the header makes it into the database fine, minus the /* */ comments)

Secondly, when I run the SQL below, I get an invalid SQL error. It says
Error processing query: ' --end procedure2 END PK_TEST_PACKAGE'
If I comment out the IF and END IF lines, I get a slightly different error in a pop up telling me I am "missing an END statement" and asks if I want to continue.

Lastly, ALL of our PL/SQL code in CVS is saved out with "/" characters after the END statements of package headers and bodies, because in SQL plus scripts, this is required to execute the statement. We cannot remove the trailing "/" characters from the end of ALL of our scripts just because MyEclipse cannot handle them. Can the SQL editor be updated to handle these?

Code:

C-REATE OR RE-PLACE PACKAGE PK_TEST_PACKAGE AS
/* Procedure 2 - this comment will be removed */
PROCEDURE PP_TEST_PROCEDURE2( param IN VARCHAR2);
END PK_TEST_PACKAGE;

C-REATE OR RE-PLACE PACKAGE BODY PK_TEST_PACKAGE AS

PROCEDURE PP_TEST_PROCEDURE2( param IN VARCHAR2) IS
test_var VARCHAR2(2000);
BEGIN
-- This comment remains
IF test_var = 'blahblah' THEN
SELECT 'foo' INTO test_var FROM DUAL;
RETURN;
END IF;

/*** THIS COMMENT WILL BE REMOVED ***/
SELECT 'foobar' INTO test_var FROM DUAL;
RETURN;
END PP_TEST_PROCEDURE2; --end procedure2
END PK_TEST_PACKAGE;

The code above (with /'s after the END PK_TEST_PACKAGE; statements) should run and get into the database as-is. When I run it in TOAD 8.6, it runs fine.

PAETEC - Jan 11, 2006 - 09:29 PM
Post subject:

Oh, and one more thing....the end of alot of SQL statements looks like the following....

Code:

END MY_PACKAGE;
/
SHOW ERRORS;

GRANT EXECUTE ON MY_PACKAGE TO SOME_USER;

Can this be supported? I believe the SQL Editor should just execute the SQL just like SQL Plus does. Is that the goal?

support-rkalla - Jan 11, 2006 - 09:57 PM
Post subject:

PAETEC,
I will file these issues to get investigated but the SQL Editor is lower priority as the requests for improvements/fixes are primarily focused on other portions of ME right now.

I appologize, I know waiting for a fix can be such a PIA in the interm, is TOAD a feasible workaround for you and your team right now?

PAETEC - Jan 12, 2006 - 02:24 AM
Post subject:

Absolutely, Toad is currently our database administration and interfacing tool of choice. One of the things we liked about purchasing ME was that a good amount of the time if we wanted to run a quick SQL script we have stored in CVS, we could just open and run it in Eclipse/MyEclipse without having to open TOAD. We'll continue to do that if we have to, obviously, but this would definitely be a much welcomed feature, considering its one of the reasons we purchased MyEclipse. All the other features are very nice as well.

We appreciate all the effort you can put into fixing this significant bug. Hopefully you can reproduce it and we are not just seeing things. :)

Thanks!

Support-Brian - Jan 12, 2006 - 03:22 PM
Post subject:

We did have a look at your issues and were able to reproduce them locally.

What I didn't understand was what you meant by your next post - could you elaborate what exatly you need and why it isn't working now?
I'm also a little rusty so what is the significance of the '\' between END MY_PACKAGE and SHOW ERRORS?

Once again, we appreciate your support :)
Best,
Brian.

support-rkalla - Jan 15, 2006 - 09:41 PM
Post subject:

PATEC,
It looks like your problems #2 and #3 have been fixed and should be available in 4.1, although #1 (the comments) is being tracked separately and isn't fixed yet.

PAETEC - Jan 17, 2006 - 02:22 PM
Post subject:

Thank you for looking into that, I am looking forward to using 4.1. Hopefully the comments fix can make it in as well, cuz thats kinda of a pain. We have CVS comments in our SQL for instance that really can't be removed. :)

The "/" is needed after a PL/SQL block in SQL*Plus. So if I type something in that starts with "BEGIN" and ends with "END;", such as a CREATE OR REPLACE statement for example, once I type it in, it will not execute. I have to type "/" to get it to execute.

Thats if I run a script through SQLPlus...if I run it in TOAD, it will just handle it and run with no problem although it does not need a trailing "/". I would just prefer it if the SQL Editor would just ignore it if it doesn't need it, much like TOAD does.

SHOW ERRORS is another database command that will show you any errors from the last statement that was executed...its helpful for debugging. Or DBAs prefer it.

PAETEC - Jan 17, 2006 - 02:26 PM
Post subject:

Oh, and the GRANT statement...sorry. If we have a procedure or package in our version control that always needs to be accessed by a certain schema/user, we generally put the GRANT statement at the end of the SQL script for the package/procedure. This helps so that if anyone runs or re-runs the script, the necessary permissions are always applied.

I figure its just another SQL statement, so the sql editor should be able to just continue on and run that too after executing the package statement above it.

THANKS!

PAETEC - Jan 24, 2006 - 03:59 PM
Post subject:

Downloaded 4.1, looking good. Unfortunately, multi-line SQL comments are still removed. It handles the slashes much better now. And if I add a GRANT statement to the end, it handles that fine as well. Very nice.

Although a problem I see is it appears to add a slash to the end of the last END; statement no matter what. So if I have no slash at the end of my last statement, all is well. But if there is a slash already, it attempts to append another one which causes a SQL Exception. I think it should check to see if the script already ends with a slash.

Besides that, lookin better. Thanks for all your work guys.

support-rkalla - Jan 24, 2006 - 05:08 PM
Post subject:

Quote:

The comment thing was tricky so it was going to take more time. Lot's of regexp work and such.

Quote:

Although a problem I see is it appears to add a slash to the end of the last END; statement no matter what. So if I have no slash at the end of my last statement, all is well. But if there is a slash already, it attempts to append another one which causes a SQL Exception. I think it should check to see if the script already ends with a slash.

Uh oh, we'll look into it ASAP.

Support-Brian - Jan 24, 2006 - 06:17 PM
Post subject:

PAETEC,

Multiline comments are something we'll be looking into for the next release, we appreciate your patience and hanging in there with us.

Regarding the appending of the slash, we did add some slash support, but I can't think of any place where we actually append a slash if there isn't one already.
Could you break it down for me with an example? Also, what do you mean by "append a slash" does one appear in your editor or do you see an error regarding the slash in the error view- what?

FYI: You should be able to post your sql to the forum without changing it in any way, we changed our security system to let that through :)

Best,
Brian.

PAETEC - Jan 29, 2006 - 02:04 AM
Post subject:

Ok, the following code works fine when run in a SQL editor....

Code:

CREATE OR REPLACE PACKAGE PK_TEST_PACKAGE1 AS
PROCEDURE PP_TEST_PROCEDURE1( param IN VARCHAR2);
END PK_TEST_PACKAGE1;
/
CREATE OR REPLACE PACKAGE PK_TEST_PACKAGE2 AS
PROCEDURE PP_TEST_PROCEDURE2( param IN VARCHAR2);
END PK_TEST_PACKAGE2;

but if I add a slash to the end...like so...

Code:

I receive a popup with the super descriptive (like ALL Oracle errors) error:
ORA-24344: success with compilation error
ORA-06512: at line 1

To see what was run, I pulled up the SQL History View, and saw this for the last execution:

Code:

CREATE OR REPLACE PACKAGE PK_TEST_PACKAGE2 AS
PROCEDURE PP_TEST_PROCEDURE2( param IN VARCHAR2);
END PK_TEST_PACKAGE2;/;

So I guess I mis-spoke in my last post. Its not appending a slash, rather, if the slash is at the end of the file, it attempts to run it like any other old statement as can be seen in the above history statement.

If I add any valid statement after the last slash, such as GRANT EXECUTE ON PK_TEST_PACKAGE2 TO SOMEUSER then it will work fine (obviously since the script no longer ends with a slash.

Oh, and a SHOW ERRORS; statement after each / causes errors still (it actually errors with an ORA-00900: invalid SQL statement and actually puts an entry in the Eclipse Error Log. I figured it would though since I don't think you are running code as if it were in a SQL*Plus prompt. But if you can make that work, that'd be cool.

Keep up the good work.

Support-Brian - Jan 30, 2006 - 03:03 PM
Post subject:

I was able to reproduce the slash problem, bad oversight on our part. This will certainly be fixed in 4.1.1.

Regarding Show Errors - did you have a look at our Oracle Source Errors view? That should probably remove/reduce your need to execute the SHOW ERRORS.
Note that the line numbers in this error view currently do not necessarily correspond to the line numbers in the editor, but they are relative to the start of the procedure (will be fixed).

Best,
Brian.

PAETEC - Jan 31, 2006 - 06:21 AM
Post subject:

Ok, great (on the fix for 4.1.1).

Can you give me an example of a piece of SQL code that would make that view populate with data? When I run SQL, such as (notice the mis-spelled parameter type):

Code:

CREATE OR REPLACE PACKAGE PK_TEST_PACKAGE2 AS
PROCEDURE PP_TEST_PROCEDURE2( param IN VARHAR2);
END PK_TEST_PACKAGE2;

I get a popup "Success with compilation error". But nothing appears in the Oracle Source Errors view. I am using the Oracle connector because I can see a ton of stuff in the DB Explorer. I am connected to a Oracl 9i db.