This discussion is archived
10 Replies Latest reply: Oct 8, 2012 9:31 AM by rp0428 RSS

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

858519 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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-Consulting-com Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.

Legend

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