 |
Forums |
Admin Discussion Forums: help Start New Thread
By: Wiehann Matthysen
 RE: assign NULL bind value to object type vars [ reply ]
2010-11-30 23:07
|
Hi Kubo,
I'm making use of ruby-plsql and ruby-plsql-spec in a project of mine and they depend on ruby-oci8. I am stuck with this issue as I want to write ruby code for plsql procedures and functions that accept object types as parameters and I want to be able to pass NULL values to those procedures and functions for testing purposes. Is there a way that this can be done? Any help would be greatly appreciated. I opened the issue against ruby-plsql-spec (as Raimonds mentioned in the previous post).
Regards,
Wiehann
|
By: Raimonds Simanovskis
assign NULL bind value to object type vars [ reply ]
2009-11-04 13:42
|
When implementing object type support for ruby-plsql I found one other issue that I cannot bind nil (NULL) value for nested object types. And also when I assign nil bind value to plain object type variable it still is not null.
See my sample test below:
------------
require 'rubygems'
gem 'ruby-oci8', '=2.0.3'
require 'oci8'
conn = OCI8.new('hr', 'hr', 'orcl')
conn.exec "DROP TYPE t_employee" rescue nil
conn.exec <<-SQL
CREATE OR REPLACE TYPE t_address AS OBJECT (
street VARCHAR2(50),
city VARCHAR2(50),
country VARCHAR2(50)
)
SQL
conn.exec <<-SQL
CREATE OR REPLACE TYPE t_employee AS OBJECT (
employee_id NUMBER(15),
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
address t_address
)
SQL
class TAddress < OCI8::Object::Base
set_typename "T_ADDRESS"
end
class TEmployee < OCI8::Object::Base
set_typename "T_Employee"
end
puts "Create new address object"
address = TAddress.new(conn, :street => 'street', :city => 'city', :country => 'country')
puts "Create new employee object"
employee = TEmployee.new(conn, :employee_id => 1, :first_name => 'First', :last_name => 'Last',
:hire_date => Date.today, :address => address)
puts "Create new employee object with NULL address - this will fail"
begin
employee = TEmployee.new(conn, :employee_id => 1, :first_name => 'First', :last_name => 'Last',
:hire_date => Date.today, :address => nil)
rescue OCIError => e
puts e.to_s
end
puts "Create new employee object with empty address - this works but is not what I want"
employee = TEmployee.new(conn, :employee_id => 1, :first_name => 'First', :last_name => 'Last',
:hire_date => Date.today, :address => TAddress.new(conn))
puts "How can I assign NULL bind value to object type variable? (this is not assigning NULL)"
cursor = conn.parse <<-SQL
DECLARE
l_address T_ADDRESS;
BEGIN
l_address := :address;
IF l_address IS NULL THEN
:result := 'IS NULL';
ELSE
:result := 'IS NOT NULL';
END IF;
END;
SQL
cursor.bind_param(:address, nil, TAddress)
cursor.bind_param(:result, nil, String, 10)
cursor.exec
puts "result #{cursor[:result]}"
cursor.close
conn.logoff
------------
Any ideas how to correctly pass NULL values for object type variables?
Raimonds
|
|
|
