Using Substitution Strings in Oracle APEX

Substitution strings in Oracle APEX are placeholders that enable you to dynamically insert values into your application’s text, SQL, PL/SQL, and other components at runtime. They provide a powerful way to customize content and behavior without hardcoding values, allowing for more flexible and maintainable applications. Understanding how to use substitution strings effectively is essential for any Oracle APEX developer.

How to Use Substitution Strings in Oracle APEX

Substitution strings use the syntax &STRING_NAME. to represent a placeholder that Oracle APEX replaces with a corresponding value during page rendering or code execution. These strings can reference various elements such as page items, application-level items, request parameters, or even custom substitution variables defined in your application.

Basic Usage Example:

If you have a page item named P1_USERNAME, you can reference its value using a substitution string like this:

Hello, &P1_USERNAME.

When the page renders, Oracle APEX substitutes &P1_USERNAME. with the current value of that page item.

Types of Substitution Strings

1. Page Item Substitution: References a page-level item value.

   • Example: &P1_ITEM_NAME.

2. Application Item Substitution: References an application-level item value.

   • Example: &APP_USER.

3. Built-in Substitutions: Oracle APEX defines built-in substitution strings such as &APP_ID., &APP_NAME., &APP_USER., etc.

4. Custom Substitution Strings: You can define your own substitution strings at the application or page level.

Using Substitution Strings in SQL and PL/SQL

Substitution strings can be used inside SQL queries or PL/SQL blocks to inject dynamic values:

SELECT * FROM employees WHERE department_id = &P2_DEPT_ID.

Note: When using substitution strings in SQL or PL/SQL, you must be careful as improper use can lead to SQL injection vulnerabilities. Use bind variables (:P2_DEPT_ID) where possible for better security and performance.

Using Substitution Strings in Page Text and Labels

You can use substitution strings in page item labels, region titles, and other text areas to personalize the interface based on the current user or session:

Welcome, &APP_USER., to the dashboard!

Substitution strings in these contexts are replaced at runtime with their current values.

Escaping Substitution Strings

If you need to display a literal ampersand & or a string that looks like a substitution string without substitution occurring, you can escape it by doubling the ampersand:

This will show &P1_ITEM_NAME. literally as &&P1_ITEM_NAME.

Best Practices for Using Substitution Strings

• Prefer using bind variables (:P1_ITEM) over substitution strings in SQL and PL/SQL to avoid SQL injection and improve performance.

• Use substitution strings primarily in text, labels, and static content where bind variables are not applicable.

• Validate and sanitize any values that might be substituted into dynamic SQL or PL/SQL to prevent security risks.

• Use meaningful and consistent naming conventions for page and application items to avoid confusion.

• Avoid excessive or unnecessary use of substitution strings to keep your application maintainable.

• Test substitution strings thoroughly to ensure correct values are substituted at runtime.

Examples

1. Using substitution in a region title:

Region Title: Sales Report for &P3_REGION_NAME.

2. Using substitution in a SQL query (with caution):

SELECT * FROM orders WHERE status = '&P4_ORDER_STATUS.'

3. Displaying current user in a page:

Logged in as: &APP_USER.

Substitution strings are used to replace or pass character strings within the application.

You can use a substitution string in page and application items and it always follows this format and these rules:

• Always starts with an ampersand (&).

• Always use capital letters.

• Always terminate the name with a  period(.).

Example:

&PXX_SOME_ITEM_NAME.

But for some other grid items it must be called as: # NAME_OF_COLUMN#

Substitution Strings can be used in the following:

• Interactive Grids

• Map Columns

• Cards

• Templates

Interactive grid syntax: &NAME_OF_COLUMN.

Syntax for other reports: # NAME_OF_COLUMN#

For Special Characters or Case Sensitive replacements use the  double quotes (“”)

Syntax is as follow: &”<name>”[!<format>].

• If the name is not quoted then it must be in the following range of characters A-Z-0-9_$#

• The name must be an application or page item.

• <format> refers to  predefined filtered names used for controlling or escaping.

• HTML

• ATTR

• JS

• RAW

• STRIPHTML

Example:

&”SOME_ITEM_NAME”.

Oracle APEX Documentation Link

For more detailed information on substitution strings, see the official Oracle APEX documentation:
https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/using-substitution-strings.html

Conclusion

Substitution strings in Oracle APEX are a versatile feature that allows you to inject dynamic content and values throughout your application. When used appropriately and securely, they enhance the flexibility and user experience of your applications. By understanding the types, syntax, and best practices for substitution strings, you can create more dynamic and personalized Oracle APEX applications.

Binding Within PL/SQL in Oracle APEX

 In Oracle APEX, PL/SQL is widely used to implement business logic, validations, computations, and processes. Binding page items within PL/SQL code is essential to interact dynamically with the data entered or selected by users. This allows PL/SQL to read or modify page item values, enabling responsive and context-aware application behavior. Understanding how to correctly bind page items within PL/SQL blocks is a fundamental skill for effective APEX development.

