1 2 3 4 Previous Next 50 Replies Latest reply on Jun 29, 2006 7:55 PM by 520886

    SQL worksheet F9 problem

      Try this, paste the following lines into the SQL statement area

      select 8

      select count(*) from user_tables;

      position the cursor on the count(*) line, press F9. the command at the cursor should be executed, but it is not. and ora-00923 error is returned.

      the problem is that the parsing is starting at the first line in the window. it will work if the first line is commented out. the proper way to handle this is to find the command associated with the cursor, and just parse (and execute) that command. that is the difference between run script and execute statement.
        • 1. Re: SQL worksheet F9 problem
          If you select the statement and use F9 it executes correctly. I wanted to let you know that it is not the partial or incorrect statement that is causing the problem. I tested with:

          select * from dual

          select * from dual;

          and get the same error. Without ending the first statement, we don't know where to begin parsing. I will discuss with the developer responsible for the Worksheet but not sure we can do anything about this one.

          -- Sharon
          • 2. Re: SQL worksheet F9 problem
            you might start at a cr/lf or blank line. that is the way i have seen it handled in other programs.

            btw...this is a really good product.
            • 3. Re: SQL worksheet F9 problem
              Barry Mcgillin-Oracle
              We use ; or / to denote the end of the statements. I suppose we could look at the blank lines in there to see if there is a blank one to ignore it, however, in procedural code, the parser is strongly typed and needs terminators for all statements. You statement was interpreted literally, select 8 select * from tab;
              I suppse both of these are getting into preferences about how people like to run their sql. My primary focus is to get this running as closely as possible to sqlplus
              • 4. Re: SQL worksheet F9 problem
                "My primary focus is to get this running as closely as possible to sqlplus"
                SQL*Plus expects a stream of text which has a start from which the context of the following 'characters' can be interpreted.
                The closest way to replicate that for Raptor would be for 'Execute' to start at a logical 'beginning' such as the first character in the first line, the first character in the current selection, or the current cursor position.

                The problem is that you haven't gone for any of those options. Instead Raptor is trying to cater for executing the 'statement' that the cursor is currently in and that means trying to locate the start of that statement. Other GUI query tools simply work back to the last blank line (and that's what many of their users have become accustomed to).

                So when faced with

                create or replace procedure dummy is


                delete from test;


                If you hit Execute when the cursor is in the 'FROM' word, you need to guess whether the start is the 'DELETE' and the statement is a single SQL command,'BEGIN' and the user is running an anonymous PL/SQL block, or the 'CREATE' and the user is creating a database object. All are equally valid interpretations. Now complicate that with potential mis-spellings, multi-line comments, dynamic SQL in user-defined quotes....

                I think it is a mistake to try to replicate the operation of a command-line tool, and you'd be better off making it behave more like the other GUI tools.
                • 5. Re: SQL worksheet F9 problem
                  What GUI tools are you referring to?

                  PL/SQL Developer is a GUI tool which separates SQL statements by semicolon. This is their SQL Window.

                  SELECT 1 FROM DUAL;
                  SELECT 2 FROM DUAL;
                  SELECT 3 FROM DUAL;

                  SELECT 4 FROM DUAL;

                  Their Program Window for PL/SQL runs the entire thing which makes sense since a stored procedure could have many semicolons in it.

                  So, I believe the way Oracle Raptor is doing it is the correct way.
                  • 6. Re: SQL worksheet F9 problem
                    What GUI tools are you referring to?
                    TOAD allows you to run a single statement that contains no blank lines (shift-F9) where other statements are on the same screen (not ended with semicolons).

                    To run a single statement, you only need to have your cursor somewhere in the statement you want to run and press shift-F9 (or press one of the buttons).

                    TOAD also allows you to run an entire window which is a single statement and does contain blank lines (F9).

                    So, for instance, I have the following on my screen:
                    select 'a' from dual

                    select 'b' from dual

                    select 'c' from dual

                    If my cursor is on the 'c' line and I press shift-F9, I get the result 'c'. If my cursor is on the 'a' line and I press shift-F9, I get the 'a' result. If my cursor is on the 'c' line and I press F9, I think I'll get some sort of malformed statement error because it tries to parse everything in the window as one statement.

                    If I have the following:
                    select 'a' from dual;

                    select 'b' from dual;

                    select 'c' from dual;

                    Again, my cursor on the 'c' line, press shift-F9, I get the 'c' result. Press F9, however, and I get a "invalid character" error on the first line because it's still trying to parse everything as one statement.

                    I could, however, have
                    select 'a'

                    from dual

                    Press F9 and I get the 'a' result. Press Shift-F9, I get an error because of the blank line.

                    TOAD also allows you to enter several statements, ended with semicolons, and run as a script. So if I have the same screen as my second example and run it as a script, I get the results for all three lines (on the script output, not as a record set).

                    Having shift-F9 (or the ability to run a single, unterminated statement included with a bunch of others in the same screen) has been a very handy feature. (Possibly a future enhancement for Raptor?)

                    SQL Navigator is similar to TOAD, but you have to select the entire statement you want to run individually, as opposed to the cursor just being in the statement.
                    So, I believe the way Oracle Raptor is doing it is
                    the correct way.
                    Oracle's SQL Worksheet must be intended as the place for writing both SQL and PL/SQL, whereas TOAD has a screen for running SQL statements and can also be used for PL/SQL development, but TOAD also has a special "code editor" specifically for PL/SQL development. SQL Navigator also has a separate editor for PL/SQL development.
                    • 7. Re: SQL worksheet F9 problem
                      The problem appears to be largely related to coping with both SQL and PL/SQL statements.

                      If we look at what SQL*Plus does when entering SQL statements - it uses blank lines as statement terminators.

                      SQL> select 'x' from dual

                      Whereas with PL/SQL, SQL*Plus doesn't use blank lines as statement terminators (either . or /):

                      SQL> begin
                      2 select 'x' from dual;
                      4 .

                      The difference between Raptor's SQL Worksheet and SQL*Plus is that SQL*Plus knows when it is in a PL/SQL statement - the first line is BEGIN / DECLARE and it checks CREATE for PROCEDURE, FUNCTION, PACKAGE. Given that SQL Worksheet is a GUI tool, it doesn't have the luxury of knowing what the first line of the statement is when the user is typing in a line - however, when the user run's a statement it should be able to tell.

                      If SQL Worksheet is meant to be as close as possible to SQL*Plus, I would be suggesting that whenever you run a statement (F9) that is not based on a selection, that it checks to see if there is PL/SQL in the worksheet. If there is no PL/SQL, then blank lines and trailing semi-colons should be used as statement terminators. If there is PL/SQL, then the / should be used as a statement terminator.
                      • 8. Re: SQL worksheet F9 problem
                        PL/SQL developer has the OPTION of 'Autoselecting' the statement to execute or not (and its default is not). If not chosen, and the command text is not explictly selected, it will try to run the whole window.
                        If the option is selected, then it still gives problems with some statements, such as

                        create or replace procedure bx is


                        delete from x;


                        With the statement above, F8 on the 'DELETE' line, offers to delete all rows, rather than create the procedure (ie ignoring the create/replace).

                        select '
                        from dual;

                        Throws it a wobbler as well.

                        Also it's error highlighting gives a much better indication of what it interprets as the 'current' statement (highlighting from the start of the statement to the error position).
                        • 9. Re: SQL worksheet F9 problem
                          I have been thinking about my suggestion on how the statement identification should work in the SQL Worksheet and I don't think I was very clear with what I was meaning.

                          My suggestion is that when the user attempts to run a statement in the SQL Worksheet with the F9 functionality, that the entire current contents of the SQL Worksheet are "parsed" at high level to split the contents into SQL statements (starting with SELECT, INSERT, UPDATE, DELETE, ALTER, etc) and PL/SQL statements (starting with BEGIN, DECLARE, CREATE [OR REPLACE] PROCEDURE/FUNCTION/PACKAGE/etc), looking only for statement terminators - without worrying about syntax. Terminators for SQL statements would be blank lines or trailing semi-colons and for PL/SQL statements would be a . or / on a new line. Once the contents are split into statements, the current statement (as per cursor position) would be selected and run.

                          This would effectively find the same statement as SQL*Plus would be executing at the same point if running the SQL Worksheet contents as a SQL script. The one difference would be actually running a SQL statement without a terminating semi-colon, but that is terminated with a blank line.

                          I think that this would avoid the need for the statement selection to be very good at SQL and PL/SQL syntax (which is where I think the current problems with F9 are coming from) - statements are identified on some basic rules and then all of the parsing is left up to the DB.
                          • 10. Re: SQL worksheet F9 problem
                            "Terminators for SQL statements would be blank lines or trailing semi-colons"
                            Still need to cater for quotes and multi-line comments.

                            I think part of my problem was not using a / terminator for PL/SQL.
                            Perhaps the "Using the SQL Worksheet" could be updated to be more explicit. I think there's an assumption that people will come to Raptor from SQL*Plus. I used TOAD for a long time and have got into its habits, and I suspect that others migrating from TOAD to Raptor will have similar issues.
                            • 11. Re: SQL worksheet F9 problem
                              I definitely wasn't suggesting that this "simpler parsing" for statements should ignore strings or comments - it just seems to me that the statement selection is trying too hard to understand the statements from syntax perspective and therefore sometimes missing the "obvious" from a human perspective.

                              When we are trying to convince some of our non-techies to move to Raptor from TOAD (they largely just do selects in the SQL window and export results), it doesn't look good when they can tell what the SQL statement is, but Raptor can't.
                              • 12. Re: SQL worksheet F9 problem
                                You can try to highlight the SQL statment you want to execute and press F9.
                                • 13. Re: SQL worksheet F9 problem
                                  I know that I can, but I am trying to get the functionality to improve. It is a pain to have to manually select a 40 line statement out of 200 lines in the SQL Worksheet, when it should be able to work it out itself.
                                  • 14. Re: SQL worksheet F9 problem
                                    For SQL statement highlighting it sounds like it could do with an extension to the 'select all' functionality, maybe a 'select part'.

                                    Something like: place cursor at the start of the statement you wanted to run in isolation (regardless of whether it's in a PL/SQL block or as part of a SQL script), click 'select part', then the statement from the cursor location up to the logical end of the statement would be highlighted - it'd remove the need to manually highlight the statement, though I appreciate that the logic of 'what is the end of the statement' remains unresolved.
                                    1 2 3 4 Previous Next