Oracle to Snowflake Migration: Types, PL/SQL and the Behaviour That Changes

Last updated: October 2026

Oracle estates tend to carry years of PL/SQL: packages, triggers, hierarchical queries and procedures that the business depends on without anyone remembering exactly why. Moving the data to Snowflake is the easier half of the job. The harder half is the code, and the small differences in how the two databases treat dates, numbers and empty strings.

This guide covers the Snowflake tooling for Oracle as it stands in October 2026, the data types and SQL constructs that change behaviour, what happens to triggers and materialized views, and the options for moving both history and ongoing changes. Where the tooling documentation disagrees with itself, we say so, because several Oracle features changed status between July and October 2026.

Tooling: SnowConvert AI and the AIM agent

SnowConvert AI is free, and Snowflake's FAQ describes it as offering full conversion functionality. For migration projects Snowflake now recommends the Snowflake AIM Agent for Data Warehouses, an assistant in Snowflake CoCo that connects to the source, converts code, deploys it, migrates data and validates the result. For Oracle, its capability table lists deterministic code conversion, cloud data migration, cloud data validation and the testing framework as supported, while code extraction, code deployment and AI conversion are listed as planned.

Oracle coverage changed quickly in 2026. Release notes between July and October add conversion of CONNECT BY NOCYCLE to recursive CTEs, BULK COLLECT to ARRAY_AGG, %ISOPEN emulation, UTL_FILE, DBMS_SQL cursor handling and cross-schema synonym resolution. Some issue pages still list features from these releases as unsupported, PRAGMA EXCEPTION_INIT among them, so the release notes for the version you install are the better guide.

Each conversion produces an assessment and issue codes. EWI codes mark code that needs a manual fix, FDM codes mark converted code whose behaviour may differ from Oracle, and PRF codes flag items for performance review, such as loop usage. On an Oracle estate the FDM list deserves close attention, because that code usually deploys and runs, and any difference only shows up in the results.

Data types that change behaviour

The mappings below are SnowConvert's documented defaults. Most of them are safe, and the third column notes what to test.

SnowConvert flags every DATE column with SSC-FDM-OR0042, and a setting called Transform Date as Timestamp controls the mapping. Turning it off maps DATE to DATE, which loses the time of day, so we leave it on unless a column is known to hold dates only. Unqualified NUMBER is harder, because Oracle stores it with the maximum range and precision while Snowflake needs a fixed scale. The SnowConvert page shows NUMBER(38,18) in its examples and mentions NUMBER(38,19) in its text, so check the actual output and compare a sample of calculated values.

Large objects fit better than they used to. Since release 9.17 in June 2025, Snowflake's VARCHAR maximum is 128 MB and BINARY is 64 MB, up from 16 MB and 8 MB. Oracle CLOBs can still be far larger, and a VARCHAR declared without a length still defaults to 16,777,216 characters, so declare a length explicitly for columns that need more.

Empty strings and NULL

Oracle treats a character value with a length of zero as NULL, and Oracle's own documentation warns that this may change in a future release. Snowflake treats an empty string as a zero-length value that is distinct from NULL. Data copied from Oracle contains no empty strings, because Oracle never stored them, so the difference appears when converted code or a new application starts writing '' into Snowflake tables.

SnowConvert emulates the Oracle behaviour by rewriting empty-string literals to NULL. It deliberately leaves them alone inside REPLACE and CONCAT, where NULL would change the result. Filters such as col IS NULL and col = '' are where the difference most often goes unnoticed, so we include both in the test suite.

-- Oracle stores '' as NULL, so this count includes every "blank" value
SELECT COUNT(*) FROM customers WHERE middle_name IS NULL;

-- Snowflake keeps '' and NULL apart, so test both after migration
SELECT COUNT_IF(middle_name IS NULL) AS null_names,
       COUNT_IF(middle_name = '')    AS empty_names
FROM customers;

PL/SQL to Snowflake Scripting

Procedures convert to Snowflake Scripting by default, with LANGUAGE SQL, EXECUTE AS CALLER and the body between $$ delimiters. OUT parameters stay in the signature, and default parameter values are added at call sites that omit them. Cursors keep their OPEN, FETCH and CLOSE structure. A JavaScript target also exists for teams that prefer it.

Several areas need manual review on most estates:

• Collections have no native Snowflake equivalent. Recent releases emulate associative arrays and BULK COLLECT with OBJECT and ARRAY values, which needs testing on real data volumes.
• Custom exception numbers must fall between -20000 and -20999. Codes outside that range are removed, and ZERO_DIVIDE is not supported as a named exception.
• COMMIT and ROLLBACK inside procedures need AUTOCOMMIT set to false in the session to behave as they did in Oracle (SSC-FDM-OR0012).
• Package constants in stateful packages are not yet supported (SSC-EWI-OR0049), so package state needs a design decision.
• DBMS_OUTPUT.PUT_LINE becomes a generated UDF that SnowConvert asks you to review, and any logging that relied on it needs a proper destination, such as a table.

