10 Replies Latest reply: Oct 8, 2012 11:31 AM by rp0428 RSS

    how to pass character string consisting of single quote(') in a store proce

    858519
      Hi all,
      Can you please suggest how to pass character string consisting of single quote(') in a store procedure?

      e.g.there is a stored procedure: p(p_str in varchar2). Now how to pass a string 'Abc's nnnn 1000' into p?

      Thanks and regards,
        • 1. Re: how to pass character string consisting of single quote(') in a store proce
          RamaKrishna.CH
          Hi,

          You can pass the single quote like below mentioned.
          p(p_str => 'Abc''s nnnn 1000').
          • 2. Re: how to pass character string consisting of single quote(') in a store proce
            Ravetd
            Hi,

            The character quote (') have to be write twice in SQL or PL/SQL : use 'Abc''s nnnn 1000'
            • 3. Re: how to pass character string consisting of single quote(') in a store proce
              Purvesh K
              855516 wrote:
              Hi all,
              Can you please suggest how to pass character string consisting of single quote(') in a store procedure?

              e.g.there is a stored procedure: p(p_str in varchar2). Now how to pass a string 'Abc's nnnn 1000' into p?
              Different ways of doing it;
              declare
              p_str    varchar2(50);
              begin
              p_str := 'This String contains Quote''s.';
              dbms_output.put_line('p_str :: ' || p_str);
              p_str := 'This String contains Quote' || CHR(39) || 's.';
              dbms_output.put_line('p_str :: ' || p_str);
              p_str := q'['This String contains Quote's.]';
              dbms_output.put_line('p_str :: ' || p_str);
              end;
              • 4. Re: how to pass character string consisting of single quote(') in a store proce
                858519
                Hi,
                Sorry, we've to tackle it from our end. Actually the stored procedure is called from java script and they are declined to make any change in their code. So we've to do it from our end (stored procedure).

                Thanks in advance.
                • 5. Re: how to pass character string consisting of single quote(') in a store proce
                  jeneesh
                  855516 wrote:
                  Hi all,
                  Can you please suggest how to pass character string consisting of single quote(') in a store procedure?

                  e.g.there is a stored procedure: p(p_str in varchar2). Now how to pass a string 'Abc's nnnn 1000' into p?

                  Thanks and regards,
                  Are you getting any error when you are calling the procedure from JAVA?
                  • 6. Re: how to pass character string consisting of single quote(') in a store proce
                    858519
                    org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call pr_fig(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [null]; error code [0]; Cursor is closed.; nested exception is java.sql.SQLException: Cursor is closed.
                         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
                         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
                         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
                         at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
                         at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
                         at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)

                    Edited by: 855516 on Oct 8, 2012 3:32 PM
                    • 7. Re: how to pass character string consisting of single quote(') in a store proce
                      858519
                      create or replace procedure pr_fig(
                      p_mkt_sec in varchar2 default null,
                      p_mkt_sub_sec in varchar2 default null,
                      p_comm_group in varchar2 default null,
                      p_comm in varchar2 default null,
                      p_sub_comm in varchar2 default null) as
                      v_commodity_level varchar2(32000);
                      begin

                      dbms_output.put_line('value'||p_comm_group);

                      if p_mkt_sec is null and p_mkt_sub_sec is null and p_comm_group is null and p_comm is null and p_sub_comm is null then
                      v_commodity_level:=null;
                      else

                      v_commodity_level := ' and ('||
                      case when p_mkt_sec is not null then ' a.mkt_sec in ('''||replace(substr(substr(p_mkt_sec,2),1,length(substr(p_mkt_sec,2))-1),',',''',''')||''') or ' else null end ||
                      case when p_mkt_sub_sec is not null then ' a.mkt_sub_sec in ('''||replace(substr(substr(p_mkt_sub_sec,2),1,length(substr(p_mkt_sub_sec,2))-1),',',''',''')||''') or ' else null end ||
                      case when p_comm_group is not null then ' a.comm_group in ('''||replace(substr(substr(p_comm_group,2),1,length(substr(p_comm_group,2))-1),',',''',''')||''') or ' else null end ||
                      case when p_comm is not null then ' a.comm in ('''||replace(substr(substr(p_comm,2),1,length(substr(p_comm,2))-1),',',''',''')||''') or ' else null end ||
                      case when p_sub_comm is not null then ' a.sub_comm in ('''||replace(substr(substr(p_sub_comm,2),1,length(substr(p_sub_comm,2))-1),',',''',''')||''')' else null end ;

                      v_commodity_level :=rtrim(v_commodity_level,'or ')||') ';

                      dbms_output.put_line('v_commodity_level'||v_commodity_level);
                      end if;
                      end;



                      exec pr_fig(null,null,'(A A G'S/ Polymers)',null,null);
                      • 8. Re: how to pass character string consisting of single quote(') in a store proce
                        6363
                        855516 wrote:

                        Sorry, we've to tackle it from our end. Actually the stored procedure is called from java script and they are declined to make any change in their code.
                        If they won't fix their bug the application is not going to work then.
                        So we've to do it from our end (stored procedure).
                        Not possible.
                        • 9. Re: how to pass character string consisting of single quote(') in a store proce
                          TPD-Opitz
                          855516 wrote:
                          org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; 
                          uncategorized SQLException for SQL [{call pr_fig(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; 
                          SQL state [null]; error code [0]; Cursor is closed.; nested exception is
                          java.sql.SQLException: Cursor is closed.
                          This does not look like a problem with unescapes quotes unless you use the in input as part of dynamic SQL at a point you didn't show yet...

                          bye
                          TPD
                          • 10. Re: how to pass character string consisting of single quote(') in a store proce
                            rp0428
                            >
                            Sorry, we've to tackle it from our end. Actually the stored procedure is called from java script and they are declined to make any change in their code. So we've to do it from our end (stored procedure).
                            >
                            Get real!

                            It should be obvious that you cannot change what is 'passed' to your procedure from 'your end'.

                            The client determines that is 'passed' so if what is passed is incorrect it is the client that has to fix it.