escaping those darn ' in a package/procedure

[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 || % '''';
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

[D Rolfe]

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