### Sample custom installation script Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/splitting-export-files-into-separate-sql-scripts.html A sample custom installation file that runs the environment setup, page scripts, and environment teardown. It includes a typo in the last line (end_environment.sq instead of .sql) as shown in the source. ```sql @@application/set_environment.sql @@application/pages/page_00001.sql @@application/pages/page_00023.sql @@application/pages/page_00095.sql @@application/end_environment.sq ``` -------------------------------- ### Welcome Message Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/creating-an-ai-assistant-to-suggest-salary.html Optional welcome message shown by the AI Assistant when it appears. Provided as an example for the Welcome Message attribute. ```text Hi. I'm your personal HR assistant. I can assist you in calculating salary raises. ``` -------------------------------- ### HTML region example: &APP_USER substitution Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html HTML region example that displays the current user using the &APP_USER. substitution string. ```text Copy Hello you are logged in as &APP_USER. ``` -------------------------------- ### PL/SQL example: htp.p with V('APP_USER') Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html PL/SQL example that outputs the current user using the V('APP_USER') function. ```plsql Copy htp.p('Hello you are logged in as'||V('APP_USER')); ``` -------------------------------- ### Bind variable example: :APP_USER Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Bind variable example that uses :APP_USER in a WHERE clause. ```sql Copy SELECT * FROM some_table WHERE user_id = :APP_USER ``` -------------------------------- ### APEX_CSP_DISPLAY_NONE example in Universal Theme region template Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example of using APEX_CSP_DISPLAY_NONE in the Inline Dialog region template. ```html
#BODY##SUB_REGIONS#
#PREVIOUS##DELETE##CLOSE#
#EDIT##CREATE##NEXT#
``` -------------------------------- ### APP_ALIAS in f?p URL Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example of using the APP_ALIAS substitution string in a URL to run an application by its alias. ```plaintext f?p=ABC:1:&APP_SESSION. ``` -------------------------------- ### HTML example: &APP_TITLE Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html HTML region example that inserts the value of the APP_TITLE substitution string with HTML escaping. ```text Copy The title of the current application is: &APP_TITLE!HTML. ``` -------------------------------- ### Immediate Action Prompt Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/creating-an-ai-assistant-to-suggest-salary.html Immediate Action Prompt used with the Initial Prompt to trigger an immediate AI-generated response. ```text Calculate the salary suggestion ``` -------------------------------- ### Connect to database as SYS on UNIX and Linux Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-instance-security.html Start SQLcl and connect to the database as SYS on UNIX and Linux. The command prompts for the SYS password. ```sql Copy $ sql /nolog SQL> CONNECT SYS as SYSDBA Enter password: SYS_password ``` -------------------------------- ### Create EBA_DEMO_APPR_METRICS table Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-task-definitions.html SQL script to create a metrics table required for the example. Run this in the SQL Commands page of the SQL Workshop before adding the action. ```sql CREATE TABLE EBA_DEMO_APPR_METRICS( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, TASK_DEF_STATIC_ID VARCHAR2(255), OUTCOME_CODE VARCHAR2(32), APPROVER VARCHAR2(255), APPROVAL_DATE DATE, DAYS_ELAPSED NUMBER ); ``` -------------------------------- ### Connect to database as SYS on Windows Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-instance-security.html Start SQLcl and connect to the database as SYS on Windows. The command prompts for the SYS password. ```sql Copy SYSTEM_DRIVE:\ sql /nolog SQL> CONNECT SYS as SYSDBA Enter password: SYS_password ``` -------------------------------- ### WORKSPACE_ID usage examples Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Examples of using WORKSPACE_ID in an HTML region, PL/SQL, bind variable, and SYS_CONTEXT. The SYS_CONTEXT example shows how to access the current workspace ID in queries and VPD policies. ```html Hello your workspace id is &WORKSPACE_ID. ``` ```plsql htp.p('Hello your workspace id is '||V('WORKSPACE_ID')); ``` ```sql SELECT * FROM some_table WHERE workspace_id = :WORKSPACE_ID ``` ```sql SELECT ... WHERE workspace_id = SYS_CONTEXT('APEX$SESSION', 'WORKSPACE_ID') ``` -------------------------------- ### With and apply directives example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-template-directives.html Example assigning values to placeholders TYPE, IMAGE, and ALT for the Template Component plug-in THEME$AVATAR. The ALT value uses an if/else directive to show 'No description.' when DESCRIPTION is empty. ```plaintext {with/} TYPE:=IMAGE IMAGE:=&IMAGE_URL. ALT:={if DESCRIPTION/}&DESCRIPTION.{else/}No description.{endif/} {apply THEME$AVATAR/} ``` -------------------------------- ### Include #TOP_PAGINATION# in Before Rows Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Example for the Before Rows attribute that includes the #TOP_PAGINATION# substitution string to place pagination above the report. The substitution string generates HTML starting and ending with table row tags. ```HTML #TOP_PAGINATION# ``` -------------------------------- ### Generic Column Template Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Generic column template example using substitution strings #ALIGNMENT# and #COLUMN_VALUE#. The #ALIGNMENT# is replaced by the user-specified alignment and #COLUMN_VALUE# by the column's value from the SQL query result. ```html ``` -------------------------------- ### Example Interactive Grid Request Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/linking-to-interactive-grid-reports.html Example of the request syntax linking to the saved report with static ID 'dept20' in the interactive grid region with HTML DOM ID 'EMP'. ```plaintext IG[EMP]_dept20 ``` -------------------------------- ### Fill Opacity Substitution Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-maps.html Example of a substitution string for the Fill Opacity attribute, which supports substitutions. The value ranges from 0 (fully transparent) to 1.0 (opaque). ```plaintext &FILL_OPACITY. ``` -------------------------------- ### Substitution String Reference Example for APP_ID Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example of using the APP_ID substitution string in a URL to reference the current application ID. ```plaintext f?p=&APP_ID.:40:&APP_SESSION. ``` -------------------------------- ### Enable automation and start execution schedule immediately Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/about-importing-and-exporting-automations.html Use this PL/SQL block to enable an automation after importing an application. It creates a session and calls apex_automation.enable with the application ID and static ID. This starts the automation's execution schedule immediately. ```plsql declare l_app_id number := {new application ID}; begin apex_session.create_session( p_app_id => l_app_id, p_page_id => {one of the application pages}, p_username => '{workspace user}'); apex_automation.enable( p_application_id => l_app_id, p_static_id => '{automation static ID}'); end; ``` -------------------------------- ### SYS_CONTEXT example: SYS_CONTEXT('APEX$SESSION', 'APP_USER') Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Uses the SYS_CONTEXT variable to access the current application user. The APEX$SESSION context is set up when APEX starts processing an incoming request. Useful for queries and VPD security policies. ```sql Copy SELECT ... WHERE username = SYS_CONTEXT('APEX$SESSION', 'APP_USER') ``` -------------------------------- ### Example: Using APP_TEXT$MY_MESSAGE and APP_TEXT$MY_MESSAGE$FR Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example that displays the default and French translated text message MY_MESSAGE using the APP_TEXT$ built-in substitution syntax. The !HTML. escape is used to HTML-escape the substituted value. ```text Copy Default text: &APP_TEXT$MY_MESSAGE!HTML. Pardon my French: &APP_TEXT$MY_MESSAGE$FR!HTML. ``` -------------------------------- ### Generate Text with AI using APEX_AI Package Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/about-including-generative-ai-in-applications.html SQL Developer example that creates an APEX session, calls apex_ai.generate to generate a response, and outputs it. Requires an APEX session linked to an app with a configured Generative AI Service. Includes exception handling to delete the session on error. ```sql set serveroutput on; declare l_result clob; begin apex_session.create_session ( p_app_id => 102, p_page_id => 1, p_username => 'ADMIN' ); l_result := apex_ai.generate( 'what''s 1+1?' ); sys.dbms_output.put_line( l_result ); apex_session.delete_session; exception when others then apex_session.delete_session; raise; end; / ``` -------------------------------- ### PL/SQL procedure using placeholders and substitutions Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-remote-servers.html Example of a Configuration Procedure that uses placeholders (e.g., #app_id#) and populates substitutions with apex_string.plist_put. This allows dynamic replacement of values in the base URL. ```plsql procedure my_server_config( p_info in apex_plugin.t_remote_server_info, p_config out apex_plugin.t_remote_server_config ) is begin p_config.base_url := 'https://app#app_id#.example.com/#tenant#'; p_config.substitutions := apex_t_varchar2(); apex_string.plist_put( p_config.substitutions, 'app_id', v('APP_ID') ); apex_string.plist_put( p_config.substitutions, 'tenant', v('TENANT') ); end; ``` -------------------------------- ### Loop directive example with TAGS column Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-template-directives.html Example that takes a comma-separated list in a column called TAGS (e.g., 'apples,cherries,pears') and renders it as an HTML list with class 'tags'. Uses APEX$ITEM substitution. ```html ``` -------------------------------- ### Use placeholders with substitutions in Configuration Procedure Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-rest-enabled-sql-service-references.html Example Configuration Procedure that uses a placeholder '#cust#' in the base URL and populates the substitution value from item P3_CUSTOMER. For application_id 100, it sets the base URL to 'https://#cust#.example.com' and adds the substitution; otherwise uses 'https://test.example.com'. ```PL/SQL procedure my_server_config( p_info in apex_plugin.t_remote_server_info, p_config out apex_plugin.t_remote_server_config ) is begin if p_info.application_id = 100 then p_config.base_url := 'https://#cust#.example.com'; p_config.substitutions := apex_t_varchar2(); apex_string.plist_put( p_config.substitutions, 'cust', v('P3_CUSTOMER') ); else p_config.base_url := 'https://test.example.com'; end if; end; ``` -------------------------------- ### Stroke Width Substitution Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-maps.html Example of a substitution string for the Stroke Width attribute, which supports substitutions. Allowed values range from 0.05 to 10.0. ```plaintext &APP_STROKE_WIDTH. ``` -------------------------------- ### Case directive example using EMP table Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-template-directives.html Example using the sample EMP table. Displays compensation differently depending on JOB: for SALESMAN shows salary and commission, for PRESIDENT shows '--' instead of salary, and for all other jobs shows just salary. ```plaintext {case JOB/} {when SALESMAN/} &SAL. (&COMM.) {when PRESIDENT/} -- {otherwise/} &SAL. {endcase/} ``` -------------------------------- ### Navigate to Shared Components of Application 570 Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/apex-menu-bar.html Example search terms that navigate to the Shared Components page of application 570. ```text 570:s 570-s ``` -------------------------------- ### APP_ALIAS in HTML Anchor Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example of using the APP_ALIAS substitution string in an HTML anchor tag to link to a page in the current application. ```html Click me to go to page 1 of the current application ``` -------------------------------- ### my_auth_config procedure to configure authentication at runtime Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-procedure-to-configure-authentication-at-runtime.html Example procedure that configures authentication at runtime for multi-tenancy. It reads the HTTP_HOST environment variable to fetch tenant-specific configuration from a customer_tenants table and sets the authentication_name, substitutions, and tenant_id attributes of the p_conf parameter. The procedure runs at least once per session on the first request, and must set the same values on each call. ```PL/SQL procedure my_auth_config ( p_conf in out nocopy apex_authentication.t_configuration ) is l_host varchar2(32767) := sys.owa_util.get_cgi_env('HTTP_HOST'); begin for i in ( select discovery_url, auth_scheme_name, credential, tenant_id from customer_tenants where hostname = l_host ) loop p_conf.authentication_name := i.auth_scheme_name; p_conf.substitutions := apex_t_varchar2 ( 'DISCOVERY_URL' , i.discovery_url, 'CREDENTIAL_STATIC_ID', i.credential ); p_conf.tenant_id := i.tenant_id; end loop; end my_auth_config; ``` -------------------------------- ### JSON Array Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-page-items.html Example of a JSON array of scalar values stored in the source column. If the column contains invalid JSON syntax, APEX will raise error messages. ```json ["APEX", "SQL", "PL/SQL"] ``` -------------------------------- ### f?p URL with REQUEST set to GO Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example of using REQUEST in Show processing by navigating to a page with f?p syntax. The fourth argument is REQUEST, setting its value to 'GO' for application 100, page 1. ```text f?p=100:1:&APP_SESSION.:GO ``` -------------------------------- ### BRANCH_TO_PAGE_ACCEPT request example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Use this request in Show processing to automatically submit the page. It is equivalent to navigating to page 1, entering a value into item P1_DATA, and clicking a button that submits the page with a SAVE request. ```text f?p=100:1:&APP_SESSION.:BRANCH_TO_PAGE_ACCEPT|SAVE:::P1_DATA:value ``` -------------------------------- ### SYS_CONTEXT Variable Example for APP_ID Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Example of using SYS_CONTEXT with the APEX$SESSION context to access the current application ID in a SQL query. ```sql SELECT ... WHERE application_id = SYS_CONTEXT('APEX$SESSION', 'APP_ID') ``` -------------------------------- ### APP_SESSION: SQL query example using bind variable Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Uses the bind variable :APP_SESSION in a SQL query to generate a hyperlink with the session ID. ```sql SELECT htf.anchor('f?p=100:5:'||:APP_SESSION,'click me') FROM DUAL; ``` -------------------------------- ### APEX_MAIL.SEND with Template and Placeholders Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-email-templates.html Example from the Load Order Details sample template showing how to send an email using a template. It uses APEX_MAIL.SEND with p_to, p_template_static_id, and p_placeholders parameters. The placeholders are built as a JSON string with APEX_JSON.STRINGIFY for each value. ```plsql begin apex_mail.send ( p_to => email_address_of_user, p_template_static_id => '', p_placeholders => '{' ' "CUSTOMER_NAME":' || apex_json.stringify( some_value ) ' ,"ITEMS_ORDERED":' || apex_json.stringify( some_value ) ' ,"MY_APPLICATION_LINK":' || apex_json.stringify( some_value ) ' ,"ORDER_DATE":' || apex_json.stringify( some_value ) ' ,"ORDER_NUMBER":' || apex_json.stringify( some_value ) ' ,"ORDER_TOTAL":' || apex_json.stringify( some_value ) ' ,"ORDER_URL":' || apex_json.stringify( some_value ) ' ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( some_value ) ' ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( some_value ) ' ,"SHIP_TO":' || apex_json.stringify( some_value ) '}' ); end; ``` -------------------------------- ### Compute Example: CASE WHEN A = 'SALES' THEN B + C ELSE B END Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/interactive-report-messages-requiring-translation.html Example computation for the Compute attribute in an Interactive Report. It references columns by their aliases (A, B, C) and returns B plus C when A equals 'SALES', otherwise returns B. ```sql CASE WHEN A = 'SALES' THEN B + C ELSE B END ``` -------------------------------- ### Unescaped dynamic output with htp.p Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/cross-site-scripting-protection.html Example of rendering an item from session state without escaping, which may be unsafe. ```plsql htp.p(v('SOME_ITEM')); ``` -------------------------------- ### PL/SQL Function Body Returning VARCHAR2 example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-plug-ins.html A PL/SQL function body that returns a result string based on a calculated price. Used as an example for the PL/SQL Function Body Returning VARCHAR2 attribute type. ```plsql declare l_price number; begin select product_price into l_price from demo_products where id = :P7_ID; return calculate_new_price(l_price); end; ``` -------------------------------- ### Enable SQL tracing with p_trace=YES in f?p URL Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/enabling-sql-tracing-and-using-tkprof.html Append p_trace=YES to the f?p URL to trace a specific page request. For example, tracing page 1 of application 100 uses the URL shown. ```text http:/.../f?p=100:1&p_trace=YES ``` -------------------------------- ### Get Theme Styles via apex.utr.config() Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-theme-roller.html Call apex.utr.config() without parameters in the browser developer console to retrieve the current custom CSS and LESS configuration. ```javascript apex.utr.config(); ``` -------------------------------- ### PL/SQL REQUEST condition examples Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/creating-buttons.html Examples of PL/SQL expressions that check the REQUEST bind variable to conditionally process based on which button was clicked. Assumes buttons named EDIT and DELETE exist. Note that button name capitalization is preserved. ```plsql If :REQUEST in ('EDIT','DELETE') then ... If :REQUEST != 'DELETE' then ... ``` -------------------------------- ### Call a page using application and page aliases Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-legacy-url-syntax.html Use this URL to call a page within an application using an application alias and a page alias. The example runs the page 'home' of the application 'myapp' and uses the current session ID. Aliases must be valid Oracle identifiers, cannot contain whitespace, and are case-insensitive. ```plaintext f?p=myapp:home:&APP_SESSION. ``` -------------------------------- ### PL/SQL conditional check on REQUEST Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html PL/SQL example that checks if REQUEST equals 'GO' and prints 'hello' using htp.p. Demonstrates referencing REQUEST in Show processing. ```plsql IF V ('REQUEST') = 'GO' THEN htp.p('hello'); END IF; ``` -------------------------------- ### Clear Cache for Entire Application using f?p syntax Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-session-state-values.html Clears the application's cache by using f?p syntax with the keyword APP in the Clear Cache argument. This example uses the URL format f?p=App:Page:Session::NO:APP. ```plaintext f?p=App:Page:Session::NO:APP ``` -------------------------------- ### activity.sql help command Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/viewing-debug-messages.html Run this command to see usage instructions for the activity.sql script. The script prints recent activity log entries and takes a comma-separated list of settings as a parameter. ```sql SQL> @activity help ``` -------------------------------- ### Add user role with APEX_ACL.ADD_USER_ROLE Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/how-access-control-works.html Use this PL/SQL block to add a user as Administrator in an application. The example adds user 'SCOTT' to application 255 with role static ID 'ADMINISTRATOR'. This API can also be executed from the command line or in an install script. ```plsql begin APEX_ACL.ADD_USER_ROLE ( p_application_id => 255, p_user_name => 'SCOTT', p_role_static_id => 'ADMINISTRATOR' ); end; ``` -------------------------------- ### Content-Security-Policy header example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-csp-in-apex.html Use this header to enforce a strict CSP policy. It blocks any resources or actions that do not comply with the rules. The #APEX_CSP_NONCE# substitution string is used to allow inline scripts and styles with a nonce. ```http Content-Security-Policy: default-src 'self' #APEX_CSP_NONCE#; object-src 'none'; img-src 'self' data:; ``` -------------------------------- ### Enable synchronization and start execution schedule immediately Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-data-synchronization.html Use this PL/SQL block to enable a REST Source Synchronization immediately after importing an application. It creates a session and calls apex_rest_source_sync.enable with the new application ID and REST Source static ID. ```plsql declare l_app_id number := {new application ID}; begin apex_session.create_session( p_app_id => l_app_id, p_page_id => {one of the application pages}, p_username => '{workspace user}'); apex_rest_source_sync.enable( p_application_id => l_app_id, p_module_static_id => '{REST Source static ID}'); end; ``` -------------------------------- ### Query APEX_COLLECTIONS view Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/about-using-collections.html Example query that accesses collection members using the APEX_COLLECTIONS view. The collection name is specified in uppercase as required. ```sql SELECT c001, c002, c003, n001, d001, clob001 FROM APEX_collections WHERE collection_name = 'DEPARTMENTS' ``` -------------------------------- ### Appearance Title Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/creating-an-ai-assistant-to-suggest-salary.html Title that displays on the dialog for the AI Assistant. Entered in the Appearance, Title attribute. ```text Personal HR Assistant ``` -------------------------------- ### Call a page with workspace specification using &c argument Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-legacy-url-syntax.html Use this URL when an application alias is not unique within the workspace. The &c argument specifies the workspace name. The example calls the page 'home' of the application 'common_alias' in workspace 'WORKSPACE_A'. ```plaintext f?p=common_alias:home:&APP_SESSION.&c=WORKSPACE_A ``` -------------------------------- ### Call a page from a button URL Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-legacy-url-syntax.html Use this URL to redirect to a page when a button is clicked. The example runs page 6001 of application 6000 and uses the current session ID. This method bypasses page submission and acts as a hyperlink. ```plaintext f?p=6000:6001:&APP_SESSION. ``` -------------------------------- ### Bind Variable Syntax for SQL Query or LOV Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-session-state-values.html Shows the basic syntax for referencing session state in regions defined as SQL Query, SQL Query (plsql function body returning SQL query), or LOV. The example is simply :MY_ITEM. ```sql :MY_ITEM ``` -------------------------------- ### Bind Variable Syntax in SQL Query Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-session-state-values.html Shows how to reference a page item as a bind variable in a SQL query. The example uses :SEARCH_STRING in a WHERE clause to filter employees by last name. ```sql SELECT * FROM employees WHERE last_name like '%' || :SEARCH_STRING || '%' ``` -------------------------------- ### APP_SESSION: SYS_CONTEXT example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Uses SYS_CONTEXT('APEX$SESSION', 'APP_SESSION') to access the current application session in queries or VPD policies. ```sql SELECT ... WHERE apex_session_id = SYS_CONTEXT('APEX$SESSION', 'APP_SESSION') ``` -------------------------------- ### Hierarchical Dynamic List Query Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/creating-dynamic-lists.html Creates a hierarchical list of employees using CONNECT BY, with the level in the first column. Starts with employees having no manager. ```SQL select level c1_level, ename c2_name_for_label apex_page.get_url(p_page => 10, p_items => 'P10_EMPNO', p_values => EMPNO) c3_target_url, null c4_is_current, 'fa-user' c5_icon_name from emp connect by prior empno = mgr start with mgr is null ``` -------------------------------- ### Content-Security-Policy-Report-Only header example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-csp-in-apex.html Use this header to test CSP settings without enforcing them. Violations generate reports instead of blocking content, making it ideal for testing and refining the policy before enforcement. ```http Content-Security-Policy-Report-Only: default-src 'self' #APEX_CSP_NONCE#; object-src 'none'; img-src 'self' data:; ``` -------------------------------- ### Named column template with column name substitutions Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Named column template example that references columns by name using substitution strings like #ENAME# and #SAL#. Suitable only for reports based on those columns. ```HTML ``` -------------------------------- ### Escape open bracket example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-template-directives.html Example where the open bracket must be escaped because a directive follows on the same line. The escaped sequence is {{/}. ```html The coordinates {{/}c, d} = {if VAL/}&VAL.{else/}unknown{endif/} ``` -------------------------------- ### Navigate to Page 10 of Application 570 Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/apex-menu-bar.html Example search terms that navigate to page 10 of application 570. The first is a page ID, the second uses the application ID and page ID with a dash, and the third uses a colon. ```text 10 570-10 570:10 ``` -------------------------------- ### Next Set Template: Link with #LINK# Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Example for Next Set Template attribute. Modifies how the Next Set portion of the pagination subtemplate appears. ```html next set ``` -------------------------------- ### HTML Inline Script with Nonce Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-content-security-policy-csp.html An inline script with a valid nonce is allowed to execute. This example shows a script with nonce="random12345" that will run because it has the correct nonce. ```html ``` -------------------------------- ### APP_SESSION_VISIBLE: SQL query example using bind variable Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Uses the bind variable :APP_SESSION_VISIBLE in a SQL query to generate a hyperlink with the session ID. ```sql SELECT sys.htf.anchor('f?p=100:5:'||:APP_SESSION_VISIBLE,'clickme') FROM DUAL; ``` -------------------------------- ### example_ai_navigation Package Body Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/about-including-generative-ai-in-applications.html Defines the package body with navigation_handler and ai_request_handler procedures. navigation_handler validates AI-generated parameters and constructs user-authorized URLs, denying access with a non-navigating link when unauthorized. ai_request_handler adds hardcoded navigation targets for the order-assistant AI Agent. ```PL/SQL create or replace package body example_ai_navigation as --============================================================================== -- Validates AI-generated parameters and constructs a user-authorized URL. --============================================================================== procedure navigation_handler ( p_param in apex_ai.t_chat_navigation_param, p_result in out nocopy apex_ai.t_chat_navigation_result ) as l_order_id number; ------------------------------------------------------------------------------- -- Replaces a denied destination with a non-navigating link. procedure deny_link is begin p_result.link_url := '#'; p_result.link_label := 'Not allowed'; p_result.icon_classes := 'fa fa-lock'; p_result.link_attributes := 'aria-disabled="true"'; end deny_link; ------------------------------------------------------------------------------- begin case p_param.target.static_id when 'orders' then if example_navigation_security.can_view_orders ( p_user_name => V( 'APP_USER' ) ) then p_result.link_url := apex_page.get_url ( p_page => 10, p_clear_cache => '10' ); else deny_link; end if; when 'order-details' then l_order_id := example_navigation_security.get_authorized_order_id ( p_user_name => V( 'APP_USER' ), p_value => p_param.parameters( 'ORDER_ID' ) ); if l_order_id is null then deny_link; else p_result.link_url := apex_page.get_url ( p_page => 20, p_clear_cache => '20', p_items => 'P20_ORDER_ID', p_values => l_order_id ); end if; else deny_link; end case; end navigation_handler; --============================================================================== -- Adds two hardcoded targets only for the order-assistant AI Agent. --============================================================================== procedure ai_request_handler ( p_param in apex_ai.t_chat_request_handler_param, p_result in out nocopy apex_ai.t_chat_request_handler_result ) as begin if p_param.agent.static_id = 'order-assistant' then p_result.request.navigation := apex_ai.t_navigation ( callback_procedure => 'EXAMPLE_AI_NAVIGATION.NAVIGATION_HANDLER', targets => apex_ai.t_navigation_targets ( apex_ai.t_navigation_target ( static_id => 'orders', ai_hint => 'Open the report that shows orders available to the current user.', link_label => 'Orders', icon_classes => 'fa fa-table' ), apex_ai.t_navigation_target ( static_id => 'order-details', ai_hint => 'Open one order by its order ID.', link_label => 'Order Details', ui_description => 'View an order.', icon_classes => 'fa fa-file-text-o', parameters => apex_ai.t_navigation_target_params ( apex_ai.t_navigation_target_param ( static_id => 'ORDER_ID', ai_hint => 'The order ID explicitly provided by the user.', is_required => true ) ) ) ) ); end if; end ai_request_handler; end example_ai_navigation; / ``` -------------------------------- ### PLSQL Expression - Example expression Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/how-does-page-processing-and-page-rendering-work.html A PL/SQL expression that evaluates to true or false. The example checks if the value of :MY_ITEM is 'YES'. ```plsql NVL(:MY_ITEM,'NO') = 'YES' ``` -------------------------------- ### Clear Cache for Current User Session using f?p syntax Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/managing-session-state-values.html Clears the application's cache for the current user session by using f?p syntax with the keyword SESSION in the Clear Cache argument. This example uses the URL format f?p=6000:6004:12507785108488427528::NO:SESSION. ```plaintext f?p=6000:6004:12507785108488427528::NO:SESSION ``` -------------------------------- ### Query BOSS REST Data with apex_exec.open_rest_source_query Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-boss-rest-data-sources-in-pl-sql.html Use this PL/SQL block to query BOSS REST data sources with a filter and an order-by expression. It creates a context with apex_exec.open_rest_source_query, iterates with next_row, and reads attributes using apex_exec.get_number, get_varchar2, and get_date. Ensure you call apex_exec.close in an exception handler to release resources; the example filters by resourceId and winPct and orders by amount descending, returning five rows. ```plsql declare l_ctx apex_exec.t_context; -- other declarations here begin -- Process top five winnable opportunties for salesperson 12345 by amount -- get_opportunities_columns() returns apex_exec.t_columns for columns -- corresponding to fields: optyId,amount,customerName,closeDate l_ctx := apex_exec.open_rest_source_query( p_static_id => 'opportunities', p_columns => get_opportunities_columns, p_external_filter_expr => 'resourceId = 12345 and winPct > 0.80', p_external_order_by_expr => 'amount:desc' p_max_rows => 5); while apex_exec.next_row(l_ctx) loop l_amount := apex_exec.get_number(l_ctx,'AMOUNT'); l_customer := apex_exec.get_varchar2(l_ctx,'CUSTOMERNAME'); l_closes := apex_exec.get_date(l_ctx,'CLOSEDATE'); -- Work with data here... end loop; apex_exec.close(l_ctx); exception when others then apex_exec.close(l_ctx); raise; end; ``` -------------------------------- ### APP_SESSION: PL/SQL example using htf.anchor Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html Uses the PL/SQL function htf.anchor to create a hyperlink with the APP_SESSION value. The V('APP_SESSION') function retrieves the session ID. ```plsql htf.anchor('f?p=100:5:'||V('APP_SESSION'),'click me'); ``` -------------------------------- ### Previous Set Template: Link with #LINK# Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Example for Previous Set Template attribute. Modifies how the Previous Set portion of the pagination subtemplate appears. ```html previous set ``` -------------------------------- ### f?p syntax with debug=YES Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/Using-url-parameters-to-control-debug-mode.html Use this f?p URL to run an application in debug mode with the default debug level (LEVEL4). The debug parameter is set to YES. ```url f?p=100:1:&APP_SESSION.::YES ``` -------------------------------- ### Package Specification for AI Navigation Handlers Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/about-including-generative-ai-in-applications.html Defines the package specification for example_ai_navigation, including procedures for validating AI-selected navigation targets and adding navigation targets to Show AI Assistant requests. The package uses authid definer and references placeholder package EXAMPLE_NAVIGATION_SECURITY for validation routines. ```plsql create or replace package example_ai_navigation authid definer as --============================================================================== -- Validates an AI-selected navigation target and returns its final URL. -- -- Parameters: -- * p_param: Selected target, AI Agent context, and untrusted parameter values. -- * p_result: Final URL and presentation properties returned to APEX. --============================================================================== procedure navigation_handler ( p_param in apex_ai.t_chat_navigation_param, p_result in out nocopy apex_ai.t_chat_navigation_result ); --============================================================================== -- Adds application-approved navigation targets to Show AI Assistant requests. -- -- Parameters: -- * p_param: Context for the current AI request. -- * p_result: Request configuration populated with navigation targets. --============================================================================== procedure ai_request_handler ( p_param in apex_ai.t_chat_request_handler_param, p_result in out nocopy apex_ai.t_chat_request_handler_result ); end example_ai_navigation; / ``` -------------------------------- ### SQL example: Using APP_UNIQUE_PAGE_ID in a URL Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-available-built-in-substitution-strings.html SQL query that constructs a unique URL using APP_UNIQUE_PAGE_ID in the request column, to help avoid browser caching problems. ```sql Copy SELECT 'f?p=100:1:'||:APP_SESSION||':'||:APP_UNIQUE_PAGE_ID|| ':::P1_EMPNO:'||employee_id, first_name, job_id FROM employees ``` -------------------------------- ### Identify Column Headers with #1# Syntax Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Example of using column header substitution strings #1#, #2#, #3# within an HTML table row to identify column headers. ```HTML ``` -------------------------------- ### SQL Query Format for Quick Picks Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-page-items.html Enter the SQL query definition to populate quick picks. Each column must have a unique name or alias; use an alias on any column with a SQL expression. The quick picks must be valid and consistent with the List of Values. ```sql select [displayValue], [returnValue] from ... where ... order by ... ``` -------------------------------- ### Friendly URL syntax with debug=YES Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/Using-url-parameters-to-control-debug-mode.html Use this URL to run an application in debug mode with the default debug level (LEVEL4). The debug query parameter is set to YES. ```url https://:/ords/r/workspace_name/application_name/page_name?session=&APP_SESSION.&debug=YES ``` -------------------------------- ### JSON_TABLE SQL Function Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-page-items.html Use the JSON_TABLE SQL function to process a JSON array value. The example extracts each element from the JSON array. ```sql select element from json_table ( :ITEM_NAME, '$[*]' columns element varchar2 path '$' ); ``` -------------------------------- ### Typical usage of APEX debug utility scripts Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/viewing-debug-messages.html Shows a typical workflow: enable debugging, reproduce the problem, disable debugging, list debug-enabled requests with d1.sql, and view details for a specific page view with d2.sql. The output includes the report from d1.sql and the editor content from d2.sql. ```sql $ cd apex/utilities/debug $ sql / as SYSDBA SQL> define _editor=vim SQL> @d0 Changed debug level from "" to "9" -- Reproduce the problem SQL> @d0 Changed debug level from "9" to "" SQL> @d1 PAGE_VIEW_ID STARTED SECS LVL CNT PATH_INFO FLOW_PAGE SESSION_ID WORKSPACE APEX_USER ____________ ________ ______ ___ ____ ___________ ___________ _______________ _________ _________ @d2 1002 14:02:48 10.84 WRN 261 100-4750:50 8488213505163 DEMO DEMO @d2 1003 14:12:22 0.87 2603 show 100:9 0-5628985429897 DEMO DEMO @d2 1006 14:12:23 0.75 252 ajax plugin 100:9 0-5628985429897 DEMO DEMO @d2 1007 14:12:41 2.04 297 ajax plugin 100:9 0-5628985429897 DEMO DEMO 4 rows selected. SQL> @d2 1003 -- This will open the editor program you defined above, in this case vim with content similar to the following: SECS MESSAGE_LEVEL MESSAGE FLOW_PAGE SID_SGID APEX_USER CALL_STACK1 ____ _____________ _______ _________ ________ _________ ___________ ... ``` -------------------------------- ### Friendly URL syntax with debug=LEVEL9 Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/Using-url-parameters-to-control-debug-mode.html Use this URL to run an application in debug mode with the most detailed debug level (LEVEL9). ```url https://:/ords/r/workspace_name/application_name/page_name?session=&APP_SESSION.&debug=LEVEL9 ``` -------------------------------- ### PL/SQL Code attribute type example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-plug-ins.html An anonymous PL/SQL block that updates employee salaries by 10%. Used as an example for the PL/SQL Code attribute type. ```plsql begin update emp set sal=sal*1.1; end; ``` -------------------------------- ### Pagination Subtemplate HTML examples Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Each of the five attributes accepts HTML with the substitution strings shown. The Pagination Template uses #TEXT# to display the entire pagination section; the individual page templates use #LINK# to set the href target. ```html #TEXT# ``` ```html next ``` ```html previous ``` ```html next set ``` ```html previous set ``` -------------------------------- ### APEX_STRING.SPLIT Example for Delimited List Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-page-items.html Use the APEX_STRING.SPLIT function to process a delimited list value, such as 'APEX,SQL,PL/SQL'. The example selects column_value from the split result. ```sql select column_value from apex_string.split ( :ITEM_NAME, ',' ); ``` -------------------------------- ### PL/SQL procedure to change base URL based on application ID Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/understanding-remote-servers.html Example of a Configuration Procedure that sets the base URL to a different value when the application ID is 100. Use this in the Configuration Procedure attribute of a remote server. ```plsql procedure my_server_config( p_info in apex_plugin.t_remote_server_info, p_config out apex_plugin.t_remote_server_config ) is begin if v('APP_ID') = 100 then p_config.base_url := 'https://app100.example.com'; else p_config.base_url := 'https://example.com'; end if; end; ``` -------------------------------- ### Column Heading Template Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Column Heading Template to colorize each column header cell. The text must include 'Help' to indicate where the heading text should be inserted. Entering 'OMIT' suppresses column headings entirely. ```html ``` -------------------------------- ### PL/SQL Expression Returning VARCHAR2 example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-plug-ins.html A PL/SQL expression that returns TRUE or FALSE based on the value of :P7_CATEGORY. Used as an example for the PL/SQL Expression Returning VARCHAR2 attribute type. ```plsql case when :P7_CATEGORY='computer' then true else false end ``` -------------------------------- ### Insert BOSS REST Data with apex_exec.add_dml_row and execute_dml Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-boss-rest-data-sources-in-pl-sql.html Use this PL/SQL block to insert data into a BOSS REST data source. It opens a DML context with apex_exec.open_rest_source_dml_context, adds rows with add_dml_row, sets attribute values with set_value, and commits with execute_dml. The example inserts two rows for 'Tesla' and 'Apple' and disables lost update detection via apex_exec.c_lost_update_none; close the context in an exception handler. ```plsql declare l_ctx apex_exec.t_context; -- other declarations here begin -- Insert two new opportunities -- get_opportunities_columns() returns apex_exec.t_columns for columns -- corresponding to fields: APEX$ResourceKey,optyId,amount,customerName,closeDate l_ctx := apex_exec.open_rest_source_dml_context( p_static_id => 'opportunities', p_columns => get_opportunities_columns, p_lost_update_detection => apex_exec.c_lost_update_none); apex_exec.add_dml_row(l_ctx, apex_exec.c_dml_operation_insert); apex_exec.set_value(l_ctx,'AMOUNT',45000); apex_exec.set_value(l_ctx,'CUSTOMERNAME','Tesla'); apex_exec.set_value(l_ctx,'CLOSEDATE','2024-05-11'); apex_exec.add_dml_row(l_ctx, apex_exec.c_dml_operation_insert); apex_exec.set_value(l_ctx,'AMOUNT',1500000); apex_exec.set_value(l_ctx,'CUSTOMERNAME','Apple'); apex_exec.set_value(l_ctx,'CLOSEDATE','2024-06-30'); -- Save the changes apex_exec.execute_dml(l_ctx); apex_exec.close(l_ctx); exception when others then apex_exec.close(l_ctx); raise; end; ``` -------------------------------- ### PL/SQL Expression Returning VARCHAR2 example (returning 'Y' or 'N') Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-plug-ins.html A PL/SQL expression that returns 'Y' or 'N' based on the value of :P7_CATEGORY. Used as an example for the PL/SQL Expression Returning VARCHAR2 attribute type. ```plsql case when :P7_CATEGORY='computer' then 'Y' else 'N' end ``` -------------------------------- ### Natural Language Query Example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/adding-natural-language-support-to-interactive-reports.html Example natural language query to filter the interactive report: 'Show projects where the Assignee is Tyson King and highlight rows in yellow if the cost is greater than 5000.' ```text Show projects where the Assignee is Tyson King and highlight rows in yellow if the cost is greater than 5000. ``` -------------------------------- ### Comment directive example with HTML span Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/using-template-directives.html Example showing a comment directive embedded in an HTML span element. The comment reminds the developer to replace a hard-coded English string with a localizable text message. ```html Name: &P1_NAME. {!to do replace Name: with text message/} ``` -------------------------------- ### Named column template with position notation Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/report-templates.html Named column template example showing both named substitution and position notation (#1#, #2#). The source presents these two rows as part of the same displayed example. ```HTML ``` -------------------------------- ### Rows returned - Example SQL query Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/how-does-page-processing-and-page-rendering-work.html A SQL query used by the 'Rows returned' condition. The condition evaluates to true if the query returns one or more rows. The example uses a bind variable :P101_DEPTNO. ```sql SELECT 1 FROM employees WHERE department_id = :P101_DEPTNO ``` -------------------------------- ### PL/SQL Function Body Returning BOOLEAN example Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/editing-plug-ins.html A PL/SQL function body that returns TRUE or FALSE based on a calculated price. Used as an example for the PL/SQL Function Body Returning BOOLEAN attribute type. ```plsql declare l_price number; begin select product_price into l_price from demo_products where id = :P7_ID; return case when calculate_new_price(l_price) > 1000 then true else false end; end; ``` -------------------------------- ### Static value for initial prompt Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/creating-an-ai-assistant-to-suggest-salary.html Static value for the initial prompt, using substitution strings to include the department name and current salary. This text is sent to the AI assistant when the button is clicked. ```plaintext Department: &P9_DEPARTMENT. Current salary: &P9_SAL. ``` -------------------------------- ### TKPROF utility command-line syntax Source: https://docs.oracle.com/en/database/oracle/apex/26.2/htmdb/enabling-sql-tracing-and-using-tkprof.html Run TKPROF from the operating system prompt. The input and output files (filename1 and filename2) are the only required arguments; all other options are optional. ```text tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n] [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table] [explain=user/password] [record=filename4] [width=n] ```
#ALIGNMENT#>#COLUMN_VALUE#
#ENAME##SAL#
#1##2##3#Item Help Text
#ENAME##SAL#
#1##2#