Oracle Database PL/SQL Packages and Types Reference

https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls
Admin
Comprehensive reference documentation for Oracle-supplied PL/SQL packages and types in Oracle...
Tokens:2,911,207
Snippets:22,566
Trust Score:9.7
Update:1 month ago
Show doc for...
Tokens:
Raw
### Initial Setup for DBMS_MGD_ID_UTL Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_MGD_ID_UTL.html

These commands set up the necessary user, roles, and server output for running DBMS_MGD_ID_UTL examples. Ensure you have the required privileges.

```SQL
SQL> connect / as sysdba;
Connected.
SQL> create user mgduser identified by password;
SQL> grant connect, resource to mgduser;
SQL> connect mgduser
Enter password: mgduserpassword
Connected.
SQL> set serveroutput on;
```

--------------------------------

### Setup commands for MGD_ID examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/MGD_ID-Package-Types.html

These commands must be executed before running the scripts in this chapter to configure the database environment and user permissions.

```sql
SQL> connect / as sysdba;
Connected.
SQL> create user mgduser identified by mgduser;
SQL> grant connect, resource to mgduser;
SQL> connect mgduser
Enter password: mgduserpassword
Connected.
SQL> set serveroutput on;
```

--------------------------------

### Prepare Directories and Execute DBMS_FILE_TRANSFER.GET_FILE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_FILE_TRANSFER.html

This example shows the setup required before using GET_FILE, including creating directory objects, granting write permissions, and then calling GET_FILE for both ASM to OS file and OS to OS file transfers. Ensure the database link 'dbs2' is established.

```PL/SQL
CREATE OR REPLACE DIRECTORY df AS '+datafile' ;
GRANT WRITE ON DIRECTORY df TO "user";
CREATE DIRECTORY DSK_FILES AS ''^t_work^'';
GRANT WRITE ON DIRECTORY dsk_files TO "user";

-- asumes that dbs2 link has been created and we are connected to the instance.
-- dbs2 could be a loopback or point to another instance.

BEGIN
-- asm file to an os file
-- get an asm file from dbs1.asm/a1 to dbs2.^t_work^/oa5.dat
  DBMS_FILE_TRANSFER.GET_FILE ( 'df' , 'a1' , 'dbs1', 'dsk_files' , 'oa5.dat' );

-- os file to an os file
-- get an os file from dbs1.^t_work^/a2.dat to dbs2.^t_work^/a2back.dat
  DBMS_FILE_TRANSFER.GET_FILE ( 'dsk_files' , 'a2.dat' , 'dbs1', 'dsk_files' , 'a2back.dat' );

END ;
/
```

--------------------------------

### Get Data Guide in Hierarchical Pretty Format Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_JSON.html

Retrieves the data guide for a specific table and column in hierarchical format with pretty-printing.

```sql
SELECT DBMS_JSON.GET_INDEX_DATAGUIDE(‘T1’, ‘PO’, DBMS_JSON.FORMAT_HIERARCHICAL, DBMS_JSON.PRETTY) 
FROM DUAL;
```

--------------------------------

### DESCRIBE_MODEL Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_database1.html

An example of calling DESCRIBE_MODEL to get information about a model named DOC_EMBED_MODEL.

```sql

dbms_vector_database.describe_model('DOC_EMBED_MODEL');
```

```json
{
  "model_name": "DOC_EMBED_MODEL",
  "algorithm": "ONNX",
  "mining_function": "EMBEDDING",
  "creation_date": "2026-05-01T10:00:00.000000+00:00",
  "attributes": [
    {
      "name": "DATA",
      "value": "TEXT",
      "data_type": "VARCHAR2",
      "data_length": 4000,
      "vector_info": null
    }
  ]
}
```

--------------------------------

### Enable All Services Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/enable_service-procedure1.html

Demonstrates how to enable all services using the default value for the ENABLE_SERVICE procedure.

```SQL
SQL> execute dbms_observability.enable_service;
```

--------------------------------

### Example: Creating a Directory, Granting Permissions, and Using CREATE_FILE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_ADVISOR.html

This example demonstrates how to create a directory, grant necessary permissions, and then use DBMS_ADVISOR.CREATE_FILE to write a task script to an external file. Ensure the directory alias 'MY_DIR' is correctly set up.