How to Bind Page Items Within PL/SQL

Binding page items in PL/SQL means referencing the values of page items inside PL/SQL code blocks. This is done using bind variable syntax, typically by prefixing the item name with a colon, for example :P1_ITEM_NAME. When PL/SQL runs on the server, Oracle APEX replaces these placeholders with the current value of the corresponding page item.

Using Bind Variables in PL/SQL Example:

BEGIN
  -- Read the value of P1_CUSTOMER_ID
  IF :P1_CUSTOMER_ID IS NOT NULL THEN
    -- Perform some processing based on the customer ID
    UPDATE customers
    SET last_order_date = SYSDATE
    WHERE customer_id = :P1_CUSTOMER_ID;
  END IF;
END;

This code updates the last order date for the customer whose ID is stored in the page item P1_CUSTOMER_ID.

Accessing Page Items Using APEX Functions

Oracle APEX also provides built-in PL/SQL functions to access page item values:

• v('P1_ITEM_NAME') returns the value of a page item as a VARCHAR2.

• nv('P1_NUMERIC_ITEM') returns the value of a numeric page item.

Example using v:

DECLARE
  l_username VARCHAR2(100);
BEGIN
  l_username := v('P1_USERNAME');
  -- Use l_username for further logic
END;

Note that using bind variables (:P1_ITEM_NAME) is generally preferred within PL/SQL blocks for performance and clarity, while v() and nv() functions are useful in certain package or function contexts.

Setting Page Item Values in PL/SQL

You can also assign values back to page items inside PL/SQL processes or computations:

BEGIN
  :P1_TOTAL_PRICE := :P1_UNIT_PRICE * :P1_QUANTITY;
END;

This code multiplies two page item values and stores the result in another page item.

Examples of Binding in Different Contexts

1. Validation Process:

BEGIN
  IF :P1_AGE < 18 THEN
    raise_application_error(-20001, 'You must be 18 or older.');
  END IF;
END;

2. Before Submit Process:

BEGIN
  INSERT INTO orders (order_id, customer_id)
  VALUES (:P1_ORDER_ID, :P1_CUSTOMER_ID);
END;

Best Practices for Binding Within PL/SQL

• Always use bind variable syntax (:P1_ITEM) when referencing page items in PL/SQL blocks to ensure clean and secure code.

• Avoid using substitution syntax (&P1_ITEM.) inside PL/SQL, as it can cause parsing issues and security risks.

• When referencing numeric values, ensure your item values are properly validated to avoid runtime errors.

• Use v() and nv() functions judiciously in package or function code where bind variables are not available.

• Always test your PL/SQL processes thoroughly to confirm the expected behavior of bound items.

• Maintain clear naming conventions for page items to avoid confusion when binding in code.

Just like SQL queries, use this type of variable binding (:XX_XXXXXX ) method when working with any of the following region types:

• Processes

• Computations

• Validations

• Conditions

• Anything defined via PL/SQL dynamic content.

Example:

IF :P01_SOME_VALUE  IS NULL THEN

----SOMETHING HERE ----

END IF;

Oracle APEX Documentation Link

For detailed information on PL/SQL integration in Oracle APEX, refer to the official documentation:
https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/plsql-in-oracle-apex.html

Conclusion

Binding within PL/SQL is a core aspect of Oracle APEX development that allows your applications to react to user input and execute dynamic logic based on page item values. Mastering the correct syntax and best practices for binding page items ensures your PL/SQL code is efficient, maintainable, and secure. By leveraging these techniques, you can build robust and interactive APEX applications that deliver great user experiences.

Binding Within LOV and SQL Query in Oracle APEX

In Oracle APEX, Lists of Values (LOVs) are essential for providing users with selectable values, often used in form items like select lists, pop-up LOVs, or shuttle items. To make these LOVs dynamic and context-sensitive, binding page items within the LOV's underlying SQL query is a powerful technique. This allows the LOV to adapt based on user input or other page state, delivering relevant and filtered choices. Understanding how to bind variables within LOV queries is critical for creating flexible and user-friendly applications.

How to Bind Page Items Within LOV SQL Queries

Binding page items within an LOV query involves referencing the values of other page items as bind variables in the SQL statement defining the LOV. This makes the LOV results depend on those values, updating automatically as the page items change.

Syntax for binding is similar to standard bind variables in Oracle APEX, using the colon prefix and the page item name, for example :P1_DEPT_ID.

Step-by-step example:

1. Suppose you have two page items:

   • P1_DEPT_ID (a select list to choose a department)

   • P1_EMP_ID (a select list that will show employees filtered by the selected department)

2. Create the LOV for P1_EMP_ID with this SQL query:

SELECT employee_name d, employee_id r
FROM employees
WHERE department_id = :P1_DEPT_ID
ORDER BY employee_name

