CODEWITHSUNDEEP
oracle-apexora-01422
Mark Killick
Mark Killick

Reputation: 21

Ref to FSP_AFTER_LOGIN_URL causes ora-01422

Oracle Apex 4.2.5, Oracle 12c database issue. The Oracle Apex application I'm working on is a single workspace with two parsing schemas. The main back office application is made up of a number of workspaces parsed by one schema.

The application having the error is parsed by the second schema, consisting of 3 applications (12000, 12001, 12006) and is used for internet facing pages. One application contains common pages to handle security, login and dashboard page and the other contain the product specific pages.

Navigating from the dashboard page to a product specific page will display the generic login page on the first attempt. The login page url has FSP_AFTER_LOGIN_URL set to the f?p=APP:PAGE of the page to be navigated to. On successful login the ora-1422 exact fetch returns more than requested number of rowserror is . Switching on and checking the debug shows the error is being thrown by the v function.

Looking in wwv_flow_data I can see 3 rows for FSP_AFTER_LOGIN_URL

"FLOW_INSTANCE" "ITEM_ID"   "ITEM_FILTER" "SESSION_STATE_STATUS" "FLOW_ID"
11304209855061  -162893176  "N"             "I"      "FSP_AFTER_LOGIN_URL"  "N"
11304209855061  -162893176  "N"             "I"      "FSP_AFTER_LOGIN_URL"  "N"
11304209855061  1456968448934338870 "N" "I" 12000    "FSP_AFTER_LOGIN_URL"  "N"

FSP_AFTER_LOGIN_URL is an application item in each of the applications. All have scope set to APP. Session State protection had been set but switching off has intermittent results.

FSP_AFTER_LOGIN_URL is an application item used internally by Apex. Apex automatically adds this item to each apex application.

Setting the value in FSP_AFTER_LOGIN_URL to a target page allows the developer to define which page to redirect the user to once they have been authenticated by a login page. See https://community.oracle.com/message/10271816?#38;#10269816

v is an Oracle Apex supplied function to return the value of an item for the session the page is currently in. Buried deep in the Oracle code is a SELECT INTO statement,

SELECT D.ITEM_VALUE_CLOB,
       D.ITEM_VALUE_VC2
  INTO L_VALUE_CLOB,
       L_VALUE_VC2
  FROM WWV_FLOW_DATA D,
       WWV_FLOW_SESSIONS$ S
 WHERE D.FLOW_INSTANCE     = S.ID
   AND D.FLOW_INSTANCE     = NVL(P_SESSION_ID,WWV_FLOW_SECURITY.G_INSTANCE)
   AND D.ITEM_ID           = C_ITEM_ID  -- GET_GLOBAL_ITEM_ID(P_ITEM_NAME)
   AND D.ITEM_NAME         = P_ITEM_NAME -- FSP_AFTER_LOGIN_URL
   AND S.SECURITY_GROUP_ID = WWV_FLOW_SECURITY.G_SECURITY_GROUP_ID;

The query will return more than one row in if the data above exists in WWV_FLOW_DATA because it does not consider the FLOW_ID.

As this is Oracle internals there's not much I can do. Other customers who use the software do not have any problems. Is there an Apex setting that is causing this problem for this particular customer?

What setting is allowing the 2 rows with item_id=-162893176 and flow_id=null to be created? What is the purpose of them? How do I workaround this issue? Other customers who use the software do not have any problems. Is there an Apex setting that is causing this problem for this particular customer?

Upvotes: 2

Views: 1172

Answers (0)

Related Questions