```PL/SQL
CREATE DIRECTORY MY_DIR as '/homedir/user4/gssmith';
GRANT READ,WRITE ON DIRECTORY MY_DIR TO PUBLIC;

DECLARE
  v_task_id NUMBER;
  v_task_name VARCHAR2(30);
  v_workload_name VARCHAR2(30);
BEGIN
  v_task_name := 'My Task';
  v_workload_name := 'My Workload';

  DBMS_ADVISOR.CREATE_TASK(
      advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR
  ,   task_id      => v_task_id
  ,   task_name    => v_task_name );
  DBMS_ADVISOR.CREATE_SQLWKLD(
      workload_name => v_workload_name
  ,   description   => 'My Workload' );
  DBMS_ADVISOR.ADD_SQLWKLD_REF(
      task_name     => v_task_name
  ,   workload_name => v_workload_name);
  DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(
      workload_name       => v_workload_name
  ,   module              => 'MONTHLY'
  ,   action              => 'ROLLUP'
  ,   cpu_time            => 100
  ,   elapsed_time        => 400
  ,   disk_reads          => 5041
  ,   buffer_gets         => 103
  ,   rows_processed      => 640445
  ,   optimizer_cost      => 680000
  ,   executions          => 2
  ,   priority            => 1
  ,   last_execution_date => SYSDATE
  ,   stat_period         => 1
  ,   username            => 'SH'
  ,   sql_text            => 'SELECT AVG(amount_sold) FROM sh.sales' );
  DBMS_ADVISOR.EXECUTE_TASK(v_task_name);
  DBMS_ADVISOR.CREATE_FILE(
      buffer   => DBMS_ADVISOR.GET_TASK_SCRIPT(v_task_name)
  ,   location => 'MY_DIR'
  ,   filename => 'script.sql' );
END;
/
```

--------------------------------

### List Compartments Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_cloud.html

Example using DBMS_CLOUD.SEND_REQUEST with HTTP GET to list all compartments in the tenancy.

```APIDOC
## SEND_REQUEST (List Compartments)

### Description
Sends an HTTP GET request to list all compartments in the tenancy. This example demonstrates passing request headers.

### Method
`DBMS_CLOUD.SEND_REQUEST` with `DBMS_CLOUD.METHOD_GET`

### Endpoint
(Endpoint not explicitly provided in source, but implied to be related to Identity and Access Management Service)

### Parameters
#### Request Headers
(Demonstrated in the example code, specific headers not detailed in source text)

### Request Example
```sql
-- Example code for listing compartments would go here, demonstrating the use of DBMS_CLOUD.SEND_REQUEST with GET method and potentially custom headers.
-- The source text indicates this functionality but does not provide the full SQL example for listing compartments.
```

### Notes
- This example relates to the Oracle Cloud Infrastructure Identity and Access Management Service API.
```

--------------------------------

### UTL_SMTP.AUTH Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/UTL_SMTP.html

Example demonstrating how to open an SMTP connection, initiate STARTTLS, and perform authentication.

```PL/SQL
DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection(
      host => 'smtp.example.com',
      port => 25,
      wallet_path => 'file:/oracle/wallets/smtp_wallet',
      wallet_password => 'password',
      secure_connection_before_smtp => FALSE);
  UTL_SMTP.STARTTLS(c);
  UTL_SMTP.AUTH(
      c => c,
      username => 'scott',
      password => 'password'
      schemes  => utl_smtp.all_schemes);
END;
```

--------------------------------

### Get List of Installed Patches

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_QOPATCH.html

Retrieves a list of all installed patches from the XML inventory, returned as an XML element.

```PL/SQL
DBMS_QOPATCH.GET_OPATCH_LIST
 RETURN XMLTYPE;
```

--------------------------------

### Get Index Data Guide Function

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_JSON.html

Retrieves a JSON data guide from a data guide-enabled JSON search index.

```sql
FUNCTION GET_INDEX_DATAGUIDE(
   tablename VARCHAR2,
   jcolname VARCHAR2,
   format NUMBER,
   pretty NUMBER DEFAULT 0) 
   RETURN CLOB;
```

--------------------------------

### Creating a Library with Credential

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_CREDENTIAL.html

This example demonstrates how to create or replace a library, associating it with a specific credential for authentication. The credential name should not be enclosed in quotes.

```PL/SQL
CREATE OR REPLACE LIBRARY test 
  AS '$ORACLE_HOME/bin/test.so' CREDENTIAL ricky_cred;
CREATE OR REPLACE FUNCTION ftest1
  (x VARCHAR2, y BINARY_INTEGER)
RETURN BINARY_INTEGER
AS LANGUAGE C
LIBRARY test
NAME "negative"
PARAMETERS(x STRING, y INT);

```

--------------------------------

### UTL_I18N.VALIDATE_CHARACTER_ENCODING Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/UTL_I18N.html

Example demonstrating the validation of character encoding for NVARCHAR2 and CLOB data, including the SQL setup and the resulting output.

```sql
CREATE TABLE temp(col1 NVARCHAR2(20), col2 CLOB);
INSERT INTO temp VALUES(UNISTR('foo\D800bar'), UNISTR('foo\D800bar'));
COMMIT;
SELECT UTL_I18N.VALIDATE_CHARACTER_ENCODING(col1) invalid_offset_column1,
       UTL_I18N.VALIDATE_CHARACTER_ENCODING(col2) invalid_offset_column2
FROM temp;
```

```text
INVALID_OFFSET_COLUMN1 INVALID_OFFSET_COLUMN2
---------------------- ----------------------
                     7                      4
```

--------------------------------

### Creating a Request Context Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/UTL_HTTP.html

An example demonstrating how to initialize a request context with wallet and cookie settings, then using it to begin an HTTP request.

