[ Home ] [ English | Japanese ]
This is the abstract super class of OCI Handles. See Class Hierarchy.
OCIHandle#attrSet(type, value)correspond native OCI function: OCIAttrSet
OCIHandle#attrGet(type)correspond native OCI function: OCIAttrGet
OCIHandle#free()explicitly free the OCI's data structure.
correspond native OCI function: OCIHandleFree
The environment handle is the source of all OCI objects. Usually there is one instance per one application.
super class: OCIHandle
correspond native OCI datatype: OCIEnv
OCIEnv.create([mode])create the environment handle. Don't call `new'.
OCI_DEFAULT, OCI_THREADED, OCI_OBJECT, OCI_SHARED or these combination. But I test only OCI_DEFAULT. Default value is OCI_DEFAULT.
OCI_THREADED: if your application use native thread(not ruby's thread), use this.
OCI_OBJECT: for OCI objects. But current implementation doesn't support OCI Object-Relational functions. This is for far future extension, because my plan doesn't include `OCI object support'.
OCI_SHARED(Oracle 8i or later): for Shared Data Mode.
correspond native OCI function: OCIEnvCreate (if OCIEnvCreate is not found, the combination of OCIInitialize and OCIEnvInit.)
OCIEnv.terminate([mode])correspond native OCI function: OCITerminate
OCIEnv#alloc(handle)create a new OCI handle.
correspond native OCI function: OCIHandleAlloc
OCIEnv#logon(username, password, dbname)Logon to the Oracle server.
See Simplified Logon and Explicit Attach and Begin Session
For example:
env = OCIEnv.create()
svc = env.logon("SCOTT", "TIGER", nil)
or
svc = env.logon("SCOTT", "TIGER", "ORCL.WORLD")
correspond native OCI function: OCILogon
The service context handle is correspond to `session' compared with other general database interfaces although OCI constains OCISession.
This handle cooperates with a server handle, a user session handle, and a transaction handle. But these three handles work at the back of it. So you don't have to use them except when you have special purpose.
super class: OCIHandle
correspond native OCI datatype: OCISvcCtx
OCISvcCtx#logoff()disconnect from Oracle.
If you use OCIServer#attach and OCISession#begin to logon, use OCIServer#detach and OCISession#end instead. See also Simplified Logon and Explicit Attach and Begin Session.
correspond native OCI function: OCILogoff
OCISvcCtx#passwordChange(username, old_password, new_password [, mode])OCI_DEFAULT or OCI_AUTH. Default value is OCI_DEFAULT.
For most cases, use default value. If you want to know detail, see "Oracle Call Interface Programmer's Guide".
correspond native OCI function: OCIPasswordChange
OCISvcCtx#commit([flags])commit the transaction.
correspond native OCI function: OCITransCommit
OCISvcCtx#rollback([flags])rollback the transaction.
correspond native OCI function: OCITransRollback
OCISvcCtx#version()get server version.
string of server version. For example
Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production
correspond native OCI function: OCIServerVersion
OCISvcCtx#release()get server version number and string
array of number and string. For example
version_number, version_str = svc.release() version_number is 0x8005000. version_str is Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production
correspond native OCI function: OCIServerVersion
Oracle 9i or later?
If you use OCIEnv#logon, you have no need to use this handle directly. Because OCIEnv#logon create this handle implicitly and set it to OCISvcCtx.
super class: OCIHandle
correspond native OCI datatype: OCIServer
OCIServer#attach(dbname [, mode])attach to the database.
OCI_DEFAULT or OCI_CPOOL(Oracle 9i). Default value is OCI_DEFAULT.
This ruby module doesn't support the connection pooling provided by OCI, so OCI_CPOOL is invalid value for now.
correspond native OCI function: OCIServerAttach
OCIServer#detach([mode])detach from the database.
correspond native OCI function: OCIServerDetach
OCIServer#version()get server version.
string of server version. For example
Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production
correspond native OCI function: OCIServerVersion
OCIServer#release()get server version number and string
array of number and string. For example
version_number, version_str = srv.release() version_number is 0x8005000. version_str is Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production
correspond native OCI function: OCIServerVersion
Oracle 9i or later?
If you use OCIEnv#logon, you have no need to use this handle directly. Because OCIEnv#logon create this handle implicitly and set it to OCISvcCtx.
super class: OCIHandle
correspond native OCI datatype: OCISession
OCISession#begin(svc [, credt [, mode]])start user session under the specified server context.
OCI_CRED_RDBMS or OCI_CRED_EXT. Default value is OCI_CRED_RDBMS.
If you use OCI_CRED_RDBMS, set OCI_ATTR_USERNAME and OCI_ATTR_PASSWORD in advance.
OCI_DEFAULT, OCI_MIGRATE, OCI_SYSDBA, OCI_SYSOPER, (OCI_SYSDBA | OCI_PRELIM_AUTH), or (OCI_SYSOPER | OCI_PRELIM_AUTH). Default value is OCI_DEFAULT.
If you need SYSDBA or SYSOPER privilege, use OCI_SYSDBA or OCI_SYSOPER respectively.
correspond native OCI function: OCISessionBegin
OCISession#end(svc [, vmode])terminate user Authentication Context
correspond native OCI function: OCISessionEnd
Statemet handle identify a SQL or PL/SQL statement and its associated attributes.
Information about SQL or PL/SQL's input/output variables is managed by the bind handle. Fetched data of select statement is managed by the define handle.
super class: OCIHandle
correspond native OCI datatype: OCIStmt
OCIStmt#prepare(stmt [, language [, mode]])set and prepare SQL statement.
OCI_DEFAULT or OCI_NO_SHARING. Default value is OCI_DEFAULT.
OCI_NO_SHARING disables Shared Data Mode for this statement.
correspond native OCI function: OCIStmtPrepare
OCIStmt#defineByPos(position, type [, length [, mode]])define the datatype of fetched column. You must define all column's datatype, before you fetch data.
correspond native OCI function: OCIDefineByPos
OCIStmt#bindByPos(position, type [, length [, mode]])define the datatype of the bind variable by posision.
correspond native OCI function: OCIBindByPos
OCIStmt#bindByName(name, type [, length [, mode]])define the datatype of the bind variable by name.
for example
stmt = env.alloc(OCIStmt)
stmt.prepare("SELECT * FROM EMP
WHERE ename = :ENAME
AND sal > :SAL
AND hiredate >= :HIREDATE")
b_ename = stmt.bindByName(":ENAME", String, 10)
b_sal = stmt.bindByName(":SAL", Fixnum)
b_hiredate = stmt.bindByName(":HIREDATE", OraDate)
correspond native OCI function: OCIBindByName
OCIStmt#execute(svc [, iters [, mode]])execute statement at the service context handle.
the number of iterations to execute.
For select statement, if there are columns which is not defined by OCIStmt#defineByPos and this value is positive, it raises exception. If zero, no exception. In any case you must define all columns before you call OCIStmt#fetch.
For non-select statement, use positive value.
Default value is 0 for select statement, 1 for non-select statement.
note: Current implemantation doesn't support array fetch and batch mode, so valid value is 0 or 1.
OCI_DEFAULT, OCI_BATCH_ERRORS, OCI_COMMIT_ON_SUCCESS, OCI_DESCRIBE_ONLY, OCI_EXACT_FETCH, OCI_PARSE_ONLY, any combinations of previous values, or OCI_STMT_SCROLLABLE_READONLY. Default value is OCI_DEFAULT.
OCI_BATCH_ERRORS and OCI_STMT_SCROLLABLE_READONLY are not supported by current implementation.
correspond native OCI function: OCIStmtExecute
OCIStmt#fetch([nrows [, orientation [, mode]]])fetch data from select statement. fetched data are stored to previously defined define handle.
number of rows to fetch. If zero, cancel the cursor. The default value is 1.
Because array fetch is not supported, valid value is 0 or 1.
correspond native OCI function: OCIStmtFetch
OCIStmt#paramGet(position)get column information of executed select statement. See Select a table whose column types are unknown.
correspond native OCI function: OCIParamGet
The define handle, which is created by OCIStmt#defineByPos.
The fetched data of select statements is got via this handle.
super class: OCIHandle
correspond native OCI datatype: OCIDefine
OCIDefine#get()get the selected date.
correspond native OCI function: nothing
The bind handle, which is created by OCIStmt#bindByPos or OCIStmt#bindByName.
super class: OCIHandle
correspond native OCI datatype: OCIBind
OCIBind#get()get the bind value, which set by OCI call.
correspond native OCI function: nothing
OCIBind#set(value)get the bind value to pass Oracle via OCI call.
correspond native OCI function: nothing
The describe handle, which is used for the explicit describe.
super class: OCIHandle
correspond native OCI datatype: OCIDescribe
For example:
dsc = env.alloc(OCIDescribe) dsc.describeAny(svc, "EMP", OCI_PTYPE_TABLE) parm = dsc.attrGet(OCI_ATTR_PARAM) ...get various information from parm...
TODO: more explanation and examples.
OCIDescribe#describeAny(svc, name, type)get various information of Oracle's schema objects: tables, views, synonyms, procedures, functions, packages, sequences, and types.
correspond native OCI function: OCIDescribeAny
note: To use this method in Oracle 8.0.5 for Linux, call OCIEnv.create with OCI_OBJECT or segmentation fault occurs. This bug was fixed 8.0.6 or later.
The abstract class of OCI descriptors.
OCIDescriptor#attrGet(type)See also Attributes of Handles and Descriptors.
OCIDescriptor#free()super class: OCIDescriptor
OCIError#code()OCIError#codes()OCIError#message()OCIError#messages()OCIError#parseErrorOffset()OCIError#sql()date and time between 4712 B.C. and 9999 A.D.
OraDate.new([year [, month [, day [, hour [, min [,sec]]]]]])OraDate.now()OraDate#to_s()OraDate#to_a()OraDate#yearOraDate#monthOraDate#dayOraDate#hourOraDate#minuteOraDate#secondOraDate#trunc()This is not a numeric class, so algebra operations and setter methods are not permitted, but a place to hold fetched date from Oracle server.
In fact as matter, this is a test class to check the algorithm to convert Oracle internal number format to Ruby's number, which is internally used by, for example, OCIStmt#attrGet(OCI_ATTR_MIN).
OraNumber.new()OraNumber#to_i()OraNumber#to_f()OraNumber#to_s()To set attribute value, use OCIHandle#attrSet or OCIDescriptor#attrSet. To get attribute value, use OCIHandle#attrGet or OCIDescriptor#attrGet.
See "Oracle Call Interface Programmer's Guide" to know detail. If you don't have the manual, you can get the PDF and HTML document from OTN.
get ROWID of current cursor from OCIStmt. This is not fixed spec. See Rowid Support.
(Oracle 9i or later.)
get statement type from OCIStmt.
The return value is OCI_STMT_SELECT, OCI_STMT_UPDATE, OCI_STMT_DELETE, OCI_STMT_INSERT, OCI_STMT_CREATE OCI_STMT_DROP, OCI_STMT_ALTER, OCI_STMT_BEGIN, or OCI_STMT_DECLARE.
get the type of OCIParam.
The return value is OCI_PTYPE_TABLE, OCI_PTYPE_VIEW, OCI_PTYPE_PROC, OCI_PTYPE_FUNC, OCI_PTYPE_PKG, OCI_PTYPE_TYPE, OCI_PTYPE_SYN, OCI_PTYPE_SEQ, OCI_PTYPE_COL, OCI_PTYPE_ARG, OCI_PTYPE_LIST, OCI_PTYPE_TYPE_ATTR, OCI_PTYPE_TYPE_COLL, OCI_PTYPE_TYPE_METHOD, OCI_PTYPE_TYPE_ARG, OCI_PTYPE_TYPE_RESULT, OCI_PTYPE_SCHEMA, or OCI_PTYPE_DATABASE.
get or set the maxinum number of characters from OCIDefine or OCIBind, rather than the number of bytes.
Don't set larger number than previous one.
(Oracle 9i or later?)
get whether the type of length semantics from OCIParam of column. If true, in characters. If false, in bytes.
(Oracle 9i or later?)
get the maximum size of the column, argument and so on in characters form OCIParam. To get in bytes, use OCI_ATTR_DATA_SIZE.
(Oracle 9i or later?)
To get the result of select statement, you should define the datatype by using OCIStmt#defineByPos. You can know the fetched data from the define handle which has been obtained when you defined the column.
For example:
require 'oci8'
# create environment handle
env = OCIEnv.create()
# logon to oracle
svc = env.logon("SCOTT", "TIGER", nil)
# prepare statement handle
stmt = env.alloc(OCIStmt)
stmt.prepare("SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL")
# define the 1st column as String whose size is 10.
col1 = stmt.defineByPos(1, String, 10)
# execute one times under the context of svc.
stmt.execute(svc, 1)
# get result from the define handle
p col1.get()
# free the statement handle.
stmt.free()
# logoff
svc.logoff()
The available datatypes to pass the second argument of OCIStmt#defineByPos are listed below.
For example:
stmt.defineByPos(1, String, 100) stmt.defineByPos(2, Fixnum) stmt.defineByPos(3, Integer) stmt.defineByPos(4, Float) stmt.defineByPos(5, Time) stmt.defineByPos(6, OraDate) stmt.defineByPos(7, OraNumber)
To fetch rows more than one, use OCIStmt#fetch.
When date are fetched successfully, it returns array of the define handles which are define by previous OCIStmt#defineByPos calls. When end of data, returns nil.
require 'oci8'
# create environment handle
env = OCIEnv.create()
# logon to oracle
svc = env.logon("SCOTT", "TIGER", nil)
# prepare statement handle
stmt = env.alloc(OCIStmt)
stmt.prepare("SELECT ename, sal FROM emp")
stmt.defineByPos(1, String, 10)
stmt.defineByPos(2, Fixnum)
# execute under the context of svc.
# When using fetch, the 2nd argument is not needed.
stmt.execute(svc)
# loop and fetch
while result = stmt.fetch()
p result[0].get() # 1st column
p result[1].get() # 2nd column
end
# get result from the define handle
stmt.free()
# logoff
svc.logoff()
The number of columns and those datatypes can be are obtained via read-only parameter descriptor dynamically.
After you get them, you can define the datatypes of select-list and get results.
For example:
require 'oci8'
env = OCIEnv.create()
svc = env.logon("SCOTT", "TIGER", nil)
stmt = env.alloc(OCIStmt)
stmt.prepare("SELECT * FROM emp")
stmt.execute(svc)
# after execute select statement,
# loop from 1 to the number of columns.
num_of_columns = stmt.attrGet(OCI_ATTR_PARAM_COUNT)
1.upto(num_of_columns) do |i|
# get the read-only parameter descriptor of the i-th column.
parm = stmt.paramGet(i)
# get parameters of the column.
name = parm.attrGet(OCI_ATTR_NAME)
datatype = parm.attrGet(OCI_ATTR_DATA_TYPE)
datasize = parm.attrGet(OCI_ATTR_DATA_SIZE)
scale = parm.attrGet(OCI_ATTR_SCALE)
precision = parm.attrGet(OCI_ATTR_PRECISION)
# change define type according to the datatype.
case datatype
when OCI_TYPECODE_VARCHAR
stmt.defineByPos(i, String, datasize)
when OCI_TYPECODE_CHAR
stmt.defineByPos(i, String, datasize)
when OCI_TYPECODE_RAW
stmt.defineByPos(i, OCI_TYPECODE_RAW, datasize)
when OCI_TYPECODE_NUMBER
if scale == 0
# Integer
if precision <= 9 # the precision of Fixnum (assuming 31 bit integer)
stmt.defineByPos(i, Fixnum)
else
stmt.defineByPos(i, Integer)
end
else
# not Integer
if precision < 15 # the precision of double.
stmt.defineByPos(i, Float)
else
stmt.defineByPos(i, OraNumber)
end
end
when OCI_TYPECODE_DATE
stmt.defineByPos(i, OraDate)
# or stmt.defineByPos(i, Time)
else
raise "unsupported datatype: #{datatype}"
end
print(", ") if i != 1
print(name)
end
print("\n")
while result = stmt.fetch()
# OCI counts from 1, but array counts from 0.
0.upto(num_of_columns - 1) do |i|
print(", ") if i != 0
print(result[i].get())
end
print("\n")
end
stmt.free()
svc.logoff()
Gettin data from a bind variable and setting data to a bind variable are done via the Bind Handle. The usage of it is same with the Define Handle except it can be set value.
Example of OCIStmt#bindByPos
stmt = env.alloc(OCIStmt)
stmt.prepare("SELECT sal FROM emp WHARE ename = :1")
stmt.defineByPos(1, Fixnum)
stmt.bindByPos(1, String, 20).set("SMITH")
stmt.execute(svc)
Example of OCIStmt#bindByName:
stmt = env.alloc(OCIStmt)
stmt.prepare("SELECT sal FROM emp WHARE ename = :ename")
stmt.defineByPos(1, Fixnum)
stmt.bindByName(":ename", String, 20).set("SMITH")
stmt.execute(svc)
For the prepared statement handle, it is possibyle to execute same statment repeatedly with the different bind values.
For example:
stmt.prepare("INSERT INTO emp(ename, sal) VALUES(:ename, :sal)")
bind_ename = stmt.bindByName(":ename", String, 20)
bind_sal = stmt.bindByName(":sal", Fixnum)
for line in ARVF
ename, sal = line.split(",")
bind_ename.set(ename)
bind_sal.set(sal.to_i)
stmt.execute(svc)
end
The bind handles are not freed until belonging statement handle is freed or prepared again. So don't create bind handles in loop statement.
Bad example:
stmt.prepare("INSERT INTO emp(ename, sal) VALUES(:ename, :sal)")
for line in ARVF
ename, sal = line.split(",")
stmt.bindByName(":ename", String, 20).set(ename) # bad example
stmt.bindByName(":sal", Fixnum).set(sal.to_i) # bad example
stmt.execute(svc)
end
To get value from PL/SQL blocks, bind the bind variable to create the bind hanlde and get date from it using COIBind#get. The binding way is same with as input.
For example:
stmt.prepare("BEGIN :foo = BAR_FUNCTION(:baz); END;")
foo = stmt.bindByName(":foo", String, 30)
baz = stmt.bindByName(":baz", String, 30)
baz.set("BAZOON") # set value to input bind handle.
stmt.execute(svc)
p foo.get() # get value from output bind handle.
require 'oci8'
begin
env = OCIEnv.create()
svc = env.logon("SCOTT", "TIGER", nil)
stmt = env.alloc(OCIStmt)
stmt.prepare("select * from table_name_which_does_not_exist")
stmt.execute(svc)
rescue OCIError
print "Error code is " + $!.code.to_s + ".\n"
print $!.message + "\n"
offset = $!.parseErrorOffset
if ! offset.nil?
if $!.respond_to?("sql")
# Oracle 8.1.6 or later.
print "sql is: " + $!.sql + "\n"
print "error at" + " " * offset + "^here\n"
else
print "error at " + offset.to_s + "\n"
end
end
ensure
stmt.free() if ! stmt.nil?
svc.logoff() if ! svc.nil?
env.free if ! env.nil?
end
require 'oci8' username = "SYS" password = "CHANGE_ON_INSTALL" dbname = nil privilege = OCI_SYSDBA env = OCIEnv.create() srv = env.alloc(OCIServer) srv.attach(dbname) svc = env.alloc(OCISvcCtx) svc.attrSet(OCI_ATTR_SERVER, srv) auth = env.alloc(OCISession) auth.attrSet(OCI_ATTR_USERNAME, username) auth.attrSet(OCI_ATTR_PASSWORD, password) auth.begin(svc, OCI_CRED_RDBMS, privilege) svc.attrSet(OCI_ATTR_SESSION, auth) ... do SYSDBA jobs ... auth.end(svc) srv.detach() svc.free() env.free()
I'll rewrite this section because rowid descriptors are supported.
This method is for single user and single connection. See also Explicit Attach and Begin Session.
For example:
env = OCIEnv.create() svc = env.logon(username, password, dbname)
To logoff:
svc.logoff()
This method is for multiple sessions or connections. In addition, if you need SYSDBA or SYSOPER privilege, use this. See also Simplified Logon.
For example:
# connect as SYSDBA env = OCIEnv.create() svc = env.alloc(OCISvcCtx) srv = env.alloc(OCIServer) srv.attach(dbname) svc.attrSet(OCI_ATTR_SERVER, srv) auth = env.alloc(OCISession) auth.attrSet(OCI_ATTR_USERNAME, username) auth.attrSet(OCI_ATTR_PASSWORD, password) auth.begin(svc, OCI_CRED_RDBMS, OCI_SYSDBA) svc.attrSet(OCI_ATTR_SESSION, auth)
To logoff:
srv.detach() auth.end(svc) svc.free()