1

I am trying to build dynamic and conditional LOV on condition taken from the page item. I came across this example ...

IF :P123_CHOICE = 'EMP' THEN
  RETURN
  'SELECT ename d, empno r
   FROM emp';
ELSE
  RETURN
  'SELECT dname d, deptno r
   FROM dept';
END IF;

However I need something little bit more complicated:

IF :P123_CHOICE = 'EMP' THEN
  RETURN
  'SELECT ename||' : '||esurname d, empno r
   FROM emp
   WHERE (admin ='N' or ADMIN = 'Y')
   ';
ELSE
  RETURN

  'SELECT ename||' : '||esurname d, empno r
   FROM emp
   WHERE admin ='N'
   ';
END IF;

As a result I get errors:

*ORA-06550: line 3, column 21: PL/SQL: ORA-01756: quoted string not properly terminated ORA-01756: quoted string not properly terminated ORA-06550: line 1, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 5, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; ORA-01756: quoted string not properly terminated*

It is clearly formatting issue I cannot solve. Any help greatly appreciated.

Jestem_z_Kozanowa
  • 607
  • 3
  • 13
  • 38

1 Answers1

3

In order to bypass special characters: 

IF :P123_CHOICE = 'EMP' THEN
RETURN
      q'[select ... ]';
ELSE
RETURN
      q'[ select ...]';
END IF;

Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1)

Jestem_z_Kozanowa
  • 607
  • 3
  • 13
  • 38
  • 1
    That's cool, and a great way to keep a character string clean. On the other hand, if you simply want to escape a single quote, then a single quote will do that: `'select ename d, empno r from emp where admin = ''N'''`. Two single quotes = one escaped quote in your character string. It's quite elementary, and you'll bump a lot more on the single quote style than the q function. – Tom Jun 11 '13 at 12:08
  • OK, so how would you escape concat from my example? like this ||'' : ''|| – Jestem_z_Kozanowa Jun 11 '13 at 13:31