```plsql
DECLARE
  request_context  UTL_HTTP.REQUEST_CONTEXT_KEY;
  req              utl_http.req;
BEGIN
  request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
       wallet_path          => 'file:/oracle/wallets/test_wallets',
       wallet_password      => NULL,
       enable_cookies       => TRUE,
       max_cookies          => 300,
       max_cookies_per_site => 20);
  req := UTL_HTTP.BEGIN_REQUEST(
       url                  => 'http://www.example.com/',
       request_context      => request_context);
END;
```

--------------------------------

### Example Usage of DBMS_SPACE.FREE_BLOCKS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SPACE.html

This example demonstrates how to call the DBMS_SPACE.FREE_BLOCKS procedure to get the number of free blocks in a specific freelist group of a cluster.

```SQL
DBMS_SPACE.FREE_BLOCKS('SCOTT', 'CLUS', 'CLUSTER', 3, :free_blocks);
```

--------------------------------

### UTL_SMTP.STARTTLS Usage Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/UTL_SMTP.html

Demonstrates opening an SMTP connection with a wallet and initiating a STARTTLS secure connection.

```plsql
DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection(
     host => 'smtp.example.com',
     port => 25,
     wallet_path => 'file:/oracle/wallets/smtp_wallet',
     wallet_password => 'password',
     secure_connection_before_smtp => FALSE);
  utl_smtp.starttls(c);
END
```

--------------------------------

### Example: Create Username-Password Credential

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/create_credential-procedure2.html

Demonstrates how to create a credential using both a username and a password.

```SQL
execute dbms_observability.create_credential( credential_name => 'my_cred_2',
                                              username        => 'my_usr',
                                              password        => 'my_pwd',
                                              comments        => NULL );

```

--------------------------------

### Valid Initial Syntax Examples for PL/SQL Compilation Units

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_PREPROCESSOR.html

Examples of valid starting syntax for various PL/SQL compilation units when using DBMS_PREPROCESSOR. These examples are intended to be used as input source text.

```text
anonymous block   (BEGIN | DECLARE) ...     
package           PACKAGE <name> ...
package body      PACKAGE BODY <name> ...
procedure         PROCEDURE <name> ...
function          FUNCTION <name> ...
type              TYPE <name> ...
type body         TYPE BODY <name> ...
trigger           (BEGIN | DECLARE) ... 
```

--------------------------------

### Example: Create SQL Tuning Set

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQLTUNE.html

Example of creating a SQL tuning set named 'my_workload' with a description.

```SQL
EXEC DBMS_SQLTUNE.CREATE_SQLSET(
  sqlset_name => 'my_workload',
  description => 'complete application workload');

```

--------------------------------

### CREATE_RESOURCE Usage Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html

Example demonstrating how to create a new resource named 'my_resource' with specific units and state.

```sql
BEGIN
   DBMS_SCHEDULER.CREATE_RESOURCE(
      resource_name => 'my_resource',
      units => 3,
      state => 'ENFORCE_CONSTRAINTS',
      comments => 'Resource1'
   )
END;
/
```

--------------------------------

### BELLMAN_FORD Start Vertex JSON Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_oga1.html

Illustrates the JSON structure required for the 'start_vertex' parameter, specifying graph details and a key value for the starting vertex.

```JSON
JSON('{"GRAPH_OWNER":"GRAPHUSER",
       "GRAPH_NAME":"ADDRESSES",
       "ELEM_TABLE":"CITY",
       "KEY_VALUE":{"CID":101}}')
```

--------------------------------

### Example File URI with Wildcards and Regex

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_cloud.html

This example shows how to specify a file URI list using wildcards and regular expressions for Cloud Object Storage. It demonstrates pattern matching for directory structures and file names.

```SQL
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.csv'
```

--------------------------------

### DBMS_SPACE.UNUSED_SPACE Example Usage

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SPACE.html

This example demonstrates how to call the DBMS_SPACE.UNUSED_SPACE procedure to get space utilization details for a table. Ensure bind variables are declared before execution.

```PL/SQL
DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, 
   :total_bytes,:unused_blocks, :unused_bytes, :lastextf,  
   :last_extb, :lastusedblock);

```

--------------------------------

### CREATE_LOAD_APP Procedure Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_KAFKA.html

Example of creating a load application named ExampleApp for the Kafka cluster ExampleCluster using a specific topic and option.

```PL/SQL
  PROCEDURE CREATE_LOAD_APP (
                             ExampleCluster         IN  VARCHAR2,
                             ExampleApp             IN  VARCHAR2,
                             my-company-app-event1  IN  VARCHAR2,
                             ProducerRecord         IN  CLOB
```

--------------------------------

### Example: Retrieving Space Usage for a Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SPACE.html

This example demonstrates how to call the DBMS_SPACE.SPACE_USAGE procedure to get detailed space usage metrics for a table segment and then prints the returned values.

