This discussion is archived
4 Replies Latest reply: May 21, 2013 3:20 AM by user11938516 RSS

passing single quotes in a lexical parameter from forms11g to reports 11g

user11938516 Newbie
Currently Being Moderated
Hi,

We've upgraded our forms and reports from 10.1.2.3 to 11.1.2.1 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||''''

select *
from emp
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

Thank you
  • 1. Re: passing single quotes in a lexical parameter from forms11g to reports 11g
    user11938516 Newbie
    Currently Being Moderated
    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.

    Thanks
  • 2. Re: passing single quotes in a lexical parameter from forms11g to reports 11g
    BEDE Explorer
    Currently Being Moderated
    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.
  • 3. Re: passing single quotes in a lexical parameter from forms11g to reports 11g
    user11938516 Newbie
    Currently Being Moderated
    Hi,

    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

    Begin
    replace(p_where_string,'$q','''');
    end;

    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.

    Thanks
  • 4. Re: passing single quotes in a lexical parameter from forms11g to reports 11g
    user11938516 Newbie
    Currently Being Moderated
    Hi,

    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]

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points