Content
- Page items
- Session override
- Authentication ( customs, mix of social sign ins)
- Ajax
- 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
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-12.12.22-1024x281.png)
and use apex session override to load it and develop it
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-12.11.20-1024x764.png)
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
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-12.49.49-1024x554.png)
Google Authentication
- create web credentials
- create api credentials on google cloud console , read here
- create idp in apex builder( read image carefully )
- two settings in Login Processing
- switching session = Y if mix authentication types
- post authentication procedure, this is the actual handler of matching emails from your custom user table ( see code snippet)
- two settings in Login Processing
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
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.20.28-1-1024x497.png)
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.24.45-1024x558.png)
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.25.09-1-1024x209.png)
MS authentications
- create web credentials
- create api credentials on azure cloud console , read here
- create APEX web credentials
- discovery link is from azure
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.52.20-1024x300.png)
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.48.39-1024x697.png)
- create authentications
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.50.19-1024x847.png)
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.53.59-1-1024x257.png)
- 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.
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-14.00.44-1024x113.png)
here are the steps:
- 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 toAPEX_AUTHENTICATION=MS
- e.g on #3 request is
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-14.03.47-1024x432.png)
![](https://chaoyu.nl/wp-content/uploads/2024/04/Screenshot-2024-04-01-at-13.38.59-907x1024.png)
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;
/