```PL/SQL
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;

begin
DBMS_SPACE.SPACE_USAGE('U1','T', 
                        'TABLE', 
                        :unf, :unfb, 
                        :fs1, :fs1b, 
                        :fs2, :fs2b, 
                        :fs3, :fs3b, 
                        :fs4, :fs4b, 
                        :full, :fullb);
end; 
/ 
print unf ; 
print unfb ; 
print fs4 ; 
print fs4b;
print fs3 ; 
print fs3b;
print fs2 ; 
print fs2b;
print fs1 ; 
print fs1b;
print full;
print fullb;

```

--------------------------------

### Describe Columns Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html

This example demonstrates how to use DESCRIBE_COLUMNS to get information about the columns of a table, similar to SQL*Plus DESCRIBE. It parses a SELECT * query and iterates through the described columns.

```PL/SQL
DECLARE
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num    NUMBER;
  PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
  BEGIN
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('col_type            =    ' || rec.col_type);
    DBMS_OUTPUT.PUT_LINE('col_maxlen          =    ' || rec.col_max_len);
    DBMS_OUTPUT.PUT_LINE('col_name            =    ' || rec.col_name);
    DBMS_OUTPUT.PUT_LINE('col_name_len        =    ' || rec.col_name_len);
    DBMS_OUTPUT.PUT_LINE('col_schema_name     =    ' || rec.col_schema_name);
    DBMS_OUTPUT.PUT_LINE('col_schema_name_len =    ' || rec.col_schema_name_len);
    DBMS_OUTPUT.PUT_LINE('col_precision       =    ' || rec.col_precision);
    DBMS_OUTPUT.PUT_LINE('col_scale           =    ' || rec.col_scale);
    DBMS_OUTPUT.PUT('col_null_ok         =    ');
    IF (rec.col_null_ok) THEN
      DBMS_OUTPUT.PUT_LINE('true');
    ELSE
      DBMS_OUTPUT.PUT_LINE('false');
    END IF;
  END;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(c, 'SELECT * FROM scott.bonus', DBMS_SQL.NATIVE);
 
  d := DBMS_SQL.EXECUTE(c);
 
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

/*
 * Following loop could simply be for j in 1..col_cnt loop.
 * Here we are simply illustrating some of the PL/SQL table
 * features.
 */
  col_num := rec_tab.first;
  IF (col_num IS NOT NULL) THEN
    LOOP
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      EXIT WHEN (col_num IS NULL);
    END LOOP;
  END IF;
 
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/
```

--------------------------------

### Example: INSERT_AUTOBIN_NUM_EQWIDTH, STACK_BIN_NUM, CREATE_MODEL, GET_MODEL_TRANSFORMATIONS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DATA_MINING_TRANSFORM.html

Demonstrates a workflow for numerical data binning. It computes bin boundaries, creates a transformation list, embeds it into a model, and retrieves the transformation expressions.

```SQL
-- Example of using INSERT_AUTOBIN_NUM_EQWIDTH, STACK_BIN_NUM, CREATE_MODEL, and GET_MODEL_TRANSFORMATIONS
-- INSERT_AUTOBIN_NUM_EQWIDTH computes bin boundaries for cust_year_of_birth in sh.customers
-- and inserts transformations into a definition table.
-- STACK_BIN_NUM creates a transformation list from the definition table.
-- CREATE_MODEL embeds the transformation list into a new model (nb_model).
-- GET_MODEL_TRANSFORMATIONS returns the embedded transformation expressions.

-- Assume necessary setup and data exist.
-- The following is a conceptual representation of the steps involved:

-- Step 1: Define binning transformations (conceptual)
-- CALL DBMS_DATA_MINING_TRANSFORM.INSERT_AUTOBIN_NUM_EQWIDTH(
--   bin_table_name => 'cust_year_bin_def',
--   data_table_name => 'sh.customers',
--   bin_num => 5,
--   exclude_list => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST('cust_id'),
--   round_num => -2
-- );

-- Step 2: Create a transformation list (conceptual)
-- CALL DBMS_TRANSFORM.STACK_BIN_NUM(
--   list_name => 'cust_year_transform_list',
--   bin_table_name => 'cust_year_bin_def'
-- );

-- Step 3: Create a model with the transformation list (conceptual)
-- CALL DBMS_PREP.CREATE_MODEL(
--   model_name => 'nb_model',
--   model_type => 'naive_bayes',
--   data_table_name => 'sh.customers',
--   target_column => 'affinity_card',
--   PREPARE_SCRIPT => 'cust_year_transform_list'
-- );

-- Step 4: Retrieve model transformations (conceptual)
-- SELECT GET_MODEL_TRANSFORMATIONS('nb_model') FROM DUAL;

-- Note: Actual SQL syntax and procedure calls may vary based on Oracle version and specific context.
-- This example illustrates the sequence and purpose of the procedures.
```

--------------------------------

### Profiling with Raw Profiler Data Table Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_HPROF.html

This example demonstrates how to use DBMS_HPROF to collect and analyze profiling data. It includes creating necessary tables, starting and stopping profiling, and analyzing the trace.

