What is Oracle to Azure Database for PostgreSQL flexible server schema conversion?

The Oracle to Azure Database for PostgreSQL schema conversion feature in the Visual Studio Code PostgreSQL extension helps you convert your existing Oracle database schema objects into PostgreSQL-compatible schema. This functionality is designed for relational schemas and produces converted schema for Azure Database for PostgreSQL flexible server.

The tool provides a project-based user interface to automate schema conversion. Review the conversion report and use Conversion Object Explorer to inspect object definitions and conversion notes. GitHub Copilot can help explain the output and edit objects that need manual changes.

Architecture

The schema conversion process involves multiple components working together:

Source Oracle database: Your existing Oracle database that contains the schema to convert.
Visual Studio Code PostgreSQL extension: The primary interface for managing the conversion process.
Azure Database for PostgreSQL flexible server: Hosts the scratch schemas used for validation and testing.
Microsoft Foundry: Provides the language models that power AI-driven schema transformation.
Schema conversion agents: AI-powered agents that handle the automated conversion process.

How it works

The schema conversion process uses an intelligent, multistage approach that combines automated transformation with human oversight:

Connection and discovery: The tool connects to your Oracle database and inventories objects in the selected schemas. Review the planned extraction scope, exclusions, permissions, and dependencies before continuing.
AI-powered transformation: Schema conversion agents use language models hosted in Microsoft Foundry to transform Oracle-specific constructs into PostgreSQL-compatible equivalents. The AI takes context, relationships, and platform best practices into account.
Scratch-database validation: The tool attempts to compile generated definitions in the selected scratch database and uses available static checks to identify conversion issues. These checks don't establish equivalent application behavior.
Object review: The HTML report and Conversion Object Explorer show conversion results, available definitions, and conversion notes. Review both Converted and Not converted objects.
Guided correction: Use Ask to understand an object's conversion or Fix for Copilot-assisted edits. Compile edited definitions manually in the database where the converted schema is deployed.
Deployment preparation: The tool generates PostgreSQL .sql files and setup instructions. Review the output and run the setup scripts separately; generating these files doesn't deploy them to your destination.

Install the extension

The Oracle to PostgreSQL schema conversion feature is built into the PostgreSQL extension for Visual Studio Code. You don't need to install a separate extension.

Installation steps

Open Extensions Marketplace: In Visual Studio Code, select the Extensions icon in the Activity Bar on the left side, or use the keyboard shortcut Ctrl+Shift+X (Windows/Linux) or Cmd+Shift+X (macOS).
Search for the extension: In the Extensions Marketplace search box, enter PostgreSQL (or the extension ID ms-ossdata.vscode-pgsql) to find the extension.
Install the extension: In the search results, locate PostgreSQL published by Microsoft, and then select Install. Several PostgreSQL extensions are available in the Marketplace, so verify that the publisher is Microsoft before you install.
Access Schema Conversion: After the extension is installed, an elephant icon appears in the Visual Studio Code Activity Bar. Select the icon to open the PostgreSQL extension and access the Schema Conversion feature.

Schema conversion workflow

The workflow starts with discovery, followed by extraction and conversion. The tool validates generated PostgreSQL definitions in a scratch database, and you review the results in Conversion Object Explorer before preparing deployment files.

Discovery

Discovery follows the Oracle connection and schema selection, before PostgreSQL scratch-database configuration. It checks source permissions and inventories schema objects without extracting DDL. The counts describe the source inventory and planned extraction, not conversion outcomes.

Result	What it tells you
Discovered	Objects found during discovery.
To be extracted	Objects planned for DDL extraction.
Excluded	Objects omitted from the planned extraction scope.
Invalid objects	Objects flagged as invalid by discovery.

Review object-type support and exclusions, together with schema dependencies, before confirming the scope. Dependent schemas lists referenced schemas that you didn't select. A schema appearing in this list isn't confirmation that it's included in the conversion scope.

For detailed steps, see Review Discovery results in the tutorial.

Extraction

Extraction reads the Oracle DDL and metadata for the reviewed source scope. The extracted definitions provide the input to conversion; successful extraction doesn't mean that an object has been converted.

Conversion

