Oracle Apex - Virtual Column error raised

Question

I am currently working in Oracle Application Express 18.1.0.00.45 and I am getting an error that I do not understand.

I created an interactive grid using the following query:

select periodic_topics_id, filter, topic,
CASE 
WHEN LINK1 like '%116%' then LINK1||:APP_SESSION 
ELSE LINK1
END AS LINK1
From periodic_topics
where meeting like :P31_MEETING_DESC
and
(nvl(:P31_FILTER,'0') = '0' or instr(:P31_FILTER||':',filter||':') > 0) 

In the table in the database, the periodic_topics_id column is the primary key and it is automatically populated when a new row is added to the table using the following trigger:

create or replace TRIGGER periodic_topics_trigger
BEFORE INSERT ON periodic_topics
FOR EACH ROW
BEGIN
:new.periodic_topics_id := periodic_topics_seq.nextval;
END;

In the APEX application, link1 is a textfield and in the "Link" section of this column's properties, the "Target" is of type URL and the URL is &LINK1. I also indicated in the APEX application that periodic_topics_id is the primary key. These are the properties of the link column that I am referring to:

The problem: when I manually insert a value into a cell in the "LINK1" column of the interactive grid, an error is raised that says:

"•Ajax call returned server error ORA-20987: APEX - Process 'Periodic Topics - Save Interactive Grid Data' raised 'ORA-01733: virtual column not allowed here' while executing a DML command. This error can occur if a column is based on an aggregation or SQL expression. Set column attribute 'Query Only' to Yes to exclude the column from the INSERT and UPDATE statement. - Contact your application administrator. for ."

However, if I create the interactive grid using the same query but without the case statement, then I have no problem adding a link in the interactive grid. No error occurs. In other words, no error occurs when I try to add a value to the "Link1" column in the interactive grid if I create the interactive grid using the following query:

select periodic_topics_id, filter, topic, link1
From periodic_topics
where meeting like :P31_MEETING_DESC
and
(nvl(:P31_FILTER,'0') = '0' or instr(:P31_FILTER||':',filter||':') > 0)

Just FYI, I need the query to have the case statement because some of the links will direct the user to external websites and others will direct the user to another page in the application. Without the case statement concatenating :APP_SESSION to the link, the user is forced to log back in to the application whenever they click on a link that directs them to another page in the application.

Does anyone know why the error would occur when the case statement is in the query but not when the case statement isn't in the query?

Thank you in advance.

Answer 1

The way I see it, this is a known issue which dates back from tabular forms (TF) (while interactive grid (IG) is its advanced version). As far as I can tell, you couldn't / can't create a TF/IG using a query with joins or fabricated columns - the one you created using CASE. Why? Because Apex has difficulties in knowing how to manipulate such a data.

Joins are commonly used in situations like Scott's EMP table, when you want to display DEPTNO along with department name (DNAME), but it is stored in DEPT table. Join is a natural choice, but it won't work. Solution is to create a function which returns such a value. And, of course, it can't be enterable.

Similarly, in your case, you actually can use CASE, but you'll have to set it to "query only" (as suggested) and use it for display purposes. Then you have to have the "original" LINK (database) column which will be enterable.

Basically, layout would look like this:

PERIODIC_TOPICS_ID  FILTER  TOPIC  LINK  LINK_DESCRIPTION
------------------  ------  -----  ----  -------------------------
<-------  enterable columns  --------->  <- your CASE construct ->

Users would click the LINK_DESCRIPTION column which will take them to URL, while you'd use LINK to enter/update that column's value.