How to change Build Option for Page on runtime in Oracle APEX

    first make select list item  P7_BUILD_OPTION

that will contain all Build Option available for your APP

use this code as source 

  select patch_name||' ('||wwv_flow_lang.system_message(patch_status)||')' d, id r 

  from APEX_200200.wwv_flow_patches where FLOW_ID = :APP_ID

and make process to update Page Build Option

BEGIN

UPDATE APEX_200200.wwv_flow_steps -

SET REQUIRED_PATCH = :P7_BUILD_OPTION  

 WHERE FLOW_ID = :P7_FLOW_ID 

 and ID = :P7_STEP_ID ; 

end;

--
thanks

How to change Page Read only on runtime in Oracle APEX

Make process to change Page Read only on runtime

BEGIN

UPDATE APEX_200200.wwv_flow_steps -- PageS

SET READ_ONLY_WHEN_TYPE = 'EXISTS' ,

READ_ONLY_WHEN = 'select 1 from dual where 1=1'

WHERE FLOW_ID = :P7_FLOW_ID -- APP ID

 and ID = :P7_STEP_ID  -- PAGE ID

; 

end;

--
thanks

How to change Page to Public or Not on runtime in Oracle APEX

    first make select list item  P7_PAGE_IS_PUBLIC_Y_N

 

use this code as source 

and make process to change Page to Public  or Not

BEGIN

UPDATE APEX_200200.wwv_flow_steps -- PageS

SET PAGE_IS_PUBLIC_Y_N = :P7_PAGE_IS_PUBLIC_Y_N -- Authorization schema

WHERE FLOW_ID = :P7_FLOW_ID -- APP ID

 and ID = :P7_STEP_ID  -- PAGE ID

; 

end;

--
thanks

How to change Page Authorization Scheme on runtime in Oracle APEX

   first make select list item  P7_SECURITY_ID

that will contain all Authorization available for your APP

use this code as source 

select NAME,ID from 

APEX_200200.wwv_flow_security_schemes

 where FLOW_ID =:P7_FLOW_ID

and make process to update Page Authorization 

BEGIN

UPDATE APEX_200200.wwv_flow_steps -- PageS

SET required_role = :P7_SECURITY_ID -- Authorization schema

WHERE FLOW_ID = :P7_FLOW_ID -- APP ID

 and ID = :P7_STEP_ID  -- PAGE ID

; 

end;

--
thanks

How to change Authorization Scheme on runtime in Oracle APEX

  first make select list item  P4_AUTHORIZTION_SCHEMA

that will contain all Authorization available for your APP

 

use this code as source 

 

select a.d, a.r from (

    select APEX_200200.wwv_flow_lang.system_message('MUST_NOT_BE_PUBLIC_USER') d,'MUST_NOT_BE_PUBLIC_USER' r, 1 

    from dual

    union all

    select APEX_200200.wwv_flow_escape.html(substr(name,1,50)),to_char(id), 1

    from APEX_200200.wwv_flow_security_schemes where flow_id = :APP_ID

    union all

    select '{'||APEX_200200.wwv_flow_lang.system_message('NOT_W_ARGUMENT',APEX_200200.wwv_flow_escape.html(substr(name,1,50)))||'}' d,

     '!'||to_char(id) r, 2

    from   APEX_200200.wwv_flow_security_schemes

    where flow_id = :APP_ID

    order by 3,1) a

and make process to update Application Authorization 

UPDATE APEX_200200.wwv_flows 

SET SECURITY_SCHEME =:P4_AUTHORIZTION_SCHEMA

where ID = :APP_ID;

--
thanks

How to change Parsing Schema on runtime in Oracle APEX

  first make select list item  P4_AUTHORIZTION_SCHEMA

that will contain all DB schema available for your workspace

use this code as source 

select APEX_200200.wwv_flow_escape.html(schema) d, schema r

from   APEX_200200.wwv_flow_company_schemas

where  security_group_id = :flow_security_group_id

order by 1

and make process to update Application Authentication 

UPDATE APEX_200200.wwv_flows 

SET SECURITY_SCHEME =:P4_AUTHORIZTION_SCHEMA

where ID = :APP_ID;

--
thanks

How to change Authentication Schemes on runtime in Oracle APEX

 first make select list item  P2_AUTHENTICATION_ID
that will contain all Authentication available for your APP

use this code as source 