Schema conversion agents use models in Microsoft Foundry to translate the extracted Oracle definitions into PostgreSQL DDL and attempt compilation in the scratch database. Review both converted and not-converted objects in Conversion Object Explorer, and independently validate behavior before deployment.

Scratch database

Select an empty database or a dedicated, unused database on Azure Database for PostgreSQL flexible server for conversion validation. The tool uses schemas corresponding to the Oracle schemas you select; it doesn't isolate existing objects in schemas with a separate scratch-name prefix.

Warning

The tool drops matching schemas with DROP SCHEMA ... CASCADE and recreates them. This action deletes their existing objects and data and can remove dependent objects in other schemas. Don't select a database that contains anything you need to preserve.

The connection user needs permission to connect, create schemas and validation objects, and drop the matching schemas. Database-level CREATE alone doesn't grant permission to drop a schema owned by another role. See Required PostgreSQL privileges.

Compilation and static checks can identify syntax, dependency, and type errors in this environment. They don't test production data, application behavior, or every runtime path.

Conversion Object Explorer

Use Conversion Object Explorer to inspect source-to-target mappings, compare Oracle and PostgreSQL definitions, and read conversion notes. Select Ask for an explanation of an object's conversion and dependencies, or Fix for Copilot-assisted changes to its PostgreSQL file. Compile the edited definition manually in the target database where the converted schema is deployed.

For detailed steps, see Review converted objects in Conversion Object Explorer.

GitHub Copilot agent mode

GitHub Copilot agent mode is an integrated Visual Studio Code feature that provides guided prompts to help you refine converted objects and align the converted schema with your application requirements. Agent mode offers:

Context-aware suggestions for schema modifications.
Best practice recommendations for Azure Database for PostgreSQL flexible server.
Code completion for complex transformations.
Integration with your existing development workflow.

Oracle connectivity modes

The schema conversion tool supports two connectivity modes for connecting to your source Oracle database: thin and thick. Understanding the difference helps you choose the right mode for your environment.

Thin client mode (default)

Thin mode connects directly to Oracle Database without additional Oracle client libraries. This mode is the default and requires no extra setup.

Requires no Oracle Instant Client installation.
Supports Oracle Database 12.1 and later.
Suits most schema conversion scenarios.
Connects by using standard TCP/IP networking.

Thick client mode

Thick mode uses Oracle Instant Client libraries to connect to Oracle Database. The schema conversion tool detects when thick mode is needed based on your Oracle network configuration and switches to it automatically.

When thick client mode is required

You can determine whether thick client mode is required by checking the Oracle network configuration files in your source environment. Look for the following parameters in the sqlnet.ora file (typically located in $ORACLE_HOME/network/admin/):

Parameter	Indicates thick mode is required
`SQLNET.CRYPTO_CHECKSUM_CLIENT`	Set to `REQUIRED` or `REQUESTED` for native network encryption
`SQLNET.ENCRYPTION_CLIENT`	Set to `REQUIRED` or `REQUESTED` for native network encryption

If any of these parameters are configured in your source Oracle environment, thick client mode is required. The schema conversion tool detects this configuration and switches to thick mode automatically. Make sure Oracle Instant Client is installed on the machine where Visual Studio Code is running before you start the conversion.

Install Oracle Instant Client

To use thick client mode, install Oracle Instant Client on the machine where Visual Studio Code and the schema conversion tool are running. For supported operating systems, see the tutorial's operating-system requirements.

Download the Oracle Instant Client Basic or Basic Light package for your operating system from Oracle Instant Client downloads. Choose a package compatible with your operating system and the architecture of the conversion environment.
Follow the installation instructions on the platform-specific download page to install the package, required dependencies, and library search-path configuration.
Restart Visual Studio Code to pick up the updated environment variables.

Authentication for Microsoft Foundry

The schema conversion tool supports two authentication methods for connecting to language models in Microsoft Foundry:

API key authentication

API key authentication uses a deployment-specific key to authorize requests. This method is straightforward and suitable for development and testing scenarios.

Microsoft Entra ID authentication

Microsoft Entra ID authentication provides token-based, identity-driven access to Microsoft Foundry without managing API keys. This method is recommended for production environments and organizations with centralized identity management.

