DataWorks:Oracle

Background information

Oracle is a relational database management system (RDBMS) used to store and process data. It provides reliable, efficient, and secure data management and analysis services. For more information, see Oracle.

Prerequisites

Before you begin, make sure you have:

• A Business Flow. DataStudio organizes development by Business Flows. Create a workflow before creating any node.

• An Oracle data source connected via JDBC. Add your Oracle database as a DataWorks data source using a JDBC connection string. For setup steps, see Oracle data source.

  Note

  Oracle nodes support only Oracle data sources that are created using a JDBC connection string.

• (RAM users only) Workspace membership with the Develop or Workspace Administrator role. Add the RAM user to the workspace and assign the appropriate role. Grant Workspace Administrator with caution due to its elevated privileges. For details, see Add members to a workspace.

Supported regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Germany (Frankfurt), US (Silicon Valley), and US (Virginia).

Step 1: Create an Oracle node

1. Go to the DataStudio page. Log in to the DataWorks console. In the top navigation bar, select your region. In the left-side navigation pane, choose Data Development and O&M > Data Development, select your workspace from the drop-down list, and click Go to Data Development.

2. Right-click the target business process and choose Create Node > Database > Oracle.

3. In the Create Node dialog box, enter a Name for the node and click Confirm. After the node is created, the node's configuration tab opens. Develop and configure the task there.

Step 2: Develop the Oracle task

SELECT * FROM ALL_TABLES;

Use scheduling parameters for dynamic queries

DataWorks scheduling parameters let you pass dynamic values into your SQL at runtime — useful for recurring tasks that process data for a rolling time window. Define a variable in your SQL using ${variable_name}, then assign a value to it in Schedule > Scheduling parameter in the right-side panel.

The following example queries tables created in the last month. The scheduling parameter a is set to $[add_months(yyyymm,-1)], which resolves to the previous month.

SELECT owner, object_name, created
FROM all_objects
WHERE object_type = 'TABLE' AND created >= TO_TIMESTAMP('${a}', 'YYYY-MM-DD');

For the full list of supported formats and configuration options, see Supported formats of scheduling parameters and Configure and use scheduling parameters.

Step 3: Configure task scheduling

Click Scheduling Configuration on the right side of the node configuration tab and set the scheduling properties.

Step 4: Debug the task

1. (Optional) Select a debugging resource group and assign parameter values. Click the icon in the toolbar. In the Parameters dialog box, select a resource group and assign values to any scheduling parameters. For details on parameter assignment logic, see Task debugging process.

2. Save and run the task. Click the icon to save, then click the icon to run.

3. (Optional) Run a smoke test. Run a smoke test during or after submission to verify execution in the development environment. See Perform smoke testing.

Step 5: Submit and publish the task

1. Click the icon in the toolbar to save the node.

2. Click the icon to submit the node task. In the Submit dialog box, enter a Change Description and select code review options.

   Note

   Configure Rerun Property and Upstream Dependent Node before submitting. If code review is enabled, a reviewer must approve the code before it can be published. See Code review.

3. In standard mode workspaces, click Publish in the upper-right corner to deploy to the production environment. See Publish tasks.

What's next

After the task is published, it runs automatically on the schedule you configured. Click O&M in the upper-right corner of the node configuration tab to open Operation Center, where you can monitor the scheduling and run status of auto triggered tasks. See Manage auto triggered tasks.