{"id":1386,"date":"2026-09-26T16:04:32","date_gmt":"2026-09-26T16:04:32","guid":{"rendered":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/automated-oracle-compatibility-assessment-a-step-by-step\/"},"modified":"2026-09-26T16:04:32","modified_gmt":"2026-09-26T16:04:32","slug":"automated-oracle-compatibility-assessment-a-step-by-step","status":"publish","type":"post","link":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/automated-oracle-compatibility-assessment-a-step-by-step\/","title":{"rendered":"Automated Oracle Compatibility Assessment_ A Step-by-Step"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/kingbase-bbs.oss-cn-beijing.aliyuncs.com\/qywx\/blogImage\/ce61dd62-5cef-4865-977d-54f9d9d884c5.webp\" alt=\"A precision screwdriver set arranged in foam cutouts, representing the systematic assessment of Oracle-to-KingbaseES compatibility.\" \/><\/p>\n<h2>Prerequisites: Defining the Oracle-to-Target Compatibility Scope<\/h2>\n<p>Before initiating any automated scan, you must establish the technical baseline. Not all Oracle features are created equal in terms of compatibility with alternative platforms. Understanding which features are candidates for automated translation versus those requiring manual refactoring is the first step in accurate cost estimation.<\/p>\n<p>KingbaseES is a commercial database software developed by China Electronics Technology Kingbase (Beijing) Technologies Inc. It offers deep compatibility with Oracle through a multi-syntax native compatible framework. This compatibility extends to SQL syntax, data types, stored procedures, triggers, and specific features such as Oracle\u2019s <code>IDENTITY<\/code>, global\/local temporary tables, and multi-table DML. However, &quot;compatibility&quot; does not imply a universal blanket for every proprietary function or undocumented behavior.<\/p>\n<p>The scope of your assessment must distinguish between:<\/p>\n<ol>\n<li><strong>Common Complex Syntax:<\/strong> Features like standard PL\/SQL packages, triggers, and standard SQL dialects. Evidence indicates KingbaseES compatibility with Oracle common complex syntax is close to 100%.<\/li>\n<li><strong>Proprietary\/Undocumented Features:<\/strong> Oracle-specific system packages, hidden parameters, or undocumented internal behaviors that may not have direct equivalents in other engines.<\/li>\n<li><strong>Transaction Modes:<\/strong> KingbaseES supports Oracle transaction modes, including auto-commit, explicit, and implicit transactions.<\/li>\n<\/ol>\n<p>Your assessment prerequisites must include a production-like Oracle schema dump or a read-only replica. Scanning a production system directly is risky. You must ensure the assessment tool can handle the volume of objects without impacting performance. The goal is to identify the &quot;gap&quot; between the source Oracle environment and the target platform\u2019s supported feature set.<\/p>\n<h2>Step 1: Executing Automated Schema Scans<\/h2>\n<p>Automated assessment tools are essential for handling high volumes of stored procedures. Manual review cannot scale to millions of lines of code. While KingbaseES provides a data migration assessment system (KDMS) and a one-click migration tool (KDTS) for automated and intelligent migration, these tools are explicitly documented for SQL Server. For Oracle compatibility assessment, users should employ vendor-neutral automated assessment workflows or verify specific Oracle assessment capabilities with the vendor.<\/p>\n<p><strong>Prerequisites for Assessment Execution:<\/strong><\/p>\n<ul>\n<li>Access to the source Oracle database (read-only user privileges).<\/li>\n<li>The target KingbaseES instance installed and configured.<\/li>\n<li>Network connectivity between the assessment tool and both databases.<\/li>\n<\/ul>\n<p><strong>Procedure:<\/strong><\/p>\n<ol>\n<li><strong>Initialize the Assessment Project:<\/strong> Launch the assessment tool interface. Create a new migration project and define the source as Oracle and the target as KingbaseES.<\/li>\n<li><strong>Configure Source Connection:<\/strong> Input the Oracle connection details. Ensure the tool can connect to the schema containing the PL\/SQL objects you wish to assess.<\/li>\n<li><strong>Select Objects for Scanning:<\/strong> You can choose to scan the entire schema or specific packages\/procedures. For a comprehensive risk assessment, select the full schema.<\/li>\n<li><strong>Execute the Compatibility Scan:<\/strong> Run the assessment job. The tool will parse the Oracle metadata, including stored procedures, functions, triggers, and packages.<\/li>\n<li><strong>Monitor Scan Progress:<\/strong> Large schemas may take significant time. Monitor the job status to ensure no connection timeouts occur.<\/li>\n<\/ol>\n<p><strong>Expected Output:<\/strong><\/p>\n<p>Upon completion, the assessment tool generates a compatibility report. This report categorizes objects based on their compatibility status. You will typically see categories such as &quot;Compatible,&quot; &quot;Needs Modification,&quot; or &quot;Incompatible.&quot;<\/p>\n<p><strong>Verification Check:<\/strong><\/p>\n<p>Confirm that the scan completed without errors. Check the log files for any connection failures or parsing exceptions. If the scan fails on specific objects, note these as potential high-risk areas requiring manual review.<\/p>\n<h2>Step 2: Mapping PL\/SQL Dependencies and Impact Scope<\/h2>\n<p>Identifying unsupported syntax is only half the battle. You must understand the blast radius of those unsupported elements. In complex Oracle environments, stored procedures, packages, and triggers often have deep interdependencies. A change in one object can cascade through the entire application.<\/p>\n<p><strong>Analyzing Dependency Chains:<\/strong><\/p>\n<p>The automated assessment report should include a dependency map. This map visualizes the relationships between database objects. Use this map to assess the impact scope of migration.<\/p>\n<ol>\n<li><strong>Identify Top-Level Dependencies:<\/strong> Look for objects that are called by the application layer but do not call other database objects. These are entry points.<\/li>\n<li><strong>Trace Downward Dependencies:<\/strong> For each entry point, trace the calls to internal packages and procedures.<\/li>\n<li><strong>Flag Circular Dependencies:<\/strong> Oracle supports circular dependencies in some cases. Check if the target platform supports similar constructs or if these require refactoring.<\/li>\n<li><strong>Highlight External Calls:<\/strong> Identify calls to external systems or non-database resources. These are often overlooked in database-only assessments but are critical for application stability.<\/li>\n<\/ol>\n<p><strong>Impact Assessment Logic:<\/strong><\/p>\n<ul>\n<li><strong>High Impact:<\/strong> An unsupported object is called by many other objects. Refactoring this object requires changing many callers.<\/li>\n<li><strong>Low Impact:<\/strong> An unsupported object is called by only one or two objects. Refactoring is isolated and less risky.<\/li>\n<\/ul>\n<p>This mapping allows you to prioritize refactoring efforts. Focus on high-impact objects first. This strategy reduces the overall risk of migration by addressing the most critical dependencies early.<\/p>\n<h2>Step 3: Quantifying Engineering Effort and Migration Risk<\/h2>\n<p>Technical scan results must be translated into business metrics. Architects need to justify migration timelines and budgets to procurement and management. Use the following framework to quantify engineering effort and risk.<\/p>\n<p><strong>Risk Quantification Framework:<\/strong><\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align:left\">Metric Category<\/th>\n<th style=\"text-align:left\">Definition<\/th>\n<th style=\"text-align:left\">Calculation Method<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align:left\"><strong>Compatibility Rate<\/strong><\/td>\n<td style=\"text-align:left\">Percentage of objects that require no changes.<\/td>\n<td style=\"text-align:left\"><code>(Compatible Objects \/ Total Objects) * 100<\/code><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Refactoring Effort<\/strong><\/td>\n<td style=\"text-align:left\">Estimated engineering hours for manual code changes.<\/td>\n<td style=\"text-align:left\">Sum of hours for &quot;Needs Modification&quot; objects based on complexity.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Risk Score<\/strong><\/td>\n<td style=\"text-align:left\">Weighted score based on unsupported features and dependency depth.<\/td>\n<td style=\"text-align:left\"><code>(High Impact Objects * 3) + (Medium Impact Objects * 2) + (Low Impact Objects * 1)<\/code><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Zero-Code Potential<\/strong><\/td>\n<td style=\"text-align:left\">Percentage of the workload that can migrate with zero changes.<\/td>\n<td style=\"text-align:left\"><code>(Zero-Change Objects \/ Total Objects) * 100<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Interpreting the Metrics:<\/strong><\/p>\n<ul>\n<li><strong>High Compatibility Rate:<\/strong> If the compatibility rate is above 95%, the migration risk is low. Engineering effort is primarily focused on data migration and minor configuration.<\/li>\n<li><strong>High Refactoring Effort:<\/strong> If the refactoring effort exceeds 20% of the total project timeline, the migration may require significant re-architecture. Consider rewriting complex logic in the application layer instead.<\/li>\n<li><strong>Zero-Code Potential:<\/strong> Real-world cases, such as a bank system with millions of lines of PL\/SQL, have demonstrated zero code changes during migration. This highlights the potential for high compatibility but requires verification for your specific workload.<\/li>\n<\/ul>\n<p><strong>Budget Justification:<\/strong><\/p>\n<p>Use these metrics to build a cost-benefit analysis. If the refactoring effort is high, the cost of migration may outweigh the benefits. In such cases, consider alternative strategies like keeping the legacy system or partial migration.<\/p>\n<h2>Step 4: Validating Scan Accuracy with PawSQL and Diagnostic Views<\/h2>\n<p>Automated tools are powerful but not infallible. False positives and false negatives can occur. You must validate the accuracy of the automated compatibility report before committing to a migration plan.<\/p>\n<p><strong>Validation Checklist:<\/strong><\/p>\n<ol>\n<li><strong>Cross-Reference with PawSQL:<\/strong> Use KingbaseES PawSQL, an intelligent SQL review tool, to analyze the PL\/SQL code. PawSQL ensures code accuracy, efficiency, readability, maintainability, and security. Compare PawSQL\u2019s findings with the assessment report.<\/li>\n<li><strong>Verify Data Type Mappings:<\/strong> Check that Oracle data types (e.g., <code>NUMBER<\/code>, <code>VARCHAR2<\/code>, <code>DATE<\/code>) are correctly mapped to KingbaseES equivalents.<\/li>\n<li><strong>Test Critical Business Logic:<\/strong> Select a sample of critical stored procedures. Execute them in a test KingbaseES environment. Verify that the output matches the Oracle source.<\/li>\n<li><strong>Check Built-in Statistical Views:<\/strong> Use KingbaseES built-in statistical views to monitor performance during the test execution. Compare execution plans with Oracle to identify potential performance regressions.<\/li>\n<li><strong>Review Transaction Behavior:<\/strong> Verify that transaction modes (auto-commit, explicit, implicit) behave as expected in the target environment.<\/li>\n<\/ol>\n<p><strong>Validation Outcome:<\/strong><\/p>\n<p>If the validation reveals significant discrepancies, update the risk assessment. Adjust the engineering effort estimates accordingly. If the validation confirms high compatibility, you can proceed with confidence.<\/p>\n<h2>Troubleshooting: Common PL\/SQL Translation Failures and False Positives<\/h2>\n<p>Automated translation can fail in specific scenarios. Understanding these failure modes helps in troubleshooting and refining the assessment.<\/p>\n<p><strong>Common Failure Modes:<\/strong><\/p>\n<ol>\n<li><strong>Undocumented Oracle Features:<\/strong> Oracle has many undocumented internal functions. Automated tools may not recognize these.\n<ul>\n<li><em>Troubleshooting:<\/em> Manually review the code for undocumented functions. Replace them with standard SQL or application-layer logic.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Complex Dynamic SQL:<\/strong> PL\/SQL that constructs SQL strings dynamically at runtime is difficult to analyze statically.\n<ul>\n<li><em>Troubleshooting:<\/em> Run the dynamic SQL in a test environment. Monitor for errors. Refactor to use static SQL where possible.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Platform-Specific Optimizers:<\/strong> Oracle\u2019s optimizer hints may not be supported in the target platform.\n<ul>\n<li><em>Troubleshooting:<\/em> Remove or replace optimizer hints. Use the target platform\u2019s native optimization tools (e.g., KingbaseES predictive and adaptive optimization methods) to tune performance.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Character Set Issues:<\/strong> Mismatched character sets can cause data corruption or sorting errors.\n<ul>\n<li><em>Troubleshooting:<\/em> Ensure the target platform\u2019s character set is compatible. Perform data validation tests to check for encoding issues.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p><strong>False Positives:<\/strong><\/p>\n<p>Automated tools may flag supported features as incompatible. This often happens with complex syntax that is actually supported but not recognized by the parser.<\/p>\n<ul>\n<li><em>Action:<\/em> Verify the flagged features against the latest compatibility documentation. If the feature is supported, mark it as a false positive in the assessment report.<\/li>\n<\/ul>\n<h2>Step 5: Rollback and Validation Strategies for Data Integrity<\/h2>\n<p>Assessment leads to migration. You must have a rollback and validation strategy to ensure data integrity and system stability.<\/p>\n<p><strong>Rollback Strategy:<\/strong><\/p>\n<ol>\n<li><strong>Pre-Migration Backup:<\/strong> Take a full backup of the Oracle database. Ensure the backup is restorable.<\/li>\n<li><strong>Parallel Run:<\/strong> Run the Oracle and target systems in parallel for a period. Compare outputs to ensure consistency.<\/li>\n<li><strong>Incremental Migration:<\/strong> Migrate data in batches. Validate each batch before proceeding.<\/li>\n<li><strong>Rollback Plan:<\/strong> If critical errors are detected during migration, revert to the Oracle system using the pre-migration backup. Ensure the application can switch back to the Oracle connection string.<\/li>\n<\/ol>\n<p><strong>Validation Strategy:<\/strong><\/p>\n<ol>\n<li><strong>Data Consistency Checks:<\/strong> Compare row counts, sums, and hashes between Oracle and the target system.<\/li>\n<li><strong>Performance Benchmarking:<\/strong> Run performance benchmarks to ensure the target system meets SLA requirements. KingbaseES has demonstrated performance improvements in various scenarios, such as a 0.5 to 16.4 times improvement in a large operator\u2019s leasing accounting system.<\/li>\n<li><strong>User Acceptance Testing (UAT):<\/strong> Have end-users test the migrated system. Collect feedback on functionality and performance.<\/li>\n<\/ol>\n<p><strong>High Availability Testing:<\/strong><\/p>\n<p>KingbaseES provides a high availability testing solution for fault coverage and verification. Use this solution to test different architecture scenarios. Systematically verify the product\u2019s overall availability under fault conditions. This ensures the target platform can handle production loads.<\/p>\n<h2>FAQ<\/h2>\n<h3>What are the key features to look for in an automated Oracle compatibility assessment tool?<\/h3>\n<p>Look for tools that support comprehensive PL\/SQL parsing, dependency mapping, and detailed reporting. The tool should categorize objects by compatibility status and provide actionable recommendations. KingbaseES offers tools such as KDMS for SQL Server migration and PawSQL for intelligent SQL review, but specific Oracle assessment capabilities should be verified with the vendor.<\/p>\n<h3>How can I validate the accuracy of an automated compatibility report before committing to a migration?<\/h3>\n<p>Validate by cross-referencing the report with intelligent SQL review tools like PawSQL. Test critical business logic in a target environment. Compare execution plans and performance metrics. Verify data type mappings and transaction behaviors.<\/p>\n<h3>What are the common failure modes in automated PL\/SQL translation and how to troubleshoot them?<\/h3>\n<p>Common failures include undocumented Oracle features, complex dynamic SQL, and unsupported optimizer hints. Troubleshoot by manually reviewing flagged code, testing dynamic SQL in a test environment, and replacing unsupported hints with native optimization tools.<\/p>\n<h3>How do I quantify the effort for manual refactoring of unsupported Oracle syntax?<\/h3>\n<p>Quantify effort by categorizing unsupported objects by complexity and dependency depth. Use a risk scoring framework that weights high-impact objects more heavily. Estimate engineering hours based on the number of objects and their complexity. Compare this effort against the potential benefits of migration.<\/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 Oracle-to-Target Compatibility Scope Before initiating any automated scan, you must establish the technical baseline. Not all Oracle features are created equal in terms of compatibility with alternative&#8230;<\/p>\n","protected":false},"author":1680,"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-1386","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/1386","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\/1680"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/comments?post=1386"}],"version-history":[{"count":0,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/1386\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/media?parent=1386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/categories?post=1386"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/tags?post=1386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}