To use Microsoft Entra ID authentication:

Assign the required role: Make sure the signed-in user or service principal has the Foundry User role (formerly Azure AI User) on the Microsoft Foundry resource that hosts your model deployment. Assign the role in the Azure portal under Access control (IAM). For more information, see Role-based access control for Microsoft Foundry.
Sign in to Azure in Visual Studio Code: Use the Azure: Sign In command from the Command Palette (Ctrl+Shift+P) to authenticate with your Microsoft Entra ID account.
Select Entra ID authentication: In the Migration Wizard language model configuration step, select Microsoft Entra ID as the authentication method instead of API key.
Provide the endpoint: Enter your Microsoft Foundry endpoint URL. The tool acquires the authentication token automatically from your signed-in session.

Microsoft Entra ID authentication requires the Azure Account extension in Visual Studio Code. The extension must be signed in with an identity that has the appropriate role assignment on the Microsoft Foundry resource.

Security and networking

When you use the schema conversion feature, make sure your Visual Studio Code environment can securely connect to both your source Oracle database and the Azure Database for PostgreSQL flexible server instance that you use as the scratch database.

Microsoft recommends connecting to your Microsoft Foundry resource by using a private endpoint. For more information, see Configure a private link for Microsoft Foundry.

For more information about securing your Microsoft Foundry connections, see Data, privacy, and security for Azure Direct Models in Microsoft Foundry.

Important

Customer validation responsibility: The same AI engine used for schema conversion can also assist with validation and review. AI systems can occasionally confirm their own mistakes. To prevent data loss, functional regressions, or security issues, independently validate generated objects and manual corrections before deploying to production. As part of your controls, consider enabling Foundry content filtering to help reduce harmful or undesired outputs. For guidance, see Content filtering in Foundry.

Why use the schema conversion feature?

Schema conversion reduces manual effort by generating PostgreSQL definitions, checking them in a scratch database, and providing object-level results and AI-assisted review. You still need to validate the generated schema against your application's requirements before deployment.

Automated conversion: Reduces manual effort by automatically converting compatible schema objects.
AI-powered transformation: Uses language models hosted in Microsoft Foundry for context-aware conversion decisions.
Scratch-database checks: Uses compilation and available static checks to identify conversion issues.
Integrated workflow: Works within the Visual Studio Code development environment.
Flexible Oracle connectivity: Supports both thin and thick client modes for connecting to Oracle databases.
Multiple authentication options: Supports API key and Microsoft Entra ID authentication for Microsoft Foundry.
Review and refinement: Combines conversion notes, source-to-target comparison, and Copilot-assisted edits.
Azure optimization: Designed for Azure Database for PostgreSQL flexible server.

Supported schema objects

The conversion tool supports a broad range of Oracle schema and code objects, including data definition elements, schema-level components, and procedural code. While you can convert many common objects automatically, you might need to manually review or customize the mapping for certain Oracle-specific features or proprietary extensions. For detailed lists of supported objects and known limitations, see the sections in this article.

How conversions are produced

Microsoft Foundry models translate extracted Oracle DDL into PostgreSQL definitions. The tool uses scratch-database compilation and available static checks to identify issues and guide corrections.

For PL/pgSQL routines, plpgsql_check provides additional static checks of routine bodies. These checks can identify issues that routine creation alone doesn't catch. For more information, see Validate converted PL/pgSQL with plpgsql_check.

A Converted status isn't a guarantee of compilation success, a complete implementation, or equivalent runtime behavior. Review object definitions and conversion notes in the HTML report and Conversion Object Explorer, including any placeholders or dependencies that need manual attention. For Oracle-specific gaps, see Schema conversion limitations.

Database schema objects

The conversion tool supports the following Oracle database objects:

Tables: Table definitions, column specifications, and table-level constraints.
Constraints: Primary keys, foreign keys, unique constraints, and check constraints.
Indexes: B-tree indexes, unique indexes, and composite indexes.
Sequences: Oracle sequence objects for autoincrementing values.
Triggers: Row-level and statement-level triggers.
Views: Standard database views.
Materialized views: Oracle materialized views and refresh logic.
Schemas: Schema-level objects and organization.
Synonyms: Public and private synonyms (with limitations).