Sequences, hierarchies and outer joins

Sequence syntax carries over, with seq_name.NEXTVAL working in queries and column defaults. CURRVAL is not supported (SSC-EWI-OR0069). Since behaviour change bundle 2024_01, new Snowflake sequences and identity columns default to NOORDER, and Snowflake guarantees unique values but not gap-free ones. Code that assumes the highest ID is the newest row needs ORDER declared explicitly or, better, a timestamp column.

Snowflake supports CONNECT BY, and its documentation describes the syntax as mostly compatible with Oracle's. LEVEL, CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH work. NOCYCLE, CONNECT_BY_ISLEAF and CONNECT_BY_ISCYCLE do not, and a hierarchy with a cycle in it keeps the query running until it succeeds, is cancelled or hits the statement timeout. SnowConvert converts NOCYCLE queries to recursive CTEs from release 2.38.0.

-- The SELECT runs unchanged on Oracle and Snowflake. In Snowflake, set a
-- timeout first: NOCYCLE is not supported and cyclic data keeps it running.
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 300;

SELECT employee_id,
       manager_id,
       LEVEL                             AS depth,
       SYS_CONNECT_BY_PATH(last_name, '/') AS reporting_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Oracle's (+) outer join notation also works in Snowflake, for LEFT and RIGHT joins only, with several restrictions on where it can appear. Snowflake recommends it only for code being ported, and we rewrite it as ANSI joins whenever a query is being changed anyway.

Triggers, synonyms and materialized views

Snowflake has no triggers. The usual replacement is a stream, which records changes to a table, and a triggered task, which runs when the stream has data. Triggered tasks run at most every 30 seconds by default, and a parameter can lower that to 10 seconds. The task runs asynchronously, after the original transaction has committed, so audit and notification triggers port well. BEFORE-row triggers that modify or reject a row inside the same transaction do not, and their rules usually move into the load or into CHECK and NOT NULL constraints, which Snowflake enforces on standard tables.

-- Replaces an AFTER INSERT/UPDATE audit trigger on SALES.ORDERS
CREATE STREAM sales.orders_changes ON TABLE sales.orders;

CREATE TASK audit.capture_order_changes
  TARGET_COMPLETION_INTERVAL = '15 MINUTES'   -- serverless; no WAREHOUSE
  WHEN SYSTEM$STREAM_HAS_DATA('sales.orders_changes')
AS
  INSERT INTO audit.order_history (order_id, status, change_action, captured_at)
  SELECT order_id, status, METADATA$ACTION, CURRENT_TIMESTAMP()
  FROM sales.orders_changes;

ALTER TASK audit.capture_order_changes RESUME;

Synonyms are not supported, and SnowConvert replaces references to a synonym with the name of the underlying object (SSC-FDM-OR0005). Materialized views need Enterprise Edition and can query only a single table, with no joins, window functions or non-deterministic functions. Oracle materialized views that join tables or refresh on a schedule therefore need another design, and we usually rebuild them as dynamic tables.

Moving the history and the changes

For the initial load, export each table to compressed files, stage them and load them with COPY INTO. Snowflake recommends files of roughly 100 to 250 MB compressed and advises against files of 100 GB or more, and a load cannot run in parallel across more files than there are. UTF-8 is the default encoding, and fields containing the delimiter or line breaks must be quoted. The AIM agent's data migration does the same work for Oracle, landing files in a stage and loading them with COPY INTO.

If changes must keep flowing until cutover, the Openflow Connector for Oracle became generally available on 27 February 2026. It takes a snapshot of each table and then applies changes captured through Oracle XStream. The licensing needs checking early:

• XStream requires a paid licence, with Oracle Database Enterprise Edition as a prerequisite. Snowflake offers an embedded licence billed by source database cores, or you can bring your own, for example through an Oracle GoldenGate licence.
• Every replicated table needs a primary key, a qualifying unique constraint or a declared logical key.
• TRUNCATE on the source is ignored, and partial LOB updates made through DBMS_LOB replicate as NULL.
• Values are limited to 16 MB by default, and Autonomous Database and multi-tenant Amazon RDS are not supported sources.

Snowflake's own pages give different minimum Oracle versions for the connector, so confirm the current requirement against your source version before planning around it.

Validating before cutover

SnowConvert AI's data validation supports Oracle and runs at three levels. Level 1 compares schema and row counts. Level 2 compares column metrics, including minimum, maximum, sum, average, null count and distinct count, with a default numeric tolerance of 0.001. Level 3 compares MD5 hashes of individual rows and is off by default because it is the most resource-intensive. The target tables must exist, and the data must not change while validation runs.

Metric comparisons catch loading errors well, and they will not catch every behavioural difference described above. We add specific tests for DATE columns that hold a time, calculated NUMBER values, empty-string filters, sequence-dependent logic and every converted trigger, then run them on both systems over the same input. Our Snowflake migration checklist covers the planning and sign-off steps around this work.