```PL/SQL
DECLARE
    analyze_runid number;
    trace_id number;
BEGIN
    -- create raw profiler data and analysis tables
    -- call create_tables with force_it =>FALSE (default) when
    -- raw profiler data and analysis tables do not exist already
    DBMS_HPROF.CREATE_TABLES;
    -- Start profiling
    -- Write raw profiler data in raw profiler data table
    trace_id := DBMS_HPROF.START_PROFILING;
    -- Run the procedure to be profiled
    test;
    -- Stop profiling
    DBMS_HPROF.STOP_PROFILING;
    -- analyzes trace_id entry in raw profiler data table and writes
    -- hierarchical profiler information in hprof’s analysis tables
    analyze_runid := DBMS_HPROF.ANALYZE(trace_id);
END;
/
```

--------------------------------

### Create and Set SQL Workload Parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_ADVISOR.html

This example demonstrates creating a SQL Workload and then setting a parameter for it using SET_SQLWKLD_PARAMETER. Parameters should be set before adding data to the workload for them to take effect.

```PL/SQL
DECLARE
  workload_name VARCHAR2(30);
BEGIN
  workload_name := 'My Workload';

  DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
  DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name, 'VALID_TABLE_LIST','SH.%');
END;
/
```

--------------------------------

### Get Total Count of Installed Patches

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_QOPATCH.html

Provides the total number of installed patches in XML format. Requires a patch number as input, though its specific use in this context is unclear from the syntax alone.

```PL/SQL
DBMS_QOPATCH.GET_OPATCH_COUNT (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;
```

--------------------------------

### ADD_COPY Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_user_certs.html

Adds a copy of an X.509 certificate to the database, assigning it a specific global unique identifier (GUID). This is useful for preserving the GUID when migrating certificates between databases, for example, when using Oracle Data Pump or Oracle GoldenGate.

```APIDOC
## ADD_COPY Procedure

### Description
Adds a copy of an X.509 certificate to the database and assigns it a specific global unique identifier (GUID). This procedure is essential for maintaining certificate GUID consistency across different databases or replication scenarios.

### Syntax
```sql
DBMS_USER_CERTS.ADD_COPY(
   x509_cert IN   BLOB,
   cert_id   IN   RAW,
   username  IN   VARCHAR2 DEFAULT NULL);
```

### Parameters
#### Input Parameters
- **x509_cert** (BLOB) - Required - The X.509 certificate used for signature verification of blockchain tables.
- **cert_id** (RAW) - Required - The Global Unique Identifier (GUID) for the certificate.
- **username** (VARCHAR2) - Optional - The name of the user who will own the certificate. A NULL value defaults to the current user.

### Usage Note
If `username` is not NULL, either `username` must refer to the current user, or the current user must be SYSDBA.
```

--------------------------------

### Display all plans for a specific SQL handle

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_XPLAN.html

Example showing how to display all plans associated with a specific SQL handle using the TYPICAL format.

```sql
SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_b1d49f6074ab95af')) t;
```

--------------------------------

### Image to Vector using Vertex AI

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_chain1.html

Partial example setup for image to vector conversion using Vertex AI.

```sql
-- declare embedding parameters

var params clob;
```

--------------------------------

### MIME_HEADER Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/OWA_UTIL.html

Example showing how to call MIME_HEADER and the resulting output.

```text
Content-type: <ccontent_type>; charset=<ccharset>
```

```sql
owa_util.mime_header('text/plain', false, 'ISO-8859-4')
```

```text
Content-type: text/plain; charset=ISO-8859-4

```

--------------------------------

### Initialize Offset with Timestamp

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_KAFKA.html

Example of using INIT_OFFSET_TS to set a starting offset for an OSAK view based on a specific date and time.

```sql
SYS.DBMS_KAFKA.INIT_OFFSET_TS (
                                          ‘ORA$DKV_EXAMPLECLUSTER_EXAMPLEAPP_0’,
                                           TO_DATE (‘2023/07/05 18:00:00’, ‘YYYY/MM/DD HH:MI:SS’))
                                           timestamp=1603507387101;
```

--------------------------------

### Example: Create Token-Based Credential

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/create_credential-procedure2.html

Shows how to create a credential using a token type and the token itself for authentication.

```SQL
execute dbms_observability.create_credential( credential_name => 'my_cred_3',
                                              token_type      => dbms_observability.oci_datakey,
                                              token           => 'abcdefghijklmnopqrstuvwxyz',
                                              comments        => 'This is my token credential!' );

```

--------------------------------

### Enable and Execute MV_CUBE_ADVICE with Tracing

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_CUBE_ADVISE.html

This example demonstrates how to enable tracing, execute the MV_CUBE_ADVICE function, and then disable tracing. Ensure SERVEROUTPUT is enabled in SQL*Plus to view diagnostic messages.

