{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "433df165-49d5-4a00-904a-103df8480d92",
   "metadata": {},
   "source": [
    "## Oracle AI Data Platform v1.0\n",
    "\n",
    "Copyright © 2025, Oracle and/or its affiliates.\n",
    "\n",
    "Licensed under the Universal Permissive License v 1.0 as shown at https://oss.oracle.com/licenses/upl/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ad176b5-9ec9-4c97-80e3-751c08fb23be",
   "metadata": {},
   "source": [
    "# Execute Oracle SQL on Oracle ALH\n",
    "\n",
    "## Prerequisites\n",
    "1. install oracledb in your cluster (create requirements.txt and add oracledb package)\n",
    "2. upload your tnsnames.ora and ewallet.pem from your wallet into the workspace\n",
    "\n",
    "## Overview\n",
    "\n",
    "This example defines a function you can reuse, it generates a sample SQL query and executes it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ddb1eeb-ae80-4c36-acea-259361859c9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the Oracle function execute_oracle_sql to execute simple SQL statements on Oracle ALH\n",
    "#\n",
    "# Parameters:\n",
    "#  - v_sql_file_path: path to Oracle SQL file in workspace or volume\n",
    "#  - v_config_dir: directry where your tnsnames.ora resides\n",
    "#  - v_wallet_dir: directory where your ewallet.pem resides\n",
    "#  - v_user: Oracle database user\n",
    "#  - v_password: Oracle database user password\n",
    "#  - v_dsn: Oracle DSN\n",
    "#  - v_wallet_password: password fow wallet\n",
    "#  - fail_on_error: True/False if statement fails, setting to True will stop the script execution, otherwise it till continue\n",
    "#\n",
    "import oracledb\n",
    " \n",
    "def execute_oracle_sql(v_sql_file_path, v_config_dir, v_wallet_dir, v_user, v_password, v_dsn, v_wallet_password, fail_on_error=False):\n",
    "  # Read the SQL file\n",
    "  sql_script=\"\"\n",
    "  with open(v_sql_file_path, 'r') as file:\n",
    "    sql_script = file.read()\n",
    " \n",
    "  # Split the script into individual statements (naive split by semicolon)\n",
    "  statements = [stmt.strip() for stmt in sql_script.split(';') if stmt.strip()]\n",
    "  try:\n",
    "    # Connect to Oracle\n",
    "    with oracledb.connect(\n",
    "     config_dir=v_config_dir,\n",
    "     user=v_user,\n",
    "     password=v_password,\n",
    "     dsn=v_dsn,\n",
    "     wallet_location=v_wallet_dir,\n",
    "     wallet_password=v_wallet_password) as connection:\n",
    "        with connection.cursor() as cursor:\n",
    "            for statement in statements:\n",
    "                print(f\"Executing: {statement}\")\n",
    "                try:\n",
    "                  cursor.execute(statement)\n",
    "                except Exception as e:\n",
    "                  if (fail_on_error):\n",
    "                      raise e;\n",
    "                  else:\n",
    "                      print(\"  Statement failed (but continuing):\", e)\n",
    "        connection.commit()\n",
    "        print(\"SQL script executed successfully.\")\n",
    "  except Exception as e:\n",
    "    print(\"Error:\", e)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9b33030-4657-4fed-a7e5-18a273faa26b",
   "metadata": {},
   "source": [
    "## Generate Sample SQL script\n",
    "End each statement with a ; it can be multi-line, no PLSQL blocks supported as simplified parsing used. It can also be DDL to create tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22b19f1a-76a2-4695-8d24-9b917346241e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%writefile /Workspace/my_oracle_script.sql\n",
    "select \"Connected\" msg from dual;\n",
    "select \"Connected\" msg from dual;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88013578-513b-44a7-b87a-c6bc8ff6ad00",
   "metadata": {},
   "source": [
    "## Execute Sample SQL Script\n",
    "\n",
    "Update the variables below with your values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0d09e31-ac66-4d2b-bf03-64c5c815b509",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_file_path = \"/Workspace/my_oracle_script.sql\"\n",
    "config_dir=\"/Workspace/your_folder_location_for_tns_names_ora\",\n",
    "user=\"your_user\",\n",
    "password=\"\",\n",
    "dsn=\"your_tns_alias\",\n",
    "wallet_location=\"/Workspace/your_folder_location_for_wallet_pem\",\n",
    "wallet_password=\"\"\n",
    " \n",
    "execute_oracle_sql(sql_file_path, config_dir, wallet_location, user, password, dsn, wallet_password)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}