5

I'm using SQL Developer 3.1.07. I ran a query to Oracle with an inline hint --+ hint in it but SQL Developer seems to have stripped the hint before submitting it to the database. Multi-line hints /*+ hint */ didn't get stripped. Is there an option to keep inline hints/comments in SQL Developer?

Alex
  • 9,250
  • 11
  • 70
  • 81
  • 3
    consider editing your question to include a small sample that anyone could use to replicate your problem. Good luck. – shellter Mar 25 '13 at 21:32
  • 4
    Where did you get the information that there is such a thing as "inline hints" ? I'm pretty sure they do not exist (I may be wrong, of course). BTW: you got the standard hint syntax wrong - Oracle uses forward slashes for that. – Frank Schmitt Mar 25 '13 at 21:59
  • 3
    @Frank Schmitt I fixed the slash directions. The single line hints exist. Read here: http://docs.oracle.com/cd/E14072_01/server.112/e10592/sql_elements006.htm – Alex Mar 25 '13 at 22:09
  • @Alex I stand corrected. Thanks for he link. – Frank Schmitt Mar 25 '13 at 22:12
  • How do you know it was stripped? can you use v$sql to capture the submitted SQL and verify that hints were removed? – Nick.Mc Mar 26 '13 at 01:57
  • @ElectricLlama we have a monitoring tool that shows the sql that was run on the server. It shows the sql that was run and the execution plan taken. The inline hint was clearly stripped when run from sql developer but left intact when running from other clients like Sql Plus. – Alex Mar 26 '13 at 13:43
  • I only ask because I've learnt that it helps to be certain about my assumptions when troubleshooting....so now i ask... is there any chance your monitoring software is stripping it out? That's why I suggest v$sql for a second opinion. I don't have much more to contribute except to note that if for some reason the SQL is submitted without carriage returns, you would need to strip out inline comments but you could leave delimited comments in. Perhaps this is something unique to SQL Developer. – Nick.Mc Mar 27 '13 at 00:49
  • @ElectricLlama how could the monitoring software be stripping it out if it shows the inline hint when other clients submit it? The execution plan reflected the absence of the hint as well. – Alex Mar 27 '13 at 01:21
  • I didn't think of that. That's good, it establishes that the issue is indeed with SQL Developer. – Nick.Mc Mar 27 '13 at 02:56
  • 1
    I think SQLDeveloper has it's own rules for the SQL Editor and a number of "things" that are usual in SQL Plus might be un-usual for SQL Developer (unfortunately). Strangely, when I run queries from SQL Developer with inline or multiline hints, it always show up on the `v$sql`. Try not to put multiline queries in "actually multiple lines that's all I can say. – Anjan Biswas May 06 '13 at 21:25
  • I may sound stupid, but this might be CR vs CR+LF problem. Try puting extra blank lines before and after the line with such hint and see, if it gets stripped off. – David Jashi Jun 20 '13 at 05:44

1 Answers1

2

I think the solution to this problem is to always use the multi-line comment syntax, even for a single hint:

select /*+ ordered */
      ename 
from emp
where ....

I'm not sure there's any genuine advantage to the uni-line --+ syntax (although please tell if you do have a use case where only this would do). On the other hand it has one blinding disadvantage: it really mucks up queries stored in places which strip out carriage returns and line feeds, such as V$SQL.SQL_TEXT.

APC
  • 144,005
  • 19
  • 170
  • 281