mkowalik mkowalik - 4 years ago 161
Ruby Question

Ruby OCI8 - bind optional parameters to Oracle Stored procedure

Assuming I have an Oracle stored procedure with the following signature:

CREATE OR REPLACE PROCEDURE "FOO" (p_1 VARCHAR(100),
p_2 VARCHAR(100),
p_opt_1 VARCHAR(100) default null,
p_opt_2 VARCHAR(100) default null) IS
...


how do I bind only the second optional parameter in Ruby script?
Using PL/SQL it's something like:

BEGIN
FOO("p1 val", "p2 val", p_opt_2 => "p2 opt val");
END;


but I haven't found information on how to do a similar thing using OCI8, so for now I'm binding the nil value to the
p_opt_1
, but it smells bad:

begin
cursor = conn.parse('BEGIN foo(:p_1, :p_2, :p_opt1, p_opt2); END;')
cursor.bind_param(':p_1', 'some string', String)
cursor.bind_param(':p_2', 'some other string', String)
cursor.bind_param(':p_opt_1', nil, String)
cursor.bind_param(':p_opt2_2', 'some value', String)
cursor.exec
rescue Exception => e
....


I'm pretty sure there has to be a better way to do it. Can someone point me to it?

Thanks a lot,
Michal

Answer Source
cursor = conn.parse('BEGIN foo(:p_1, :p_2, p_opt_2 => :p_opt_2); END;')
cursor.bind_param(:p_1, 'some string', String)
cursor.bind_param(:p_2, 'some other string', String)
cursor.bind_param(:p_opt_2, 'some value', String)
cursor.exec

more simply:

conn.exec('BEGIN foo(:p_1, :p_2, p_opt_2 => :p_opt_2); END;',
          'some string', 'some other string', 'some value')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download