11 Replies Latest reply: Jul 4, 2007 10:10 AM by BluShadow RSS

    Special characters

      Hi ,

      I have an SP which takes in a string in_str as input.
      Now when this string has charecters like single quotes ( ') it gives me 'quoted string not terminated ' error.
      when this has ' & ' then it proopmts for value..

      similiarly for other special characters also I am facing issue

      There are such 50 SPs in my package and I am facing problems due to this in all 50 SPs.

      Which would be the best way to handle this case.
      Me using oracle 10 g

        • 1. Re: Special characters
          Dmytro Dekhtyaryuk
          For example You can use chr
          SQL> select chr(38)||' and '||chr(39) from dual
            2  /

          & and '

          • 2. Re: Special characters
            can you post the code here?
            If you use SQL plus and u have issues with &,then you can give set feedback off.
            For other Special characters, you have to post the code here
            • 3. Re: Special characters
              The code is something like this.

              procedure p_test(in_value in varchar2)
              select a,b,c from tbl1
              where a = in_value


              Now if in_value contains " ' " ," & " , " % " etc....such special characters, then the code bombs!

              Help me out folks if you have some inputs.

              I have alsmost 50 such SPs in my package :-(


              ( NB. Don't know i had formatted the code still it doesnt show the formatting ...all starts from same line here )

              Message was edited by:
              • 4. Re: Special characters
                this doesn't help us. You should have posted where you are getting hit and the error message
                • 5. Re: Special characters

                  to format your code use [ code] and [ \code] tags around your code (without the spaces)

                  Message was edited by:
                  • 6. Re: Special characters
                    John Spencer
                    You seem to be calling the procedure from sqlplus based on the characters that are causing errors, so it is the call to the procedure that is failing, not the procedure itself.

                    To pass a parameter that contains a single quote, you need to escape it using two single quotes. So, something like:
                    exec p_test('John''s Row')
                    To avoid having sqlplus prompt for a value when you use &, you can do:
                    SET DEFINE OFF
                    at the sql prompt. You could also set it to another character if you want to be able to use substitution variables as well.

                    • 7. Re: Special characters
                      Or you should be able to use q strings as in:
                      exec p_test(q'[John's Row]')
                      • 8. Re: Special characters
                        Hi Folks,

                        The SPs are called from Actuate reporting tool and not SQL* plus

                        The person handling said that he had hit errors like

                        for " ' " -> 'qouted string not terminated

                        for " & " -> prompting for input.

                        So there should be a generic way of handling this
                        ( I mean some function written in the package itself so that we can check for special characters and then escape it?)


                        Message was edited by:
                        • 9. Re: Special characters
                          But the package itself and the subsequent queries it fires against the database will not have a problem with & and ' characters.

                          The problem lies in the calling application.
                          • 10. Re: Special characters
                            Ok, I got it.
                            So you mean to say I wont have to do anything in my SPs.
                            Its only that the calling application has to handle this.
                            But if I use dynamic query,then I will have to handle right?
                            Thenwhats the solution for this. One of my SPs in future might face this problem right?

                            • 11. Re: Special characters
                              If you use dynamic SQL it depends what you are doing with it, but if necessary you can use q strings as demonstrated or you can replace single quotes in the string with double quotes to make them compatible e.g.
                              -- Note: these are all single quotes, 4 in the first part 6 in the second
                              myval := replace(p_myparam, '''', '''''')