9 Replies Latest reply: Dec 20, 2006 12:47 PM by 504440 RSS

    chr(39)

    504440
      Hi,

      I need your kind help for the following.

      i need to save the last name O'NEIL to the variable and then need to retreive in the report the saved value.

      last_name := O'NEIL (it has chr(39))

      select last_name from dual? so i can get back O'NEIL

      THANKS
        • 1. Re: chr(39)
          21205
          last_name := 'O''NEIL';
          • 2. Re: chr(39)
            SomeoneElse
            SQL> select 'O''Neil' from dual;

            'O''NE
            ------
            O'Neil

            SQL> select 'O' || chr(39) || 'Neil' from dual;

            'O'||C
            ------
            O'Neil
            • 3. Re: chr(39)
              504440
              thanks Eric.

              but the problem is that value will be from the forms and i'll pass that to the report. so if name has a quote like O'NEIL then how would i pass this as a parameter.

              thx
              • 4. Re: chr(39)
                324137
                I don't know how you call your report from Forms but could that be an option:

                :
                :
                ADD_PARAMETER(....,AddQuote(<FormItemName>))


                Create a function in Forms:
                FUNCTION AddQuote(ctext VARCHAR2) RETURN VARCHAR2 IS
                BEGIN
                  RETURN (REPLACE(cText,CHR(39),CHR(39) || CHR(39)));
                END;
                • 5. Re: chr(39)
                  21205
                  I think you would just pass the value from the form to the report as-is, it's just a bind-variable. Have you tried it? Did you experience any problems, and if so what were they?
                  SQL> create procedure pl (p_in in varchar2)
                    2  is
                    3  begin
                    4     dbms_output.put_line (p_in);
                    5* end;
                  SQL> /

                  Procedure created.

                  SQL> var x varchar2(10)
                  SQL> exec :x := 'O''Neil';

                  PL/SQL procedure successfully completed.

                  SQL> @ssoo
                  SQL> exec pl (:x)
                  O'Neil

                  PL/SQL procedure successfully completed.

                  SQL>
                  • 6. Re: chr(39)
                    324137
                    I think you would just pass the value from the form
                    to the report as-is, it's just a bind-variable.
                    That is right, no need for the conversion or to add a quote.

                    Unless you are trying to concatenate that name with another string ?
                    • 7. Re: chr(39)
                      504440
                      Thank you, but if you try to pass as-it , still doesn't work, i don't see any error when i check the report server (show jobs). i get the message from the form that
                      error while running report. Also last name can have spaces or dashes as well.

                      DECLARE
                      .............
                      last_name varchar2(60);

                      BEGIN
                      ............

                      last_name := :block.item;
                      Add_parameter(lp_id,'PRM_LAST_NAM',TEXT_PARAMETER,''''||''||last_name||''||'''');

                      ...................



                      thx
                      • 8. Re: chr(39)
                        324137
                        Are you ok now ?
                        • 9. Re: chr(39)
                          504440
                          Nope, still doesn't work