Friday, November 16, 2012

View data from APEX collections in PL/SQL IDE

If you ever wondered, there's an easy way to see data from APEX collections in your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...).

You only have to run short anonymous PL/SQL block and define:

  • workspace name (workspace ID)
  • application ID
  • session ID.

Here's the script:

declare
  v_workspace_id apex_workspaces.workspace_id%type;
begin
  select workspace_id
    into v_workspace_id
    from apex_workspaces
   where workspace = '&WORKSPACE_NAME';
   
   -- Set Workspace ID
   apex_util.set_security_group_id(v_workspace_id);

   -- Set Application ID
   apex_application.g_flow_id  := &APP_ID;     
   
   -- Set Session ID
   apex_application.g_instance := &APP_SESSION;  
end;

After you run this script you can easily do query from apex_collections view and you'll see the result.

5 comments:

  1. Fantastic. Thank you for the pointer.

    ReplyDelete
  2. what am I doing wrong? :

    declare
    v_workspace_id apex_workspaces.workspace_id%type;
    BEGIN

    select workspace_id
    into v_workspace_id
    from apex_workspaces
    where workspace = 'GCOS_UPGRADE';

    -- Set Workspace ID
    apex_util.set_security_group_id(v_workspace_id);

    -- Set Application ID
    apex_application.g_flow_id := &APP_ID;

    -- Set Session ID
    apex_application.g_instance := &APP_SESSION;

    FOR i IN (SELECT *
    FROM APEX_collections
    WHERE collection_name = 'P506_COMPARE_BULK_TAGS')
    LOOP
    HTP.p ('');
    END LOOP;
    END;

    ReplyDelete
    Replies
    1. Seems ok, but I think there's no output. What's the output of your PL/SQL block?
      If you're on APEX 18.* or above you can use apex_session.create_session.

      Delete
    2. Look at this post https://apexbyg.blogspot.com/2018/06/apex-181-debugging-of-apex-sessions-was.html

      Delete