469,112 Members | 2,016 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

escaping those darn ' in a package/procedure

this block is from a (successful) implementation of FGAC. that is
a good thing.

IF l_context <> 'FOOBAR' THEN
retval := 'user_id = '' ' || USER || '''';
ELSE
retval := '1 = 1';
END IF;

now, they want this:

IF l_context <> 'FOOBAR' THEN
retval := 'user_id like '' ' || USER || % '''';
ELSE
retval := '1 = 1';
END IF;

but i can't figure out (and the error msgs don't help much) how
to balance the % and the 's to get it to parse. help?

i submit it from SQL*Plus, 8.1.7.

thanks,
robert
Jul 19 '05 #1
1 1483

Robert,
this block is from a (successful) implementation of FGAC. that is
a good thing.

IF l_context <> 'FOOBAR' THEN
retval := 'user_id = '' ' || USER || '''';
ELSE
retval := '1 = 1';
END IF;

now, they want this:

IF l_context <> 'FOOBAR' THEN
retval := 'user_id like '' ' || USER || % '''';

retval := 'user_id like '' ' || USER || ' % ''';

You do realise that there are leading and trailing spaces on either side
of USER?

For example:

SQL> r
1 select
2 'user_id like '' ' || USER || ' % '''
3* from dual

'USER_IDLIKE'''||USER||'%'''
-------------------------------------------------
user_id like ' SCOTT % '

ELSE
retval := '1 = 1';
END IF;

David Rolfe
Orinda Software
Dublin, Ireland

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by bobbyballgame | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
3 posts views Thread by ramorac | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.