Home » SQL & PL/SQL » SQL & PL/SQL » how escape character in query
how escape character in query [message #589866] Thu, 11 July 2013 08:43 Go to next message
juamd
Messages: 21
Registered: November 2011
Location: Colombia
Junior Member
Hi all


I am creating a dynamic sql and to do so I want to concat this string:

v_consulta := v_listado_condiciones || 'UPPER(nombre) like UPPER(''/%'' '|| PRAZONSOCIAL ||' ''/%'') escape ''/'' '



But I get errors when I try to escape % operator, how could I do that ?


Thanks!!!!

Re: how escape character in query [message #589868 is a reply to message #589866] Thu, 11 July 2013 08:47 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+q+quote
Re: how escape character in query [message #589917 is a reply to message #589866] Fri, 12 July 2013 00:32 Go to previous messageGo to next message
sap_arul
Messages: 27
Registered: November 2005
Location: BANGALORE
Junior Member
Hi,

please use Quoting mechanism which is available from 10g instead of escape sequences.

Sample is given below.


DECLARE
v_sql VARCHAR2(1024);
v_cnt PLS_INTEGER;
BEGIN
v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%']';
EXECUTE IMMEDIATE v_sql INTO v_cnt;
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.'
);

dbms_output.put_line(q'#My job is done Thank'u!#');

END;



Regards, Arul.
Re: how escape character in query [message #589918 is a reply to message #589917] Fri, 12 July 2013 00:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2207
Registered: May 2013
Location: World Wide on the Web
Senior Member
sap_arul wrote on Fri, 12 July 2013 11:02
please use Quoting mechanism which is available from 10g instead of escape sequences. Sample is given below.


You think Blackswan did not know what quoting string literals is? Why did he provide the link? For the OP to understand the topic on his own and post his code.

[Updated on: Fri, 12 July 2013 00:48]

Report message to a moderator

Re: how escape character in query [message #589922 is a reply to message #589918] Fri, 12 July 2013 01:17 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Personally, I don't see anything wrong in Arul posting an example which shows the way to do that. Answers to most questions can be found somewhere on the Internet and Google is probably capable of finding them (as long as you know keywords to search for). What would we discuss about, then? Answer to all questions with LMGTFY?
Re: how escape character in query [message #589923 is a reply to message #589922] Fri, 12 July 2013 01:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2207
Registered: May 2013
Location: World Wide on the Web
Senior Member
I did not point anything to the sample provided by Arul, that's good. It's just that most of the original posters never come back with what they did or learn. Of course, we are here to learn and share.

BTW, if the OP would copy & paste and run that code, he would get the following error:-
SQL> DECLARE
  2  v_sql VARCHAR2(1024);
  3  v_cnt PLS_INTEGER;
  4  BEGIN
  5  v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%']';
  6  EXECUTE IMMEDIATE v_sql INTO v_cnt;
  7  DBMS_OUTPUT.PUT_LINE(
  8  TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.'
  9  );
 10  
 11  dbms_output.put_line(q'#My job is done Thank'u!#');
 12  end;
 13  /

DECLARE
v_sql VARCHAR2(1024);
v_cnt PLS_INTEGER;
BEGIN
v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%']';
EXECUTE IMMEDIATE v_sql INTO v_cnt;
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.'
);

dbms_output.put_line(q'#My job is done Thank'u!#');
end;
 
ORA-01756: quoted string not properly terminated


Tried again:-
SQL> DECLARE
  2  v_sql VARCHAR2(1024);
  3  v_cnt PLS_INTEGER;
  4  BEGIN
  5  v_sql := q'!SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%'!';
  6  EXECUTE IMMEDIATE v_sql INTO v_cnt;
  7  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.');
  8  dbms_output.put_line(q'#My job is done Thank'u!#');
  9  DBMS_OUTPUT.put_line(q'!This is my STRING!');
 10  END;
 11  /
 
0 tables in USER_OBJECTS.
My job is done Thank'u!
This is my STRING
 
PL/SQL procedure successfully completed

And it worked.

[Updated on: Fri, 12 July 2013 04:02]

Report message to a moderator

Re: how escape character in query [message #589938 is a reply to message #589923] Fri, 12 July 2013 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lalit Kumar B

BTW, if the OP would copy & paste and run that code, he would get the following error:-


Works OK for me, no errors:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> DECLARE
  2  v_sql VARCHAR2(1024);
  3  v_cnt PLS_INTEGER;
  4  BEGIN
  5  v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%']';
  6  EXECUTE IMMEDIATE v_sql INTO v_cnt;
  7  DBMS_OUTPUT.PUT_LINE(
  8  TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.'
  9  );
 10
 11  dbms_output.put_line(q'#My job is done Thank'u!#');
 12
 13  END;
 14  /
0 tables in USER_OBJECTS.
My job is done Thank'u!

PL/SQL procedure successfully completed.

SQL>
Re: how escape character in query [message #589948 is a reply to message #589938] Fri, 12 July 2013 06:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2207
Registered: May 2013
Location: World Wide on the Web
Senior Member
[quote title=Littlefoot wrote on Fri, 12 July 2013 15:30Works OK for me, no errors:
[/quote]

Hmm...I am surprised to see such a trivial thing, I am still getting an error.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as Lalit
 
SQL> 
SQL> DECLARE
  2  v_sql VARCHAR2(1024);
  3  v_cnt PLS_INTEGER;
  4  BEGIN
  5  v_sql := q'!SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%'!';
  6  EXECUTE IMMEDIATE v_sql INTO v_cnt;
  7  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.');
  8  dbms_output.put_line(q'#My job is done Thank'u!#');
  9  END;
 10  /
 
DECLARE
v_sql VARCHAR2(1024);
v_cnt PLS_INTEGER;
BEGIN
v_sql := q'!SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE' and OBJECT_NAME LIKE 'AC%'!';
EXECUTE IMMEDIATE v_sql INTO v_cnt;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.');
dbms_output.put_line(q'#My job is done Thank'u!#');
END;
 
ORA-01756: quoted string not properly terminated


I guess some locale-specific settings or some PL/SQL developer tool settings have gone wrong in my system. Need to find out.
Re: how escape character in query [message #589953 is a reply to message #589866] Fri, 12 July 2013 07:52 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
juamd wrote on Thu, 11 July 2013 09:43

But I get errors when I try to escape % operator, how could I do that ?


Maybe I overlooked something or I have my "dumb" hat on today, but why are we trying to escape the percentage sign? If you left out the backslash entirely, the assignment statement would do what you want.
Re: how escape character in query [message #590003 is a reply to message #589953] Sat, 13 July 2013 06:01 Go to previous message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As Juan never returned back, we still don't know whether anything of the above makes sense.

However, yesterday afternoon, while I was mowing lawn, I was thinking about Joy Division's message and I *think* that he's the only one who spotted the problem. BlackSwan threw a bait, Arul fetched it first which made Lalit react which made me upset and here we go. Wrong direction, in my opinion. Who's to blame? BlackSwan, of course! ./fa/1604/0/ (Can't be me for not using what's between my ears).

Juan never said he wanted to escape a single quote character. Nope, he clearly said that he wants to escape the percentage sign. It must be because of the LIKE operator, i.e. he needs to find strings that contain percentage string.

Therefore, here's an example of how to do that: in
where col like '%' || 'has \%%' escape '\';
we have to escape the % character (usually it is backslash \, but could be anything else, such as !, for example) and add another % sign because we are looking for it, therefore \%% is to be used. Specify escape character with the ESCAPE.

Records with ID 2 and 3 should be returned, as they contain the % sign.
SQL> with test as
  2    (select 1 id, 'No percentage sign, but has #' col from dual union
  3     select 2 id, 'This one has % within'         col from dual union
  4     select 3 id, 'Another one that has % in it'  col from dual
  5    )
  6  select *
  7  from test
  8  where col like '%' || 'has \%%' escape '\';

        ID COL
---------- -----------------------------
         2 This one has % within
         3 Another one that has % in it

SQL>



Some more examples:
 where col like '%' || 'has !%%' escape '!';
returns the same result (changed the escape character from \ to !).

 where col like '%' || 'has !%' escape '!';
returns nothing (missing the second % sign).

where col like '%' || 'has !%%' escape '%'
                    *
ERROR at line 8:
ORA-01424: missing or illegal character following the escape character
obviously, returns an error (wrong escape character).
Previous Topic: Query Output {Urgent Please}
Next Topic: Undo a dropped Column from a table
Goto Forum:
  


Current Time: Mon Sep 01 20:17:45 CDT 2014

Total time taken to generate the page: 0.06724 seconds