Oracle APEX cheat sheets (on going)

Content

• Page items
• Session override
• Authentication ( customs, mix of social sign ins)
• Ajax
• APEX Tab Container Dynamic Set Active
  • on Page Load
  • with Javascript when page is loaded
• plsql table collection (array)

Page items

:P1_ITEM	reference in sql /plsql on page designer(only)
&P1_ITEM.	Static Text (exact) Note: Exact substitution syntax should be avoided in SQL or PL/SQL code because it can result in SQL Injection vulnerabilities
#P1_ITEM#	template
v(‘P1_ITEM’), nv(‘P1_ITEM’) – ! it is not deterministic function , can lead to performance issue on heavy queries	on page designer and database ( use apex_session.attach in database) if ever facing performance issue on v and nv , create pipelined function with in parameters , or try apex session collection to store page items and use saclar sub query to cross join it

Session Override for better CSS and JS development

use VS code “Go Live” option to host local css or js files

and use apex session override to load it and develop it

Authentications

Custom Authentication

the use case is that you have a user table defined that you like to use for fine-grained access control.

it is relatively simple, create an custom type of authentication and provide a function which returns boolean. ( if user pass username + pwd return true else false ) and reference it in settings > authentication function name. it takes in on default 2 parameters p_username, p_password

Google Authentication

• create web credentials
  1. create api credentials on google cloud console , read here
  2. create idp in apex builder( read image carefully )
     • two settings in Login Processing
       1. switching session = Y if mix authentication types
       2. post authentication procedure, this is the actual handler of matching emails from your custom user table ( see code snippet)

procedure my_post_auth
is
l_count number;
begin
select count(1) into l_count from readcast_user t where upper(t.email) = upper(v('APP_USER'));
    if l_count = 1 then 
null;
else 
 htp.init;
 apex_util.redirect_url (p_url => 'https://oci.chaoyu.nl/ords/r/api/readcast/error-page'); 
 apex_application.stop_apex_engine;
end if;

• create authentications

MS authentications

• create web credentials
  • create api credentials on azure cloud console , read here
  • create APEX web credentials
    • discovery link is from azure

• create authentications

• Use Mixed Authentication Schemas

I have defined 3 types of Authentications types, I am having custom ( username + pwd ) as defaut. And I would like to use all 3 of them at the same time.

here are the steps:

1. create buttons on page, change action to redirect to a none public page
   • e.g on #3 request is APEX_AUTHENTICATION=GOOGLE or MS change it to APEX_AUTHENTICATION=MS

Here to read on even more advanced use case.

AJAX

make apex.server.process into async and await

function myAsyncFunction() {
    return apex.server.process("SYNC_PODCAST",
        {
            pageItems: ["P9005_ID"] -- this is important, else server side code cannot read P9005_ID
        }
    )
};
async function myAsyncTask() {
    try {
        let result = await myAsyncFunction();
        console.log("Async operation successful:", result);
        --json_result = JSON.stringify(result);
        --console.log(json_result);
        if (result.status == 'error') {
            apex.message.showErrors([
                {
                    type: "error",
                    location: "page",
                    message: result.message,
                    unsafe: false
                }
            ]);
        } else {
            apex.message.showPageSuccess("Podcast synced!");
        }
        // Further processing after async operation completes
    } catch (error) {
        console.error("Async operation failed:" + error);
        // Handle error
        apex.message.showErrors([
            {
                type: "error",
                location: "page",
                message: "Ajax error has occurred! " +error,
                unsafe: false
            }
        ]);
    }
}

-- to call the function
myAsyncTask(); 

Server side code

begin 
    readcast_main.pr_merge_podcast_from_rss_xml(i_podcast_id => :P9005_ID);
    -- Open a new JSON object
    apex_json.open_object;
    -- Write a key-value pair to the JSON object
    apex_json.write('status', 'successful');
    apex_json.write('id', :P9005_ID);
    -- Write another key-value pair to the JSON object
    -- Close the JSON object
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
    -- sqlerrm is a PL/SQL built-in function that retrieves the textual 
    -- description of the error associated with the most recent exception.
    --  It is commonly used for debugging and error handling.
    -- Open a new JSON object
    apex_json.open_object;
    -- Write a key-value pair to the JSON object
    apex_json.write('status', 'error');
    apex_json.write('message', sqlerrm);
    -- Write another key-value pair to the JSON object
    -- Close the JSON object
    apex_json.close_object;
end;

PLSQL COLLECTION

declare a type

  type ty_my_record_type is record(
     item_1 varchar2(100 char)
    ,item_2 varchar2(100 char));

declare a table type

 type ty_my_table is table of ty_my_record_type;

Init a table with table type

l_my_table ty_my_table;
 --- fill collection --- 
select *
from   (select '1'
              ,'2'
        from   dual
        union all
        select '1'
              ,'2'
        from   dual
        union all
        select '1'
              ,'2'
        from   dual
        union all
        select '1'
              ,'2'
        from   dual)
bulk   collect
into   l_my_table;

--- using dynamic sql --- 
declare 
  l_sql varchar2(3000 char) := 'select :1, :2 from dual';
begin
  execute immediate l_sql bulk collect
    into l_my_table
    using 'text1', 'text2';
end;
/

APEX Tab Container Dynamic Set Active

Both methods require you to set static id for tab container and each seperated tabs.

On Page Load (require Remeber Active tab enabled)

Say, you have 3 pages.

From page A , B you would like to redirect to Page C.

When C is opened from A, you would like to make tab-0001 active.

When C is opened from B, you would like to make tab-0002 ative.

The trick is use session storage, what we need to do is to set the session storage item ORA_WWV_apex.apexTabs.111.1907.Demo1.activeTab to the static id of the tab you would like to have active

var sesStorage = apex.storage.getScopedSessionStorage({
    prefix: "ORA_WWV_apex",
    useAppId: false
});
sesStorage.setItem("apexTabs."+"&APP_ID."+".3.Demo1.activeTab", "#SR_baseball_tb_reg")

#3 is the page number for page C
# #SR_baseball_tb_reg is the tab static id

this function should be attached on page A and B on the action before redirect.

After Page is Loaded

say you would like to create a button to set tab active and unactive, here is how.

apex.region("{tab_container_id}").widget().aTabs("getTabs")["#{tab_id}"].makeActive();