Using PL/SQL Function returning query to populate LOV

Question

long time lurker, first time asker.

I'm trying to populate a LOV using a PL/SQL Function returning a Query for the first time. I can't seem to get the syntax correct as I'm always receiving the following error on validation:

ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query!

ORA-06550: line 2, column 145: ORA-00923: FROM keyword not found where expected

of the following PL/SQL Function:

DECLARE
EMPLOYEES NUMBER := 0;
QR varchar(4000);

BEGIN

SELECT COUNT(*) INTO EMPLOYEES FROM TS4_USER@TS_INFO_4.STT
WHERE MAN_QT = :APP_USER

IF EMPLOYEES = 0 THEN

QR :=     'select FIRST_NAME|| '' '' ||LAST_NAME d, select FIRST_NAME|| '' '' 
    ||LAST_NAME r 
     FROM TS4_USER@TS_INFO_4.STT 
     WHERE MAN_QT IN (SELECT QT FROM TS4_USER@TS_INFO_4.STT
            WHERE MAN_QT = (SELECT MAN_QT FROM TS4_USER@TS_INFO_4.STT 
                      WHERE QT = :APP_USER)) AND FL = ''ESA''';

ELSE

QR :=     'select FIRST_NAME|| '' '' ||LAST_NAME d, select FIRST_NAME|| '' '' 
    ||LAST_NAME r 
    from TS4_USER@TS_INFO_4.STT 
    where MAN_QT = :APP_USER';
END IF;

RETURN QR;
END

This is confusing as Line 2 is part of my declaration area and I wouldn't expect a FROM here. I've done some googling on the matter and come up with very little. I'm fairly new to SQL and APEX, so I realize I could be overlooking something fairly simple. Any and all help is appreciated.

EDIT: Sorry about the formatting

Littlefoot Littlefoot · Accepted Answer · 2020-08-18T18:58:13

At first glance, queries use wrong syntax. You can't have two SELECTs, e.g.

select FIRST_NAME|| '' '' ||LAST_NAME d, select FIRST_NAME||
------                                   ------
here                                     here

Should have been

select FIRST_NAME|| ' ' ||LAST_NAME d, FIRST_NAME||

As you noticed (if you did, that is), I substituted two consecutive single quotes with one. Of course, it'll fail because you're writing dynamic SQL and have to "escape" single quotes. But, I'd suggest you to use the q-quoting mechanism which is easier to write and maintain. Something like this:

QR := q'[select FIRST_NAME|| ' ' || LAST_NAME d, 
                FIRST_NAME|| ' ' || LAST_NAME r 
         FROM TS4_USER@TS_INFO_4.STT 
         WHERE MAN_QT IN (SELECT QT FROM TS4_USER@TS_INFO_4.STT
                          WHERE MAN_QT = (SELECT MAN_QT FROM TS4_USER@TS_INFO_4.STT 
                                          WHERE QT = :APP_USER
                                         )
                         ) 
           AND FL = 'ESA']';

Though, aren't you overcomplicating it? Isn't it just

select ...
from ts4_user
where qt = :APP_USER
  and fl = 'ESA'

Also, select lists have two values: display and return ones. We usually display names and return IDs; you're having "names" in both cases. I'm not saying that it is wrong, it's just somewhat unusual.

Therefore: first make sure that query you write returns desired value in SQL*Plus (or SQL Developer or whichever tool you use), then move it to Apex.

P.S. Don't be sorry for formatting. Put some effort in it and make your (and our) life simpler.

Using PL/SQL Function returning query to populate LOV

1 Answers