Oracle stored procedures & UDFs to BigQuery
Re-home PL/SQL business logic—procedures, functions, packages, and trigger-driven side effects—into BigQuery routines with an explicit behavior contract and test harness so retries and backfills don’t change outcomes.
- Input
- Oracle Stored procedure / UDF migration logic
- Output
- BigQuery equivalent (validated)
- Common pitfalls
- Triggers ignored: implicit side effects (audits, derived columns) disappear unless recreated.
- Package state assumptions: session state and global variables don’t translate; state must be explicit.
- Mixed-type branches: CASE/IF returns mixed types; BigQuery needs explicit casts to preserve intent.
Why this breaks
Oracle estates often embed business correctness in PL/SQL: packages and procedures that enforce rules, triggers that create side effects, and functions used pervasively in SQL. In migration, teams convert tables and queries first—then discover procedural logic was the real system. BigQuery can implement equivalent outcomes, but only if procedural behavior is turned into explicit routines with testable semantics for state, errors, and idempotency.
Common symptoms after migration:
- Outputs drift due to type coercion and NULL/empty-string differences
- Dynamic SQL behaves differently (quoting, binding, identifier resolution)
- Error handling changes; pipelines fail differently or silently continue
- Trigger side effects disappear (audits, derived columns, control table updates)
- Row-by-row PL/SQL patterns become expensive and fragile if ported directly
A successful migration extracts the behavior contract and validates it with a replayable harness, not ad-hoc spot checks.
How conversion works
- Inventory & classify PL/SQL assets: procedures, functions, packages, triggers, and their call sites (ETL, apps, reports).
- Extract the behavior contract: inputs/outputs, side effects, error semantics, state assumptions, and performance constraints.
- Choose the target form per asset:
- BigQuery SQL UDF for pure expressions
- BigQuery JavaScript UDF for complex logic where JS is appropriate
- BigQuery stored procedure (SQL scripting) for multi-statement control flow
- Set-based refactor where PL/SQL loops can be eliminated
- Pipeline-side enforcement for trigger-like side effects (DQ, audit, derived columns)
- Rewrite dynamic SQL safely using parameter binding and explicit identifier rules.
- Validate with a harness: golden inputs/outputs, branch and failure-mode tests, and side-effect assertions (audit/control writes).
Supported constructs
Representative Oracle procedural constructs we commonly migrate to BigQuery routines (exact coverage depends on your estate).
| Source | Target | Notes |
|---|---|---|
| Oracle functions used in SQL | BigQuery SQL UDFs / JavaScript UDFs | Preserve typing and NULL/empty-string intent with explicit casts. |
| PL/SQL procedures | BigQuery stored procedures (SQL scripting) | Control flow rewritten; state and side effects modeled explicitly. |
| Packages and shared utilities | Modular routines + shared tables/views | Replace package state with explicit inputs and control tables. |
| Triggers (audit/derived columns) | Pipeline-enforced side effects (procedures + DQ/audit steps) | Side effects become explicit and testable. |
| Dynamic SQL (EXECUTE IMMEDIATE) | BigQuery EXECUTE IMMEDIATE with parameter binding | Normalize identifier rules; reduce drift and injection risk. |
| Row-by-row procedural transforms | Set-based SQL refactors | Avoid cost and reliability cliffs in BigQuery. |
How workload changes
| Topic | Oracle | BigQuery |
|---|---|---|
| Execution model | PL/SQL often relies on session state and triggers | Routines should be explicit about state, inputs, and side effects |
| Dynamic SQL | String concatenation common | Prefer parameter binding and explicit identifier rules |
| Performance | Row-by-row loops sometimes tolerated | Set-based refactors usually required for cost/latency |
| Restartability | State encoded in packages/control tables | Applied-window tracking + idempotency markers enforced |
Examples
Illustrative patterns for moving Oracle PL/SQL behavior into BigQuery routines. Adjust datasets, types, and identifiers to match your environment.
-- BigQuery SQL UDF example (pure expression)
CREATE OR REPLACE FUNCTION `proj.util.safe_div`(n NUMERIC, d NUMERIC) AS (
IF(d IS NULL OR d = 0, NULL, n / d)
);Common pitfalls
- Triggers ignored: implicit side effects (audits, derived columns) disappear unless recreated.
- Package state assumptions: session state and global variables don’t translate; state must be explicit.
- Mixed-type branches: CASE/IF returns mixed types; BigQuery needs explicit casts to preserve intent.
- NULL vs empty string: Oracle treats empty strings as NULL in many contexts; intent must be explicit.
- Dynamic SQL injection risk: concatenation without bindings/escaping causes drift and risk.
- Row-by-row loops: procedural loops should be refactored into set-based SQL to avoid cost cliffs.
- No harness: without replayable tests, parity becomes a debate at cutover.
Validation approach
- Compile + interface checks: each routine deploys and signatures match the contract (args/return types).
- Golden tests: curated input sets validate outputs, including NULL/empty-string and boundary cases.
- Branch + failure-mode coverage: expected failures (invalid inputs, missing rows) are tested.
- Side-effect verification: assert expected writes to audit/control tables and idempotency under retries/backfills.
- Integration replay: run routines within representative pipelines and compare downstream KPIs/aggregates.
- Performance gate: confirm no hidden row-by-row scans; set-based refactors validated with scan bytes/runtime baselines.
Migration steps
- 01
Inventory PL/SQL assets and build the call graph
Collect procedures, functions, packages, and triggers. Map call sites across ETL, applications, reports, and scheduled jobs. Identify side effects and state dependencies.
- 02
Define the behavior contract
For each asset, specify inputs/outputs, typing/NULL/empty-string intent, expected errors, side effects, restart semantics, and performance expectations. Choose the target form.
- 03
Convert logic with safety patterns
Rewrite casts and NULL/empty-string behavior explicitly, migrate dynamic SQL using bindings, and refactor row-by-row patterns into set-based SQL where feasible.
- 04
Recreate trigger side effects explicitly
Implement audit/control writes, derived column logic, and DQ enforcement as explicit steps (procedures or pipeline stages) with measurable outputs.
- 05
Build a validation harness and cut over
Create golden inputs, edge cohorts, and failure-mode tests. Validate outputs and side effects deterministically, then cut over behind gates with rollback-ready criteria.
We inventory your Oracle procedures/functions/triggers, migrate a representative subset into BigQuery routines, and deliver a harness that proves parity—including side effects and rerun behavior.
Get a conversion plan, review markers for ambiguous intent, and validation artifacts so procedural logic cutover is gated by evidence and rollback criteria.