Forum Stats

  • 3,827,444 Users
  • 2,260,774 Discussions
  • 7,897,248 Comments

Discussions

Oracle Apex - virtual column error raised

KatReed
KatReed Member Posts: 52
edited Aug 28, 2018 9:04AM in APEX Discussions

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:

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">CASE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">WHEN</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'%116%'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">APP_SESSION <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ELSE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">END</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AS</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span>

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:

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> replace </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">TRIGGER</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_trigger <br/>BEFORE </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">INSERT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ON</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">FOR</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> EACH </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ROW</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">BEGIN</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">periodic_topics_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_seq</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nextval</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">END</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

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:

pic.png

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:

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> link1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span>

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.

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,978 Red Diamond
    edited Aug 28, 2018 6:22AM Answer ✓
    KatReed wrote: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:<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">CASE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">WHEN</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'%116%'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">APP_SESSION <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ELSE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">END</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AS</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span>

    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:

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> replace </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">TRIGGER</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_trigger <br/>BEFORE </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">INSERT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ON</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">FOR</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> EACH </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ROW</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">BEGIN</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">periodic_topics_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_seq</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nextval</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">END</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

    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:

    pic.png

    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:

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> link1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span>

    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?

    The error is occurring because the case expression does indeed turn LINK1 into a virtual column. Oracle cannot use the LINK1 column in DML operations because it is sourced from an expression, not a column name.

    There is a simple fix.

    1. Change the IG source query to:

    select periodic_topics_id, filter, topic, link1From periodic_topicswhere meeting like :P31_MEETING_DESCand (nvl(:P31_FILTER,'0') = '0' or instr(:P31_FILTER||':',filter||':') > 0)

    2. Manually add a column to the grid (Ctrl+Click on the Columns node in Page Designer and select Create Column).

    3. Set the following properties on the new column:

    Name: LINK_SESSION

    Type: Hidden

    Source Type: SQL Expression

    SQL Expression:

    case  when link1 like '%116%' then :app_session   else null end 

    4. Change the link Target on the LINK1 column to &LINK1.&LINK_SESSION.

    However this is not a robust solution:

    • What if the application ID has to change?
    • What if a user pastes in an external URL that coincidentally includes the characters "116"?
    • What if a ":" is missed from the end of an internal URL?

Answers

  • Pierre Yotti
    Pierre Yotti Member Posts: 4,040 Bronze Crown
    edited Aug 27, 2018 6:03PM

    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 ."

    Set the links column attribut Query Only to Yes and Boom it will works  

  • Bharat G
    Bharat G Member Posts: 490 Bronze Badge
    edited Aug 27, 2018 6:04PM

    I hope Link1 is not a Number field. Try to change the case statement like below and check.

    <span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">CASE</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">WHEN</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> LINK1 </span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #7d2727;">'%116%'</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">then</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> LINK1</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">||'&</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">APP_SESSION.'<br/></span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">ELSE</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">END</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">AS</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #303336;"> </span>

    And what is this 116 in when condition refers to ?

    Regards,

    Bharat

  • KatReed
    KatReed Member Posts: 52
    edited Aug 27, 2018 6:18PM

    Thank you for your response. I tired setting the attribute "query only" to yes for the Link column, but when I did that I was not able to edit the Link column in the interactive grid. Meaning, I couldn't manually enter a new URL value into the Link column in the interactive grid because it was set to "query only", so it was excluded from insert and update statements. @Alli Pierre Yotti

  • KatReed
    KatReed Member Posts: 52
    edited Aug 27, 2018 6:47PM

    @Bharat G Thank you for your response. Link1 is a textfield. The 116 is the application number for my application and because the application number is in the URL for every page in the application, I use that number as a way of telling Oracle Apex that if that application number is in the URL then to concatenate the APP_SESSION to the end of the string. Thank you for your possible solution, but unfortunately when I tried to implement your solution the same error was raised when I clicked the "Save" button on the Interactive Grid.

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,978 Red Diamond
    edited Aug 28, 2018 6:22AM Answer ✓
    KatReed wrote: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:<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">CASE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">WHEN</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'%116%'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">APP_SESSION <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ELSE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">END</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AS</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LINK1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span>

    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:

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> replace </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">TRIGGER</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_trigger <br/>BEFORE </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">INSERT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ON</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">FOR</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> EACH </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ROW</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">BEGIN</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">periodic_topics_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_seq</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nextval</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">END</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

    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:

    pic.png

    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:

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> topic</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> link1 <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">From</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> periodic_topics <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> meeting </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">like</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_MEETING_DESC <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'0'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> instr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">P31_FILTER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">filter</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">||</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">':'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span>

    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?

    The error is occurring because the case expression does indeed turn LINK1 into a virtual column. Oracle cannot use the LINK1 column in DML operations because it is sourced from an expression, not a column name.

    There is a simple fix.

    1. Change the IG source query to:

    select periodic_topics_id, filter, topic, link1From periodic_topicswhere meeting like :P31_MEETING_DESCand (nvl(:P31_FILTER,'0') = '0' or instr(:P31_FILTER||':',filter||':') > 0)

    2. Manually add a column to the grid (Ctrl+Click on the Columns node in Page Designer and select Create Column).

    3. Set the following properties on the new column:

    Name: LINK_SESSION

    Type: Hidden

    Source Type: SQL Expression

    SQL Expression:

    case  when link1 like '%116%' then :app_session   else null end 

    4. Change the link Target on the LINK1 column to &LINK1.&LINK_SESSION.

    However this is not a robust solution:

    • What if the application ID has to change?
    • What if a user pastes in an external URL that coincidentally includes the characters "116"?
    • What if a ":" is missed from the end of an internal URL?
  • KatReed
    KatReed Member Posts: 52
    edited Aug 28, 2018 6:21AM

    @fac586 Thank you for your response. Your solution does make sense and I tried to implement it, but when I got down to number 3, APEX would not accept the SQL Expression you suggested because it says that "LINK1" is not a valid identifier. The error states: ORA-06550: line 2, column 5: ORA-00904: "LINK1": invalid identifier

  • KatReed
    KatReed Member Posts: 52
    edited Aug 28, 2018 9:04AM

    @fac586 Nevermind, your solution worked! For some reason, I was still able to "Save and Run" the page despite the "invalid identifier" error, which seems strange. When I ran the page with your solution, I was able to add a URL to the LINK1 column and the interactive grid saved without error! Thank you very much for your help.

This discussion has been closed.