Package body opens with syntax error in Toad 10.1

Toad for Oracle
1

This weekend we ran into an issue with a package body that wouldn’t open in the Toad 10.1.0.7 or 10.1.1.8 editor without reporting a syntax error. It would open in Toad 9.6.1.1 and 9.7.2.5. This appears to be an editor issue, as the error shows up as soon as the file is loaded, even if there is no DB connection. The package compiled just fine with Toad 9.

As the real package body was big and cryptic, I decided to strip away as much as possible to see if I could determine specifically what was causing the error. Below you’ll find two minimal package bodies that, if saved to separate files, will hopefully illustrate when loaded by Toad 10.1. They should be functionally the same (albeit meaningless), with only slightly different formatting within the SELECT statement.

has_error_in_10_1.pkb

2

I seem to have lost most my indention in the code examples. If you need my nicely formatted files, I’ll provided them.

-Rick

3

Hello,

I ran this through the current beta parser and it doesn’t like this line:

SUM( 123 )

Error: [Error] Syntax check (8: 26): ERROR line 8, col 26, ending_line 8, ending_col 26,Expecting: ) , -or- AS CANONICAL COMPATIBILITY identifier USING -or- OR -or- AND -or- BETWEEN IN LIKE LIKE2 LIKE4 LIKEC MEMBER SUBMULTISET -or- -or- ! != <> = > >= ^ ^= IS NOT -or- + - || -or- * / MOD REMAINDER -or- ** -or- (+) AT DAY MULTISET YEAR -or- % . [ -or- FROM IGNORE KEEP OVER RESPECT WITHIN -or- (

Is this what you see?

I’m far from a PL/SQL expert so I don’t know if this syntax is correct or not, but I’ll send it to our parser guy.

Thanks,
Brad

4

I ran the following in both SQL*Plus and Toad (F9)

5

SQL Plus will interpret that slash as ‘end of statement’, but Toad
should be able to handle it.

6

Hm, on 2nd thought, I take it back. Toad looks for a slash on a line by itself
too. That’s the problem.

Try it with something simple like

select

1 / 4

from dual

vs

select

1

/

4

from dual

7

But once you execute it, it will be under the control of SQL*Plus or the Oracle
client or something so I would think it doesn’t matter if Toad can handle
since it will blow up on execution. So maybe Toad should not be able to handle
it.
image001.jpeg

8

Not sure I agree Erwin. SQLPlus does do some client side stuff independent of
the database, so if the database will accept the syntax and SQLPlus will not,
that doesn’t mean it’s wrong.

If Toad allows it, and the database executes it, then that’s an example of
SQL*Plus NOT being the gold standard…

9

Good call.

Execute using F9. Unless you come across an example that will not work with F9,
F9 is the preferred method. In time you will terminate all statements in the
Editor properly as well. I will say that if you can get used to executing with
F9 then you will encounter far fewer execution issues. Shift+F9/Ctrl+Enter has
its place, but in most cases F9 can be used.

10

Arrrrggggg – did someone mention Gold Doubloons ??? Shiver me timbers, but
I always get excited regarding gold ….

11

This is (actually: was) a bug in the parser. Indeed this has nothing to see with
Sql*Plus settings, it’s inside PL/SQL.

Fixed in the upcoming QP5 version 5.145.

Andre

12

SQLPlus may not be the Gold Standard but if you are in a situation where
developers use Toad but QA, Production or PROMOTE-TO-PRODUCTION use SQLPlus
then there may be a problem if it works in Toad but not in SQL*Plus
image001.jpeg

13

Interesting…

I was curious so I attempted to create a basic procedure in SQL Plus:

CREATE OR REPLACE test_proc AS

. num_tmp number;

BEGIN

. SELECT 1/1 INTO num_tmp FROM DUAL;

END;

Without the leading dots of course, they’re in there to preserve the
indenting from Yahoo!

Anyways, that worked as expected. I then attempted to create the same from the
command line with the dividing symbol on its own line and it failed as expected.

However, when I entered the editor from SQL Plus on the good build and moved the
dividing symbol to its own line, saving and exiting, SQL Plus accepted it:

1 create or replace procedure test_proc as

2 num_tmp number;

3 begin

4 select 1

5 /

6 1 into num_tmp from dual;

7* end;

XAD>/

Procedure created.

Very curious. I’d wager it’s a slight difference between
what’s allowed from a SQL editing perspective vs a PL SQL editing
perspective.

Roger S.

14

Right, but that would be a known requirement, and testing of your code would
include making sure it also work in SQL*Plus…not to beat a dead horse or
anything J

15

Consider this:

Case 1: I’m type some code in Sql*Plus editor (actually notepad), and then I
enter a slash. The procedure compiles fine.

SQL> ed

Wrote file afiedt.buf

1 create procedure p105

2 is

3 a number;

4 begin

5 a:= 2

6 /

7 3;

8* end;

SQL> /

Procedure created.

Now I enter the text lines one by one. As soon as I have entered the division
sign then SqlPlus sends the unfinished block to Oracle.

SQL> create procedure p106

2 is

3 a number;

4 begin

5 a := 2

6 /

Warning: Procedure created with compilation errors.

SQL>

The parser bug was that at times a whole block (like the on top) was wrongly
chunked on the slash.

Andre

16

More:

Entering line by line: (please note the leading spaces in line 6)

SQL> set sqlpluscompat 8.1.7

SQL> create procedure p114

2 is

3 a number;

4 begin

5 a := 2

6 /

7 3;

8 end;

9 /

Procedure created.

SQL>

The “old” SqlPlus behavior was that a slash preceded by spaces was not
considered as a terminator.

Sqlpluscompatibility does not influence Oracle when sending a full block to the
server.

Andre

17

Avaast me hearties & scurvy dogs alike,

(aka morning all!)

This is an interesting thread. I’m sticking my nose in and adding my own £0.02.

In SQL*PLus there are two buffers, one for SQL statements (INSERT, DELETE etc) and one for PL/SQL.

When you type DECLARE or BEGIN or CREATE OR REPLACE {PROCEDURE|FUNCTION|PACKAGE [BODY]} etc, you enter the PL/SQL mode and use the PL/SQL buffer for all your typing. This is how you are able to enter a semicolon followed by ENTER and not have that statement executed - which is what happens in the SQL buffer.

To exit from the PL/SQL buffer, you enter a slash as the first character on the line, followed by any number of spaces that you like, followed by ENTER.

Alternatively, to exit and throw away the buffer contents, type a full stop as the first character - trailing characters are ignored.

When you hit ENTER after that slash in column 1, Oracle sends the PL/SQL buffer’s contents top the PL/SQL engine to be parsed and hopefully, executed.

The use of two buffers is a good idea as far as I’m concerned. Another database I use (Firebird/Interbase) has a single buffer. In order to type in Firebird’s equivalent of PL/SQL, I have to change the terminator character:

SET TERM “^”;

…
SELECT stuff
FROM table
INTO variable
WHERE condition;
…
^

When the interpreter sees the final ‘^’ character, it know that I’m done typing and executes the contents of the buffer.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

18

When you type DECLARE or BEGIN or CREATE OR REPLACE {PROCEDURE|FUNCTION
|PACKAGE [BODY]} etc, you enter the PL/SQL mode and use the PL/SQL buffer for
all your typing. This is how you are able to enter a semicolon followed by ENTER
and not have that statement executed - which is what happens in the SQL buffer.

To exit from the PL/SQL buffer, you enter a slash as the first character on the
line, followed by any number of spaces that you like, followed by ENTER. <

This is just the fun. In fact SqlPlus has nothing against sending a whole block
of PL/SQL containing solitaire slashes, for instance by editing them using the
edit command and then entering a slash to execute them. But if you type the text
in line by line then you are in SqlPlus command mode, and then a slash just
means: execute it! Sql*Plus does not know if a slash is a division sign
because it does not parse the PL/SQL content.

Just for clarity, the SUM(123) error at the top of this thread was due to the
parser error described earlier. The parser was wrongly thinking that the slash
on the line by itself (the division sign) was a separator. Then the parser got a
second statement starting with SUM(123) ... which it did not recognize.

So I believe that the parser fix should solve the problem.

Thanks,

Andre

19

Wow.

Thank you all for your thought and comments.

-Rick

20

Hmm, I do NOT believe anymore that the parser fix solves everything. Here’s
why.

Create a script crpr.sql with the following content:

create procedure p2

is

a number;

begin

a := 2

/

3;

end;

/

Now run it in Sql*Plus:

SQL> @crpr

Warning: Procedure created with compilation errors.

3* a number;

SP2-0042: unknown command “end” - rest of line ignored.

create procedure p2

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

What happens? The script is executed in “command mode”, not in block mode,
in other words, Sql*Plus did not send it to Oracle using OCI or by any other
means. So that means that a solitaire slash WILL be interpreted as a separator.

Let’s look at the buffer after the above execution:

@ed

create procedure p2

is

a number;

begin

a := 2

/

This is the upper part of the procedure only, hence the compilation errors. Then
SqlPlus encounters the second slash and it re-executes the (partial) create
procedure, hence the ORA-00955.

So what can we conclude? Toad and/or the parser should interpret a slash as a
terminator if it really want to run in SqlPlus compatibility mode. However,
people not using SqlPlus may not always appreciate the “division sign is
separator” behavior. So ideally Toad should be capable of running in a
non-SqlPlus aware mode as well.

The parser fix is fine for consistency in non-SqlPlus mode, however the parser
should also be capable of emulating the SqlPlus behavior if requested.

Question is: which of both modes does Toad support (or want to support) ?

Thanks,

Andre