Run APEX Code from SQL Developer Web

Do you want to run Oracle APEX code from SQL Developer Web? Some commands will work just fine, but others will fail. Sometimes silently.

Consider APEX_APP_SETTING.GET_VALUE.

If you run this command in SQL Developer Web it won't fail, but it won't work as you might either:

DECLARE
    l_value VARCHAR2 (4000);
BEGIN
    l_value := APEX_APP_SETTING.GET_VALUE (p_name => 'ROLE_READER');
    DBMS_OUTPUT.put_line (l_value);
END;        

Running that command will produce no real output.

Why doesn't it just work?

You need to set a little additional context for this to work! A session must be created (and cleaned up) to provide the context to the rest of the code.

APEX_SESSION.CREATE_SESSION

APEX_SESSION.DELETE_SESSION

DECLARE
    l_value   VARCHAR2 (4000);
BEGIN
    -- Provide the context of an APEX Session
    APEX_SESSION.CREATE_SESSION(
        p_app_id => 100,  -- Use the Application ID fo your app
        p_page_id => 2,   -- Use a page id from your app
        p_username => 'jstortz' -- Use your username
    );
    l_value := APEX_APP_SETTING.GET_VALUE (p_name => 'ROLEREADER');
    DBMS_OUTPUT.put_line (l_value);
    -- Now that we're done, cleanup the session
    APEX_SESSION.DELETE_SESSION;
END;        

#orclAPEX