Automated Code Conversion: From PL/SQL to Python Using LangChain

Introduction

As technology evolves, many businesses find themselves needing to migrate legacy codebases to modern, more versatile languages. One such common scenario is converting PL/SQL, Oracle's procedural language extension for SQL, into Python, a language widely praised for its simplicity, flexibility, and powerful ecosystem.

In this blog post, we'll walk you through the process of using LangChain, powered by OpenAI, to automate the conversion of PL/SQL code into Python. We'll demonstrate how to not only convert the code itself but also generate and run corresponding test cases, ensuring the converted code works as expected.

While the example provided here is simple, it's important to note that Revivesoft's proprietary workflows enable production-level conversion from legacy environments, handling complex applications with ease.

Step 1: Setting Up Your Environment

Before we begin, you need to set up your environment with the necessary libraries.

1.1 Installation

Create a virtual environment and install the required packages to ensure that you have the necessary dependencies to proceed.

python3 -m venv venv
source venv/bin/activate
pip install langchain langchain-openai

1.2 Configure OpenAI API Key

Make sure you have your OpenAI API key set up in your environment to enable interaction with OpenAI's API.

export OPENAI_API_KEY=<your_key>

Step 2: Converting PL/SQL Code to Python

We'll start by creating a script that uses LangChain to convert a simple PL/SQL function and its corresponding test case into Python.

2.1 Code Conversion Script

Create a file named convert.py:

from langchain_openai import OpenAI
from langchain_core.prompts import PromptTemplate

# Initialize the LLM
llm = OpenAI(temperature=0.2)

# Define the conversion prompt for the function
function_conversion_prompt = PromptTemplate(
    input_variables=["plsql_code"],
    template=\"\"\"
You are an expert in both PL/SQL and Python. Convert the following PL/SQL function into Python code:

PL/SQL Code:
{plsql_code}

Python Code:
\"\"\"
)

# Define the conversion prompt for the test case
test_conversion_prompt = PromptTemplate(
    input_variables=["plsql_test_case"],
    template=\"\"\"
You are an expert in both PL/SQL and Python. Convert the following PL/SQL test case into a Python test case using the unittest framework.

Make sure to include the import statement for the calc_factorial function at the top of the test case file.

PL/SQL Test Case:
{plsql_test_case}

Python Test Case:
\"\"\"
)

def convert_plsql_to_python(plsql_code, plsql_test_case):
    function_prompt_text = function_conversion_prompt.format(plsql_code=plsql_code)
    python_code = llm(function_prompt_text)["choices"][0]["text"]
    
    test_prompt_text = test_conversion_prompt.format(plsql_test_case=plsql_test_case)
    python_test_case = llm(test_prompt_text)["choices"][0]["text"]
    
    return python_code, python_test_case

Step 3: Saving the Converted Code

After converting the PL/SQL code to Python, you'll want to save it in separate files to organize the code and test cases properly.

###3.1 Save Script Create a file named save_code.py:

def save_code_to_file(python_code, python_test_case):
    with open('factorial.py', 'w') as f:
        f.write(python_code)

    with open('test_factorial.py', 'w') as f:
        f.write(python_test_case)

This script will save the converted Python code and test cases to factorial.py and test_factorial.py respectively.

Step 4: Running the Test Cases

Once the Python code and test cases are saved, you can run the tests to ensure everything works as expected. This step will validate the accuracy of the conversion.

4.1 Test Runner Script

Create a file named run_tests.py:

import subprocess

def run_python_tests():
    result = subprocess.run(['python', 'test_factorial.py'], capture_output=True, text=True)
    
    print("Test Output:\n", result.stdout)
    
    if "OK" in result.stdout:
        print("All tests passed successfully!")
    else:
        print("Some tests failed. Please review the output above.")

    if result.stderr:
        print("Errors:\n", result.stderr)
    
    return result.stdout

This script runs the test cases and checks whether they succeeded.

Step 5: Putting It All Together

Finally, create a main script to tie everything together and run the conversion, save the code, and execute the tests in one seamless process.

5.1 Main Script

Create a file named main.py:

from convert import convert_plsql_to_python
from save_code import save_code_to_file
from run_tests import run_python_tests

plsql_code = \"\"\"
CREATE OR REPLACE FUNCTION calc_factorial(n IN NUMBER) RETURN NUMBER IS
    result NUMBER := 1;
BEGIN
    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;
    RETURN result;
END calc_factorial;
\"\"\"

plsql_test_case = \"\"\"
DECLARE
    v_result NUMBER;
BEGIN
    v_result := calc_factorial(5);
    DBMS_OUTPUT.PUT_LINE('Factorial of 5 is ' || v_result);
    IF v_result = 120 THEN
        DBMS_OUTPUT.PUT_LINE('Test Passed');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Test Failed');
    END IF;
END;
\"\"\"

# Convert PL/SQL to Python
python_code, python_test_case = convert_plsql_to_python(plsql_code, plsql_test_case)

# Print the converted code
print("Converted Python Code:\n", python_code)
print("Converted Python Test Case:\n", python_test_case)

# Save the converted code and test case to files
save_code_to_file(python_code, python_test_case)

# Run the test case and display the results
print("Running Tests...\n")
run_python_tests()

Conclusion

By following this guide, you've automated the conversion of PL/SQL code to Python and verified the conversion using generated test cases. While this is a simple example, it illustrates the power of AI-driven code conversion.

However, real-world applications are far more complex. Revivesoft's proprietary workflows go beyond these basic examples, enabling production-level conversions from legacy environments with the ability to handle intricate codebases and integrations. With Revivesoft, your transition to modern technology is not just possible—it's seamless and efficient.

GitHub Repository

You can find the complete code for this example on GitHub: GitHub Repository Link

For more information on how Revivesoft can help with your legacy code migration, contact us today!