🛠️How to Attach to an Oracle APEX Session from SQL Developer

Sometimes when debugging or testing PL/SQL in VSC SQL Developer extension or SQL Developer, you need access to the same session context that your APEX application is using. This includes values like :APP_USER, page items (:P155_CUSTOMER_ID), and session state.

By using the built-in APEX_SESSION.ATTACH API, you can attach your SQL Developer session to an existing APEX session.

---

📌 The Code Snippet

BEGIN
  apex_session.attach (
      p_app_id     => 181,         -- APEX Application ID
      p_page_id    => 155,         -- Page ID
      p_session_id => 4249790329496 -- Active Session ID
  );
END;

Once attached, any calls to :APP_USER, :APP_ID, apex_util.get_session_state, or v('ITEM_NAME') behave as if you were inside the browser session.

---

💡 Why This Is Useful

• Debugging: Run code in SQL Developer that relies on APEX session items.

• Testing: Reproduce user scenarios without re-entering session data.

• Troubleshooting: Investigate a specific user session to understand issues.

• Convenience: Query or run packages as if you were in the app runtime.

---

🛠️ How To Use It

1. Get a valid session ID

   • In APEX Developer Toolbar → Session → copy the session ID.

   • Or query it:

       select * from apex_workspace_sessions where app_id = 181;
     

2. Run the snippet in SQL Developer, replacing p_app_id, p_page_id, and p_session_id with real values.

3. Verify the attachment

    select v('APP_USER'), v('P155_CUSTOMER_ID') from dual;
   

   The values should match your APEX session.

4. Detach when done

    BEGIN
      apex_session.detach;
    END;
   

---

⚠️ Things To Keep In Mind

• The session must still be active (not expired).

• Run as the parsing schema of your APEX app.

• Wrong session IDs return: ORA-20987: APEX - session not found.

• Don’t hard-code session IDs in scripts; fetch them when needed.

---

✅ Pro Tip

This is a great way to debug page processes, validations, or packages outside of APEX while still using the real session context.