This query uses :P1_DEPT_ID as a bind variable. When P1_DEPT_ID changes, the list of employees updates accordingly.

3. To make this dynamic, you typically add a Dynamic Action on the P1_DEPT_ID item that refreshes the P1_EMP_ID item or the region it belongs to.

More Details and Use Cases

• Cascading LOVs: Binding within LOVs is the basis for cascading select lists, where the options in one LOV depend on the selected value in another.

• Performance: Use bind variables in LOV queries instead of concatenating values to prevent SQL injection and improve performance through query caching.

• Null handling: When the bind variable is null, you can modify your query to return no rows or all rows, depending on business logic. For example:

WHERE (:P1_DEPT_ID IS NULL OR department_id = :P1_DEPT_ID)

• Substitution syntax like &P1_DEPT_ID. should be avoided in LOV queries as it can lead to security risks and parsing errors.

Examples

1. Simple LOV binding:

SELECT country_name d, country_code r
FROM countries
WHERE region_id = :P2_REGION_ID
ORDER BY country_name

2. Handling optional bind variable:

SELECT product_name d, product_id r
FROM products
WHERE (:P3_CATEGORY_ID IS NULL OR category_id = :P3_CATEGORY_ID)
ORDER BY product_name

Best Practices

• Always use bind variables (:ITEM_NAME) in LOV queries to protect against SQL injection.

• Refresh dependent LOV items or regions using Dynamic Actions when the bound page item changes.

• Provide fallback or default values to handle null bind variables gracefully.

• Test LOVs with various page item values to ensure expected behavior.

• Document dependencies clearly in your application for easier maintenance.

When you have a region that is a SQL Query, or a PL/SQL function returning a SQL Query, or a LOV (List of Values) you use the following syntax to bind variablles:

:SOME_ITEM_NAME

Example:

SELECT country_name

FROM countries

WHERE country_id = :P01_COUNTRY_ID

Oracle APEX Documentation Link

For more information on LOVs and binding, see the official Oracle APEX documentation:
https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/creating-and-managing-lists-of-values.html

Conclusion

Binding within LOV and SQL queries is a fundamental skill for Oracle APEX developers looking to build dynamic and context-aware forms. By referencing page items as bind variables in LOV SQL queries, you can tailor list content to user choices and improve application usability. Following best practices ensures your LOVs are secure, performant, and maintainable. Mastering this technique will enhance your ability to create rich, interactive user experiences in Oracle APEX applications.

Binding a Page Item to a SQL Call as a Variable in Oracle APEX

 In Oracle APEX, page items play a crucial role in capturing user input and interacting with SQL queries or PL/SQL processes. One of the most powerful features of APEX is the ability to bind these page items to SQL statements and PL/SQL blocks as variables. This enables dynamic filtering, flexible logic execution, and personalized content based on user interactions. Understanding how to properly bind page items to SQL calls is essential for building responsive and data-driven applications.

Using Page Items as Bind Variables in SQL

Binding a page item to a SQL statement allows the item’s value to influence the results returned by a query or executed in logic. Oracle APEX supports bind variable syntax using the colon (:) prefix for use inside SQL and PL/SQL code.

Syntax

SELECT * FROM employees WHERE department_id = :P1_DEPT_ID

In this example, :P1_DEPT_ID is a page item whose value will be used during the query execution. At runtime, APEX will substitute the value of that item when executing the SQL.

How to Bind Page Items in SQL Queries

1. Create a page item:

   • Go to your page in Page Designer.

   • Add a new item, for example: P1_EMP_ID.

2. Reference the item in your SQL query:

   • In a region (like Classic Report or Interactive Report), use the item in a WHERE clause:

     SELECT * FROM employees WHERE employee_id = :P1_EMP_ID
     

3. Use Dynamic Actions or processes to set the item’s value before the SQL query runs, if needed.

Using Page Items in PL/SQL Processes

In PL/SQL, you can access the value of a page item using the same bind variable syntax:

DECLARE
  l_emp_id NUMBER;
BEGIN
  l_emp_id := :P1_EMP_ID;

  UPDATE employees
  SET salary = salary * 1.1
  WHERE employee_id = l_emp_id;
END;

This approach is useful for processes like validations, computations, or custom buttons.

Detailed Example: Filter Report Based on Department

1. Create a select list item: P1_DEPT_ID

   • List of values query:

     SELECT department_name d, department_id r FROM departments
     

2. Create a Classic Report region:

   • SQL Query:

     SELECT * FROM employees
     WHERE department_id = :P1_DEPT_ID
     

3. Set the report to refresh using a Dynamic Action when the select list changes.

Best Practices

• Always validate user input before using it in dynamic SQL to prevent SQL injection.

• Use :ITEM_NAME syntax only in SQL and PL/SQL that are processed by the APEX engine.

