help - execute plsql stub with dbms output

[bk]

Hello All,
how can I use ruby to execute a block of plsql code?
The sql that I'm trying to execute is inside a .sql file
here is what I've tried so far and it does not work.
What is the right way to get this working so that I can capture the
dbms
output to a variable?

require "rubygems"
require "ruby-plsql"

sql = s = IO.read('my_sql_stub.sql')
plsql.connection = OCI8.new("user","pass","db")
plsql.execute(sql)

Thanks in advance for your help.

[Raimonds Simanovskis]

You can execute just one SQL statement or one PL/SQL block with
plsql.execute
So you need to split your input file in separate statements - so if
these are just SQL statements then you can probably split by ';' but
if they are PL/SQL blocks then probably you need to use '/' as
separator between different blocks.

Probably this script could work:

require "rubygems"
require "ruby-plsql"

plsql.connection = OCI8.new("user","pass","db")

plsql.dbms_output_stream = StringIO.new

sql = File.read('my_sql_stub.sql')
sql.split(';').each do {|s| plsql.execute(s)}
# or you can try to use this if line with single '/' is used as PL/SQL
blocks separator
# sql.split(/^\s*\/\s*$/).each do {|s| plsql.execute(s)}

# get results of DBMS_OUTPUT
puts plsql.dbms_output_stream.string

[bk]

Thank you very much.

On Feb 26, 12:41 pm, Raimonds Simanovskis

[bk]

I'm a newbie with plsql & ruby your help is greatly appreciated.
Here is my actual sql file, do you think the code you recommended
would work with this?

DECLARE
i_fin_inst_id NUMBER;
i_adv_tax_id VARCHAR2(9);
i_ssn_tin VARCHAR2(23);
i_user_type NUMBER;
i_port_id NUMBER;

more data tyles declared

BEGIN
i_fin_inst_id := 16;
i_adv_tax_id := '123234234';
i_ssn_tin := '232323234';
i_port_id := -1;
i_log_flag := 'Y';
i_flag_arr := sone_string_arr();
i_flag_arr.extend(60);
i_flag_arr(1) := 'Y';
i_flag_arr(2) := 'Y';
i_flag_arr(3) := 'Y';
i_flag_arr(4) := 'N';
i_flag_arr(5) := 'Y';
i_flag_arr(6) := 'N';
i_flag_arr(7) := 'N';
i_flag_arr(8) := 'Y';
i_flag_arr(9) := 'N';

plenty more variables here

somePackage_pkg.someotherPpkg (
o_segment_list => o_segment_list,
o_output_rows => o_header_row,
o_fi_rpt_pkg_disc => o_fi_rpt_pkg_disc,
o_period => o_period,
o_execution_method => o_execution_method,
i_fin_inst_id => i_fin_inst_id,
i_adv_tax_id => i_adv_tax_id,
i_ssn_tin => i_ssn_tin,
i_user_type => i_user_type,
i_port_id => i_port_id);

display_row_data_csv(o_header_row);
dbms_output.put_line('========================');
display_row_data_csv(o_fi_rpt_pkg_disc);

Thanks
BK

On Feb 26, 12:41 pm, Raimonds Simanovskis
<raimonds.simanovs...@gmail.com> wrote:

[Raimonds Simanovskis]

If you want to execute just one
DECLARE
...
BEGIN
...
END;

block then you should be able to do this just with one plsql.execute
call. But why do you want to do that from Ruby with ruby-plsql? In
your case if you want to use more Ruby and less PL/SQL then you can
directly call

plsql.somePackage_pkg.someotherPpkg(...)

and pass necessary parameters and handle results in Ruby. Otherwise
just stick with sqlplus for executing large PL/SQL code blocks :)

Raimonds

[bk]

I'm trying to write some tests using the ruby watir library and wanted
to include some unit tests with it and that is the only reason i
wanted to run the plsql using ruby, I will try what you've
recommended. Thanks for your help!!

On Feb 28, 3:37 am, Raimonds Simanovskis