select name as d,

       id   as r

  from APEX_200200.wwv_flow_authentications

 where flow_id           = :APP_ID

   and security_group_id = :WORKSPACE_ID

 order by 1

and make process to update Application Authentication 

UPDATE APEX_200200.wwv_flows 

SET AUTHENTICATION_ID =:P2_AUTHENTICATION_ID

where ID = :APP_ID;

--
thanks

Send mail from Oracle APEX

How to Send mail from Oracle APEX
1 : add configuration of mail server

1. Sign in to Oracle Application Express Administration Services.
2. Click Manage Instance.
3. Under Instance Settings, click Instance Settings.
4. add smpt configuration

Host:smtp.mailtrap.io

Port:25 or 465 or 587 or 2525

Username:

Password:

 like Video

https://www.youtube.com/watch?v=YhT_Hmdkjvk

2:  Enabling Network Service

DECLARE

  ACL_PATH VARCHAR2(4000);
BEGIN
    -- Look for the ACL currently assigned to '*' and give APEX_190100
    -- the "connect" privilege if APEX_190100
    -- does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
    WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (ACL_PATH,'APEX_190100','connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_190100', TRUE, 'connect');
  END IF;
  EXCEPTION
    -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_190100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

BEGIN

  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'power_users.xml',
    principal    => 'STOCK',
    is_grant     => TRUE,
    privilege    => 'connect');
  COMMIT;
END;
/
Like Video
https://www.youtube.com/watch?v=2mARJbeo7to

thanks

Interactive Grid Action Using Dynamic Action

Interactive Grid Region Static ID 'ig_emp'

Add New raw For Interactive Grid

apex.region( "ig_emp" ).widget().interactiveGrid( "getActions" ).invoke( "selection-add-row" );

View Video

Delete raw from Interactive Grid

apex.region( "ig_emp" ).widget().interactiveGrid( "getActions" ).invoke( "selection-delete" );

View Video

Enable Edit Mode in Interactive Grid 

apex.region( "ig_emp" ).widget().interactiveGrid( "getActions" ).set("edit", true);
View Video

Save Interactive Grid 

apex.region( "ig_emp" ).widget().interactiveGrid( "getActions" ).invoke( "save" );

View Video

preview image from directory in oracle apex

1: Create Directory to save File and add privilege to write on Directory to You User

CREATE OR REPLACE DIRECTORY STOCK_DIR2 AS 'C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\i\STOCK_IMAGE';

 GRANT read, write ON DIRECTORY STOCK_DIR2 TO STOCK;

2: Create column html expression in interactive grid Report

<img src="http://localhost:8080/i\STOCK_IMAGE/aa.png" alt="Smiley face" height="42" width="42">

2: to get image dynamically

<img src="http://localhost:8080/i/STOCK_IMAGE/&PRODUCT_CODE..png" alt="Smiley face" height="42" width="42">

you tube link

How to Store Blob File Outside Oracle Database In Oracle APEX

1: add privilege to use UTL_FILE  to You User

grant execute on utl_file to STOCK;

2 : Create Directory to save File and add privilege to write on Directory to You User

CREATE OR REPLACE DIRECTORY STOCK_DIR AS 'F:\STOCK_IMAGES';

GRANT read, write ON DIRECTORY STOCK_DIR TO STOCK;

3: Use this Procedure to write BLOB file to your disk

create or replace PROCEDURE blob_to_file (p_blob      IN OUT NOCOPY BLOB,
                                          p_dir       IN  VARCHAR2,
                                          p_filename  IN  VARCHAR2)
AS
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  l_blob_len := DBMS_LOB.getlength(p_blob);
  
  -- Open the destination file.
  l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767);

  -- Read chunks of the BLOB and write them to the file until complete.
  WHILE l_pos <= l_blob_len LOOP
    DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
  
  -- Close the file.
  UTL_FILE.fclose(l_file);
  
EXCEPTION
  WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;

END blob_to_file;

4- use this process to upload file 

DECLARE
  l_blob  BLOB;
BEGIN
  -- Ge SELECT filename,
select
                      BLOB_CONTENT INTO   l_blob
                   FROM apex_application_temp_files
               where NAME= :P5_PRODUCT_IMAGE ;
  
  

  blob_to_file(p_blob     => l_blob,
               p_dir      => 'STOCK_DIR',
               p_filename => :P5_PRODUCT_IMAGE);
END;

YouTube Link
How to Store Blob File Outside Oracle Database In Oracle APEX