• For stored procedures outside APEX, use APEX API functions to retrieve item values explicitly.

• Avoid using substitution syntax (&ITEM.) inside SQL queries—it is suitable only for static text and can pose security risks.

• Use page items with clear naming conventions (P1_, P2_) to avoid confusion across multiple pages.

• Used in SQL defined Regions.

• The name of the item must correspond to the variable name.

• Not case sensitive.

• Limited to 30 characters in length.

• The data type is always varchar2.

Example:

SELECT * FROM employees WHERE last_name like '%' || :SEARCH_STRING || '%'

Since the variable can only be a varchar2, you might need to convert it explicitly in order to make it work.

Example:

SELECT * FROM employees WHERE start_date < to_date(:DATE_STRING,'DD-MON-YYYY')

Oracle APEX Documentation Link

Refer to the official documentation on bind variables and SQL support here:
https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/using-bind-variables.html

Conclusion

Binding a page item to a SQL call as a variable is a foundational technique in Oracle APEX development. It enables the creation of interactive, responsive applications that respond to user input in real time. By leveraging the power of bind variables, you ensure your applications are not only dynamic but also secure and efficient. Mastering this concept will allow you to connect user interface elements directly to the database logic, providing a seamless experience for both developers and users.

Using APEX_UTIL.STRING_TO_TABLE to Convert Selected Values in Oracle APEX

 

In Oracle APEX, it's common to work with multiple values selected by a user from a form element such as a multi-select list, checkbox group, or shuttle item. These components typically return a comma-separated string of values. To effectively process these selections in PL/SQL—for example, to loop through them or use them in SQL queries—you need to convert the string into a PL/SQL collection. Oracle APEX provides the APEX_UTIL.STRING_TO_TABLE function specifically for this purpose. This built-in utility function simplifies the handling of delimited strings by turning them into a PL/SQL table (array) that can be easily looped through or used in queries.

Using APEX_UTIL.STRING_TO_TABLE in Detail

The APEX_UTIL.STRING_TO_TABLE function converts a delimited string into a PL/SQL table of VARCHAR2 values. This is extremely useful when you need to iterate through selected values or use them in IN clauses within dynamic SQL or PL/SQL blocks.

Syntax

APEX_UTIL.STRING_TO_TABLE (
    p_string   IN  VARCHAR2,
    p_separator IN  VARCHAR2 DEFAULT ','
) RETURN APEX_APPLICATION_GLOBAL.VC_ARR2;

• p_string: The string of values you want to convert (usually comma-separated).

• p_separator: The delimiter used in the string. Default is a comma.

Detailed Example 1: Loop Through Selected Values

Suppose you have a checkbox group item P1_DEPARTMENTS that returns a value like '10,20,30'.

DECLARE
  l_dept_ids APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_dept_ids := APEX_UTIL.STRING_TO_TABLE(:P1_DEPARTMENTS);
  
  FOR i IN 1 .. l_dept_ids.COUNT LOOP
    -- Process each department ID
    DBMS_OUTPUT.PUT_LINE('Selected Department ID: ' || l_dept_ids(i));
  END LOOP;
END;

This block reads the selected department IDs and prints them individually.

Detailed Example 2: Using in Dynamic SQL

DECLARE
  l_dept_ids APEX_APPLICATION_GLOBAL.VC_ARR2;
  l_sql      VARCHAR2(4000);
BEGIN
  l_dept_ids := APEX_UTIL.STRING_TO_TABLE(:P1_DEPARTMENTS);

  l_sql := 'SELECT * FROM employees WHERE department_id IN (';
  
  FOR i IN 1 .. l_dept_ids.COUNT LOOP
    IF i > 1 THEN
      l_sql := l_sql || ', ';
    END IF;
    l_sql := l_sql || l_dept_ids(i);
  END LOOP;

  l_sql := l_sql || ')';

  EXECUTE IMMEDIATE l_sql;
END;

This example dynamically builds and executes a SQL statement that filters employees based on selected department IDs.

Best Practices

• Always validate or sanitize user input before using it in dynamic SQL to prevent SQL injection.

• Use bind variables in dynamic SQL when possible for better performance and security.

• Avoid hardcoding delimiters—use the default comma unless your data specifically uses another delimiter.

• When working with numeric values, make sure they are converted properly to avoid data type mismatches.

• Use logging or debugging outputs (DBMS_OUTPUT) while developing to ensure your values are being parsed correctly.

Oracle APEX Documentation Link

For the official reference on APEX_UTIL.STRING_TO_TABLE, visit:
https://docs.oracle.com/en/database/oracle/apex/24.1/aeapi/APEX_UTIL.html#GUID-7E172D82-63ED-4A5E-9A3C-071DF00F6B34

Conclusion

