Here is the image you requested.
' || utl_tcp.crlf || 'Thanks,
' || utl_tcp.crlf ||
'The EveryCorp Dev Team
' || utl_tcp.crlf ||
'';
l_id := apex_mail.send (
p_to => 'some_user@example.com', -- change to your email address
p_from => 'some_sender@example.com', -- change to a real senders email address
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'Requested Image' );
select filename, mime_type, blob_content
into l_filename, l_mime_type, l_image
from apex_application_files
where id = 123;
apex_mail.add_attachment(
p_mail_id => l_id,
p_attachment => l_image,
p_filename => l_filename,
p_mime_type => l_mime_type,
p_content_id => l_content_id );
COMMIT;
END;
```
--------------------------------
### Example: Using APEX_EXEC for DML with Array Rows
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_EXEC.ADD_DML_ARRAY_ROW-Procedure
This PL/SQL example demonstrates how to use various APEX_EXEC procedures to construct and submit DML data, including nested array structures. It shows defining columns, opening a REST source DML context, adding rows, setting values, and specifically adding array rows using ADD_DML_ARRAY_ROW.
```PL/SQL
declare
l_columns apex_exec.t_columns;
l_context apex_exec.t_context;
begin
--
-- I. Define DML columns
--
-- 1. row-level columns
--
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'CUSTOMER_NAME',
p_data_type => apex_exec.c_data_type_varchar2 );
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'ORDER_DATE',
p_data_type => apex_exec.c_data_type_date );
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'ORDER_ITEMS',
p_data_type => apex_exec.c_data_type_array );
--
-- 2. child columns of the ORDER_ITEMS array column
--
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'PRODUCT_ID',
p_data_type => apex_exec.c_data_type_number,
p_parent_column_path => 'ORDER_ITEMS' );
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'PRODUCT_NAME',
p_data_type => apex_exec.c_data_type_varchar2,
p_parent_column_path => 'ORDER_ITEMS' );
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'UNIT_PRICE',
p_data_type => apex_exec.c_data_type_number,
p_parent_column_path => 'ORDER_ITEMS' );
apex_exec.add_column(
p_columns => l_columns,
p_column_name => 'AMOUNT_ORDERED',
p_data_type => apex_exec.c_data_type_number,
p_parent_column_path => 'ORDER_ITEMS' );
--
-- II. Open the context object
--
l_context := apex_exec.open_rest_source_dml_context(
p_columns => l_columns,
p_static_id => '{module static id}' );
--
-- III: Provide DML data
--
-- 1. the first row
--
apex_exec.add_dml_row(
p_context => l_context,
p_operation => apex_exec.c_dml_operation_insert );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'CUSTOMER_NAME',
p_value => 'John Doe' );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'ORDER_DATE',
p_value => date'2024-03-15' );
--
-- 1.1. the first line item of the first row
--
apex_exec.add_dml_array_row(
p_context => l_context,
p_operation => apex_exec.c_dml_operation_insert,
p_column_name => 'ORDER_ITEMS');
apex_exec.set_value(
p_context => l_context,
p_column_name => 'PRODUCT_ID',
p_value => 100 );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'PRODUCT_NAME',
p_value => 'Men\'s Jeans size L' );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'UNIT_PRICE',
p_value => 30.99 );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'AMOUNT_ORDERED',
p_value => 10 );
--
-- 1.2. the second line item of the first row
--
apex_exec.add_dml_array_row(
p_context => l_context );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'PRODUCT_ID',
p_value => 101 );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'PRODUCT_NAME',
p_value => 'Women\'s T-Shirt size M' );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'UNIT_PRICE',
p_value => 15.50 );
apex_exec.set_value(
p_context => l_context,
p_column_name => 'AMOUNT_ORDERED',
p_value => 5 );
--
-- IV. Execute DML
--
apex_exec.execute_rest_source_dml( l_context );
--
-- V. Close context
--
apex_exec.close_context( l_context );
end;
```
--------------------------------
### APEX_UTIL.KEYVAL_VC2 Function - Get Package Variable Value
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/KEYVAL_VC2-Function
The KEYVAL_VC2 function retrieves the value of the package variable `apex_utilities.g_val_vc2`, which is typically set by the `APEX_UTIL.SAVEKEY_VC2` procedure. This function is part of the APEX_UTIL package in Oracle APEX. It takes no parameters and returns the current value stored in `g_val_vc2` as a VARCHAR2. It is related to the SAVEKEY_VC2 function for setting the variable.
```APIDOC
APEX_UTIL.KEYVAL_VC2;
```
```APIDOC
DECLARE
VAL VARCHAR2(4000);
BEGIN
VAL := APEX_UTIL.KEYVAL_VC2;
END;
```
--------------------------------
### ADD_DATA_SOURCE Procedure - APEX_DG_DATA_GEN
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/ADD_DATA_SOURCE-Procedure
Creates a data source for Oracle APEX, identifying a table or query. It takes various parameters to define the data source type, table name, SQL query, and other options. The procedure returns the ID of the created data source.
```APIDOC
APEX_DG_DATA_GEN.ADD_DATA_SOURCE Procedure
Description:
Creates a data source which identifies a table or query from which you can source data values.
Syntax:
APEX_DG_DATA_GEN.ADD_DATA_SOURCE (
p_blueprint IN VARCHAR2,
p_name IN VARCHAR2,
p_data_source_type IN VARCHAR2,
p_table IN VARCHAR2 DEFAULT NULL,
p_preserve_case IN VARCHAR2 DEFAULT 'N',
p_sql_query IN VARCHAR2 DEFAULT NULL,
p_where_clause IN VARCHAR2 DEFAULT NULL,
p_inline_data IN CLOB DEFAULT NULL,
p_order_by_column IN VARCHAR2 DEFAULT NULL,
p_data_source_id OUT NUMBER )
Parameters:
p_blueprint: Identifies the blueprint.
p_name: Name of a data source. Name is upper cased and must be 26 characters or less.
p_data_source_type: 'TABLE' or 'SQL_QUERY'.
p_table: For 'source type = TABLE'. Typically this will match p_name.
p_preserve_case: Defaults to 'N' which forces p_table_name to uppercase, if 'Y' preserves casing of p_table.
p_sql_query: For p_data_source_type = SQL_QUERY.
p_where_clause: For p_data_source_type = TABLE, this adds the 'where' clause. Do not include "where" keyword (for example, deptno <= 20).
p_inline_data: For p_data_source_type = JSON_DATA.
p_order_by_column: Not used.
p_data_source_id: The ID of the added data source (OUT).
Related Procedures:
Refer to the APEX_DG_DATA_GEN package for other data generation utilities.
```
```PL/SQL
DECLARE
l_data_source_id number;
BEGIN
apex_dg_data_gen.add_data_source(
p_blueprint => 'Cars',
p_name => 'apex_dg_builtin_cars',
p_data_source_type => 'TABLE',
p_table => 'apex_dg_builtin_cars',
p_data_source_id => l_data_source_id );
END;
```
--------------------------------
### GET_WORKSPACE_PARAMETER Procedure - APEX_INSTANCE_ADMIN
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_WORKSPACE_PARAMETER
Retrieves a specific workspace parameter from Oracle APEX. It takes the workspace name and the parameter name as input and returns the parameter's value. Supported parameters include various configuration settings like account lifetime, banner settings, and session limits.
```APIDOC
APEX_INSTANCE_ADMIN.GET_WORKSPACE_PARAMETER
- Gets the workspace parameter.
Syntax:
APEX_INSTANCE_ADMIN.GET_WORKSPACE_PARAMETER (
p_workspace IN VARCHAR2,
p_parameter IN VARCHAR2 )
Parameters:
p_workspace: The name of the workspace from which you are getting the workspace parameter.
p_parameter: The name of the parameter to get. Parameter names include:
- ACCOUNT_LIFETIME_DAYS
- ALLOW_HOSTNAMES
- ENV_BANNER_COLOR
- ENV_BANNER_LABEL
- ENV_BANNER_POS
- ENV_BANNER_YN
- EXPIRE_FND_USER_ACCOUNTS
- MAX_LOGIN_FAILURES
- MAX_SESSION_IDLE_SEC
- MAX_SESSION_LENGTH_SEC
- MAX_WEBSERVICE_REQUESTS
- QOS_MAX_SESSION_KILL_TIMEOUT
- QOS_MAX_SESSION_REQUESTS
- QOS_MAX_WORKSPACE_REQUESTS
- RM_CONSUMER_GROUP
- WEBSERVICE_LOGGING
- WORKSPACE_EMAIL_MAXIMUM
- WORKSPACE_MAX_FILE_BYTES
Example:
The following example prints the value of ALLOW_HOSTNAMES for the HR workspace.
```
BEGIN
DBMS_OUTPUT.PUT_LINE (
APEX_INSTANCE_ADMIN.GET_WORKSPACE_PARAMETER (
p_workspace => 'HR',
p_parameter => 'ALLOW_HOSTNAMES' ));
END;
```
Parent topic: APEX_INSTANCE_ADMIN
```
--------------------------------
### Perform DML Operations with APEX_EXEC in PL/SQL
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_EXEC.ADD_DML_ARRAY_ROW-Procedure
This PL/SQL code snippet demonstrates how to use the APEX_EXEC package to insert data into a table. It involves opening a context, adding DML rows, setting values for columns, and executing the DML statement. Error handling is included to close the context on exceptions.
```PL/SQL
DECLARE
l_context apex_exec.t_context;
BEGIN
-- Initialize context for DML operations
l_context := apex_exec.open_context(
p_module_name => 'MY_MODULE',
p_operation => apex_exec.c_dml_operation_insert
);
-- 1. the first row
apex_exec.add_dml_row(
p_context => l_context,
p_operation => apex_exec.c_dml_operation_insert
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'PRODUCT_NAME',
p_value => 'Ladies Jeans size S'
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'UNIT_PRICE',
p_value => 30.99
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'AMOUNT_ORDERED',
p_value => 10
);
-- 2. the second row
apex_exec.add_dml_row(
p_context => l_context,
p_operation => apex_exec.c_dml_operation_insert
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'CUSTOMER_NAME',
p_value => 'Jane Doe'
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'ORDER_DATE',
p_value => date'2024-03-16'
);
-- 2.1. the first line item of the second row
apex_exec.add_dml_array_row(
p_context => l_context,
p_operation => apex_exec.c_dml_operation_insert,
p_column_name => 'ORDER_ITEMS'
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'PRODUCT_ID',
p_value => 100
);
-- Set "cursor" back to the first child in order to change a value
apex_exec.set_array_current_row(
p_context => l_context,
p_current_row_idx => 1
);
apex_exec.set_value(
p_context => l_context,
p_column_name => 'AMOUNT_ORDERED',
p_value => 20
);
-- Execute the DML statement
apex_exec.execute_dml(
p_context => l_context,
p_continue_on_error => false
);
apex_exec.close( l_context );
exception
when others then
apex_exec.close( l_context );
raise;
end;
```
--------------------------------
### APEX_MAIL.ADD_ATTACHMENT Procedure Signature and Parameters
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/ADD_ATTACHMENT-Procedure-Signature-1
Defines the signature and parameters for the APEX_MAIL.ADD_ATTACHMENT procedure, which adds a BLOB attachment to an email. It includes details on the email ID, attachment content, filename, MIME type, and an optional content ID for inline attachments.
```APIDOC
APEX_MAIL.ADD_ATTACHMENT (
p_mail_id IN NUMBER,
p_attachment IN BLOB,
p_filename IN VARCHAR2,
p_mime_type IN VARCHAR2,
p_content_id IN VARCHAR2 DEFAULT NULL );
Parameters:
- p_mail_id: The numeric ID associated with the email. This is the numeric identifier returned from the call to APEX_MAIL.SEND to compose the email body.
- p_attachment: A BLOB variable containing the binary content to be attached to the email message.
- p_filename: The filename associated with the email attachment.
- p_mime_type: A valid MIME type (or Internet media type) to associate with the email attachment.
- p_content_id: An optional identifier for the attachment. If non-null, then the file attaches inline. That attachment may then be referenced in the HTML of the email body by using the "cid". Note: Be aware that automatic displaying of inlined images may not be supported by all e-mail clients.
```
--------------------------------
### Oracle APEX UPD_REPORT_ALIGNMENT Procedure
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/UPD_REPORT_ALIGNMENT-Procedure
The UPD_REPORT_ALIGNMENT procedure sets the report alignment user interface default. This default is used by wizards when creating reports based on tables and columns, determining if the report column should be left, center, or right justified.
```PL/SQL
APEX_UI_DEFAULT_UPDATE.UPD_REPORT_ALIGNMENT (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_report_alignment IN VARCHAR2 );
```
```APIDOC
Parameters:
p_table_name: Table name.
p_column_name: Column name.
p_report_alignment: Defines the alignment of the column in a report. Valid values are L (left), C (center) and R (right).
```
```PL/SQL
APEX_UI_DEFAULT_UPDATE.UPD_REPORT_ALIGNMENT(
p_table_name => 'DEPT',
p_column_name => 'DEPTNO',
p_report_alignment => 'R');
```
--------------------------------
### APEX_EXEC Array Processing Functions
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_EXEC.ADD_DML_ARRAY_ROW-Procedure
This section documents key functions and procedures within the APEX_EXEC package used for managing array data during DML operations. It covers opening and closing array contexts, navigating rows, and setting values within array structures.
```APIDOC
APEX_EXEC Package - Array Processing:
This group of procedures and functions facilitates the manipulation of array data within Oracle APEX DML operations.
Related Procedures/Functions:
- OPEN_ARRAY Procedure
- CLOSE_ARRAY Procedure
- ADD_DML_ARRAY_ROW Procedure
- SET_ARRAY_CURRENT_ROW Procedure
- GET_ARRAY_ROW_DML_OPERATION Function
- NEXT_ARRAY_ROW Function
Example Usage:
These functions are typically used in conjunction with `apex_exec.open_context`, `apex_exec.add_dml_row`, `apex_exec.set_value`, and `apex_exec.execute_dml` to build and execute complex DML statements involving arrays or nested data structures.
Specific Function Details:
OPEN_ARRAY Procedure Signature 1:
OPEN_ARRAY(p_context IN OUT NOCOPY t_context, p_column_name IN VARCHAR2)
- Opens an array context for a specified column.
- Parameters:
- p_context: The execution context.
- p_column_name: The name of the column that contains array data.
- Returns: None.
CLOSE_ARRAY Procedure:
CLOSE_ARRAY(p_context IN OUT NOCOPY t_context, p_column_name IN VARCHAR2)
- Closes the array context for a specified column.
- Parameters:
- p_context: The execution context.
- p_column_name: The name of the column that contains array data.
- Returns: None.
ADD_DML_ARRAY_ROW Procedure:
ADD_DML_ARRAY_ROW(p_context IN OUT NOCOPY t_context, p_operation IN VARCHAR2, p_column_name IN VARCHAR2 DEFAULT NULL)
- Adds a new row to an array context.
- Parameters:
- p_context: The execution context.
- p_operation: The DML operation for the new row (e.g., apex_exec.c_dml_operation_insert).
- p_column_name: The name of the column that contains array data (optional, if already set in context).
- Returns: None.
SET_ARRAY_CURRENT_ROW Procedure:
SET_ARRAY_CURRENT_ROW(p_context IN OUT NOCOPY t_context, p_current_row_idx IN PLS_INTEGER)
- Sets the current row index for an array context, allowing modification of existing array rows.
- Parameters:
- p_context: The execution context.
- p_current_row_idx: The index of the row to set as current (1-based).
- Returns: None.
GET_ARRAY_ROW_DML_OPERATION Function:
GET_ARRAY_ROW_DML_OPERATION(p_context IN t_context, p_column_name IN VARCHAR2, p_row_index IN PLS_INTEGER) RETURN VARCHAR2
- Retrieves the DML operation for a specific row within an array.
- Parameters:
- p_context: The execution context.
- p_column_name: The name of the column that contains array data.
- p_row_index: The index of the row to query.
- Returns: The DML operation code (e.g., 'INSERT', 'UPDATE', 'DELETE').
NEXT_ARRAY_ROW Function:
NEXT_ARRAY_ROW(p_context IN t_context, p_column_name IN VARCHAR2) RETURN BOOLEAN
- Advances the array cursor to the next row and returns TRUE if a next row exists, FALSE otherwise.
- Parameters:
- p_context: The execution context.
- p_column_name: The name of the column that contains array data.
- Returns: TRUE if there is a next row, FALSE otherwise.
```
--------------------------------
### APEX_EXEC.ADD_DML_ARRAY_ROW Procedure
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_EXEC.ADD_DML_ARRAY_ROW-Procedure
This procedure adds a child row for the current array or a specified array column. It positions the cursor on the new row, allowing subsequent SET_VALUE calls to target its attributes. It's exclusively supported within DML contexts on REST Data Sources and requires the array column to be a direct child.
```APIDOC
APEX_EXEC.ADD_DML_ARRAY_ROW Procedure
Adds a child row for the current array or the array column provided as p_column_name. The cursor moves to the new row within the specified array column, and all subsequent calls to SET_VALUE target the attributes of this new array element. Only supported within DML contexts on REST Data Sources.
Syntax:
APEX_EXEC.ADD_DML_ARRAY_ROW (
p_context IN t_context,
p_column_name IN VARCHAR2 DEFAULT NULL,
p_column_position IN PLS_INTEGER,
p_operation IN t_dml_operation DEFAULT NULL )
Parameters:
- p_context: Context object obtained with one of the OPEN_ functions.
- p_column_name: Name of the array column (must exist within the current context) to add a new row for. If NULL, the new row is added to the current array column.
- p_column_position: Position of the column to set the value for within the DML context. This parameter is required when p_column_name is specified.
- p_operation: DML operation to be executed on this row. Use constants c_dml_operation_*. If omitted, the child row inherits the operation from its parent.
```
--------------------------------
### APEX_AUTOMATION.EXIT Procedure
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_AUTOMATION_EXIT-Procedure
Exits automation processing, including for remaining rows. Use this procedure in automation action code. It writes a message to the automation log.
```APIDOC
APEX_AUTOMATION.EXIT (
p_log_message IN VARCHAR2 DEFAULT NULL )
Parameters:
p_log_message: Message to write to the automation log.
Example:
BEGIN
IF :SQL > 10000 THEN
apex_automation.exit( p_log_message => 'Dubious SAL value found. Exit automation.' );
ELSE
my_logic_package.process_emp(
p_empno => :EMPNO,
p_sal => :SAL,
p_depto => :DEPTNO );
END IF;
END;
```
--------------------------------
### APEX_ACL.REMOVE_ALL_USER_ROLES Procedure
Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/REMOVE_ALL_USER_ROLES-Procedure
This procedure removes all assigned roles from a user within a specified Oracle APEX application. It requires the application ID and the username as input.
```APIDOC
APEX_ACL.REMOVE_ALL_USER_ROLES (
p_application_id IN NUMBER DEFAULT apex_application.g_flow_id,
p_user_name IN VARCHAR2 );
Parameters
Parameter | Description
---|---
`p_application_id` | The application ID for which you want to remove all assigned roles from a user. Defaults to the current application.
`p_user_name` | The case-insensitive name of the application user to remove all assigned roles from.
```
```PLSQL
BEGIN
APEX_ACL.REMOVE_ALL_USER_ROLES (
p_application_id => 255,
p_user_name => 'SCOTT' );
END;
```
=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.