Oracle Database

Detailed information on the Oracle Database state store component

Component format

Create a component properties yaml file, for example called oracle.yaml (but it could be named anything ), paste the following and replace the <CONNECTION STRING> value with your connection string. The connection string is a standard Oracle Database connection string, composed as: "oracle://user/password@host:port/servicename" for example "oracle://demo:demo@localhost:1521/xe".

In case you connect to the database using an Oracle Wallet, you should specify a value for the oracleWalletLocation property, for example: "/home/app/state/Wallet_daprDB/"; this should refer to the local file system directory that contains the file cwallet.sso that is extracted from the Oracle Wallet archive file.

apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: <NAME>
spec:
  type: state.oracledatabase
  version: v1
  metadata:
  - name: connectionString
    value: "<CONNECTION STRING>"
  - name: oracleWalletLocation
    value: "<FULL PATH TO DIRECTORY WITH ORACLE WALLET CONTENTS >"  # Optional, no default
  - name: tableName
    value: "<NAME OF DATABASE TABLE TO STORE STATE IN >" # Optional, defaults to STATE

Warning

The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described here.

Spec metadata fields

What Happens at Runtime?

When the state store component initializes, it connects to the Oracle Database and checks if a table with the name specified with tableName exists. If it does not, it creates this table (with columns Key, Value, Binary_YN, ETag, Creation_Time, Update_Time, Expiration_time).

Every state entry is represented by a record in the database table. The key property provided in the request is used to determine the name of the object stored literally in the KEY column. The value is stored as the content of the object. Binary content is stored as Base64 encoded text. Each object is assigned a unique ETag value whenever it is created or updated.

For example, the following operation

curl -X POST http://localhost:3500/v1.0/state \
  -H "Content-Type: application/json"
  -d '[
        {
          "key": "nihilus",
          "value": "darth"
        }
      ]'

creates the following records in table STATE:

Dapr uses a fixed key scheme with composite keys to partition state across applications. For general states, the key format is: App-ID||state key. The Oracle Database state store maps this key in its entirety to the KEY column.

You can easily inspect all state stored with SQL queries against the tableName table, for example the STATE table.

Time To Live and State Expiration

The Oracle Database state store component supports Dapr’s Time To Live logic that ensures that state cannot be retrieved after it has expired. See this How To on Setting State Time To Live for details.

The Oracle Database does not have native support for a Time-To-Live setting. The implementation in this component uses a column called EXPIRATION_TIME to hold the time after which the record is considered expired. The value in this column is set only when a TTL was specified in a Set request. It is calculated as the current UTC timestamp with the TTL period added to it. When state is retrieved through a call to Get, this component checks if it has the EXPIRATION_TIME set and if so, it checks whether it is in the past. In that case, no state is returned.

The following operation :

curl -X POST http://localhost:3500/v1.0/state \
  -H "Content-Type: application/json"
  -d '[
        {
          "key": "temporary",
          "value": "ephemeral",
          "metadata": {"ttlInSeconds": "120"}}
        }
      ]'

creates the following object:

with the EXPIRATION_TIME set to a timestamp 2 minutes (120 seconds) (later than the CREATION_TIME)

Note that expired state is not removed from the state store by this component. An application operator may decide to run a periodic job that does a form of garbage collection in order to explicitly remove all state records with an EXPIRATION_TIME in the past. The SQL statement for collecting the expired garbage records:

 delete dapr_state 
 where  expiration_time < SYS_EXTRACT_UTC(SYSTIMESTAMP);

Concurrency

Concurrency in the Oracle Database state store is achieved by using ETags. Each piece of state recorded in the Oracle Database state store is assigned a unique ETag - a generated, unique string stored in the column ETag - when it is created or updated. Note: the column UPDATE_TIME is also updated whenever a Set operation is performed on an existing record.

Only when the Set and Delete requests for this state store specify the FirstWrite concurrency policy, then the request needs to provide the actual ETag value for the state to be written or removed for the request to be successful. If a different or no concurrency policy is specified, then no check is performed on the ETag value.

Consistency

