{"id":1373,"date":"2026-09-25T16:05:30","date_gmt":"2026-09-25T16:05:30","guid":{"rendered":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/migrate-oracle-pl-sql-compatibility-validation-steps\/"},"modified":"2026-09-25T16:05:30","modified_gmt":"2026-09-25T16:05:30","slug":"migrate-oracle-pl-sql-compatibility-validation-steps","status":"publish","type":"post","link":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/migrate-oracle-pl-sql-compatibility-validation-steps\/","title":{"rendered":"Migrate Oracle PL_SQL_ Compatibility Validation Steps"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/kingbase-bbs.oss-cn-beijing.aliyuncs.com\/qywx\/blogImage\/c1c05ad1-b943-48eb-936b-a4b73b911926.webp\" alt=\"Technical tutorial visual showing organized blank instruction cards in a workshop setting, representing structured compatibility validation steps for database migration.\" \/><\/p>\n<h2>Prerequisites: Defining the Compatibility Boundary and Environment<\/h2>\n<p>Successful migration of legacy Oracle workloads requires a target platform with a high-fidelity compatibility layer. The primary constraint is minimizing PL\/SQL refactoring while maintaining transactional integrity. This tutorial focuses on the technical validation and execution of compatibility using KingbaseES V009R002C012 as a commercial example.<\/p>\n<p>Before initiating migration, define the compatibility boundary. KingbaseES V009R002C012 enhances Oracle compatibility in SQL, PL\/SQL, and client interfaces. The architecture uses a pluggable model to support heterogeneous database syntax compatibility modes. This allows flexible extension and adaptation to various Oracle dialects.<\/p>\n<p>Verify the following prerequisites for the target environment:<\/p>\n<ul>\n<li><strong>Target Version:<\/strong> Ensure KingbaseES V009R002C012 or later is deployed. This version explicitly strengthens Oracle compatibility.<\/li>\n<li><strong>Interface Support:<\/strong> Confirm the target system supports required client interfaces. KingbaseES supports SQL, PL\/SQL, Python, Node.js, JavaScript, JDBC, ODP.NET, and OCI interfaces.<\/li>\n<li><strong>Network and Storage:<\/strong> Validate network connectivity between source Oracle and target KingbaseES. Ensure sufficient storage for TB-scale data if applicable.<\/li>\n<li><strong>Tool Availability:<\/strong> Procure KingbaseES Migration Tool (KDTS) and Heterogeneous Data Sync Tool (KFS). These tools enable online migration for all Oracle versions.<\/li>\n<\/ul>\n<p>Define the workload characteristics. Identify if the application relies on complex PL\/SQL stored procedures, Oracle-specific SQL syntax, or heavy transactional loads. This determines the depth of compatibility testing required.<\/p>\n<h2>Auditing PL\/SQL Constructs: From %ROWTYPE to PARALLEL_ENABLE<\/h2>\n<p>Complex PL\/SQL constructs often cause migration friction. Audit existing code against the target platform\u2019s compatibility layer to minimize refactoring. KingbaseES V009R002C012 provides specific enhancements to reduce code adjustment costs.<\/p>\n<p>Analyze the following PL\/SQL features:<\/p>\n<ul>\n<li><strong>Nested Table Initialization:<\/strong> Check for <code>NEW<\/code> initialization of nested tables and varrays. In supported constructs, KingbaseES supports this Oracle-style initialization, reducing syntax changes.<\/li>\n<li><strong>%ROWTYPE Matching:<\/strong> Review stored procedure calls for record type parameters. In supported constructs, the target platform automatically matches different table record type parameters (%ROWTYPE) during calls. This eliminates manual type mapping.<\/li>\n<li><strong>Deterministic Functions:<\/strong> Inspect package headers for <code>DETERMINISTIC<\/code> keywords. In supported constructs, KingbaseES simplifies this by requiring declaration only in the package header, not the body. This aligns with Oracle behavior.<\/li>\n<li><strong>Parallel Execution:<\/strong> Identify functions requiring concurrent execution. In supported constructs, KingbaseES supports the <code>PARALLEL_ENABLE<\/code> subclause for declaring function concurrency. This ensures safe multi-process invocation.<\/li>\n<li><strong>Package Capacity:<\/strong> Assess the number of functions per package. KingbaseES expands package capacity to support nearly 10,000 functions. This accommodates complex business encapsulation without fragmentation.<\/li>\n<\/ul>\n<p>Document any constructs outside these supported features. These require targeted refactoring or custom compatibility wrappers.<\/p>\n<h2>Validating SQL Dialect Parity: System Views and Date Functions<\/h2>\n<p>Oracle-specific SQL syntax must execute identically to ensure application stability. Validate dialect parity for date handling, aggregation, and system views.<\/p>\n<p>Use the following table to verify supported features in KingbaseES V009R002C012:<\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align:left\">Feature Category<\/th>\n<th style=\"text-align:left\">Oracle Syntax<\/th>\n<th style=\"text-align:left\">KingbaseES Support Status<\/th>\n<th style=\"text-align:left\">Validation Action<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align:left\"><strong>Date\/Time<\/strong><\/td>\n<td style=\"text-align:left\"><code>TIMESTAMPADD<\/code>, <code>TO_TIMESTAMP<\/code> (multi-format)<\/td>\n<td style=\"text-align:left\">Supported<\/td>\n<td style=\"text-align:left\">Execute sample queries with complex date formats.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Aggregation<\/strong><\/td>\n<td style=\"text-align:left\"><code>LISTAGG WITH GROUP<\/code> clause<\/td>\n<td style=\"text-align:left\">Supported<\/td>\n<td style=\"text-align:left\">Run queries using optional <code>WITH GROUP<\/code> syntax.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>System Views<\/strong><\/td>\n<td style=\"text-align:left\"><code>V$VERSION<\/code>, <code>V$SESSION<\/code>, <code>V$LOCKED_OBJECT<\/code><\/td>\n<td style=\"text-align:left\">Supported<\/td>\n<td style=\"text-align:left\">Query these views to verify metadata access.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Partition Indexes<\/strong><\/td>\n<td style=\"text-align:left\"><code>ALL_PART_INDEXES<\/code>, <code>DBA__PART_INDEXES<\/code><\/td>\n<td style=\"text-align:left\">Supported<\/td>\n<td style=\"text-align:left\">Check index metadata consistency.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Heterogeneous Data<\/strong><\/td>\n<td style=\"text-align:left\">Custom collection types<\/td>\n<td style=\"text-align:left\"><code>ANYDATASET<\/code> supported<\/td>\n<td style=\"text-align:left\">Test storage of mixed-type dynamic collections.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>String Concat<\/strong><\/td>\n<td style=\"text-align:left\"><code>CONCAT<\/code> (arbitrary params)<\/td>\n<td style=\"text-align:left\">Optimized support<\/td>\n<td style=\"text-align:left\">Test <code>CONCAT<\/code> with varying argument counts.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Execute sample queries against the target database. Compare execution plans and result sets with the source Oracle system. Discrepancies indicate compatibility gaps.<\/p>\n<p>Pay attention to <code>ANYDATASET<\/code>. This collection type supports storing different data types in dynamic collections. It extends member functions and procedures for heterogeneous data processing. Use this to validate complex data handling scenarios.<\/p>\n<h2>Execution: Online Migration with KDTS and KFS<\/h2>\n<p>Perform the actual migration using KingbaseES Migration Tool (KDTS) and Heterogeneous Data Sync Tool (KFS). This procedure enables online migration. Note that performance results vary based on data volume, network, and complexity. The following steps represent a vendor-neutral procedure; specific commands and parameters must be verified with vendor documentation.<\/p>\n<p>Follow these ordered steps for TB-scale data migration:<\/p>\n<ol>\n<li><strong>Pre-Migration Check:<\/strong> Run KDTS pre-checks on the source Oracle database. Identify schema objects, dependencies, and potential conflicts.<\/li>\n<li><strong>Schema Migration:<\/strong> Use KDTS to migrate schema definitions. The tool handles PL\/SQL objects based on the compatibility layer.<\/li>\n<li><strong>Data Sync Initialization:<\/strong> Configure KFS for initial full data synchronization. Set up the connection between source Oracle and target KingbaseES.<\/li>\n<li><strong>Incremental Sync:<\/strong> Enable incremental data synchronization. KFS captures and applies changes from Oracle to KingbaseES in real-time.<\/li>\n<li><strong>Verification:<\/strong> Validate data consistency between source and target. Ensure row counts and checksums match.<\/li>\n<li><strong>Cutover:<\/strong> Switch application connections to KingbaseES. Monitor for errors during the transition.<\/li>\n<\/ol>\n<p>This process achieved hour-level migration for nearly 10TB of data in a specific 4A system case study. The process ensured zero business interruption in that context. Front-end business remained imperceptible during the cutover in that case. Performance results vary based on data volume, network, and complexity.<\/p>\n<p>Adjust step durations based on data volume and network bandwidth. Monitor KFS sync lag closely during the incremental phase.<\/p>\n<h2>Verification: Ensuring Data Consistency and Transaction Parity<\/h2>\n<p>Validate data integrity and transaction isolation after the engine switch. Use the following checklist to confirm operational stability.<\/p>\n<ul>\n<li><strong>Data Consistency:<\/strong> Run checksums or row counts on critical tables. Verify that no data was lost or corrupted during sync.<\/li>\n<li><strong>Transaction Parity:<\/strong> Test long-running transactions. Ensure commit and rollback behaviors match Oracle\u2019s isolation levels.<\/li>\n<li><strong>PL\/SQL Execution:<\/strong> Run a suite of stored procedures. Verify output matches source Oracle results.<\/li>\n<li><strong>Performance Baseline:<\/strong> Compare query execution times. Ensure no significant degradation in critical paths.<\/li>\n<li><strong>Interface Validation:<\/strong> Test connections using JDBC, ODP.NET, and OCI. Confirm application connectivity is stable.<\/li>\n<\/ul>\n<p>Use KingbaseES\u2019s <code>ANYDATASET<\/code> support to verify heterogeneous data handling. Test edge cases involving mixed data types in collections.<\/p>\n<p>Document any discrepancies. Investigate root causes before proceeding to production cutover.<\/p>\n<h2>Troubleshooting and Rollback: Handling Compatibility Failures<\/h2>\n<p>Identify compatibility mismatches and revert to the legacy Oracle state if critical failures occur.<\/p>\n<p>Common failure points include:<\/p>\n<ul>\n<li><strong>Unsupported Syntax:<\/strong> Queries using features not covered by the compatibility layer.<\/li>\n<li><strong>Data Type Mismatch:<\/strong> Errors in date or string conversion due to format differences.<\/li>\n<li><strong>Permission Issues:<\/strong> Oracle system views returning different metadata structures.<\/li>\n<\/ul>\n<p>Follow these rollback steps if migration fails:<\/p>\n<ol>\n<li><strong>Stop Sync:<\/strong> Halt KFS incremental synchronization immediately.<\/li>\n<li><strong>Revert Connections:<\/strong> Switch application connection strings back to the source Oracle database.<\/li>\n<li><strong>Data Reconciliation:<\/strong> Compare final state between Oracle and KingbaseES. Identify divergent records.<\/li>\n<li><strong>Fix Compatibility:<\/strong> Update application code or KingbaseES configuration to address the failure.<\/li>\n<li><strong>Restart Migration:<\/strong> Resume the migration process from the last consistent checkpoint.<\/li>\n<\/ol>\n<p>KingbaseES\u2019s pluggable architecture allows flexible fallback configurations. Use this to isolate incompatible components during troubleshooting.<\/p>\n<h2>FAQ<\/h2>\n<h3>How do I verify PL\/SQL compatibility before full migration?<\/h3>\n<p>Run a compatibility audit using KDTS pre-checks. Test specific PL\/SQL constructs like <code>NEW<\/code> initialization, <code>%ROWTYPE<\/code> matching, and <code>PARALLEL_ENABLE<\/code> against the target KingbaseES instance. Compare execution results with the source Oracle system.<\/p>\n<h3>Can I migrate stored procedures without significant code changes?<\/h3>\n<p>Yes, if the stored procedures use supported constructs. KingbaseES V009R002C012 supports <code>NEW<\/code> initialization for nested tables, automatic <code>%ROWTYPE<\/code> matching, simplified <code>DETERMINISTIC<\/code> declarations, and <code>PARALLEL_ENABLE<\/code>. Uncovered features require refactoring.<\/p>\n<h3>How to validate data integrity after switching database engines?<\/h3>\n<p>Perform data reconciliation using checksums or row counts. Verify transaction isolation by testing long-running transactions. Ensure application interfaces (JDBC, ODP.NET, OCI) connect correctly and return expected results.<\/p>\n<hr \/>\n<p><strong>\ud83d\udca1 More Resources<\/strong><\/p>\n<p>If you would like to dive deeper into KingbaseES and its application practices across various industries, we have compiled the following official resources to help you get started quickly and develop and operate with efficiency:<\/p>\n<ul>\n<li><a href=\"https:\/\/bbs.kingbase.com.cn\/\">Kingbase Community<\/a>: A one-stop interactive platform for technical exchanges, Q&amp;A, and experience sharing\u2014join forces with fellow DBAs and developers.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Solution-Oracle.html\">Kingbase Solutions<\/a>: One-stop full-stack database migration and cloud-native solutions, supporting smooth migration of multi-source heterogeneous data, ensuring high availability, real-time integration, and sustained high performance.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Customers.html\">Kingbase Case Studies<\/a>: Real-world user scenarios and implementation outcomes, showcasing KingbaseES&#8217;s outstanding capabilities in high availability, high performance, and IT adaptation.<\/li>\n<li><a href=\"https:\/\/docs.kingbase.com.cn\/en\">Kingbase Documentation<\/a>: Authoritative and comprehensive product manuals and technical guides, covering the entire lifecycle from installation and deployment to development, programming, and operations management.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Download.html\">Free Download<\/a>: Get the latest installation packages, drivers, tools, and patches, supporting multiple platforms and domestic chip architectures.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/blog\/\">Digital Construction Encyclopedia<\/a>: Covers digital strategy planning, data integration, metrics management, database visualization applications, and more to empower enterprise digital transformation.<\/li>\n<\/ul>\n<p><strong>Open Source Resources:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/github.com\/hgsandy\/Kingbase-docs\">GitHub &#8211; Kingbase-docs<\/a>: Kingbase documentation open-source repository\u2014Stars and contributions are welcome.<\/li>\n<li><a href=\"https:\/\/gitee.com\/hgsandy\/kingbase-docs\">Gitee &#8211; Kingbase-docs<\/a>: Domestic mirror repository for Kingbase documentation for faster access.<\/li>\n<\/ul>\n<p>Welcome to explore the resources above and begin your Kingbase journey!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Prerequisites: Defining the Compatibility Boundary and Environment Successful migration of legacy Oracle workloads requires a target platform with a high-fidelity compatibility layer. The primary constraint is minimizing PL\/SQL refactoring while&#8230;<\/p>\n","protected":false},"author":1039,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"meta_description":"","_kingbase_seo_description":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-1373","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/1373","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/users\/1039"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/comments?post=1373"}],"version-history":[{"count":0,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/1373\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/media?parent=1373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/categories?post=1373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/tags?post=1373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}