Oracle7 Module for Ruby version 0.2.11 Yoshida Masato - Introduction This is the module to access to Oracle 7. Many functions also work with Oracle 8. Not all Oracle functions are implemented. It has been confirmed to work with 7.3.3/Solaris2, 8.0.4/Solaris2, 8.0.5/Linux and 8.0.5/WindowsNT. - Installation This can work with ruby-1.4. I recommend you to use ruby-1.6.1 or later. And Oracle Call Interface (OCI) must be installed. Change extconf.rb properly, (and set the environment variable ORACLE_HOME), then make and install usually. ruby extconf.rb make make site-install -- Compilation with Visual C++ Change extconf.rb properly, and set the following environment variables: set INCLUDE=c:\orant\OCI80\INCLUDE;%INCLUDE% set LIB=c:\orant\OCI80\LIB\MSVC;%LIB% And append OCI.LIB to LIBS in Makefile of Ruby. -- Compilation with CYGWIN Create a import library from Oracle's DLL and change extconf.rb properly. Oracle8 dlltool -d OCI.def -D $ORACLE_HOME/BIN/OCI.DLL -l libOCI.a Oracle7.3 dlltool -d OCI73.def -D $ORACLE_HOME/BIN/ORA73.DLL -l libOCI.a - Usage You must load this module: require "oracle" You can use low-level APIs (nearly OCI) and high-level APIs (more easier). -- High-level APIs These APIs allocate data buffer automatically and recognize data type. Oracle class Singleton methods: new(uid=nil, pswd=nil, conn=nil) Connects to Oracle. Uid is user ID, pswd is password, and conn is a connection string of SQL*Net. It creates an Oracle object. The failure of the connection raises a exception. If you have connected with Pro*C, you can get the connection without any parameters. It may be convenient for using with Pro*C modules. Oracle::Binary(str) Type conversion method for bindvars of Oracle#exec. The bindvars for LONG RAW must be converted into Oracle::RawData object by this method. See samples/lotest.rb. Methods: logoff Disconnects the connection. The transaction that has not be committed will be rolled back. If the connection was established by Pro*C, you should disconnect it with Pro*C. exec(sql, *bindvars) Executes a SQL. The query result can get by fetch method. If bindvars are specified, each string is bound with SQL variables :0, :1, :2, ... in SQL statement. It returns a Cursor object. The failure of the execution raises a exception. It can be called as a iterator. The block parameter is an array of fetched values. parse(sql) Parse and prepare a SQL statement and return the cursor connected to it. If you want to execute this statement you have first to Cursor#bind values to them, or pass the values during the call to Cursor#exec commit Commits a transaction. rollback Rolls back a transaction. autocommit Gets autocommit mode. autocommit=(mode) Sets autocommit mode (true or false). Oracle::Cursor class Methods: initialize(conn, sql=nil, *bindvars) Initialize the new ORAcursor instance. If a SQL statement was given, the statement will be parsed/prepared. The values contained in *bindvars will be bound to the ORAcursor instance and ORAcursor#exec will be invoked. However, if there was no SQL statement given, no preparation or execution takes place. parse(sql) Parse and prepare a SQL statement and connect it with the current Cursor instance (and therefore with the underlying ORAcursor instance). bind(*bindvars) Binds the values of *bindvars to the internal ORAcursor instance. Necessary before you call Cursor#exec. But you may pass the *bindvars also to Cursor#exec instead. exec(*bindvars) Binds the values of *bindvars to the internal ORAcursor and executes the statement. You may fetch the rows with Cursor#fetch afterwards. getColNames Gets an array of the column names. getDWidth Gets an array of the display size. fetch Fetches a row and gets an array of fetched values. On the end of rows, it returns nil. It can be called as a iterator. An integer precision NUMBER value is returned by Fixnum or Bignum, the other NUMBER value is returned by Float, the other type value is returned by String. close Closes the cursor. Cursors might use a large number of memories, you should close useless cursors. -- Low-level APIs These APIs can access OCI directly. But some parameters are fixed for my own work. ORAconn class Singleton methods: new(uid, pswd=nil, conn=nil) logon(uid, pswd=nil, conn=nil) Connects to Oracle. Uid is user ID, pswd is password, and conn is a connection string of SQL*Net. Uid can be specified like "user/passwd@seavice" without pswd and conn. It returns an ORAconn object. The failure of the connection raises a exception. getConnection Gets the connection if you have connected with Pro*C. It may be convenient for using with Pro*C modules. It returns an ORAconn object. The failure of the connection raises a exception. Methods: disconnect logoff Disconnects the connection. open Creates a cursor. It returns an ORAcursor object. commit Commits a transaction. rollback Rolls back a transaction. nbset Changes into non-blocking mode. nbclear Changes into blocking mode. nbtest Tests whether the connection is non-blocking mode or not. break Breaks the executing SQL. commiton Enables autocommit. commitoff Disables autocommit. ORAcursor class Methods: close Closes the cursor. parse(sql) Parses a sql. describe(pos) Get informations of the specified select-list item. Pos is the position index (the first index is 1). It returns an array [maximum size of the column, internal data type, name of the column, maximum display size, precision, scale, nullok]. define(pos, buflen, datatype) Defines an output variable for the specified select-list item and allocates output buffers. Pos is the position index (the first index is 1), buflen is the internal buffer size (< 65536), and datatype is one of the following integer value. VARCHAR2 = 1 NUMBER = 2 INTEGER = 3 FLOAT = 4 LONG = 8 ROWID = 11 DATE = 12 RAW = 23 LONG_RAW = 24 UNSIGNED_INT = 68 CHAR = 96 MLSLABEL = 105 If datatype is INTEGER, UNSIGNED_INT or FLOAT, buflen value is ignored and used the system-depended size of int or double. Some NLS_LANG cause larger outputs than the internal data. So buffer size for some type (such as VARCHAR2) may be estimated larger than the internal data size. The size of LONG and LONG RAW data cannot be known by describe. You must set the proper size (I recommend 65535 bytes). You can always get full size of data. exec Executes the SQL that is specified by parse method. When the SQL is DML, it returns the processed rows count. fetch Fetches a row of the result of the query. It returns the processed rows count. On the end of rows, it returns a nil. Fetched data store in internal buffers, and you can get those with getCol method. cancel Cancels to fetch. bindrv(var, progv, ftype = CHAR) Associates the Ruby string with the placeholder in the SQL. Var is the placeholder name that starts with ':', progv is the string that is referred/updated when exec method is called. Ftype can specify the data type by an integer value same as define method. When progv receives a value, it should be enough large to store. An assignment to progv might change the object address, you should re-bindrv before exec. getCol(index) Gets a specified column's data in the fetched row. Index is the position index (the first index is 1). It returns an array [data, indicator, error code]. If datatype is INTEGER or UNSIGNED_INT, data is an integer value, if FLOAT, data is a float value, otherwise data is a string value. NULL value is nil. getROWID Gets ROWID of the executed SQL. It might not be reliable value, for it referred OS-dependent area of Cursor Data Area (CDA). It does not work on Oracle8. - BUG When DB charset differ from runtime charset (NLS_LANG), LONG type data over 64K bytes cannot be retrieved correctly, because partial fetch offset is assumed to be the same as fetched data size. LONG RAW data have no problem. - Copying This extension module is copyrighted free software by Yoshida Masato. You can redistribute it and/or modify it under the same term as Ruby. - Author Yoshida Masato - History Dec 23, 2000 version 0.2.11 Oracle#exec can be used as iterator. Dec 21, 2000 version 0.2.10 Supports autocommit. Nov 19, 2000 version 0.2.9 Oracle::Cursor was improved by Clemens Hintze. Dec 15, 1999 version 0.2.8 Fixes small buffer problem. Dec 14, 1999 version 0.2.7 Fixes LONG data over 32KB problem of fetch. Supports LONG and LONG RAW data over 64KB. Sep 1, 1999 version 0.2.6 English document Sep 21, 1998 version 0.1