[PAETEC Communications]

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?

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 Communications]

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

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?

[Riyad Kalla]

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 Communications]

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!

[Brian Fernandes]

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.

[Riyad Kalla]

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 Communications]

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 Communications]

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 Communications]

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.

[Riyad Kalla]

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.

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

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.

[Brian Fernandes]

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 Communications]

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

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…

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;
/

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:

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.

[Brian Fernandes]

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 Communications]

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

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.

[Author]

Posts