Skip to main content

Your Oracle Migration Isn’t a Data Problem. It’s a PL/SQL Problem.

Most Oracle-to-Snowflake migrations are scoped as a data project. Count the tables, size the terabytes, pick a loading tool, plan a cut-over weekend. Then the project hits the procedures, packages and triggers that hold the business logic, and the timeline stops meaning anything.

We have spent years inside large Oracle PL/SQL estates: nightly batch packages thousands of lines long, triggers that quietly enforce business rules, and procedures that build their SQL as strings at runtime. That work taught us one thing about migrations: moving the data is the easy part. Moving the behavior is the project.

If your organization runs on Oracle and Snowflake is on the table, this is the article we wish every team read before signing off on a plan.

The data moves in weeks. The logic takes the months.

Tables, views and the data in them are well understood. Snowflake publishes an Oracle migration guide, and loading data in bulk is a solved problem. Your PL/SQL is different. Snowflake's own guide says plainly that Oracle PL/SQL (packages, procedures, functions and triggers) must be completely rewritten, with targets such as Snowflake Scripting, JavaScript or Python procedures, or logic moved out into tools like dbt and Airflow.

"Rewritten" is the word that sinks budgets. A line count is not a plan. What matters is what the code does, who depends on it, and which of its behaviors nobody wrote down.

Six things that don't come across as-is

1. Triggers

Snowflake has no triggers. Every trigger in your schema is either audit logic, default-value logic or a business rule that blocks bad data, and each needs a new home: inside the loading pipeline, in a stored procedure the application calls, or in change-tracking with Snowflake streams and tasks. The audit triggers are easy to find. The ones that carry business rules are the ones that break reports months after go-live.

2. Packages

Snowflake has no equivalent of an Oracle package. Conversion tooling typically maps a package to a schema and turns its members into standalone procedures and functions. That keeps the names, but package-level variables, private helpers and initialization code need a deliberate redesign, because code that relied on package state will not behave the same way.

3. Row-by-row processing

Cursor loops, collections, records and BULK COLLECT are how a lot of PL/SQL was written. Automated conversion of cursor, collection and record variables into Snowflake Scripting is limited, and even where a loop can be translated it is usually the wrong design. Snowflake rewards set-based SQL. The best migrations treat these loops as an opportunity to rewrite logic as a few clear SQL statements, not a line-by-line port.

4. Constraints that stop being enforced

In Snowflake, primary, unique and foreign keys are declared but not enforced (only NOT NULL is). If your Oracle schema relied on constraints to reject duplicates or orphaned rows, that protection disappears silently. Your pipelines and tests have to take over the job, and someone has to decide that explicitly before go-live.

5. Small semantic differences in dates and numbers

Date arithmetic, DATE versus timestamp handling, implicit conversions and NUMBER precision behave differently enough to shift a total or a cut-off date by a day. None of this throws an error. It just produces a slightly different answer, which is the worst kind of migration bug. Only value-level reconciliation against the old system catches it.

6. Dynamic SQL and hidden dependencies

Procedures that assemble SQL in strings and run it with EXECUTE IMMEDIATE are invisible to most dependency analysis. The tables they touch don't appear in a static scan, so they are missed in scoping and discovered in testing, or in production. In older estates this is often where the most important logic lives.

Free conversion tools help. They don't finish the job.

Snowflake now offers SnowConvert AI free of charge. It converts Oracle DDL, SQL and much of your PL/SQL into Snowflake syntax and flags what it couldn't convert. You should use it; it removes a large amount of mechanical work.

But a converter answers "what is the Snowflake syntax for this?" It does not answer "should this logic still exist?", "who reads this table at 6 a.m.?" or "does the converted procedure produce the same numbers?" Those questions decide whether a migration succeeds, and they need people who understand both the Oracle code and the business it runs.

Five questions to answer before you commit to a plan

  1. How much logic lives in the database? Count packages, procedures, functions, triggers and scheduler jobs, not just tables.
  2. How much of it is dynamic SQL? This is your hidden-dependency risk, and it needs reading, not scanning.
  3. Which triggers and constraints enforce business rules? Each one needs a named new owner in the target design.
  4. Who consumes the outputs? BI dashboards, extracts, downstream applications, partner feeds. Migration order should follow consumers, not schemas.
  5. How will you prove it's right? Agree up front which reports and totals must reconcile, at what tolerance, and for how long you will run both systems side by side.

If these questions can't be answered from documentation, that is normal. It means the first phase of your migration is discovery, and it should be budgeted that way.

What a sensible migration looks like

  • Assess first. Inventory the code, map dependencies between procedures, tables and consumers (including what dynamic SQL touches), and identify the logic that carries business rules.
  • Decide rewrite versus redesign, object by object. Convert what translates cleanly. Redesign row-by-row loops, triggers and package state for how Snowflake works.
  • Migrate in slices. Move one subject area or consumer group at a time, with its data, logic and reports together, rather than a big-bang weekend.
  • Run in parallel and reconcile. Compare values, not row counts, until the business signs off.
  • Retire deliberately. Decommission Oracle objects only once nothing reads them.

And sometimes, the answer is "not yet"

Snowflake is not the only modern path for an Oracle shop. Oracle's own cloud databases now run inside AWS, Azure and Google Cloud, and for estates that are deeply procedural, staying on Oracle in the cloud can be the lower-risk move for now. A good assessment should tell you honestly which path fits, and what each would cost you in effort and risk.

Thinking about leaving Oracle?

Datwa Labs is a Snowflake Partner Network member with deep, day-to-day Oracle PL/SQL experience. Our migration assessment inventories your Oracle code, maps the dependencies that tools miss, and gives you a clear plan: what converts, what needs redesign, how long it will take and how you'll prove the result is right.

Talk to us about an assessment

← All articles