The APEX_UTIL.STRING_TO_TABLE function is a powerful utility in Oracle APEX that bridges the gap between front-end item selections and backend PL/SQL logic. It enables developers to efficiently handle multiple selected values and use them in loops, validations, or dynamic SQL queries. By incorporating this function into your APEX development workflow, you can streamline your processing of multi-valued items and make your applications more dynamic and responsive. Always test thoroughly and leverage APEX’s rich built-in functionality to write clean, secure, and efficient code.

REFERENCING ITEMS USING JAVASCRIPT

In Oracle APEX, JavaScript plays a powerful role in enhancing interactivity and customizing the behavior of pages beyond what declarative settings offer. One of the most common tasks is referencing and manipulating page items using JavaScript. Whether you're dynamically setting values, validating inputs, or controlling visibility, knowing how to correctly reference APEX items with JavaScript is an essential skill for any developer working in the platform.

Referencing APEX Items with JavaScript

To reference a page item using JavaScript in Oracle APEX, the most common approach is using the apex.item API. This API provides a consistent way to get and set values, manage focus, disable or enable fields, and more.

1. Getting a Value from an Item

To retrieve the value of an item, use the getValue() method.

var myValue = apex.item("P1_EMP_NAME").getValue();

This will store the value of item P1_EMP_NAME into the JavaScript variable myValue.

2. Setting a Value to an Item

To set the value of an item, use the setValue() method.

apex.item("P1_EMP_NAME").setValue("John Smith");

This sets the item P1_EMP_NAME to "John Smith".

3. Enabling and Disabling Items

apex.item("P1_EMP_NAME").disable();
apex.item("P1_EMP_NAME").enable();

Use these methods to programmatically disable or enable an item on the page.

4. Focusing on an Item

apex.item("P1_EMP_NAME").setFocus();

This method puts the cursor focus on the specified item.

5. Working with Checkboxes and Radio Groups

For checkboxes, values are returned as comma-separated strings:

var selected = apex.item("P1_OPTIONS").getValue(); // e.g., "A,B,C"

To set multiple checkbox values:

apex.item("P1_OPTIONS").setValue(["A", "C"]);

6. Handling Hidden Items or Items in Dynamic Actions

Sometimes items may not be visible immediately due to being in conditional regions or hidden. In such cases, ensure the item's DOM element exists or wait for regions to render using dynamic action events or setTimeout() if necessary.

7. Using jQuery for Custom DOM Access

While apex.item() is preferred, jQuery can still be useful:

$("#P1_EMP_NAME").val(); // get value
$("#P1_EMP_NAME").val("Jane Doe"); // set value

However, avoid mixing too much jQuery manipulation with APEX APIs, as it can interfere with APEX internal processes.

Examples

Example: Conditionally show a region when a select list changes:

$("#P1_STATUS").change(function() {
  if ($(this).val() === "Active") {
    apex.region("emp_details").show();
  } else {
    apex.region("emp_details").hide();
  }
});

Example: Set default value on page load:

$(document).ready(function(){
  apex.item("P1_DEPT").setValue("HR");
});

Best Practices

• Use apex.item() whenever possible instead of raw DOM manipulation.

• Use proper naming conventions (P1_ITEM_NAME) for clarity and maintainability.

• Avoid hardcoding values unless necessary. Instead, use dynamic or context-sensitive values.

• Wrap JavaScript logic in dynamic actions when possible for better readability and reusability.

• Test across different browsers and devices to ensure your JavaScript works consistently.

• Use console.log() for debugging JavaScript values during development.

• Keep JavaScript in the Page Designer's "Execute When Page Loads" or in dynamic actions for organization.

Official Documentation

For a detailed overview of the apex.item JavaScript API and additional usage examples, refer to the official Oracle documentation:
https://docs.oracle.com/en/database/oracle/apex/24.1/aexjs/apex.item.html

Conclusion

Referencing and working with items using JavaScript in Oracle APEX gives you powerful control over your application's behavior and user interface. The apex.item API provides a reliable and consistent way to interact with items on the page. By following best practices and leveraging the available methods, you can build responsive, user-friendly APEX applications that adapt in real-time to user input. Always test and validate your scripts, and consult the documentation to stay up to date with APEX features and capabilities.

BINDING AND SUBSTITUTION SYNTAX

When working with item values in Oracle APEX, it's important to understand the correct syntax based on context. Different environments require different ways to reference item values, whether you're writing SQL queries, PL/SQL blocks, or setting values dynamically.

Syntax for Referencing Item Values

1. SQL Context

• Syntax: :MY_ITEM

• Use: Standard bind variable syntax for referencing items in SQL queries or inside PL/SQL blocks (within APEX).

• Notes: Use this when the item name is 30 characters or fewer. This is the preferred method in SQL.

2. PL/SQL Context – Character Items

• Syntax: V('MY_ITEM')

• Use: Use this inside PL/SQL code such as packages, functions, or stored procedures when referencing character item values.

• Notes: Avoid using this inside SQL queries, as it may lead to performance issues.

