We've upgraded our forms and reports from 10.1.2.3 to 220.127.116.11 and this has broken a number of reports where we pass a string of single quoted variables to reports as a lexical parameter.
v_string := ''''||:block.item1||''''||','||''''||:block.item2||''''
where emp_id in (&p_string)
where v_string is simply passed as a text parameter to the report.
I'm not clear why this should break now (its worked since 6i) and whether anyone is aware of it and/or a solution.
Any help much appreciated as always
I've been investigating the Q quote operator which appears to be the 10G onwards way of dealing with single quoted strings, however I unable to make it work for this particular action.
If anyone knows of another simple way to achieve what we're trying to do or a fix to the issue we're experiencing that would be great.
At this point we will raise a TAR too and I'll add any information I get back to this post.
I guess you should try not to use the quotes, bsome but rather some strings more or less like in HTML, that you will afterwards replace before executing the report query.
Thus, say that instead of single quote you will use the string $$q and instead of double quote you will use $$dq, and then, in the afterpform trigger of the report you will replace(p_where_string,'$q','''').
I think this should do.
Thanks for the suggestion I will try and give it a go.
Do you mean for example in the form:
v_string := $$dq||:block.item1||$$dq||','||$$dq||:block.item2||$$dq
And in the report
I would say that this even if this works it will prove painful to be updating every form AND report where this is used as we have a lot! I do really appreciate anyone taking the time to reply though!
It would appear that there are several bugs recorded against this type of problem when moving to reports 11... Bug 11840698 - SIMPLE QUOTE REMOVED WHEN USING RUN_REPORT_OBJECT WITH PARAMLIST
Status 11 - Code/Hardware Bug (Response/Resolution
I will continue to update with responses from my SR.
The Bug for Reports is not fixed yet but, you can use a workaround:
- In Forms code used in 11g concatenate chr(39) to create literal single quote
e.g. instead of : v_site_string := v_site_string||''''||:cg$ctrl.wired||''''||',';
you can use: v_site_string := v_site_string||chr(39)||chr(39)||:cg$ctrl.wired||chr(39)||chr(39)||',';
The workaround is mentioned in the Bug and in this note for a similar issue:
HOW TO EMBED SINGLE QUOTE IN STRING [Doc ID 1005607.6]