Method: OCI8#exec

Defined in:

lib/oci8/oci8.rb

#exec(sql, *bindvars, &block) ⇒ Object

Executes the sql statement. The type of return value depends on the type of sql statement: select; insert, update and delete; create, alter and drop; and PL/SQL.

When bindvars are specified, they are bound as bind variables before execution.

select statements without block

It returns the instance of OCI8::Cursor.

example:

conn = OCI8.new('scott', 'tiger')
cursor = conn.exec('SELECT * FROM emp')
while r = cursor.fetch()
  puts r.join(',')
end
cursor.close
conn.logoff

select statements with a block

It acts as iterator and returns the processed row counts. Fetched data is passed to the block as array. NULL value becomes nil in ruby.

example:

conn = OCI8.new('scott', 'tiger')
num_rows = conn.exec('SELECT * FROM emp') do |r|
  puts r.join(',')
end
puts num_rows.to_s + ' rows were processed.'
conn.logoff

PL/SQL block (ruby-oci8 1.0)

It returns the array of bind variables’ values.

example:

conn = OCI8.new('scott', 'tiger')
conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123)
# => ["0123", 123]
conn.logoff

Above example uses two bind variables which names are :str and :num. These initial values are “the string whose width is 4 and whose value is ‘ABCD’” and “the number whose value is 123”. This method returns the array of these bind variables, which may modified by PL/SQL statement. The order of array is same with that of bind variables.

If a block is given, it is ignored.

PL/SQL block (ruby-oci8 2.0)

It returns the number of processed rows.

example:

conn = OCI8.new('scott', 'tiger')
conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123)
# => 1
conn.logoff

If a block is given, the bind variables’ values are passed to the block after executed.

conn = OCI8.new('scott', 'tiger')
conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123) do |str, num|
  puts str # => '0123'
  puts num # => 123
end
conn.logoff

FYI, the following code do same on ruby-oci8 1.0 and ruby-oci8 2.0.

conn.exec(sql, *bindvars) { |*outvars| outvars }

Other SQL statements

It returns the number of processed rows.

example:

conn = OCI8.new('scott', 'tiger')
num_rows = conn.exec('UPDATE emp SET sal = sal * 1.1')
puts num_rows.to_s + ' rows were updated.'
conn.logoff

example:

conn = OCI8.new('scott', 'tiger')
conn.exec('CREATE TABLE test (col1 CHAR(6))') # => 0
conn.logoff

# File 'lib/oci8/oci8.rb', line 260

def exec(sql, *bindvars, &block)
  @last_error = nil
  exec_internal(sql, *bindvars, &block)
end