3. PL/SQL Context – Numeric Items

• Syntax: NV('MY_NUMERIC_ITEM')

• Use: Use this when referencing numeric items inside PL/SQL code (outside of SQL statements).

• Notes: As with V(), avoid using NV() in SQL queries to maintain performance.

4. Static Substitution in Text

• Syntax: &MY_ITEM.

• Use: Used for exact text substitution in HTML or static regions.

• Notes: Avoid using this in SQL or PL/SQL, as it may expose your app to SQL injection vulnerabilities.

Setting Item Values in Oracle APEX

You can set item values in several ways, depending on where and when you need the value to change.

1. Set Value Using the Source Attribute (Page Items)

• Navigate to the Page Designer.

• Click on the item name.

• Scroll to the Source section.

• Set the value using the appropriate source type (static value, SQL query, PL/SQL function, etc.).

2. Set Value Using PL/SQL Code

Example:

BEGIN
  :MY_ITEM := 'new value';
END;

Use this in PL/SQL blocks, such as page processes or validations.

3. Pass Value Through URL (f?p Syntax)

Example:

f?p=100:101:10636547268728380919::NO::MY_ITEM:ABC

This method passes the value ABC to the item MY_ITEM when calling the application via URL.

4. Use a Computation

Computations are useful for dynamically setting values during page processing.

Example:

TO_CHAR(SYSDATE, 'Day DD Month, YYYY')

Create a computation on the item to set its value at runtime.

5. Use the PL/SQL API (APEX_UTIL or APEX_SESSION)

Example using APEX_SESSION:

APEX_SESSION.SET_ITEM('MY_ITEM', 'new value');

Use this when setting item values programmatically, especially from packages or procedures running outside the page process.

Best Practices

• Use :ITEM_NAME for most SQL and PL/SQL code inside APEX.

• Avoid V() and NV() in SQL contexts to prevent performance degradation.

• Do not use &ITEM. substitution inside SQL or PL/SQL to avoid SQL injection.

• Always validate and sanitize input values when setting them dynamically.

• Leverage page computations and APEX built-in APIs for clean, maintainable logic.

Here's the rewritten version in plain text using clear structure and plain formatting, suitable for an Oracle APEX blog:

Referencing and Setting Item Values in Oracle APEX

In Oracle APEX, referencing item values correctly is essential when writing SQL queries, PL/SQL code, or setting values dynamically in the application. The syntax used depends on the context—whether you’re in SQL, PL/SQL, or static regions.

Referencing Item Values: Syntax Overview

1. In SQL Context

• Syntax: :MY_ITEM

• Use: Standard bind variable format. Recommended for referencing item values in SQL statements or PL/SQL blocks.

• Note: This method works when the item name is 30 bytes or fewer.

2. In PL/SQL Code (Character Values)

• Syntax: V('MY_ITEM')

• Use: Use this when accessing item values in PL/SQL procedures, functions, or packages.

• Avoid: Do not use this in SQL queries. It can negatively impact performance.

3. In PL/SQL Code (Numeric Values)

• Syntax: NV('MY_NUMERIC_ITEM')

• Use: Ideal for retrieving numeric item values within PL/SQL contexts.

• Avoid: Like V(), avoid this in SQL statements to maintain good performance.

4. In Static Text Regions

• Syntax: &MY_ITEM.

• Use: This is for exact text substitution in HTML or static text content.

• Caution: Do not use this in SQL or PL/SQL. It may open the door to SQL injection vulnerabilities.

Ways to Set Item Values in Oracle APEX

There are several ways to set the value of an item depending on how and where it's needed in your application.

1. Set Value Using Source Attribute

• In Page Designer, click on the item name.

• Scroll to the Source section.

• Choose the source type (Static Value, SQL Query, PL/SQL Function, etc.) and enter the value logic.

2. Set Value in PL/SQL Code

Inside a PL/SQL process or region, you can assign a value directly to an item like this:

BEGIN
  :MY_ITEM := 'new value';
END;

3. Set Value from a URL

You can pass a value to a page item using the f?p URL syntax:

f?p=100:101:1234567890::NO::MY_ITEM:ABC

In this case, the item MY_ITEM will be set to the value 'ABC'.

4. Set Value Using a Computation

Page computations are a built-in way to assign values at different processing points.

Example:

TO_CHAR(SYSDATE, 'Day DD Month, YYYY')

This might be used to assign the current date as a readable string.

5. Set Value Programmatically Using APEX PL/SQL API

For cases where you need to assign values from outside the page or session context, use the APEX_SESSION or APEX_UTIL API:

Example:

APEX_SESSION.SET_ITEM('MY_ITEM', 'new value');

Best Practices

• Use :ITEM_NAME for most SQL and PL/SQL references inside APEX.

• Avoid V() and NV() in SQL contexts to prevent performance issues.

• Do not use &ITEM. in PL/SQL or SQL to avoid security risks.