```SQL
SQL> SET SERVEROUT ON FORMAT WRAPPED
SQL> EXECUTE dbms_cube_advise.trace(1);
DBMS_COAD_DIAG: Changing diagLevel from [0] to [1]
 
PL/SQL procedure successfully completed. 

SQL> SELECT sqlobject, sqltext, droptext 
     FROM TABLE(
     dbms_cube_advise.mv_cube_advice('GLOBAL', 'CB$UNITS_CUBE')) 
     WHERE apiobject='UNITS_CUBE';
 
SQLOBJECT       SQLTEXT                                  DROPTEXT
--------------- ---------------------------------------- ----------------------------------------
UNITS_FACT      alter table "GLOBAL"."UNITS_FACT" add co alter table "GLOBAL"."UNITS_FACT" drop c
                nstraint "COAD_PK000222" PRIMARY KEY ("C onstraint "COAD_PK000222" cascade
                HANNEL_ID", "ITEM_ID", "SHIP_TO_ID", "MO
                NTH_ID") rely disable novalidate
 
CB$UNITS_CUBE   alter materialized view "GLOBAL"."CB$UNI alter materialized view "GLOBAL"."CB$UNI
                TS_CUBE" compile                         TS_CUBE" compile
 
 
20070706 07:25:27.462780000 DBMS_COAD_DIAG NOTE: Parameter mvOwner  : GLOBAL
20070706 07:25:27.462922000 DBMS_COAD_DIAG NOTE: Parameter mvName   : CB$UNITS_CUBE
20070706 07:25:27.462967000 DBMS_COAD_DIAG NOTE: Parameter factTab  : .
20070706 07:25:27.463011000 DBMS_COAD_DIAG NOTE: Parameter cubeName : UNITS_CUBE
20070706 07:25:27.463053000 DBMS_COAD_DIAG NOTE: Parameter cnsState : rely disable novalidate
20070706 07:25:27.463094000 DBMS_COAD_DIAG NOTE: Parameter NNState  : disable novalidate
20070706 07:25:27.462368000 DBMS_COAD_DIAG NOTE: Begin NN:
20070706 07:25:27.833530000 DBMS_COAD_DIAG NOTE: End   NN:
20070706 07:25:27.833620000 DBMS_COAD_DIAG NOTE: Begin PK:
20070706 07:25:28.853418000 DBMS_COAD_DIAG NOTE: End   PK:
20070706 07:25:28.853550000 DBMS_COAD_DIAG NOTE: Begin FK:
20070706 07:25:28.853282000 DBMS_COAD_DIAG NOTE: End   FK:
20070706 07:25:28.853359000 DBMS_COAD_DIAG NOTE: Begin RD:
20070706 07:25:29.660471000 DBMS_COAD_DIAG NOTE: End   RD:
20070706 07:25:29.661363000 DBMS_COAD_DIAG NOTE: Begin CM:
20070706 07:25:29.665106000 DBMS_COAD_DIAG NOTE: End   CM:

SQL> EXECUTE dbms_cube_advise.trace(0);
DBMS_COAD_DIAG: Changing diagLevel from [1] to [0]
 
PL/SQL procedure successfully completed.
```

--------------------------------

### Get Oracle Home Installation Information

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_QOPATCH.html

Returns an XML element containing Oracle Home details, including patch and inventory locations.

```PL/SQL
DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO
 RETURNS XMLTYPE;
```

--------------------------------

### CREATE_SNAPSHOT Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_WORKLOAD_REPOSITORY.html

Example of creating a snapshot for the local database with the flush level set to ALL.

```sql
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
```

--------------------------------

### Create retail fiscal year schedule

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html

This example defines a fiscal year starting on the Sunday closest to February 1st and sets up quarterly periods.

```sql
begin
 dbms_scheduler.create_schedule('year_start', repeat_interval=>
       'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN');
 dbms_scheduler.create_schedule('retail_fiscal_year',
        to_timestamp_tz('15-JAN-2005 12:00:00','DD-MON-YYYY HH24:MI:SS'),
         'year_start,year_start+13w,year_start+26w,year_start+39w;periods=4');
end;
/
```

--------------------------------

### SHOW_SERVICE_STATUS Function Usage Example (Container Info)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/show_service_status-function.html

This example shows how to retrieve the configuration for the current container using SHOW_SERVICE_STATUS. Server output must be enabled.

```sql
set serveroutput on;
DECLARE
  v_config  VARCHAR2;
BEGIN
  -- See container's configuration
  SELECT dbms_observability.show_service_status( info_level => dbms_observability.container_info )
  INTO   v_config;

  dbms_output.put_line( v_config );
END;
/
```

--------------------------------

### Get DBMS_DEBUG Version

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DEBUG.html

Call PROBE_VERSION to obtain the major and minor version numbers of the DBMS_DEBUG package on the server. This is useful for verifying installation compatibility.

```PL/SQL
DBMS_DEBUG.PROBE_VERSION (
   major out BINARY_INTEGER,
   minor out BINARY_INTEGER);

```

--------------------------------

### SHOW_SERVICE_STATUS Function Usage Example (All Info)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/show_service_status-function.html

This example illustrates two ways to retrieve all service information using SHOW_SERVICE_STATUS, either by default or explicitly with all_info. Server output must be enabled.

