Oracle Data Source Template

  • Published on May 7, 2026
  • 2 minute(s) read
Prev Next

Overview

The Oracle data source template is a marketplace SQL template for Oracle Database connections. It provides metadata ingestion scripts that query Oracle's data dictionary views (all_objects, all_tab_columns, all_col_comments), data type mappings including Oracle-specific types, and standard profiling definitions using Oracle-compatible SQL expressions.

Platform: Oracle Database (12c+)
Connection Category: Database
Template Category: Marketplace

What's Included

Connection Configuration

  • Supports Oracle native and ODBC connections
  • Identifier delimiters: " / "

Data Type Mappings

Includes mappings for Oracle data types including platform-specific types:

  • String types: VARCHAR2, CLOB, ROWID, character, character varying, char, varchar, text
  • Numeric types: NUMBER, integer, bigint, smallint, numeric, decimal, real, double precision, money
  • Date/time types: date, timestamp, timestamp without time zone, timestamp with time zone, time, interval
  • Boolean: boolean
  • Binary/Other: RAW, bytea, binary, varbinary
  • Geospatial: SDO_GEOMETRY, point, line, polygon, box, circle
  • Guid: uuid, RAW

Metadata Ingestion

Schema Level

Queries all_objects for distinct owner values where object_type is TABLE or VIEW.

Table Level

Queries all_objects to discover tables and views, returning schema owner, object name, and object type.

Column Level

Queries all_objects joined with all_tab_columns and all_col_comments to discover columns with data types, nullability, identity flag, string max length, numeric precision/scale, and column comments.

Profiling

Includes the standard set of ~40 profile definitions using Oracle-compatible SQL expressions, including:

  • LENGTH() for string length calculations
  • TRANSLATE() for numeric string detection
  • percentile_cont() WITHIN GROUP for median and quartile calculations
  • STDDEV_POP() for standard deviation
  • first_value() OVER for longest/shortest value identification

Macros

Includes 8 pre-built query macros:

  • Table — Select all records from a table
  • Row Count — Count records in a table
  • Standard Profile of a Column — Distinct count, min, max, and null count for a column
  • Distinct Values in a Column — List all distinct values
  • Row Count grouped by a Column — Group-by count
  • Key Value List — Distinct key-value pairs from two columns
  • List of Tables — All tables and views from all_objects
  • List of Tables in a Schema — Tables and views filtered by schema
  • Column Metadata for a Table — Full column metadata from all_tab_columns

Installation

Import from the Validatar Marketplace. Navigate to Settings > Data Source Templates > Import and select the Oracle template file.

Customization

Common customizations:

  • Schema filters — Restrict ingestion to specific owners, exclude Oracle system schemas (SYS, SYSTEM, DBSNMP)
  • Tablespace awareness — Add tablespace filtering to ingestion scripts
  • Partitioned tables — Adjust ingestion to include partition metadata from all_tab_partitions
  • Synonyms — Extend table discovery to include public and private synonyms
  • Custom types — Add data type mappings for user-defined object types

Related Articles