Home » SQL & PL/SQL » SQL & PL/SQL » How to Select data when it has a single quote in the value. (PL/SQL on Win 7 Pro 64bit)
How to Select data when it has a single quote in the value. [message #594981] Wed, 04 September 2013 10:46 Go to next message
jfranchi
Messages: 3
Registered: September 2013
Location: US
Junior Member
Hi all, hopefully someone has the answer, I am new to PL/SQL.

The descrip field in the item table has the single quote used as the symbol for feet.
I have the same issue pulling from a last name field in other tables. (Like O'Connor)


select descrip into v_result
from c_ship_hist
where shipment_dtl_id = :SDID;
exception when others then null;

The error I get is "Missing right quote". How do I code around this issue without having to change the data?

Thank you
Re: How to Select data when it has a single quote in the value. [message #594982 is a reply to message #594981] Wed, 04 September 2013 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

http://askanantha.blogspot.com/2007/12/q-quote-operator-introduced-in-oracle.html

Re: How to Select data when it has a single quote in the value. [message #594983 is a reply to message #594981] Wed, 04 September 2013 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:
exception when others then null;


This the BIGGEST bug you can make in PL/SQL.
Read WHEN OTHERS.

Quote:
The descrip field in the item table has the single quote used as the symbol for feet.
I have the same issue pulling from a last name field in other tables. (Like O'Connor)


Which issue? I have none with those data.
Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: How to Select data when it has a single quote in the value. [message #594984 is a reply to message #594981] Wed, 04 September 2013 10:55 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Hi jfranchi
in addition to what the guys (I use the term in a non gender specific way Wink )
I made up a small demo, making some guesses
SQL> drop table desctest ;
drop table desctest
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table desctest (descrip varchar2(10), id number);

Table created.

SQL>
SQL> insert into desctest values(q'!6'4''!', 1);

1 row created.

SQL> insert into desctest values(q'!5'4''!', 2);

1 row created.

SQL> insert into desctest values(q'!7'4''!', 3);

1 row created.

SQL>
SQL> select *
  2  from desctest;

DESCRIP            ID
---------- ----------
6'4''               1
5'4''               2
7'4''               3

SQL>
SQL> declare
  2    v_result varchar2(30);
  3  begin
  4  select descrip into v_result
  5  from desctest
  6  where id = 1;
  7  exception
  8    when others
  9    then null;
 10  end;
 11  /

PL/SQL procedure successfully completed.
No issues.
Also, I don't think that what you posted ever would generate that error (due to you bug in the exception block.)
Re: How to Select data when it has a single quote in the value. [message #594985 is a reply to message #594984] Wed, 04 September 2013 11:02 Go to previous messageGo to next message
jfranchi
Messages: 3
Registered: September 2013
Location: US
Junior Member
This is the full piece of code. It works fine as long as the DESCRIP field has no quote in it. I don't know what SQL*Plus is as I am going through system to access the Oracle DB. Version is 11.2.0.3.0

declare
  v_result varchar2(60);
begin
  begin
    select edi_out.GET_RECORDED_Element(:ODID, 'PO1', 0, 'VP' ) into v_result from dual;
    exception when others then null;
  end;
  if NVL(v_result, 'N') = 'N' then
    begin
      select descrip into v_result
      from c_ship_hist
      where shipment_dtl_id = :SDID;
      exception when others then null;

    end;
  end if;
  edi.v_edi_string := v_result;
end;
-- Comment line. This statement fails when the description has a quote in it. 


Examples of the data would be:
Green Ball
100' of rope
Silver Bicycle

The code works fine for examples 1 & 3 but bombs on #2.
Re: How to Select data when it has a single quote in the value. [message #594986 is a reply to message #594985] Wed, 04 September 2013 11:06 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Cheers for that, can you show us a SQL Plus session where this happens. Even better, provide the test case as requested, you'll need to include details of the called function. Even, even better, do both those things.
Re: How to Select data when it has a single quote in the value. [message #594987 is a reply to message #594986] Wed, 04 September 2013 11:09 Go to previous messageGo to next message
jfranchi
Messages: 3
Registered: September 2013
Location: US
Junior Member
Never mind, please close this thread.
Re: How to Select data when it has a single quote in the value. [message #594988 is a reply to message #594986] Wed, 04 September 2013 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
>The code works fine for examples 1 & 3 but bombs on #2.
since when is "bombs" part or Oracle's error vocabulary?
How can we reproduce what you report?
Re: How to Select data when it has a single quote in the value. [message #594989 is a reply to message #594987] Wed, 04 September 2013 11:14 Go to previous message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
jfranchi wrote on Wed, 04 September 2013 17:09
Never mind, please close this thread.

I'm puzzled. I have been helpful and polite, I have asked you for information that will allow posters her to replicate your error so that someone might be able to help you, yet you seem to have decided that it's not worth your effort to get other people to fix your problem for free.
OH well. Good luck to you, I hope someone is able to correctly guess your issue at some point.
Previous Topic: WILD CARD search
Next Topic: Hierarchial query - CONNECT_BY_LOOP
Goto Forum:
  


Current Time: Wed Apr 16 23:49:38 CDT 2014

Total time taken to generate the page: 0.10344 seconds