Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Apex - virtual column error raised

KatReedAug 27 2018 — edited Aug 28 2018

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:

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:

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.
This post has been answered by fac586 on Aug 28 2018
Jump to Answer

Comments

vijai
Hi,

I hope you would have gone through these links

http://jobinesh.blogspot.co.uk/2010/12/using-oraclejbodomainarray-with.html
http://jobinesh.blogspot.co.uk/2011/07/tips-on-using-oraclejbodomainarray-as.html

Hoping so, I guess from the details, since the error is ORA , it is raised by the database. please cross check once where you are setting the bind variables that it is set as you intended.

Last but not the least, please verify if the user used to connect with the database by the application has enough rights on 'STRARRAY' object.
vanna
I went through those links, and I am sure the user has enough rights on STRARRAY object.
I noticed that when running application module things work fine if I firstly execute RootView - Master view.
and then double click on VL which shows detail view in table.

The error occurs if I click on ViewLink first. I am not sure if I am even "allowed" to do that.
I set the parameters with "setNamedWhereClauseParam" and print them out before i call super.executeQueryForCollection() and this the output i get:
ExecuteQueryForCollectionRoot
Permission: [AdfIn2Ogrodje.ROOT, AdfIn2Ogrodje.ADMINISTRACIJA, HOME]
[87] MetVMenuRoot1 ViewRowSetImpl.setNamedWhereClauseParam(listOfUserPermission, oracle.jbo.domain.Array@e1af74d9)
[88] MetVMenuRoot1 ViewRowSetImpl.setNamedWhereClauseParam(menuRoot, AdfIn2Ogrodje.ROOT)
//Print before execution.
EXECUTE_MENUROOT menuRoot: AdfIn2Ogrodje.ROOT
EXECUTE_MENUROOT permission: oracle.jbo.domain.Array@e1af74d9
    Permission: AdfIn2Ogrodje.ROOT
    Permission: AdfIn2Ogrodje.ADMINISTRACIJA
    Permission: HOME
[89] MetVMenuRoot1>#q computed SQLStmtBufLen: 537, actual=447, storing=477
[90] SELECT MetVMenu.CHILD_ID,         MetVMenu.CHILD_IME_MODULA,         MetVMenu.PARENT_ID,         MetVMenu.PARENT_IME_MODULA,         MetVMenu.ZST,         MetVMenu.NIVO,         MetVMenu.CHILD_NAZIV_V_MENIJU,         MetVMenu.CHILD_TIP_MODULA,         MetVMenu.CHILD_OPIS_MODULA FROM MET_V_MENU MetVMenu WHERE MetVMenu.PARENT_IME_MODULA like :menuRoot and MetVMenu.CHILD_IME_MODULA in (SELECT * FROM TABLE(CAST(:listOfUserPermission AS STRARRAY)))
[91] ViewObject: [adfin2.menu.model.views.MetVMenuRoot]MetMenuAppModule.MetVMenuRoot1 Created new QUERY statement
[92] Bind params for ViewObject: [adfin2.menu.model.views.MetVMenuRoot]MetMenuAppModule.MetVMenuRoot1
[93] Binding null of type 12 for "menuRoot"
[94] Binding null of type 12 for "listOfUserPermission"
protected void executeQueryForCollection(Object object, Object[] object2, int i) {
        System.out.println("ExecuteQueryForCollectionRoot");
        setParametersForSessionTest(); // method where i set the parameters.
        printExecute(); // printing
        super.executeQueryForCollection(object, object2, i);
    }
After a few clicks on OK button the query executes normally.
What I am guessing is, that executeQueryForCollection just takes whatever is in object2 and that's null at the beginning.
I tried to use this method, which sets the bind variables in object2. And it gives me "Invalid column type" error.
http://packtlib.packtpub.com/library/9781849684767/ch04lvl1sec07
vanna
After printing out object2 elements I found out, that I must pass Array as oracle.sql.ARRAY.
I got the Invalid column type because I used oracle.jbo.domain.Array
Any idea how to do that?
vanna
With further exploration I now use only prepareRowSetForQuery(), where bind variables are set before executeQuery or executeQueryForCollection are called.
executeQuery or executeQueryForCollection methods are now not overridden anymore.
public void prepareRowSetForQuery(ViewRowSetImpl viewRowSetImpl) {
        System.out.println("PrepareRowSetForQueryROOT");
       // Number departmentId = vrsImpl.ensureVariableManager().setVariableValue( "DepartmentId", departmentId); super.prepareRowSetForQuery(vrsImpl); }
        viewRowSetImpl.ensureVariableManager().setVariableValue("menuRoot", "ROOT");
        viewRowSetImpl.ensureVariableManager().setVariableValue("listOfUserPermission", getUserPermissionArray().getArray());

        super.prepareRowSetForQuery(viewRowSetImpl);
    }
	
	getUserPermissionArray() returns oracle.jbo.domain.Array
It's wokring fine in application module, however on a jsp page I get only the first result of every level in tree table.
For exampe if this is my menu:
ROOT
	 -MENU1
		- ITEM1
		- TTEM2
	 -MENU2
		- ITEM1
		- TTEM2
the tree table only shows this:
 ROOT
	-MENU1
		-ITEM1
	-MENU2
		-ITEM1
Edited by: vanna on 29.1.2013 3:33
vanna
Answer
prepareRowSetForQuery() solved the problem for me. The Array bind variable was null and that was the reason for inconsistent datatype error.
What concerns the tree error -> it was a mistake in cardinality definition.
Marked as Answer by vanna · Sep 27 2020
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 25 2018
Added on Aug 27 2018
#apex-discussions
7 comments
10,565 views