I have a query that I want to pass an Oracle hint within, which really looks like a comment. I can see the query from the database side and it is not including the hint in the SQL, it strips it out like it is just a comment. For example…

SELECT /*+ hint here */
  field_name
FROM table_name

I can see the SQL string in the query in Lucee, but the database never gets that hint. When it actually runs in the database, it looks like…

SELECT field_name
FROM table_name

It doesn’t matter where the comment/hint is at in the SQL, it strips it out. Any thoughts on how to get Lucee to pass the hint/comment?

I want to say it's maybe "preserve single quotes", or another datasource setting around SQL injection, but it's been a while.

not sure if Oracle supports this still of comment

SELECT -- hint here 
  field_name
FROM table_name

would have to ask if you're doing this in a cfscript block (queryExecute, new Query(), etc) or is it in a <cfquery> tag? that is, are you certain it's the query call itself stripping the comment or is it the script equivalent cfquery function you're using? have you tried setting something like `var hint = "/*+ blah */";`and then using #hint# in the sql statement

SELECT #hint# field_name ...

It does support that type of comment, but this is a special execution plan hint that kind of masquerades as a comment.

sure, but all you care about is ensuring that cf is including the comment in the sql it's sending, correct?

Yeah, you have some good ideas for troubleshooting. I was just commenting on the -- idea as this is a "special" comment that has to be like it is. I'll definitely try this stuff out tomorrow and report back.

ooh, look at the second code example here: https://forums.oracle.com/ords/apexds/post/allow-nested-multiline-comments-9597

That's interesting, comment inception. Definitely something to play with.

I'm pretty sure I've run into this before, and it was something like PSQ doing the deed, but I'm drawing a blank. Maybe I'll peek at the sources when I get a chance, as "when in doubt" 😃

It does support that type of comment, but this is a special execution plan hint that kind of masquerades as a comment. Ahhhh I have seen that in other places too... Where the configuration "Parameters" are actually a comment - but are enacted upon by the server/service - as if there were a non-commented configuration item!

Just reporting back on a few things… • variable within the SQL to hold the comment still doesn’t pass the comment • The SQL in the query before execution shows the comment, but the SQL in the result of the query does not • “--” type comments also get swallowed up (although it wouldn’t help if it worked, in this case) • Comment inception doesn’t work either. If you keep a single comment but escape the last slash (/*+ stuff *_/), it escapes the comment in Lucee land so you just get a syntax error. You can escape a comment within a comment this way, but like I said, it still gets swallowed up before getting passed to Oracle. Still not sure if there is a way to deal with this.

is moving to a stored proc an option

Not a bad idea! I'd swear it's a white-space or SQL injection type of setting that does this, and that a cfsetting or something similar gets around it, but I might be thinking of another issue that was just similar. I think worst case you could grab a connection from the pool and run the query yourself, which at least gets you the pool (versus the pool and the caching and QoQ etc.)

We had a workaround in our back pocket where we can write the query without a bind variable (which makes it less reusable) and Oracle is happy and does a much better execution plan. Not sure why it hates the bind variable in this case, but it works. So we were forced to create a one off query with hardcoded values since we couldn't use the execution hint.

Oh yeah I forgot to say, that if anything it's the QueryParamConverter, so maybe if you didn't add any parameters it would work? 😁 https://github.com/lucee/Lucee/blob/935eaff5d5f0cebf9a1082f9ca327e72e163a237/core/src/main/java/lucee/runtime/tag/util/QueryParamConverter.java#L138