OraFAQ Forum: SQL & PL/SQL » how escape character in query

Home » SQL & PL/SQL » SQL & PL/SQL » how escape character in query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how escape character in query [message #589866]

Thu, 11 July 2013 08:43

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!!!!

Report message to a moderator

Re: how escape character in query [message #589868 is a reply to message #589866]

Thu, 11 July 2013 08:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.lmgtfy.com/?q=oracle+q+quote

Report message to a moderator

Re: how escape character in query [message #589917 is a reply to message #589866]

Fri, 12 July 2013 00:32

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.

Report message to a moderator

Re: how escape character in query [message #589918 is a reply to message #589917]

Fri, 12 July 2013 00:48

Lalit Kumar B
Messages: 3174
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

Littlefoot
Messages: 21825
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?

Report message to a moderator

Re: how escape character in query [message #589923 is a reply to message #589922]

Fri, 12 July 2013 01:34

Lalit Kumar B
Messages: 3174
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

Littlefoot
Messages: 21825
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>

Report message to a moderator

Re: how escape character in query [message #589948 is a reply to message #589938]

Fri, 12 July 2013 06:09

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: how escape character in query [message #589953 is a reply to message #589866]

Fri, 12 July 2013 07:52

joy_division
Messages: 4963
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.

Report message to a moderator

Re: how escape character in query [message #590003 is a reply to message #589953]

Sat, 13 July 2013 06:01

Littlefoot
Messages: 21825
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! /forum/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).

Report message to a moderator

Previous Topic:	Query Output {Urgent Please}
Next Topic:	Undo a dropped Column from a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 18 18:07:44 CDT 2025