Oracle code objects

The conversion tool supports the following Oracle code constructs:

Triggers: Complex trigger logic and event handling.
Packages: Oracle package specifications and bodies.
Functions: User-defined functions with complex logic.
Stored procedures: Oracle stored procedures and parameter handling.
Types and collections: Oracle object types, TYPE BODY member methods, VARRAY, nested tables, and SUBTYPE declarations.

Package conversion

Oracle packages don't have a direct PostgreSQL equivalent. The generated mappings depend on the source definitions, and you must review package members and their callers:

Constants: Generated getter functions need the correct value, type, and volatility.
Collections and cursors: Mappings can use arrays, domains, jsonb, or refcursor. Check bounds, indexing, and caller expectations.
Package state: Generated helpers and session settings require validation of initialization and state across calls, transactions, and sessions.
Exceptions: Review the mapping of Oracle exceptions and error codes to PostgreSQL SQLSTATE values and exception handlers.
Specification-only packages: Output can contain placeholder routines that raise an error rather than implement the missing body.

Hierarchical queries

Oracle hierarchical queries can be represented by PostgreSQL WITH RECURSIVE common table expressions. Review the generated handling of roots, parent-child joins, depth, paths, sibling ordering, and cycles. A recursive query that compiles doesn't establish that its results match the original Oracle query.

Transaction control

Conversion of routines that use PRAGMA AUTONOMOUS_TRANSACTION can depend on a separate connection through dblink. Review the generated function or procedure, required extensions, and any changes needed in its callers. Test transaction boundaries, connection and session context, commits, rollbacks, and error handling. Don't assume that compilation preserves the original transaction behavior or calling conventions.

Validate converted PL/pgSQL with plpgsql_check

Creating a PL/pgSQL routine doesn't validate every statement it can execute. Some invalid references or type mismatches appear only when the relevant execution path runs. The plpgsql_check extension can detect additional issues through static analysis of routine bodies.

Use these checks in addition to compilation, not as proof of equivalent business logic or production readiness. Review the generated definitions and complete functional and data validation independently.

Where the check runs

The check runs on the scratch database you select for conversion. It examines converted routines in that environment, not production data or application traffic. plpgsql_check is a validation aid; separately prepare the runtime extensions that the converted code requires.

What happens when the extension isn't available

Without plpgsql_check, additional body checks aren't available. Successful compilation or a Converted status doesn't mean that these checks ran. Confirm extension availability before conversion, and review and test routines independently even when the extension is installed.

Make the check available

Check the extension list for your PostgreSQL version, add plpgsql_check to the server's allow list, and configure its required preload library. Restart the server after changing shared_preload_libraries, then confirm that the extension is installed in the selected scratch database. For the full procedure, see Allow and install required extensions.

Supported Oracle versions

This section summarizes the database engine versions that support automated schema conversion and highlights compatibility considerations. Use the listed supported Oracle and PostgreSQL releases for the best results. Validate conversions in a nonproduction test environment, and use the latest minor patch of each supported major release. If your environment uses an unsupported version or includes Oracle proprietary features, you might need to perform extra manual mapping or review before deployment.

The following Oracle database versions support schema conversion:

Oracle 21c
Oracle 19c
Oracle 18c
Oracle 12.2
Oracle 12.1

Feedback and support

For bugs, feature requests, and issues related to the schema conversion feature or the PostgreSQL extension, use the built-in feedback tool in Visual Studio Code. You can access this tool in two ways:

Go to Help > Report Issue.

Command Palette

Open the Command Palette with Ctrl+Shift+P (Windows/Linux) or Cmd+Shift+P (macOS).
Run the command: PGSQL: Report Issue.

When you create your issue or provide feedback, include Schema Conversion: as a prefix in your title. This prefix helps the development team quickly identify and prioritize schema conversion-related feedback. This feedback mechanism helps the development team continuously improve the schema conversion feature and address any issues you encounter during your Oracle to Azure Database for PostgreSQL migration projects.

Feedback

Was this page helpful?

Last updated on 2026-10-05