4 Replies Latest reply: May 21, 2013 5:20 AM by user11938516 RSS

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

    user11938516
      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
          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
            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
              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
                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