DBD::OCI8

DBD::OCI8 is a database driver of Ruby/DBI. Refer to the following pages.

• DBI_SPEC
• Using the Ruby DBI Module

This article describes only the DBD::OCI8 specific features.

connect to a remote server.

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:dbname', 'username', 'password')

connect to a local server.

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:', 'username', 'password')

connect as sys user.

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:', 'sys', 'password_of_sys', {'Privilege' => :SYSDBA})

or

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:', 'sys', 'password_of_sys', {'Privilege' => :SYSOPER})

OS authentication

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:', nil, nil)

non-blocking mode

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:', 'username', 'password', {'NonBlocking' => true})

After connected, SQL execution are done by non-blocking mode.

note: Connecting to a server is done with blocking mode.

bind variables

 require 'dbi'
 dbh = DBI.connect('DBI:OCI8:', 'username', 'password')
 sth = dbh.parse('INSERT INTO emp(ename, sal) VALUES (?, ?)')
 sth.execute('FOOMAN', 100)

explicitly define the data type.

 dbh = DBI.connect('DBI:OCI8:', 'username', 'password')
 sth = dbh.prepare('SELECT SYSDATE FROM DUAL')
 sth.func(:define, 1, Date) # fetch the 1st column as Date.
 sth.execute()

BLOB

To insert BLOB data:

 image = open('SagradaFamilia.gif', "rb").read()
 # insert 'EMPTY_BLOB()' to the BLOB column.
 sth = dbh.prepare("INSERT INTO photos(name, image) VALUES (?, EMPTY_BLOB())")
 sth.execute("Sagrada Familia")
 # get the rowid of inserted row.
 rowid = sth.func(:rowid) # call driver specific code.
 # select the empty BLOB column as a OCI8::BLOB.
 lob = dbh.select_one("SELECT image FROM photos WHERE ROWID = ?", rowid)[0] # 1st row, 1st column
 # write data to the LOB.
 lob.write(image)

To update BLOB data:

 new_image = open('SagradaFamilia.gif', "rb").read()
 # select a BLOB column as a OCI8::BLOB with 'FOR UPDATE' to lock the row.
 lob = dbh.select_one("SELECT image FROM photos WHERE name = ? FOR UPDATE", 'Sagrada familia')[0]
 # write data to the lob by OCI8::BLOB#write(data).
 lob.write(new_image)
 # fix the length of its content by OCI8::BLOB#truncate(data.size).
 lob.truncate(new_image.size)

To select BLOB data:

 # select a BLOB column as a OCI8::BLOB.
 lob = dbh.select_one("SELECT image FROM photos WHERE name = ?", 'Sagrada familia')[0]
 # read its content by OCI8::BLOB#read
 image = lob.read()

To delete BLOB data:

 # delete the row.
 dbh.do("DELETE FROM photos WHERE name = ?", 'Sagrada familia')

CLOB

To insert CLOB data:

 text = open('gutenberg.txt', "r").read()
 # insert 'EMPTY_CLOB()' to the CLOB column.
 sth = dbh.prepare("INSERT INTO books(title, content) VALUES (?, EMPTY_CLOB())")
 sth.execute("Project Gutenberg")
 # get the rowid of inserted row.
 rowid = sth.func(:rowid) # call driver specific code.
 # select the empty CLOB column as a OCI8::CLOB.
 lob = dbh.select_one("SELECT content FROM books WHERE ROWID = ?", rowid)[0] # 1st row, 1st column
 # write data to the LOB.
 lob.write(text)

To update CLOB data:

 new_text = open('gutenberg.txt', "r").read()
 # select a CLOB column as a OCI8::CLOB with 'FOR UPDATE' to lock the row.
 lob = dbh.select_one("SELECT content FROM books WHERE title = ? FOR UPDATE", 'Project Gutenberg')[0]
 # write data to the lob by OCI8::CLOB#write(data).
 lob.write(new_text)
 # fix the length of its content by OCI8::CLOB#truncate(OCI8::CLOB#pos).
 # don't use "lob.truncate(new_text.size)". new_text.size is the number of bytes.
 # But the 1st argument of #truncate is the number of characters.
 lob.truncate(lob.pos)

To select CLOB data:

 # select a CLOB column as a OCI8::CLOB.
 lob = dbh.select_one("SELECT content FROM books WHERE title = ?", 'Project Gutenberg')[0]
 # read its content by OCI8::CLOB#read
 text = lob.read()

To delete CLOB data:

 # delete the row.
 dbh.do("DELETE FROM books WHERE content = ?", 'Project Gutenberg')

output parameter

 # enable DBMS_OUTPUT package
 dbh.do("BEGIN DBMS_OUTPUT.ENABLE; END;")
  ... execute DBMS_OUTPUT.PUT_LINE ...
 # get PUT_LINEed strings by using DBMS_OUTPUT.GET_LINE.
 dbh.prepare("BEGIN DBMS_OUTPUT.GET_LINE(?, ?); END;") do |sth|
   # bind the 1st parameter as string whose length is 255
   sth.bind_param(1, ' ' * 255)
   # bind the 2rd parameter as integer
   sth.bind_param(2, 0)
   while true
     sth.execute
     # if the 2nd parameter value is zero, output value exists.
     break if sth.func(:bind_value, 2) != 0
     # putput the 1st parameter value.
     puts sth.func(:bind_value, 1)
   end
 end