Lua

<- Legacy

Overview

Lua is a very powerful and compact language that can be used to write Oracle maintenance scripts. The following covers the essential steps to install and configure Lua for writing maintenance scripts for Oracle 10g/11g on a Windows Oracle database system. With Lua you may find yourself writing less code and getting more done than many other scripting languages.

Prerequisites

Oracle database or at least the Oracle client software (standard or instant) has been installed. The Oracle environment variables are set: ORACLE_HOME etc. If older MS OS download and install Microsoft Visual C++ 2008 Redistributable Package (x86). If you have this package in the same directory as the LuaForWindows install .exe it will be installed.

Procedure

1. Download and install Lua For Windows.
   Select full installation option.
2. Download LuaSQL.
3. Configure ODBC DSN for database connection.
4. Configure your global scripts configuration file (lua.cfg).
5. Configure application vars.

Sample Code

Connection Test

require "luasql.odbc"

-- Init Variables
local sSQL = "SELECT sysdate FROM dual"
local env  = assert( luasql.odbc() )
local conn = assert( env:connect("DB01","scott","tiger") )
local cur  = assert( conn:execute(sSQL) )
local row = cur:fetch({}, "n")  -- "n" = Numeric Indices

-- Output Value
print(row[1])  

-- Housekeeping
cur:close()
conn:close()
env:close()

-- Pause
io.write("Press to continue...")
io.stdin:read()

Cursor Loop
require "luasql.odbc"

-- Init Variables
local env  = assert( luasql.odbc() )
local conn = assert( env:connect("DB01","scott","tiger") )
local cur  = assert( conn:execute("SELECT emp_name FROM HR.emp") )

-- Processing Loop
row = cur:fetch({}, "a") 
while row do
   print(row.EMP_NAME)       -- Field names must be UPPER case.   
   row = cur:fetch({}, "a")  -- "a" = Alphanumeric (FIELD NAME) Indices
end   

-- Housekeeping
cur:close()
conn:close()
env:close()

runLuaScript.bat (for use in Schedulers)

@echo off
cls

set ORACLE_SID=DB01
set ORACLE_HOME=C:\app\oracle\product\11.2.0\client32
set TNS_ADMIN=C:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN

echo Lua Script Environment
echo ORACLE_HOME: %ORACLE_HOME%
echo ORACLE_SID:  %ORACLE_SID%
echo TNS_ADMIN:   %TNS_ADMIN%
echo.

rem
rem Run Lua Script
rem
cd C:\app\oracle\scripts
"C:\Program Files (x86)\Lua\5.1\lua.exe" %1

Comments

• At the time of this writing the Lua package used to connect to Oracle (LuaSQL) is undergoing changes. As a result I used the ODBC connection method. The new site for LuaSQL seems to be here.
• In some schedulers you may need to run it from a batch file that sets the ORACLE environment variables. Set the ORACLE_HOME to the Oracle client being used. Set the ORACLE_SID=DBSvcName and TNS_ADMIN=Path_To_tnsnames.ora

<- Legacy