• When possible, rely on computations or source attributes to keep your logic clean and declarative.

• Always validate input if you're setting values based on user data or URLs.

Official Oracle APEX Documentation

To learn more, visit:
Referencing Items and Using Bind Variables – Oracle APEX Docs

Conclusion

Understanding how to reference and set item values correctly is critical to building dynamic, secure, and efficient applications in Oracle APEX. By using the appropriate syntax in the right context and following best practices, you ensure your application behaves predictably and performs well. Always refer to the Oracle documentation for the latest features and examples.

The APEX Login Procedure in Oracle APEX

 Understanding how the login process works in Oracle APEX is essential for securing your applications and controlling access to data. Whether you're building a public app or a secure enterprise system, the login procedure defines how users authenticate and begin interacting with your app. Oracle APEX provides a flexible, declarative way to implement login behavior using authentication schemes and built-in procedures. In this post, we’ll dive deep into how the APEX login process works, how to configure and customize it, and how to follow best practices to keep your application secure.

How the Login Procedure Works in Oracle APEX

Oracle APEX uses authentication schemes to manage how users log in. The default authentication scheme is Application Express (using APEX workspace credentials), but you can choose other methods such as database accounts, LDAP, social sign-in, or custom PL/SQL procedures.

The login process typically involves these steps:

1. User accesses the application.
   If authentication is required, APEX redirects to the login page.

2. Login page (typically page 101) is displayed.
   The user enters their credentials.

3. Credentials are validated using the selected authentication scheme.
   For APEX authentication, APEX validates the user against workspace users.

4. If successful, the user is redirected to the home page or target page.
   The username is stored in the built-in APP_USER variable.

5. If authentication fails, an error is displayed, and the login page is shown again.

Login processing has the following steps:

1. Run authentication scheme's pre-authentication procedure.

2. Run authentication scheme's authentication function to check the user credentials (p_username, p_password), returning TRUE on success.

3. If result=true: run post-authentication procedure.

4. If result=true: save username in session table.

5. If result=true: set redirect url to deep link.

6. If result=false: set redirect url to current page, with an error message in the notification_msg parameter.

7. Log authentication result.

8. Redirect.

Syntax

APEX_AUTHENTICATION.LOGIN ( 

    p_username IN VARCHAR2, 

    p_password IN VARCHAR2, 

    p_uppercase_username IN BOOLEAN DEFAULT TRUE );

Creating and Managing Authentication Schemes

To view or create an authentication scheme:

1. Go to Shared Components > Authentication Schemes.

2. You will see a list of existing schemes. The one with the green checkmark is the currently active scheme.

3. Click Create to start a new one. Choose from predefined types or select Custom for a PL/SQL-based login.

4. Configure the scheme’s properties, including:

   • Authentication Function Name (for custom schemes)

   • Login Processing options

   • Logout URL

   • Post-Logout Redirect

Custom Login Using PL/SQL Function

If you need custom logic, you can define a PL/SQL function that returns BOOLEAN. For example:

