{"id":16846,"date":"2025-02-11T08:52:20","date_gmt":"2025-02-11T16:52:20","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=16846"},"modified":"2025-07-08T09:15:37","modified_gmt":"2025-07-08T16:15:37","slug":"plsql-to-javascript-udf-conversion-tool","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/plsql-to-javascript-udf-conversion-tool\/","title":{"rendered":"A Tool to Ease Your Transition From Oracle PL\/SQL to Couchbase JavaScript UDF"},"content":{"rendered":"

What is PL\/SQL?<\/span><\/h2>\n

PL\/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. It includes procedural language elements such as conditions and loops, and can handle exceptions (run-time errors).<\/span><\/p>\n

PL\/SQL is native to Oracle databases, and databases like IBM DB2, PostgreSQL, and MySQL support PL\/SQL constructs through compatibility features.<\/span><\/p>\n

What is a JavaScript UDF?<\/span><\/h2>\n

JavaScript UDF is Couchbase\u2019s alternative to PL\/SQL.<\/span><\/p>\n

JavaScript UDF brings JavaScript’s general-purpose scripting flexibility to databases, allowing for dynamic and powerful operations across modern database systems and enhances flexibility in data querying, processing, and transformation.<\/span><\/p>\n

Most modern databases like Couchbase, MongoDB, Snowflake, and Google BigQuery support JavaScript UDF.<\/span><\/p>\n

The problem<\/span><\/h2>\n

A common problem seen by users migrating from Oracle to Couchbase is porting their PL\/SQL scripts. Instead of supporting PL\/SQL, Couchbase lets users construct user-defined functions in JavaScript (supported since 2021).<\/span>
\n<\/span>
\n<\/span>JavaScript UDFs allow easy, intuitive manipulation of variant and JSON data. Variant objects passed to a UDF are transformed to native JavaScript types and values.<\/span><\/p>\n

The unintended consequence of this is that the majority of RDBMS that have been in existence for the last ten years have strongly encouraged developers to access the database using their procedural extensions to SQL (PL\/pgSQL, PL\/SQL), which support procedural constructs, integration with SQL, error handling, functions and procedures, triggers, and cursors, or at the very least, functions and procedures (like Sakila). For any attempt to move away from them, all of their scripts would need to be rewritten.<\/span><\/p>\n

Rewriting code is often a tedious task, especially when dealing with PL\/SQL scripts that have been written in the 2000s and maintained since then. These scripts can be complex, often extending to thousands of lines, which can be overwhelming for the average enterprise user.<\/span><\/p>\n

Solution<\/span><\/h3>\n

The ideal approach would be to develop a whole new PL\/SQL evaluator, but that would require an excessive amount of engineering hours, and for the same use case, we already have a modern, stable, and fast JSEvaluator; So why support another evaluator?<\/span><\/p>\n

This makes the problem a perfect use-case to leverage the ongoing advances in AI and LLMs. And that’s what we have done here. We have used Generative AI models to <\/span>automate the conversion of PL\/SQL to JSUDF<\/b>.<\/span>
\n<\/span>
\n<\/span>As of June 2024, models have a limited context window<\/a>, w<\/span>hich means longer PL\/SQL get hit with the error:\u00a0\u00a0<\/span><\/p>\n

This model's maximum context length is 8192 tokens. However, your messages resulted in <More-than-8192> tokens. Please reduce the length of the messages.<\/pre>\n
Note, that this is for GPT4.<\/span><\/p>\n
So do we wait for AI to become more powerful and allow more tokens (like Moore\u2019s Law but for the AI\u2019s context-length-vs-precision)?<\/span><\/p>\n
No, that\u2019s where ANTLR<\/a>, a<\/span> parser generator tool, comes in. ANTLR is well-known to be used for Compiler and Interpreter Development. That way we can break the big script to smaller units that can be translated independently.<\/span><\/p>\n
So are we now building a <\/span>transpiler? <\/b>Well, yes and no.<\/span>
\n<\/span>
\n<\/span>Stages in a transpiler:<\/strong><\/p>\n
    \n
  1. \n
      \n
    1. Lexical Analysis (Tokenization)<\/span><\/li>\n
    2. Syntactic Analysis (Parsing)<\/span><\/li>\n
    3. Semantic Analysis<\/span><\/li>\n
    4. Intermediate Representation (IR) Generation<\/span><\/li>\n
    5. Optimization (Optional)<\/span><\/li>\n
    6. Target Code Generation<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
      How the AI translator works<\/span><\/strong><\/h4>\n
      Above steps 1,2 are done using ANTLR.\u00a0<\/span>We use ANTLR\u2019s Listener interface to <\/span>grab individual Procedure\/Function\/Anonymous-block<\/b>,\u00a0 as they are independent blocks of code. In a case where the\u00a0 Procedure\/Function\/Anonymous-block are themselves exceeding the context window, we translate at a statement level (where the LLM assumes the existence of use of variables\/function calls that aren\u2019t defined here but somewhere before).<\/span><\/p>\n
      Subsequently, steps 3, 4, 5, and 6 are left to the LLM (e.g., GPT), i.e., translating each PL\/SQL block into a JavaScript function to the best of its ability that also preserves the operational semantics of the block and is syntactically accurate.<\/span><\/p>\n
      The results are surprisingly quite positive; the translation is 80-85% accurate.<\/span><\/p>\n
      Another benefit of the solution is that we reduce hallucination by focusing on one task at a time, resulting in more accurate translations.<\/span><\/p>\n
      To visualize:<\/span><\/p>\n
      \"automate<\/a><\/p>\n
      How to use the tool<\/span><\/h2>\n