PL SQL FUNCTION BODY RETURN SQL QUERY GETS ERROR: ORA-20001: Query must begin with SELECT or WITH

Question

I get an ORA-20001: Query must begin with SELECT or WITH when I verify the code below. Does that mean I can't use FOR record (sql query) and a loop? Is it ok to load APEX card info the way I did in the loop? Any help is appreciated.

My goal is to display all media for a project and make the icon turn green for MEDIA that is referenced in a specific STEP_MEDIA row. I have tried outer joins, but I get duplicates. If there is a better way please let me know.

Oracle Cloud Application Express 21.1.7 Database Version: 19c

return q'~

DECLARE
n_count number;
BEGIN
FOR record in (SELECT *
FROM MEDIA
where KHN_PROJECT_ID = :P300_PROJECT_ID
 ORDER BY FILE_NAME)

LOOP 
 CARD_TITLE := record.FILE_NAME;
 CARD_SUBTITLE := record.FILE_MIMETYPE;
CARD_LINK := apex_util.prepare_url( '#' );
       CASE WHEN record.FILE_MIMETYPE LIKE 'video%'
        THEN
      CARD_TEXT :=  '<video> ...</video>';
       ELSE 
      CARD_TEXT := '<img> ...</img>';
      END CASE; 
       
      CARD_ICON := 'fa-check';
      SELECT COUNT(STEP_MEDIA_ID) INTO n_count
      FROM STEP_MEDIA
      WHERE MEDIA_ID = record.MEDIA_ID AND STEP_ID = :P300_STEP_ID_LOADED;
       IF ncount = 0
       THEN 
      CARD_COLOR := 'u-success';
       ELSE
       CARD_COLOR := 'u-normal';
       END IF;
        
CARD_SUBTEXT := '<button class="t-Button t-Button--noLabel t-Button--icon add-favorite" id="fav_'||record.MEDIA_ID||'" type="button">Select</span></button>';
END LOOP;
END;
 
~';

Here is the code I wrote using an outer join. It produces duplicate rows.

select m.FILE_NAME AS CARD_TITLE,
       m.FILE_MIMETYPE AS CARD_SUBTITLE,
      
       m.MEDIA_ID,
apex_util.prepare_url( '#' ) CARD_LINK,
       CASE WHEN m.FILE_MIMETYPE LIKE 'video%'
        THEN
        '<video >...</video>'
       ELSE 
       '<img >...</img>'
      END 
       CARD_TEXT,
       'fa-check' CARD_ICON,
       CASE WHEN sm.step_media_id is NULL
       THEN 
       'u-success'
       ELSE 
       'u-normal'
       END
        CARD_COLOR,

  from MEDIA m
  LEFT OUTER JOIN STEP_MEDIA sm
  ON m.MEDIA_ID = sm.MEDIA_ID
 where m.KHN_PROJECT_ID = :P300_PROJECT_ID OR sm.STEP_ID = :P300_STEP_ID_LOADED
 ORDER BY m.FILE_NAME

Answer 1

This is Apex, right?

Errors with codes from -20000 to -20999 are user-defined. In this case, Apex developers created it (so it's not one of built-in Oracle errors).

What does it say? Looks like you want to "dynamically" create a Classic report with the Cards layout. You're supposed to return a valid query, and it begins with a select or the with keyword (if you're using a with factoring clause, i.e. a CTE (common table expression)).

There's no evidence that code you posted returns what I said.

From my point of view, you shouldn't be doing it in a loop anyway. Everything can be done with a single query (in your case, that would be a join of two tables) that uses CASE expressions to set certain CARD_... values.

That query would then be Classic report's source.