FUNCTION custom_login (
   p_username IN VARCHAR2,
   p_password IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
   IF p_username = 'demo_user' AND p_password = 'demo123' THEN
      APEX_UTIL.SET_AUTHENTICATED_SESSION(p_username);
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

You would reference this function in your custom authentication scheme.

Accessing Logged-in User Information

After login, you can reference:

• APP_USER — contains the logged-in username.

• V('APP_USER') — same value, in SQL or PL/SQL.

• APEX_UTIL.GET_SESSION_STATE('APP_USER') — retrieves session value.

Best Practices for Login Procedure

• Use built-in APEX authentication for admin and developer applications.

• For production apps, consider Social Sign-In, OAuth2, or LDAP for scalability and security.

• Avoid storing plain text passwords. Always hash and validate securely.

• Use Page Access Protection settings (e.g., Arguments Must Have Checksum) to prevent URL tampering.

• Do not disable Session State Protection without understanding the risks.

• Customize login error messages and branding through Page 101 for a better user experience.

Extending Login with Post-Authentication Logic

Use the Post-Authentication Procedure Name to run PL/SQL after a successful login. For example:

BEGIN
   APEX_CUSTOM_AUTH.SET_USER_ROLES(:APP_USER);
END;

You can use this to log activity, check additional permissions, or set session variables.

Oracle APEX Documentation Reference

For more details, visit the official documentation:
Authentication in Oracle APEX

Conclusion

The APEX login procedure is a foundational part of any secure Oracle APEX application. Whether you use the built-in schemes or create a custom one, understanding how authentication works gives you full control over who accesses your app and how. With proper implementation, you can ensure security, streamline access, and create a seamless login experience for your users. Take time to configure and test your authentication schemes thoroughly — it’s one of the best investments you can make in your APEX development.

AVAILABLE ITEM TYPES

 In Oracle APEX App Builder, page items are UI components that display data or capture user input. Each item type offers distinct features and is governed by attributes that control behavior, display, and validation. Below is a comprehensive breakdown of common item types and their configurable properties in Page Designer.

Check Box

Used to display multiple values as checkboxes, allowing users to select more than one value. The selected values are stored in a colon-delimited string.

• Use Case: For multi-select inputs.

• Note: For a Yes/No option, prefer the Switch item.

• Example:

  SELECT ename, empno FROM emp;
  

• Key Attributes:

  • Validation > Value Required: Ensures the item is not null upon submit.

  • Settings > Number Columns: Defines how many checkbox columns to display.

• More Info: Refer to APEX_UTIL for handling multiple returned values.

Color Picker

Allows users to select or input a color code using a palette.

• Note: Choose "Popup LOV" then set to "Color Picker".

• Key Attributes:

  • Validation > Value Required

• See Also: “Configuring Color Picker Display”

Date Picker

Displays a calendar popup to pick dates and optionally time.

• Time: Shown if the format mask includes time components.

• Key Attributes:

  • Highlighted Date, Min/Max Date

  • Show Other Months

  • Format Mask

Display Image

Renders an image from a BLOB column or image URL.

• Key Attributes:

  • Based On: Defines image source.

  • BLOB Last Updated Column: Enables caching.

Display Only

Displays static, non-editable values.

• Key Options:

  • Save Session State

  • Based On: Can display values from a list, PL/SQL code, or item value.

File Browse

Enables file uploads.

• Storage Options:

  • BLOB Column

  • APEX_APPLICATION_TEMP_FILES

• Key Attributes:

  • Purge File At

  • Allow Multiple Files

  • MIME Type, Filename, Character Set Columns

  • Download Link Text

Hidden

Stores data without rendering it to the user.

• Key Attribute:

  • Value is Protected: Prevents client-side tampering.

List Manager

Allows users to manage a set of values using Add/Remove controls.

• Storage: Selected values stored in colon-delimited format.

• Key Attribute:

  • Fetch: Defines LOV behavior.

Number Field

Used to input numbers.

• Validation: Auto-checks numeric values.

• Key Attributes:

  • Minimum / Maximum Values

  • Number Alignment

Password

Accepts secure input (obscured characters).

• Security Best Practice: Avoid storing passwords in session state.

• Key Attributes:

  • Submit on Enter

  • Store Encrypted in Session State

Percent Graph

Displays numeric value (0–100) as a visual percentage bar.

Popup LOV

Displays a searchable list in a popup dialog.

• Best for: Large data sets.

• Example:

  SELECT ename || ' ' || job display_value, empno FROM emp;
  

• Key Attributes:

  • Input Field

  • Fetch Method

Radio Group

Displays a single-select set of options as radio buttons.

• Example:

  SELECT ename, empno FROM emp;
  

• Key Attributes:

  • Number of Columns

  • Page Action on Selection

  • Execute Validations

Rich Text Editor

Provides a WYSIWYG editor for formatted content entry.

• Key Attributes:

  • Editor, Toolbar Options

  • Toolbar Position

Select List

Displays a drop-down list of predefined values.

• Best for: Small to medium lists.

• Example:

  SELECT displayValue, returnValue FROM ...
  

• Key Attributes:

  • Page Action on Selection

  • Execute Validations

Shuttle

Allows users to move multiple values between two lists.

• Storage: Colon-delimited string.

• Key Attributes:

  • Show Controls: Define available move/order buttons.

Switch

Toggle control used for binary choices (Yes/No).

• Key Attributes:

  • Settings: Use default or custom.

  • Validation > Value Required

Slider

Used in jQuery Mobile apps.

• Key Attributes:

  • Min/Max Values

  • Step Increment

  • Highlight Selected

Text Field

Basic single-line input field.

• Key Attributes:

  • Subtype: HTML5 input types (e.g., email, tel).

  • Validation > Value Required

Text Field with Autocomplete

Shows suggestions as the user types.

• Best for: Quick filtering of large datasets.

• Key Attributes:

  • Search Type

  • Lazy Loading

  • Max Values

  • Highlight Matching Terms

Text Area

Multi-line input area.

• Key Attributes:

  • Resizable

  • Auto-Height

  • Character Counter

Plug-ins

Additional item types may be available through plug-ins, appearing as <My Plug-in> [Plug-in].

Best Practices

• Always use Value Required validation where appropriate to enforce data integrity.

• For dynamic or long lists, prefer Popup LOV or Autocomplete for better performance.

• For secure inputs like passwords, avoid saving values in session state and use encryption.

• Use BLOB Last Updated to improve image/file cache handling.

• Avoid exposing hidden item values to client-side manipulation—enable Value is Protected.

For More Details
Visit the official Oracle APEX documentation:
Oracle APEX Item Types – Documentation

Let me know if you’d like this adapted into a downloadable format or published-ready markdown.