Toad World® Forums

detection of embedded comments within a hint text

All,

Today we ran into the following issue. When Oracle encounters a hint as indicated by a /*+ comment with an embedded – indicating an embedded comment on the hint,
then the package will compile ok, but at execution time we get an Oracle error: “ORA-00921: unexpected end of SQL command”

It’s kind of nasty of Oracle to compile without warning, yet throw the exception at execution time…

Since we’re on Oracle 9, there’s no point in filing this issue with Oracle.

So, how big an issue would it be to have the formatter issue a warning?

Working your way back from the Oracle error to find the exact error cause has taken us quite some time :frowning:
When executing the very same statment interactively, using F9, it does execute ok.

Would you regard this as a reasonable request? Any alternatives for preventing this type of error?
I really would like to have early detection of this error, as it may remain lurking in code
that is rarely executed; only to raise its ugly head when the code is actually executed.

Oh - the reason we’re putting in comments is, this program is pretty hefty (some 60K lines in 12 packages)
when looking at an issue, using the session monitor, we’d like to have an identifyig comment in the query.
We found that only the hint is displayed in the session manager, not ‘normal’ comments. So we moved
the identifiyng comments inside the hint, then got into the ORA-00921 issue)

Kind regards,
Abe Kornelis

Hi Abe,

Could you post an example of problematic code (perhaps simplified) please?

Thanks,
Andre

André,

No problem.

Select /*+choose – sample query */ * from dual;

Will execute interactively without problem.
When embedded (please add into clause) in a procedure it will compile cleanly (on my Oracle 9 system)
but will fail to execute with the aforementioned error message.

Abe

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 18 21:01:01 2016

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 z VARCHAR (1);
3 BEGIN
4 SELECT /*+choose
5 – sample query
6 /
7 * INTO z FROM DUAL;
8
END;
9 /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 z VARCHAR (1);
3 BEGIN
4 SELECT /*+choose
5 – sample query /
6 * INTO z FROM DUAL;
7
END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
ORA-06512: at line 4

SQL>

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 z VARCHAR (1);
3 BEGIN
4 SELECT /*+choose – sample query /
5 * INTO z FROM DUAL;
6
END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
ORA-06512: at line 4

SQL>

If we agree on this Oracle bug then I understand that you would like Toad to warn against that?

André,

Yes - it’s not something that we can change since i’t’s an Oracle peculiarity.

As far as I can see, the best we could do is to warn when a hint contains embedded double-dash.
Since I had no clue how much effort that would be for you, I simply asked whether it would be a worthwhile improvement.

So I’ll leave it up to you to decide…

BTW: comments with /* */ embedded within a hint do not suffer from this peculiarity.
These are diagnosed and flagged correctly by Oracle.

Kind regards,
Abe

Abe,

A “fix”, rather call is “mod”, is already on its way. Parser/formatter will just mimick Oracle’s bug. (QP-1780)

I hope it’ll make if into Toad GA, but can’t promise, not my shop. :slight_smile:

Thanks for reporting this interesting case,
Andre

Andre,

that is really quick. A well-deserved compliment to you and your team.

Thanks, and have a good weekend,
Abe

It’s very late in the release cycle so I want to just lay out how it works so expectations are kept realistic.

The parser flags problems and the Editor displays them using the underline squiggles and margin markers. The Editor does not block execute/compile based on errors flagged by the parser. I suspect you’d like a blocking feature or at least a popup warning about this problem, but this is not what the Editor does and it can’t be implemented this late. To see the errors you’ll need to scan through your code looking for them or rt-click and if there are errors the top-most menu item is “Syntax Errors.” You can use that as a problem indicator. It will require you to remember to check rather than Toad letting you know about the problem.

I’ll log an issue for this to revisit after release. I think a good option may be to use parser detected errors as a roadblock with a message box or something that you can choose to ignore the errors and send to Oracle anyway.

Michael

Speaking for myself I hadn’t thought that far, never thought about blocking. Just a flag such as any other syntax error (squiggle). I guess that this is what Abe would be happy with. Or did I simplify too much?

André, Michael,

André is right: a marker during reformat would be enough for me. I had not expected that blocking an Oracle compile would even be possible.

I’ll be happy to see it in some future release. If you cannot make it for the next one, so be it.
It’s not worth the risk of reduced stability (which is usually the price we pay for speed).

I’ve been aware from the beginning that the code construct under discussion is a rare occasion.
Therefore I would not want to draw away too much development time.
That’s the reason I started asking how much work it would be, rather than directly asking for the feature.
It Always remains a trade-off, after all.

Of course, this text just reflects just my opinion, I cannot speak for other customers.

Come to think of it, the formatter probably (I’m not 100% sure here) could add or adjust hints
to include a source location identifier. How valuable would such a feature be to other customers?
I’m sure such a feature helps to make code fragments traceable in a way that no other
vendor supports (yet).

For completeness’ sake here’s an example from our live code:
select /*+choose
-iib_krg.enriching.build_item_msg aggregate data from item_stage_log */

as you see, the hint itself is essentially superfluous (on Oracle 10 and above)
but it does allow me to specify some comments that identify the query and that
will show up in the session browser, where all other comments are lost.

I’ll let you folks mull it over :slight_smile:

Have a nice weekend!
Abe

André is right: a marker during reformat would be enough for me.

Perfect. If Andre gets it done shortly then you’ll have it for release.

I had not expected that blocking an Oracle compile would even be possible.

It is and we’d have to do very little work to support it. The syntax errors from parser are already known to Editor when you compile so there’s no risk to speed/stability. It would just be a matter of showing you the errors allowing you to make corrections or ignore the error and send to Oracle anyway. Usually this is not needed because Oracle will return an ORA error and you know at that time. This case where there is no compile error, but execution error occurs may justify an intervention. Of course there would have to be a “Don’t show me this again” option.

Michael,

Ok, fair enough. Thanks for explaining!

Abe

Abe now you see how we are able to rush on Fridays to (try to) catch up on the work we didn’t do in the beginning of the week. :slight_smile:

Have a good weekend,
Andre