The Oracle Database state store supports Transactions. Multiple Set and Delete commands can be combined in a request that is processed as a single, atomic transaction.

Note: simple Set and Delete operations are a transaction on their own; when a Set or Delete requests returns an HTTP-20X result, the database transaction has been committed successfully.

Query

Oracle Database state store does not currently support the Query API.

Create an Oracle Database and User Schema

• Self-Hosted
• Autonomous Database on OCI

1. Run an instance of Oracle Database. You can run a local instance of Oracle Database in Docker CE with the following command - or of course use an existing Oracle Database:

   docker run -d -p 1521:1521 -e ORACLE_PASSWORD=TheSuperSecret1509! gvenzl/oracle-xe

   This example does not describe a production configuration because it sets the password for users SYS and SYSTEM in plain text.

   When the output from the conmmand indicates that the container is running, learn the container id using the docker ps command. Then start a shell session using:

   docker exec -it <container id> /bin/bash

   and subsequently run the SQL*Plus client, connecting to the database as the SYS user:

   sqlplus sys/TheSuperSecret1509! as sysdba

2. Create a database schema for state data. Create a new user schema - for example called dapr - for storing state data. Grant this user (schema) privileges for creating a table and storing data in the associated tablespace.

   To create a new user schema in Oracle Database, run the following SQL command:

   create user dapr identified by DaprPassword4239 default tablespace users quota unlimited on users;
   grant create session, create table to dapr;

3. (optional) Create table for storing state records. The Oracle Database state store component checks if the table for storing state already exists in the database user schema it connects to and if it does not, it creates that table. However, instead of having the Oracle Database state store component create the table for storing state records at run time, you can also create the table in advance. That gives you - or the DBA for the database - more control over the physical configuration of the table. This also means you do not have to grant the create table privilege to the user schema.

   Run the following DDL statement to create the table for storing the state in the dapr database user schema :

   CREATE TABLE dapr_state (
           key varchar2(2000) NOT NULL PRIMARY KEY,
           value clob NOT NULL,
           binary_yn varchar2(1) NOT NULL,
           etag varchar2(50)  NOT NULL,
           creation_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL ,
           expiration_time TIMESTAMP WITH TIME ZONE NULL,
           update_time TIMESTAMP WITH TIME ZONE NULL
     )

1. Create a free (or paid for) Autonomous Transaction Processing (ATP) or ADW (Autonomous Data Warehouse) instance on Oracle Cloud Infrastructure, as described in the OCI documentation for the always free autonomous database.

   You need to provide the password for user ADMIN. You use this account (initially at least) for database administration activities. You can work both in the web based SQL Developer tool, from its desktop counterpart or from any of a plethora of database development tools.

2. Create a schema for state data. Create a new user schema in the Oracle Database for storing state data - for example using the ADMIN account. Grant this new user (schema) privileges for creating a table and storing data in the associated tablespace.

   To create a new user schema in Oracle Database, run the following SQL command:

   create user dapr identified by DaprPassword4239 default tablespace users quota unlimited on users;
   grant create session, create table to dapr;

3. (optional) Create table for storing state records. The Oracle Database state store component checks if the table for storing state already exists in the database user schema it connects to and if it does not, it creates that table. However, instead of having the Oracle Database state store component create the table for storing state records at run time, you can also create the table in advance. That gives you - or the DBA for the database - more control over the physical configuration of the table. This also means you do not have to grant the create table privilege to the user schema.

   Run the following DDL statement to create the table for storing the state in the dapr database user schema :

   CREATE TABLE dapr_state (
           key varchar2(2000) NOT NULL PRIMARY KEY,
           value clob NOT NULL,
           binary_yn varchar2(1) NOT NULL,
           etag varchar2(50)  NOT NULL,
           creation_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL ,
           expiration_time TIMESTAMP WITH TIME ZONE NULL,
           update_time TIMESTAMP WITH TIME ZONE NULL
     )

Related links

• Basic schema for a Dapr component
• Read this guide for instructions on configuring state store components
• State management building block

Last modified July 27, 2022: Remove namespace element from component examples (#2647) (ff9de5c8)