```sql
set serveroutput on;
DECLARE
  v_config  VARCHAR2;
BEGIN
  -- See all with default value
  SELECT dbms_observability.show_service_status
  INTO   v_config;
  -- or see all information using the all_info constant
  SELECT dbms_observability.show_service_status( info_level => dbms_observability.all_info )
  INTO   v_config;

  dbms_output.put_line( v_config );
END;
/
```

--------------------------------

### Yearly Frequency Expansion Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html

Demonstrates how a yearly frequency without specific date clauses retrieves month, day, and time defaults from the start date.

```text
start_date:      4/15/05 9:00:00
repeat_interval: freq=yearly
```

```text
freq=yearly;bymonth=4;bymonthday=15;byhour=9;byminute=0;bysecond=0
```

--------------------------------

### CREATE_VIEW_FROM_DG Function Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/SODA_TYPES.html

Creates a view with relational columns using scalar JSON fields from a data guide. Available for Autonomous Database starting from 19c.

```sql
CREATE_VIEW_FROM_DG (
   data_guide               IN   CLOB,
   view_name                IN   VARCHAR2,   
   materialize              IN   BOOLEAN DEFAULT FALSE,
   mv_refresh_mode          IN   NUMBER DEFAULT 1,
   path                     IN   VARCHAR2 DEFAULT '$',
   resolve_name_conflicts   IN   BOOLEAN   DEFAULT FALSE,
   col_name_prefix          IN   VARCHAR2 DEFAULT NULL,
   mixed_case_columns       IN   BOOLEAN DEFAULT FALSE)
  RETURN NUMBER;
```

--------------------------------

### Display plan table using DBMS_XPLAN.DISPLAY

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_XPLAN.html

Demonstrates generating an explain plan and displaying it using DBMS_XPLAN.DISPLAY.

```sql
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';
```

```sql
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```

```text
Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."ENAME"='benoit')

15 rows selected.
```

--------------------------------

### Example JSON parameters for database provider

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_chain1.html

A concrete example of setting the provider, summary level, and paragraph count.

```json
{
  "provider"          : "database",
  "glevel"            : "sentence",
  "numParagraphs"     : 1
}
```

--------------------------------

### DEINIT_CLEANUP Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_AUDIT_MGMT.html

This procedure undoes the setup and initialization performed by the INIT_CLEANUP procedure. It clears the value of the default_cleanup_interval parameter. Deprecated starting in Oracle AI Database 26ai.

```APIDOC
## DEINIT_CLEANUP Procedure

### Description
This procedure undoes the setup and initialization performed by the `INIT_CLEANUP` procedure. It clears the value of the `default_cleanup_interval` parameter. This procedure is deprecated starting in Oracle AI Database 26ai.

### Syntax
```sql
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
   audit_trail_type  IN PLS_INTEGER,
   container         IN PLS_INTEGER DEFAULT CONTAINER_CURRENT);
```

### Parameters
#### Path Parameters
* **audit_trail_type** (PLS_INTEGER) - Required - The audit trail type for which the procedure needs to be called. Audit trail types are listed in Table 31-1.
* **container** (PLS_INTEGER) - Optional - Values: `CONTAINER_CURRENT` for the connected pluggable database (PDB) or `CONTAINER_ALL` for all pluggable databases (PDBs).

### Example
```sql
BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
```
```

--------------------------------

### Get Current Time for Elapsed Time Measurement

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_UTILITY.html

Returns the current time in hundredths of a second. Primarily used by calling the function at the start and end of a process to calculate the duration.

```PL/SQL
DBMS_UTILITY.GET_TIME 
  RETURN NUMBER;
```

--------------------------------

### Example Call to BUILD_PART_INDEX

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_PCLXUTIL.html

Demonstrates how to call the BUILD_PART_INDEX procedure to create local indexes on a partitioned table. This example specifies two jobs per batch, four processes per job, the table name 'PROJECT', the index name 'IDX', and forces a rebuild of all partitions.

```SQL
SQLPLUS> EXECUTE dbms_pclxutil.build_part_index(2,4,'PROJECT','IDX',TRUE); 
Statement processed. 
INFO: Job #21 created for partition PROJ002 with 4 slaves 
INFO: Job #22 created for partition PROJ001 with 4 slaves 

```

--------------------------------

### Example Usage of PUT_FILE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_cloud_repo1.html

A basic example demonstrating the call to the PUT_FILE procedure. Ensure the 'repo' parameter is correctly set.

```sql
BEGIN
  DBMS_CLOUD_REPO.PUT_FILE(
     repo   => l_repo,
  );
END;
/
```

--------------------------------

### Get Bug List for a Patch

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_QOPATCH.html

Retrieves a list of bugs associated with a given patch number in XML format. If no patch number is provided, it lists all bugs from installed patches.

```PL/SQL
DBMS_QOPATCH.GET_OPATCH_BUGS (
   patchnum IN VARCHAR2 DEFAULT NULL);
 RETURN XMLTYPE;
```

--------------------------------

