You are here: Foswiki>Service Web>ApexCollectionInSQLDeveloper (16 Nov 2016, MezianeKettou)Edit Attach
-- MezianeKettou - 16 Nov 2016

When working with APEX-Collections, the tables used to save the data from these collections are only visible in the current session, This means we can't see the contenst of those tables in SQL-Developer for example, even if logged as APEX_SERCVICE user..

Submitting select * from apex_collections; will therefore show no data.

To be able to see the APEX-Collection's contens saved in the underlying tables, we have to set the workspace-id, the application-id and the session-ID in the SQL-Developer.

These 3 ids must be identical with these of APEX-Application.

The application-id and the session-id can be directly copied from the APEX-URL of the Application.

For more information about APEX-URL, take a look at APEX URL Format.

In the following URL example, the application-id is: 102 and the session-id is: 101541735273283.

https://ords.acc.gsi.de/ords/accdev/f?p= 102:10:101541735273283:::::

For the workspace-id, as in general there is only a unique workspace, its workspace-id can be get from the apex_workspaces:

select workspace_id into v_ws_id from apex_workspaces;

In other case, the workspace name ist displayed in the APEX-Application Builder on the bottom ot he left side, so the workspace-id can be obtained with:

select workspace_id into v_ws_id from apex_workspaces where workspace='workspace_name';

To set the 3 ids, just run the following procedure:

declare
     v_ws_id number;
     v_app_id number := 102;
     v_session_id number := 101541735273283 ;
begin
     select workspace_id into v_ws_id from apex_workspaces;
     wwv_flow_api.set_security_group_id(v_ws_id);
     wwv_flow.g_flow_id := v_app_id;
     wwv_flow.g_instance := v_session_id;
     --wwv_flow.g_user := 'MKETTOU';
end;
/

PL/SQL procedure successfully completed.

Now you are able to see what is in the APEX_COLLECTIONS. This can be very helpful to debug your application.

select COLLECTION_NAME, SEQ_ID, c001, c002, c003, c004, c005, c006, c007, c050
from apex_collections
order by C006, COLLECTION_NAME;
Topic revision: r1 - 16 Nov 2016, MezianeKettou
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback