Oracle APEX PL/SQL Unknown Source of Invalid Identifier

Question

The goal of this program is to match a form's element IDs corresponding to different questions with the actual questions, so the resulting table's column names are the questions themselves instead of the ID numbers.

I am generating a query with pl/sql, and I know the generating part works since I can copy and paste the result into SQL Workshop where it returns the desired table. However, when I try to put it in "PL/SQL Function Body Returning a SQL Query", I get this error after running it.

ORA-00904: "D"."DUMMY": invalid identifier

Here is the code that generates the query.

DECLARE
    p_formid        VARCHAR2(20);
    v_sql           CLOB;
    v_element_sql   CLOB := '';
    v_other_sql     CLOB := '';
BEGIN
    p_formid := :P3_NATHAN;

    -- Build dynamic ELEMENT_ columns
    BEGIN
        SELECT LISTAGG(
             'ELEMENT_' || e.ID || ' AS "' || e.TITLE || '"',
             ', '
           ) WITHIN GROUP (ORDER BY e.ID)
        INTO v_element_sql
        FROM U_FB_FORM_ELEMENT e
        JOIN USER_TAB_COLUMNS c
          ON c.TABLE_NAME = 'U_P_' || p_formid || '_FORMS'
         AND c.COLUMN_NAME = 'ELEMENT_' || TO_CHAR(e.ID)
        WHERE e.U_FB_FORM_ID = p_formid
          AND e.TITLE IS NOT NULL
          AND LENGTH(e.TITLE) > 0;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_element_sql := '';
    END;

    -- Get other columns
    BEGIN
        SELECT LISTAGG(COLUMN_NAME, ', ')
        INTO v_other_sql
        FROM USER_TAB_COLUMNS
        WHERE TABLE_NAME = 'U_P_' || p_formid || '_FORMS'
          AND COLUMN_NAME NOT LIKE 'ELEMENT_%';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_other_sql := '';
    END;

    IF v_other_sql IS NULL THEN
        RETURN 'SELECT DUMMY FROM DUAL';
    END IF;

    v_sql := 'SELECT ' || v_other_sql;
    
    IF v_element_sql IS NOT NULL AND LENGTH(TRIM(v_element_sql)) > 0 THEN
        v_sql := v_sql || ', ' || v_element_sql;
    END IF;

    v_sql := v_sql || ' FROM U_P_' || p_formid || '_FORMS';

    RETURN v_sql;
END;

When I print the query, "Dummy" does not show up anywhere. Looking at the debug:

select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
 from (select "DUMMY"
from ((select /*+ qb_name(apex$inner) */d."DUMMY" from (SELECT ID, APPROVAL_STATUS, ...)

I am not sure why it is still selecting "DUMMY" given v_other_sql is not null.

The bind variable :P3_RESULT holds the generated query, but I cannot do

BEGIN
    :P3_RESULT;
END;

Any idea/possible fixes for "Dummy"?

But there's still :P3_NATHAN that could get empty while using it in a "PL/SQL Function Body Returning a SQL Query"? — Guillaume Outters
– Guillaume Outters, Commented Jun 19 at 18:32
@GuillaumeOutters :P3_NATHAN is for the form id of the form I am pulling. After selecting a form from a drop-down menu, the page refreshes and returns the query in a text area region. For the text area, the type is just "PL/SQL Function Body". That works fine, so I do not think it gets empty, but I am trying to run the query on an interactive report region through "PL/SQL Function Body Returning a SQL Query", and that is where I am running into the issue. — nathan
– nathan, Commented Jun 19 at 19:20
@GuillaumeOutters When I first open the page, the IF statement for SELECT DUMMY FROM DUAL triggers (correctly), and it does provide the correct table on the page. However, as soon as I choose a form, the error happens. — nathan
– nathan, Commented Jun 19 at 19:28
Then I would say (in the same vein as what I developed in my answer) you could try modifying this hardcoded dummy query to something that will return the same column names as when you're running real, on an U_P_x_FORMS table. At least for the first column. Because that first "simple" query is what gets parsed by APEX to infer the first column name in both cases (when running the dummy query of the IF, but also when running the real, dynamic query). So would maybe a SELECT DUMMY AS ID FROM DUAL would make this hardcoded query compatible with the dynamically generated ones. — Guillaume Outters
– Guillaume Outters, Commented Jun 19 at 19:40
What is the region type ? Classic report, interactive report ? — Koen Lostrie
– Koen Lostrie, Commented Jun 19 at 20:28

Guillaume Outters · Accepted Answer · 2025-06-19 19:33:01Z

0

(trying to infer APEX workings from literature)

By reading this discussion from 2005 which includes a fictive select in return /*select 1 from dual */ someschema.somepackage.someprocedure;,
I understand that fictive selects are used to induce APEX' dumb parser (it doesn't differentiate a select in a comment from an effective select) into thinking that someprocedure's return value will start with a 1 column.

So if APEX works this way, it looks for the first SELECT <column_name> in your PL/SQL block (be it in a comment, or in a string as you do) to determine it is the name of the first column that will be returned by your procedure;
then it generates this:

select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
 from (select "<name of the first thing encountered that looks a column name to him>"
from ((select /*+ qb_name(apex$inner) */d."<name of the first thing encountered that looks a column name to him>" from (SELECT ID, APPROVAL_STATUS, ...)

So I'd say:
start your block with a comment including a SELECT with the name of the expected first column:

[…]
BEGIN
    /* SELECT ID FROM DUAL *//* <- Give this to APEX so it sees it as the name of the first column we generate */
    p_formid := :P3_NATHAN;
    […]

Rationale

I would say that the most simple conventional "PL/SQL Function Body Returning a SQL Query" should look like that:

return 'SELECT COL1, COL2, COL3 FROM XXX';

and that APEX parses it to know that it should expect a result with COL1, COL2, COL3, which allows it to:

use those columns as the expected result header to display?
use (arbitrarily) the first column (COL1 here) to construct this complicated sub-sub-select:
select i.* … from (select "COL1" from ((select … d."COL1" from (SELECT COL1, COL2, COL3 FROM XXX /* ← Here goes the returned query. */))))

But in case your query is completely dynamic, it can't find this pattern SELECT <SOMETHING_THAT_LOOKS_LIKE_A_COLUMN_NAME>,
the first SELECT it sees is SELECT LISTAGG('ELEMENT_' || e.ID || ' AS "' || e.TITLE || '"', ', ') with 's and (s and ||s that surely defeat its parser and where it can't find a "clean" column name.
And in fact the SELECT that returns the SELECT ID, APPROVAL_STATUS, ... seen in the debug is probably SELECT LISTAGG(COLUMN_NAME, ', ') INTO v_other_sql, which APEX' parser won't find a column name into either.

So all in all, you have to help APEX by putting an example of what column names to expect from this dynamic query;
thus the idea of having your PL/SQL start by including a literal, as easily parseable as possible:
/* SELECT ID FROM DUAL */
(provided that every U_P_x_FORMS table ever handled by this block has an ID column).

edited Jun 19 at 19:33

answered Jun 19 at 18:53

Guillaume Outters

7,4961 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

nathan Jun 19 at 19:51

Thank you for the answer, I just tried it. Unfortunately, the comment idea did not work, but I directly changed the SELECT DUMMY FROM DUAL in the IF statement to SELECT ID FROM table_name (this table actually does have ID as a column). This sort of worked in that when I chose the form from the drop-down menu, the correct IDs did display! It changed from the IDs in table_name to the IDs from U_P_x_FORMS. However, the resulting table only showed ID, and it did not show any other column from the query. This means I would have the hardcode each column name, but I cannot bec

nathan Jun 19 at 19:58

Fixing Last Sentence: This means I would have to hardcode each column name, but I cannot because they vary.

Guillaume Outters Jun 19 at 20:03

Thus it would mean that those "PL/SQL Function Body Returning a SQL Query" can handle different tables only if they have the same columns :-( ? Or at least the same column number, with each column of the result appearing under the corresponding column name in the first literal SELECT <list of columns> APEX can find?

Guillaume Outters Jun 19 at 20:08

@nathan, so I suppose that with a variable number of columns, you could still get something by giving to your DUMMY query as many columns as the max number of columns of your U_P_x_FORMS tables: SELECT DUMMY ID, DUMMY COL1, DUMMY COL2, DUMMY COL3, DUMMY COL4 FROM DUAL WHERE 0>0. However this will loose specific column names for each form, which I suppose defeats the purpose of having one view presenting every possible form…

nathan Jun 19 at 21:46

Yeah, I am trying to match column names with the generic ID labels and present it in a table. I am not sure why it cannot just run the generated query without looking for the literal SELECT statement in the PL/SQL.

Guillaume Outters Jun 20 at 5:41

@nathan And what happens if you prevent it to find the SELECT, for example cutting it with a || (return 'SEL'||'ECT DUMMY FROM DUAL';)?

nathan Jun 20 at 14:19

Nothing changes unfortunately--the same issue occurs.

nathan Jun 25 at 14:53

The query crashes before. For now, I am just copy and pasting the query into SQL workshop instead of trying to get it to work on pages. I will return to work on this issue later, but thank you for all the help!

|

Koen Lostrie · Accepted Answer · 2025-06-20 14:52:30Z

This can be done but it is a lot of work. The example shown in this question only works for a classic report because the classic report is the only component that allows a variable number of columns. The other report types generate a lot of internal column metadata at compilation time so the number of columns need to be known beforehand. I'm going to list below one way to do this for an interactive report, in bullet points. You'll have to do the work yourself.

Do not use "Function Body returning SQL Query". Instead populate the result of your query in a collection Use apex_collection.create_collection_from_query method. Do this in a page process.
Create an interactive report on top of the collection
Create a page item for each column header (P1_HEADER01 .. P1_HEADER50) and set heading of each column to &P1_HEADERxx. You need to define as much items as you will have max columns since the number of columns needs to be define. APEX_COLLECTIONS only has 50 varchar2 columns so that is a hard limit anyway. Th
Use DBMS_SQL to get the column count of the query and store that count in a page item. Then on each of the columns you put a serverside condition of type expression :P1_COLCOUNT >= <column_index>

Collectives™ on Stack Overflow

Oracle APEX PL/SQL Unknown Source of Invalid Identifier

2 Answers 2

Rationale

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Rationale

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related