### Opening an SMTP Connection Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/UTL_SMTP.html

Example demonstrating how to open a secure SMTP connection using a wallet.

```plsql
DECLARE
  c utl_smtp.connection;
BEGIN
  c := UTL_SMTP.OPEN_CONNECTION(
   host => 'smtp.example.com',
   port => 465,
   wallet_path => 'file:/oracle/wallets/smtp_wallet',
   wallet_password => 'password',
   secure_connection_before_smtp => TRUE);
END;
```

--------------------------------

### Get Report for First 60 Seconds from Start

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_app_cont_report.html

Retrieves statistics and events for the first 60 seconds from the earliest recorded data. This is useful for analyzing the initial phase of an operation or capture.

```sql
execute dbms_app_cont_report.acchk_report(dbms_app_cont_report.FULL, duration => 60);
```

--------------------------------

### Create and Assign Wallet ACL Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_NETWORK_ACL_ADMIN.html

Demonstrates creating an ACL, adding privileges, and then assigning that ACL to a wallet. This is an example of using multiple DBMS_NETWORK_ACL_ADMIN procedures in sequence.

```SQL
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'wallet-acl.xml',
    description => 'Wallet ACL',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'use-client-certificates');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl         => 'wallet-acl.xml',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'use-passwords');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
    acl         => 'wallet-acl.xml',
    wallet_path => 'file:/example/wallets/test_wallet');
END;

```

--------------------------------

### Applying an Object Filter List with GATHER_SCHEMA_STATS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_STATS.html

This example demonstrates how to gather statistics on stale objects in the SAMPLE schema starting with 'T' or any table in the HR schema using an object filter list.

```plsql
    DECLARE
      filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
    BEGIN
      filter_lst.extend(2);
      filter_lst(1).ownname := 'SAMPLE';
      filter_lst(1).objname := 'T%';
      filter_lst(2).ownname := 'HR';
      DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
                                     options => 'GATHER STALE');
    END; 
```

--------------------------------

### INITIALIZE_REPLAY rac_inst_list Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_WORKLOAD_REPLAY.html

Example of specifying a list of Oracle RAC instances for the rac_inst_list parameter.

```text
rac_inst_list='1,3,5'
```

--------------------------------

### Get Recommendation Attributes with DBMS_ADVISOR

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_ADVISOR.html

This example shows how to retrieve specific attributes of a recommendation generated by an advisor task. It involves creating a task, workload, adding references, executing the task, and then fetching an attribute.

```PL/SQL
DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
  workload_name VARCHAR2(30);
  attribute VARCHAR2(100);
BEGIN
  task_name := 'My Task';
  workload_name := 'My Workload';
  DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
  DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
  DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
  DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
                                     100,400,5041,103,640445,680000,2,
                                     1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
                                     FROM sh.sales WHERE promo_id = 10');
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
  DBMS_ADVISOR.GET_REC_ATTRIBUTES(task_name, 1, 1, 'NAME', attribute);
END;
/
```

--------------------------------

### CREATE_VECTOR_TABLE Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_database1.html

An example of creating a bring-your-own-vector table with specific index and metadata parameters.

```sql
dbms_vector_database.create_vector_table(
    name => 'product_vectors',
    comment => 'Product catalog embeddings',
    table_params => JSON('{"auto_generate_id": false}'),
    annotations => JSON('{"domain": "retail"}'),
    index_params => JSON('{
      "vector_index_params": {
        "auto_index": true,
        "organization": "PARTITIONS",
        "distance_metric": "COSINE",
        "accuracy": 90
      },
      "metadata_index_params": {
        "auto_index": true,
        "include_paths": ["category", "price"]
      },
      "parallel_creation": 2
    }')
);
```

```json
{
  "table_name": "PRODUCT_VECTORS",
  "comment": "Product catalog embeddings",
  "table_params": {
    "auto_generate_id": false
  },
  "annotations": {
    "domain": "retail"
  },
  "vector_type": "dense",
  "vector_table_type": "BYOV",
  "embed_params": null,
  "index_params": {
    "vector_index_params": {
      "auto_index": true,
      "organization": "PARTITIONS",
      "distance_metric": "COSINE",
      "accuracy": 90
    },
    "metadata_index_params": {
      "auto_index": true,
      "include_paths": ["category", "price"]
    },
    "parallel_creation": 2
  },
  "owner": "APPUSER",
  "indexes": [],
  "status": "Empty",
  "stats": {
    "total_vectors": 0
  },
  "created": "2026-05-01T10:00:00.000000+00:00",
  "updated": "2026-05-01T10:00:00.000000+00:00"
}
```

--------------------------------

### Get Check Parameters Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_HM.html

This SQL query retrieves the list of parameters, their default values, and descriptions for a specific check, such as 'Data Block Integrity Check', by joining V$HM_CHECK_PARAM and V$HM_CHECK views.

```SQL
SELECT a.* FROM v$hm_check_param a, v$hm_check b
WHERE a.check_id = b.id
AND b.name = 'Data Block Integrity Check';
```
Tokens:
Raw