Home » SQL & PL/SQL » SQL & PL/SQL » Single quote issue in oracle (Oracle 11g)
Single quote issue in oracle [message #607910] Thu, 13 February 2014 07:18 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

Hi All,

I am facing the issue of single quote, how to solve the below error with quoting or escape or any other way.

SQL> declare
  2  p_type VARCHAR2(60):='`~!@#$%^&*()-=_+[]\{}|;':",./<>?';
  3  lv_type VARCHAR2(60);
  4  begin
  5  lv_type := '''' || p_type || '''';
  6  dbms_output.put_line( lv_type );
  7  end;
  8  /
ERROR:
ORA-01740: missing double quote in identifier
Re: Single quote issue in oracle [message #607912 is a reply to message #607910] Thu, 13 February 2014 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select 'a quote -->''<--' v,
  2         q'[a quote -->'<--]' v
  3  from dual;
V               V
--------------- ---------------
a quote -->'<-- a quote -->'<--

[Updated on: Thu, 13 February 2014 07:22]

Report message to a moderator

Re: Single quote issue in oracle [message #607915 is a reply to message #607912] Thu, 13 February 2014 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also:
SQL> SELECT 'a quote -->'||CHR(39)||'<--' FROM dual;
 
'AQUOTE-->'||CHR(39)||'<--'
---------------------------
a quote -->'<--
 
SQL> 
Re: Single quote issue in oracle [message #607916 is a reply to message #607915] Thu, 13 February 2014 07:57 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

p_type values come from front end

lv_type := '''' || p_type || ''''
Re: Single quote issue in oracle [message #607918 is a reply to message #607916] Thu, 13 February 2014 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
then there shouldn't be a problem.
Re: Single quote issue in oracle [message #607920 is a reply to message #607918] Thu, 13 February 2014 08:26 Go to previous message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
SQL> DECLARE
  2   qt VARCHAR2(10):='''';
  3  p_type VARCHAR2(60):='~!@#$%^&*()-=_+[]\{}|;'':",./<>?';
  4  lv_type VARCHAR2(60);
  5  begin
  6    lv_type :=qt||p_type||qt;
  7   dbms_output.put_line( lv_type );
  8  end;
  9  /
   
   '~!@#$%^&*()-=_+[]\{}|;':",./<>?' 

PL/SQL procedure successfully completed.
 


I think one correct?

Your expected this one ~!@#$%^&*()-=_+[]\{}|;':",./<>?

[Updated on: Thu, 13 February 2014 08:45]

Report message to a moderator

Previous Topic: Query help
Next Topic: Oracle SQL Developer questions
Goto Forum:
  


Current Time: Tue Apr 23 07:06:54 CDT 2024