When i try connect to database through ruby-plsql, then getting error: java.sql.SQLDataException: ORA-01882: timezone region not found
ruby-plsql or Oracle TimeZone error. :(

A problem exists in plsql\schema.rb
If the database object it tries to load is has compilation errors, instead of raising the ArgumentError it is raising a different exception. I've traced the problem to line # 246 of schema.rb. For whatever reason when the query is executed against my db it does not return any results that the procedure is invalid (I executed the sql to select the errors and there are no results - although TOAD shows me it's invalid). Since the results are empty, the unless statement forces it to return 'nil', which then tries to get appended to the ArgumentError message string on line 209, which throws an exception.

I've tried the following in both my 1.9.1 irb and jirb
irb(main):001:0> "test" << nil
TypeError: can't convert nil into String
from (irb):1
from C:/Ruby191/bin/irb:12:in `

Let me know if you want me to fork and fix it

Here is my stack trace:
can't convert nil into String
c:/java/jruby-1.5.1/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.3/lib/plsql/schema.rb:209:in find_database_object' c:/java/jruby-1.5.1/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.3/lib/plsql/schema.rb:179:inmethod_missing'

We have two databases in use and often have to run stored procedures in each. This means we change connections frequently.
Since upgrading to 11g, we have noticed a delay (2-3 seconds) in switching connections. This traces to the SQL (in procedure.rb, line 15, in the 'find' method:

SELECT o.owner, o.object_name
FROM all_synonyms s, all_objects o
WHERE s.owner IN (:owner, 'PUBLIC')
AND s.synonym_name = :synonym_name
AND o.owner = s.table_owner
AND o.object_name = s.table_name
AND o.object_type IN ('PROCEDURE','FUNCTION')
ORDER BY DECODE(s.owner, 'PUBLIC', 1, 0)

which (in our case at least) can be made to perform better by changing to:

        SELECT o.owner,  o.object_name
        FROM all_synonyms s inner join all_objects o
        on o.object_name = s.table_name
        WHERE s.owner IN (:owner, 'PUBLIC')
          AND s.synonym_name = :synonym_name
          AND o.owner = s.table_owner
          AND o.object_type IN ('PROCEDURE','FUNCTION')
          ORDER BY DECODE(s.owner, 'PUBLIC', 1, 0)

(the change is to use the 'inner join' syntax, which 11g seems to like better).

We are using version 0.3.1 of ruby_plsql. I haven't checked later versions

That issue emerged while testing a change in #98.

Java::JavaSql::SQLException: Could not commit with auto-commit set on
oracle.jdbc.driver.PhysicalConnection.commit(oracle/jdbc/driver/PhysicalConnection.java:4439)
oracle.jdbc.driver.PhysicalConnection.commit(oracle/jdbc/driver/PhysicalConnection.java:4486)
java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:606)
RUBY.commit(/home/user/RubymineProjects/ruby-plsql/spec/../lib/plsql/jdbc_connection.rb:65)
RUBY.(root)(/home/user/RubymineProjects/ruby-plsql/spec/plsql/procedure_spec.rb:1848)
ruby.__dash_e__.(root)(-e:1)
ruby.__dash_e__.(root)(ruby/-e:1)

Two observations so far:

• JRuby connections are currently auto-commit by default (that's JDBC default)
• commit is failing when invoked in auto-commit mode. Maybe that's OK, yet it's good to see what's the behavior in case of MRI ruby and ruby-oci8. As per JDBC docs commit, rollback, setAutocommit are not supported in autocommit=true mode.

Hi,

I use an 'Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production'. Ruby v2.0.0 - v2.1.4 and ruby-plsql v0.5.2.

I try to insert a timestamp in a table but nothing happens. Like this:

create table tmp_time_tbl (
  time_stamp timestamp;
);

and then in ruby

require 'ruby-plsql'

# connect to oracle db

ts = { time_stamp: Time.now }

tmp_time_tbl.insert ts

It returns nil and nothing is inserted. When I insert a value by sqlplus, then I can fetch it:

pry [v2.1.4]  >> ap plsql.tmp_time_tbl.first 
{
    :time_stamp => 2014-11-09 08:36:40 +0100
}
=> nil

ap is awesome_print.

The problem still exists, when I try it by using pl/sql functions. If you are interested, I can post a example too.

Here is my ruby code:

    interface_lines_rec = {:ibtxn_interface_line_id => 123,
                           :ib_instance_id => 10004389,
                           :serial_number =>  'F40164',
                           :inventory_item_id => 158041,
                           :order_line_id => 1234
    }
    p plsql.nics_ibtxn_util.update_ib_instance(interface_lines_rec)

It's simply calling a procedure with one input parameter, which is a PL/SQL record:

 PROCEDURE update_ib_instance(p_ibtxn_interface_lines_rec   IN     t_ibtxn_interface_lines_rec)

The PL/SQL record happens to be a huge one....

TYPE t_ibtxn_interface_lines_rec IS RECORD (
     ibtxn_interface_line_id        NUMBER,
     ibtxn_interface_id             NUMBER,
     order_line_id                  NUMBER,
     order_date                     NUMBER,
     ship_from_org_id               NUMBER,
     sold_to_party_id               NUMBER,
     sold_to_cust_account_id        NUMBER,
     ship_to_org_id                 NUMBER,
     ship_to_org_party_id           NUMBER,
     ship_to_org_cust_account_id    NUMBER,
     ship_to_org_party_site_id      NUMBER,
     bill_to_org_id                 NUMBER,
     bill_to_org_party_id           NUMBER,
     bill_to_org_cust_account_id    NUMBER,
     installed_at_party_id          NUMBER,
     installed_at_party_site_id     NUMBER,
     sold_to_contact_party_id       NUMBER,
     ship_to_contact_party_id       NUMBER,
     bill_to_contact_party_id       NUMBER,
     inventory_item_id              NUMBER,
     part_number                    NUMBER,
     serialized_item                NUMBER,
     serial_number                  NUMBER,
     plan_level                     NUMBER,
     sort_order                     NUMBER,
     gen_object_id                  NUMBER,
     mtl_completion_date            NUMBER,
     part_of_order                  NUMBER,
     pto_order                      NUMBER,
     bom_explosion                  NUMBER,
     serial_number_assigned         NUMBER,
     parent_ibtxn_interface_line_id NUMBER,
     ib_instance_id                 NUMBER,
     ib_instance_status             NUMBER,
     ib_active_start_date           NUMBER,
     ib_active_end_date             NUMBER,
     ib_last_oe_order_line_id       NUMBER,
     ib_location_id                 NUMBER,
     ib_install_location_id         NUMBER,
     ib_owner_party_id              NUMBER,
     ib_owner_party_account_id      NUMBER,
     ib_sn_correct                  NUMBER,
     ib_rel_correct                 NUMBER,
     new_ib_instance_id             NUMBER,
     new_ib_sn_corrected            NUMBER,
     new_ib_rel_created             NUMBER,
     new_ib_contacts_created        NUMBER,
     ib_instance_fix_status         NUMBER,
     ib_instance_error_message      NUMBER,
     parent_order_line_id           NUMBER);

When I run the ruby code, I will always get this error message:

     OCIError:
       ORA-01008: not all variables bound
     # stmt.c:243:in oci8lib_240.bundle
     # ./spec/ibic.rb:125:in `block (2 levels) in <top (required)>'

It is obviously a bug. When I reduce the size of the PL/SQL, the error will not show up. Any idea where I can start debugging? I've spent days on this bug but no progress yet.

Dear Ray!

Following - guess pretty rare problem.
ruby_plsql executes procedures/functions always fully qualified SCHEMA.PACKAGE.PROCEDURE.
Even if it's a public synonym not the synonym is executed', e.g. I have synonym "exeme"
that points to "scott.foo" "scott.foo" is executed and not "exeme".

That leads to the rare problem, that we have if a user, has the same name like an oracle-standard package.

-- this case does not work, because it's called directly and there is a package that has the same schema name 'SDO' in MDSYS schema

connect scott/tiger

DECLARE
ret number;
BEGIN
ret := SDO.abctest.testproc(NULL);
END;
/

connect scott/tiger

-- here it works, because here we created a public synonym for abctest (create public synonym abctest for sdo.abctest)
DECLARE
ret number;
BEGIN
ret := abctest.testproc(NULL);
END;
/

So plsql-ruby fails with PLS-00302: component 'ABCTEST' must be declared - ORA-06550.

I (ruby-noob) worked around with removing the schema name (@call_sql in procedure_call) if the object was retrieved through a synonym (by using a global variable -> shame on me!).
It is just the question if direct execution of the synonym would have any other advantage.
Sometimes there are side-effects as it's implemented now (rights-management?) other than this I described. if you don't execute the public synonym directly.

Manfred

P.S.
plsql-ruby is really good work!

Timezone issue on Windows clients.

Issue 1

Windows uses different Tiemzone names and abbreviations.
This causes Ruby TZ env variable to make no impact on Windows, when non windows-compliant TZname is specified.

The following code

Time.now.utc_offset / 60 / 60
ENV['TZ']='Europe/Warsaw'
Time.now.utc_offset / 60 / 60
ENV['TZ']='Europe/London'
Time.now.utc_offset / 60 / 60

Gives on my Windows 7 x64 home (Polish).

irb(main):001:0> Time.now.zone
=> "GMT (czas letni)"
irb(main):002:0> Time.now.utc_offset / 60 / 60
=> 1
irb(main):003:0> ENV['TZ']='Europe/Warsaw'
=> "Europe/Warsaw"
irb(main):004:0> Time.now.utc_offset / 60 / 60
=> 1
irb(main):005:0> ENV['TZ']='Europe/London'
=> "Europe/London"
irb(main):006:0> Time.now.utc_offset / 60 / 60
=> 1
irb(main):007:0>

Gives on my Ubunto 14.10 x64 VM (English).

1.9.3-p547 :001 > Time.now.zone
=> "IST"
1.9.3-p547 :002 > Time.now.utc_offset / 60 / 60
=> 1
1.9.3-p547 :003 > ENV['TZ']='Europe/Warsaw'
=> "Europe/Warsaw"
1.9.3-p547 :004 > Time.now.utc_offset / 60 / 60
=> 2
1.9.3-p547 :005 > ENV['TZ']='Europe/London'
=> "Europe/London"
1.9.3-p547 :006 > Time.now.utc_offset / 60 / 60
=> 1
1.9.3-p547 :007 >

The current solution presented in spec_helper (setting TZ variable) makes the tests fail on Windows machines, due to the above.

Issue 2

Oracle Client by default sets the timezone to a static offset rather than the local named timezone.
This means that you cannot rely on the default settings of Oracle Client to have proper timezone when working in a daylight saving-enabled timezone.

Seems that the existing solution of using TZ setting combined with ALTER SESSION SET_TIMEZONE = ENV['TZ'], was to mitigate the issue with Oracle default session timezone.

Instead of using TZ variable, it seems more appropriate to set the timezone through Oracle ENV variable: ORA_SDTZ
ENV['ORA_SDTZ'] = 'Europe/London'

• This way resolve the issue of Oracle Client having a fixed offset timezone (without daylight saving).
• This way we avoid issue of discrepancies in timezone names between Windows and other systems.

The value set as ORA_SDTZ should be the Timezone of the local machine.

Ruby handles properly the Windows timezones, so no need to set the TZ variable unless you need to use different timezone than the local one.

Setting ENV['ORA_SDTZ']='OS_TZ' will give the fixed offset timezone, so it should not be used if your local timezone the daylight saving kind. This gives identical results as not setting the ORA_SDTZ at all (form what I understood).

According to Oracle Documentation!
The default client session time zone is an offset even if the client operating system uses a named time zone. If you want the default session time zone to use a named time zone, then set the ORA_SDTZ variable in the client environment to an Oracle time zone region name.

While adding support for additional PLSQL datatypes and refactoring the tests I've found a bug but don't know how to solve it.
On Ruby 1.9.3 the below scenario works fine.
On JRuby 1.7.12, the scenario returns 0 for all cases instead of NULL / nil.
Can you help me investigate/fix it?

  describe "PLS_INTEGER/SIMPLE_INTEGER shoudl be nullable" do

    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
          CREATE OR REPLACE FUNCTION test_pls_f ( p_num PLS_INTEGER ) RETURN PLS_INTEGER IS
          BEGIN
            RETURN p_num;
          END;
      SQL
      plsql.execute <<-SQL
          CREATE OR REPLACE FUNCTION test_bin_f ( p_num BINARY_INTEGER ) RETURN BINARY_INTEGER IS
          BEGIN
            RETURN p_num;
          END;
      SQL
      plsql.execute <<-SQL
          CREATE OR REPLACE PROCEDURE test_pls_p ( p_num IN OUT PLS_INTEGER ) IS
          BEGIN
            NULL;
          END;
      SQL
      plsql.execute <<-SQL
          CREATE OR REPLACE PROCEDURE test_bin_p ( p_num IN OUT BINARY_INTEGER ) IS
          BEGIN
            NULL;
          END;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_pls_f"
      plsql.execute "DROP FUNCTION test_bin_f"
      plsql.execute "DROP PROCEDURE test_pls_p"
      plsql.execute "DROP PROCEDURE test_bin_p"
      plsql.logoff
    end

    it 'should return null for a function call with NULL PLS_INTEGER param' do
      expect(plsql.test_pls_f(nil)).to be_nil
    end

    it 'should return null for a function call with NULL BINARY_INTEGER param' do
      expect(plsql.test_bin_f(nil)).to be_nil
    end
    it 'should return null for a procedure call with NULL PLS_INTEGER param' do
      expect(plsql.test_pls_p(nil)[:p_num]).to be_nil
    end
    it 'should return null for a procedure call with NULL BINARY_INTEGER param' do
      expect(plsql.test_bin_p(nil)[:p_num]).to be_nil
    end

  end

I'm chasing up an error on an external system, the error is "expect Numeric but String", this is covered in an earlier issue. However I'm looking to try and debug exactly what's erroring in the external system.

Is there a way to output the exact SQL parsed by ruby-plsql so I can chase this problem down in my oracle tool?

Hello,
I'm trying to fetch from a cursor which is the out parameter of an Oracle procedure.
The setup:
create or replace procedure kkt (po out sys_refcursor) is
begin
open po for select 1 from dual;
end kkt;
/

irb(main):031:0> r = plsql.kkt(nil)
=> {:po=>#<PLSQL::OCIConnection::Cursor:0x341bf08 @raw_cursor=#OCI8::Cursor:0x341c070, @connection=#<PLSQL::OCIConnection:0x345f228 @raw_driver=:oci, @database_version=[10, 2], @activerecord_class=nil, @raw_connection=#OCI8:XXX>>}

irb(main):032:0> c = r[:po]
=> #<PLSQL::OCIConnection::Cursor:0x341bf08 @raw_cursor=#OCI8::Cursor:0x341c070, @connection=#<PLSQL::OCIConnection:0x345f228 @raw_driver=:oci, @database_version=[10, 2], @activerecord_class=nil, @raw_connection=#OCI8:XXX>>

irb(main):033:0> c.fetch
TypeError: invalid argument NilClass (expect OCI8)
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/oci_connection.rb:105:in fetch' from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/oci_connection.rb:105:infetch'
from (irb):33

Shouldn't I be able to fetch from the c cursor (PLSQL::OCIConnection::Cursor)?

Thank you,
Watercase

Discrepancy in type mapping.
On Ruby the INTEGER PLSQL datatype is converted to Bignum
(result of a function call returning INTEGER).
On JRuby however this is returned as a Fixnum.

Not sure if it's a bug or a feature, but it's an undocumented discrepancy.

My Config:
JRuby 1.5
ojdbc14.jar thin client selected in connect string

Test case producing error provided below:

Oracle Setup
drop table typed_ref_cursor_table ;
create table typed_ref_cursor_table
( col1 VARCHAR2(10), col2 NUMBER ) ;

insert into typed_ref_cursor_table values ('first row', 1 ) ;
insert into typed_ref_cursor_table values ('second row', 2 ) ;
insert into typed_ref_cursor_table values ('last row', 99 ) ;
commit ;

create or replace package typed_ref_cursor_test as
  type test_rec is record ( col1 VARCHAR2(10), col2 NUMBER ) ;
  type test_rec_ref is ref cursor return test_rec ;

  function get_all return test_rec_ref ;
end typed_ref_cursor_test ;
/

create or replace package body typed_ref_cursor_test as
  function get_all return test_rec_ref is
    rc test_rec_ref ;
  begin
    open rc for select * from typed_ref_cursor_table ;
    return rc ;
  end get_all ;
end typed_ref_cursor_test ;
/
sho err

grant all on typed_ref_cursor_table to public ;
grant all on typed_ref_cursor_test to public ;

create or replace public synonym typed_ref_cursor_test for typed_ref_cursor_test ;
create or replace public synonym typed_ref_cursor_table for typed_ref_cursor_table ;

Ruby RSpec code snippet:
describe "test" do
it "should have 3 rows in table" do # this passes, as expected
row_count = plsql.typed_ref_cursor_table.count ;
row_count.should == 3
end

  it "should return 3 rows" do
    plsql.typed_ref_cursor_test.get_all do |cursor|
      rc_row = cursor.fetch
      puts "Row from curosr: #{rc_row.inspect}"
    end
  end
end

Results:
Test 1 passes

Test 2 dumps this error:
NoMethodError in 'test should return 3 rows'
undefined method `[]' for nil:NilClass
./spec/test_rc.rb:13

I think the Oracle data-type field has changed from XMLTYPE -> OPAQUE/XMLTYPE.

I've got a fix for the JDBC version of this library. It, however, does add a dependency to Oracle xdb/xmlparsev2 jars (which are even more annoying to get ahold of than the JDBC jar).

I'm building off my fork for the moment. Is it something you want in your project (happy to refactor).

Here's the main commit. AledLewis@14added

When passing a table to a procedure, the table is received as empty when using ruby-oci8 v2.0.6.
Below is example PL/SQL and Ruby.

--BEGIN PL/SQL
--CREATE TYPES

create or replace
TYPE example_o AS OBJECT
(aa INTEGER, bb INTEGER);

create or replace
TYPE example_t IS TABLE OF example_o;

--CREATE PACKAGES
create or replace
PACKAGE EXAMPLE_PKG
IS

PROCEDURE oci_fail_example(example_table_in example_t); 

END;

create or replace
PACKAGE BODY EXAMPLE_PKG
IS

PROCEDURE oci_fail_example(
    example_table_in example_t )
IS

BEGIN
dbms_output.put_line(example_table_in(1).aa);

  -- ORA-06531: Reference to uninitialized collection results from the below
  dbms_output.put_line(example_table_in(1).bb);
  dbms_output.put_line('aaa');
END oci_fail_example;

END;

--END PL/SQL

BEGIN TEST SPEC EXAMPLE

require File.expand_path('spec/spec_helper')
plsql.dbms_output_stream = STDOUT

describe "The function oci_fail_example()" do

testdate = Time.local(2011,03,01,00)

test_table = [{ :aa => 1,
:bb => -1},
{:aa => 2,
:bb => -1},
{:aa => 3,
:bb => -1}
]

it "1. " do

plsql.example_pkg.oci_fail_example(test_table)

end

end

END TEST SPEC EXAMPLE

The reason for failure seems to be that ruby-oci8 2.0.6 does not work the same as 2.0.4.
The failure is experienced on 2.0.6:

F

Failures:

1. The function oci_fail_example() 1.
   Failure/Error: plsql.example_pkg.oci_fail_example(test_table)
   OCIError:
   ORA-06531: Reference to uninitialized collection
   ORA-06512: at "DTE.EXAMPLE_PKG", line 8
   ORA-06512: at line 5

   stmt.c:312:in oci8lib_191.so

   ./spec/test_spec.rb:23:in `block (2 levels) in <top (required)>'

Finished in 0.07007 seconds
1 example, 1 failure

Failed examples:

rspec ./spec/test_spec.rb:20 # The function oci_fail_example() 1.
false
Failing tests!

If I rollback to 2.0.4, then it works as expected.

A possible short term fix is to depend on =2.0.4, not >=2.0.4

In previous versions of oci8 (pre 2.0.3) and plsql (pre 0.4.1), I could call something

plsql.schema.my_pkg.my_fn(phone_num)

and have it run correctly. Under oci8 2.0.3/plsql 0.4.1, I get the exception:

"expect Numeric but String"

I've been hunting through the code (both yours and the oci8 adapter) to try to find where this changed between versions and so far am having no luck. I really liked the old version where I didn't have to be explicit and the adapters picked up and parsed to the type that it needed. Any idea on where to look for this or who's code is at fault?

Hi ,

I try to use plsql from several threads but it kills the ruby runtime with an error can not write to memory ...

Is threading supportet by plsql and if so what I am doing wrong ?

My environment:
Windows XP ruby 1.9.2
ruby-oci8 2.04
ruby-plsql 0.4.4

some code:
....
threads = []

Benchmark.bm do|b|
b.report("mrc") do
nthread.times { |i|
threads[i] = Thread.new {
plsql.connection = OCI8.new(user,password,db)
10_000.times { plsql.one_employee_mrc(1); }
}
}
threads.each { |t| t.join }
end
threads.each { |t| t.join }
end

end
...

I hesitate to call this a bug, but it's more of an issue than a new feature. The ojdbc jar for jdbc connections shouldn't be requiring the jar directly. Doing this removes a number of options that should be afforded to the developer:

1. Developers shouldn't be forced to use a particular version of the jar.
2. It shouldn't be required to be named exactly ojdbc14
3. It shouldn't be necessary to place the jar in specific locations.

I would suggest that the documentation simply state that developers must require 'ojdbc14.jar' themselves, prior to requiring ruby_plsql. This would allow the developers to name the jar as they see fit; place it where they see fit; and use the version that they wish (ojdbc5 or 6 for instance).

I fetch a cursor like this:

plsql.connection = OCI8.new foo, bar, baz
records = nil
plsql.foo_pkg.accountList { |cursor| records = cursor.fetch_all }

Then i get the error message

stmt.c:230:in oci8lib_210.so: ORA-00904: "P"."OBJECT_TYPE": invalid identifier (OCIError)

I use

ruby 2.1.3p242
ruby-oci8 (2.1.7)
ruby-plsql (0.5.1)

When I use

ruby 2.1.1p76
ruby-oci8 (2.1.7)
ruby-plsql (0.5.0)

everything works fine.

I am inserting records into a table using a PL/SQL procedure. For the date parameters, I am first setting a hash entry using the ruby date function like this:
create_rec= { ..., :my_date => Date.new(2010,01,01), ... }

Then I pass them into my table insert procedure, which does the insert correctly.

I then want to compare my original hash to the one returned from the table directly, via the table select function

new_rec = plsql.my_table.first  # assuming only one record in the table (the one I just created)
new_rec.should == create_rec

And this fails because the dates appear to be different formats:
2)
... FAILED
expected: {..., :robprds_start_date=>#Date:4910395/2,0,2299161,...},
got: {..., :robprds_start_date=>Fri Jan 01 00:00:00 -0800 2010, ...} (using ==)

Is there a way to control how dates are returned by the table support in ruby-plsql?

ruby-plsql now requires ruby-oci8 2,buy active_record oracle adapter rails 2 only use oci1,

now how can i use your plsql tool?

OCI error (missing parenthensis) during call to procedure with array or table parameter with nvarchar2 or nchar column.

How to reproduce:

PL\SQL code:

create or replace package pl_test as
type t_test_list is table of nvarchar2(250);
function test_in_table(
    lst     t_test_list
    ) return varchar2;
end;
/
create or replace package body pl_test as
function test_in_table(
    lst     t_test_list
    ) return varchar2
    as
    tmp1 nvarchar2(2000);
    x pls_integer;
begin
    if lst.count=0 then
        return N'';
    end if;
    
    x := lst.first;
    while x is not null loop
        tmp1 := tmp1 || x || ' ' || lst(x) || ',';
        x := lst.next(x);
    end loop;
    return tmp1;
end;
end;
/

Ruby:

plsql.pl_test.test_in_table(...)

Result SQL create table with invalid column definition (oracle not supported CHAR or BYTE in nvarchar2 and nchar columns).

Also invalid column data size specified (500 in temp table via 250 in type declaration).

CREATE GLOBAL TEMPORARY TABLE ruby_224962_77799_1_1 (
lst  NVARCHAR2(500 CHAR),
i__ NUMBER(38)
) ON COMMIT PRESERVE ROWS

Proposed patch

diff --git "a/C:\\Users\\oleh\\AppData\\Local\\Temp\\procedure_HEAD.rb" "b/D:\\projects\\Ruby\\gems\\ruby-plsql\\lib\\plsql\\procedure.rb"
index ceec53c..2921554 100644
--- "a/C:\\Users\\oleh\\AppData\\Local\\Temp\\procedure_HEAD.rb"
+++ "b/D:\\projects\\Ruby\\gems\\ruby-plsql\\lib\\plsql\\procedure.rb"
@@ -52,12 +52,18 @@ module PLSQL
       when 'NUMBER'
         precision, scale = metadata[:data_precision], metadata[:data_scale]
         "NUMBER#{precision ? "(#{precision}#{scale ? ",#{scale}": ""})" : ""}"
-      when 'VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR'
-        length = metadata[:data_length]
+      when 'VARCHAR2', 'CHAR'
+        length = metadata[:char_used] == 'C' ? metadata[:char_length] : metadata[:data_length]
         if length && (char_used = metadata[:char_used])
           length = "#{length} #{char_used == 'C' ? 'CHAR' : 'BYTE'}"
         end
         "#{metadata[:data_type]}#{length ? "(#{length})": ""}"
+      when 'NVARCHAR2', 'NCHAR'
+        length = metadata[:char_length]
+        if length
+          length = "#{length}"
+        end
+        "#{metadata[:data_type]}#{length ? "(#{length})": ""}"
       when 'PL/SQL TABLE', 'TABLE', 'VARRAY', 'OBJECT'
         metadata[:sql_type_name]
       else
@@ -87,7 +93,7 @@ module PLSQL
       @schema.select_all(
         "SELECT #{subprogram_id_column}, object_name, TO_NUMBER(overload), argument_name, position, data_level,
               data_type, in_out, data_length, data_precision, data_scale, char_used,
-              type_owner, type_name, type_subname
+              char_length, type_owner, type_name, type_subname
         FROM all_arguments
         WHERE object_id = :object_id
         AND owner = :owner
@@ -98,7 +104,7 @@ module PLSQL
 
         subprogram_id, object_name, overload, argument_name, position, data_level,
             data_type, in_out, data_length, data_precision, data_scale, char_used,
-            type_owner, type_name, type_subname = r
+            char_length, type_owner, type_name, type_subname = r
 
         @overloaded ||= !overload.nil?
         # if not overloaded then store arguments at key 0
@@ -134,6 +140,7 @@ module PLSQL
           :data_precision => data_precision && data_precision.to_i,
           :data_scale => data_scale && data_scale.to_i,
           :char_used => char_used,
+          :char_length => char_length && char_length.to_i,
           :type_owner => type_owner,
           :type_name => type_name,
           :type_subname => type_subname, 

Best regards, Oleh Mykytyuk

Hi Ray!
I have an issue. I think it it more of an issue or RSpec.configure.
The thing is that the current transactions and savepoints handling doesn't work well with nested "describe" blocks.
I find using nested describe very useful.
Here is a test to show the issue.
Pleas let me know if there is a workaround for that.

require File.dirname(FILE) + '/../spec_helper'

begin
plsql.execute("DROP TABLE t_tmp_transaction_test")
rescue
end
plsql.execute("CREATE TABLE t_tmp_transaction_test (block_name VARCHAR2(100))")

def mark_transaction(bn)
plsql.t_tmp_transaction_test.insert(:block_name=>bn.last)
end

describe "Spec - Transaction Handling. Main Block" do

transaction_points = []

before(:all) do
mark_transaction(transaction_points << "Before All - Main block")
puts transaction_points.last
end

before(:each) do
mark_transaction(transaction_points << " Before Each - Main block")
puts transaction_points.last
end

[:first, :second].each do |block_no|
describe "Nested describe block - #{block_no.to_s}." do

  before(:all) do
    mark_transaction( transaction_points  << "  Before All - #{block_no.to_s} block" )
    puts transaction_points.last
  end

  before(:each) do
    mark_transaction( transaction_points  << "   Before Each - #{block_no.to_s} block" )
    puts transaction_points.last
  end

  [:First, :Second].each do |test_no|
    it "#{test_no.to_s} Test" do
      mark_transaction( transaction_points  << "    Running #{test_no.to_s} Unit Test - #{block_no.to_s} block" )
      puts transaction_points.last
      result = plsql.t_tmp_transaction_test.all.map{|row| row.values}.flatten
      result.should =~ transaction_points
      transaction_points.pop
    end
  end

  after(:each) do
    puts "   After Each - #{block_no.to_s} block"
    transaction_points.pop
  end

  after(:all) do
    puts "  After All - #{block_no.to_s} block"
    transaction_points.pop
  end
end

end

[:third].each do |block_no|
describe "Nested describe block - #{block_no.to_s}." do

  before(:all) do
    mark_transaction( transaction_points  << "  Before All - #{block_no.to_s} block" )
    puts transaction_points.last
  end

  before(:each) do
    mark_transaction( transaction_points  << "   Before Each - #{block_no.to_s} block" )
    puts transaction_points.last
  end

  [:First, :Second].each do |test_no|
    it "#{test_no.to_s} Test" do
      mark_transaction( transaction_points  << "    Running #{test_no.to_s} Unit Test - #{block_no.to_s} block" )
      puts transaction_points.last
      result = plsql.t_tmp_transaction_test.all.map{|row| row.values}.flatten
      result.should =~ transaction_points
      transaction_points.pop
    end
  end

  after(:each) do
    puts "   After Each - #{block_no.to_s} block"
    transaction_points.pop
  end

  after(:all) do
    puts "  After All - #{block_no.to_s} block"
    transaction_points.pop
  end

end

end

it "Test outside the nested block" do
result = plsql.t_tmp_transaction_test.all.map{|row| row.values}.flatten
result.should =~ transaction_points
end

after(:each) do
puts " After Each - Main block"
transaction_points.pop
end

after(:all) do
puts "After All - Main block"
transaction_points.pop
end

end

Hi,

it's me again :)

Another problem on the same project, tests broke when some of the other devs ran them on Oracle 11g, due to ORA-00910. I ran the trace in procedure.rb ensure_tmp_tables_created:

188 def ensure_tmp_tables_created(overload) #:nodoc:
189 return if @tmp_tables_created.nil? || @tmp_tables_created[overload]
190 @tmp_table_names[overload] && @tmp_table_names[overload].each do 
|table_name, argument_metadata|
191 sql = "CREATE GLOBAL TEMPORARY TABLE #{table_name} (\n"
192 element_metadata = argument_metadata[:element]
193 case element_metadata[:data_type]
194 when 'PL/SQL RECORD'
195 fields_metadata = element_metadata[:fields]
196 fields_sorted_by_position = fields_metadata.keys.sort_by{|k| 
fields_metadata[k][:position]}
197 sql << fields_sorted_by_position.map do |field|
198 metadata = fields_metadata[field]
199 "#{field} #{ProcedureCommon.type_to_sql(metadata)}"
200 end.join(",\n")
201 else
202 sql << "element #{ProcedureCommon.type_to_sql(element_metadata)}"
203 end
204 sql << ",\ni__ NUMBER(38)\n"
205 sql << ") ON COMMIT PRESERVE ROWS\n"
206 sql_block = "DECLARE\nPRAGMA AUTONOMOUS_TRANSACTION;\nBEGIN\nEXECUTE 
IMMEDIATE :sql;\nEND;\n"
207
208 puts "SQL_BLOCK: >>#{sql_block}<<"
209 puts "SQL: >>#{sql}<<"
210
211 @schema.execute sql_block, sql
212 end
213 @tmp_tables_created[overload] = true
214 end

and got this out:

SQL_BLOCK: >>DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE :sql;
END;
<<

SQL: >>CREATE GLOBAL TEMPORARY TABLE ruby_15391171_110739_7_0 (
id NUMBER(38),
statement_id NUMBER(38),
fee_configuration_detail_id NUMBER(38),
statement_target_id NUMBER(38),
statement_target_type VARCHAR2(1020 CHAR),
fee_type_logid VARCHAR2(100 CHAR),
amount NUMBER(38),
currency VARCHAR2(12 CHAR),
kind VARCHAR2(160 CHAR),
comments VARCHAR2(16000 CHAR),
due_date DATE,
invoice_id NUMBER(38),
created_at DATE,
updated_at DATE,
i__ NUMBER(38)
) ON COMMIT PRESERVE ROWS
<<

In truth, comments is a 4000 long varchar2 column and statement_target_type is 255 long varchar2 column. Apparently all_arguments data_length column returns 16000 when NLS_CHARACTERSET is AL32UTF8 and "correctly" returns 4000 when WE8MSWIN1252, also the AL32UTF8 server is Oracle Enterprise 11.2.0.1.0 64 bit and WE8MSWIN1252 server is Oracle 10.2.0.1 XE.

Obvious workaround in this case would be to use char_length from all_arguments instead of data_length, but my ORA FU is not strong enough to predict what the consequences of that would be.

Possibly, the select from all_arguments in get_argument_metadata can be rewritten to return char_length when char_used='C' else data_length.

Clients in both cases are running:

Rails 3.2.8
MRI ruby 1.9.2p180
activerecord-oracle_enhanced-adapter (1.4.1)
ruby-oci8 (2.1.2)
ruby-plsql (0.4.4)

Borna

Hi,
I'm having an issue passing NULL to object parameters in object constructors using ruby-plsql syntax.
More specifically, I can't pass a null value to sdo_point when creating an sdo_geometry object.

Here's the test case:

describe "sdo_geometry" do
    before(:all) do
        plsql.execute <<-SQL
            CREATE OR REPLACE FUNCTION bind_sdo_geometry(
                p_sdo_geometry sdo_geometry


            ) RETURN VARCHAR2 IS
            BEGIN 

            RETURN 'bound_sdo_geometry';

            END bind_sdo_geometry;
        SQL
    end

    after(:all) do 
        plsql.execute "DROP FUNCTION bind_sdo_geometry"
    end

    describe "constructor" do
        context "null sdo_point" do 
            let(:sdo_geometry) do
                 {
                sdo_gtype:2003 ,
                sdo_srid:4230,
                sdo_point:NULL,
                sdo_elem_info:[1,1003,1],
                sdo_ordinates:[5,1, 8,1, 8,6, 5,7, 5,1]
                }
            end

            it "should not error" do
                pending("Having a null sdo_point should be supported")
                expect(plsql.bind_sdo_geometry(sdo_geometry)).not_to raise_error
            end

            it "should function the same as writing the pl/sql manually" do

                result_from_sql = plsql.select(:first, <<-SQL 
                  SELECT bind_sdo_geometry(
                    sdo_geometry(
                        sdo_gtype=>2003 ,
                        sdo_srid=>4230,
                        sdo_point=>NULL,
                        sdo_elem_info=>sdo_elem_info_array(1,1003,1),
                        sdo_ordinates=> sdo_ordinate_array(5,1, 8,1, 8,6, 5,7, 5,1)
                    )
                  ) result
                  FROM dual
                  SQL
                )

                expect(result_from_sql[:result]).to eq('bound_sdo_geometry')
                
            end
        end
           
        context "all parameters" do 
            let(:sdo_geometry) do
                 {
                sdo_gtype:2003 ,
                sdo_srid:4230,
                sdo_point:{x:1,y:3,z:0},
                sdo_elem_info:[1,1003,1],
                sdo_ordinates:[5,1, 8,1, 8,6, 5,7, 5,1]
                }
            end

            it "should not error" do
                expect{plsql.bind_sdo_geometry(sdo_geometry)}.not_to raise_error
            end
        end        
    end
end

Could you give me some pointers as to how to resolve this? We're keen to do some charaterisation tests for sdo_geom as we're (finally) upgrading to 12c and have hit some bugs in that package in the past.

Cheers,

Aled

considering pl/sql package like this

plsql.execute <<-SQL
    create or replace package test_package
    as
        procedure test1(a number := null, out_curs OUT sys_refCursor);
    end;

    create or replace package body test_package
    as
        procedure test1(a number := null, out_curs OUT sys_refCursor)
        as
            null;
        end;
    end;
SQL

when I make a call

plsql.test_package.test

It fails with "wrong number or types of arguments in call"
The reason is that generated sql doesn't contain OUT param.
but if I do call like this

plsql.test_package.test(:a=>1)
plsql.test_package.test({})

it works.

as far as I understand in case of call 'plsql.test_package.test' arguments will be added only if they are all OUT. but they are not all OUT

Procedure_call.rb
197: if args.size < argument_count &&
                      (args.size...argument_count).all?{|i| arguments[argument_list[i]][:in_out] == 'OUT'}

I'll fix this

There is a minor issue with the regex used to determine if a package variable is supported or not. VARCHAR2 is definitely supported. However, the space between the type and size is causing the variable to fall through a block in the Variable.metadata method.

    def metadata(type_string)
      case type_string
      when /^(VARCHAR2|CHAR|NVARCHAR2|NCHAR)(\((\d+)[\s\w]*\))?$/
        {:data_type => $1, :data_length => $3.to_i, :in_out => 'IN/OUT'}

I would suggest adding some support for whitespace between the type and the size, maybe:

      when /^(VARCHAR2|CHAR|NVARCHAR2|NCHAR)(\s*)?(\((\d+)[\s\w]*\))?$/

A simple work around in meantime is to remove the spaces between the type and size, assuming you have the access required to the underlying DB objects.

I have the following table:

  create_table "fee_types", :id => false, :force => true do |t|
    t.string "logid",       :limit => 25
    t.string "fee_subject", :limit => 29
    t.text   "comment"
  end

  add_index "fee_types", ["logid"], :name => "index_fee_types_on_logid", :unique => true

Populated with simple enough data:

[143] pry(main)> FeeType.all
  FeeType Load (6.8ms)  SELECT "FEE_TYPES".* FROM "FEE_TYPES" 
=> [#<FeeType logid: "test_fixed", fee_subject: "Transaction", comment: "TEST - \"normal\" percentage fee">,
 #<FeeType logid: "test_percentage", fee_subject: "Transaction", comment: "TEST - \"normal\" percentage fee">]

And a simple function in a package fetching that data into an array:

create or replace package wb_types as

  type t_fee_types_ary is table of fee_types%rowtype index by pls_integer;

  function get_fee_types_ary return wb_types.t_fee_types_ary;

end wb_types;

/

create or replace package body wb_types as

  function get_fee_types_ary return wb_types.t_fee_types_ary is
    l_ary_fee_types  wb_types.t_fee_types_ary;

    cursor cur is 
      select * from fee_types;
  begin
    -- Visit SQL
    open cur;
    fetch cur bulk collect into l_ary_fee_types;  
    close cur;

    return l_ary_fee_types;
  end get_fee_types_ary;

end wb_types;

/

alter package wb_types compile package

/

The function works when executed from sql developer, but from ruby it always returns ORA-00904:

[144] pry(main)> plsql.wb_types.get_fee_types_ary
OCIError: ORA-00904: : invalid identifier
ORA-06512: at line 4
from stmt.c:253:in oci8lib_191.so

And the following temp table is left behind:

create_table "ruby_50365_188389_84470_0", :temporary => true, :id => false, :force => true do |t|
  t.integer "element", :precision => 38, :scale => 0
  t.integer "i__",     :precision => 38, :scale => 0
end

I'm using:

• Oracle 10g XE
• Rails 3.2.8
• MRI ruby 1.9.2p180
• activerecord-oracle_enhanced-adapter (1.4.1)
• ruby-oci8 (2.1.2)
• ruby-plsql (0.4.4)

The very same pattern works for another package & table in the same project (currently only 2 tables with this), I have tried changing the text field to string, adding a default ID column, changing names of methods etc, but to no luck.

As far as I can tell, only trial and error change that yielded different results is changing the cursor definition in the package (select from dual worked for example).

Any and all help would be greatly appreciated,

Borna

Hi.
I'd like to user ruby-plsql-spec on a database where XMType is hevily used (all API is bazsed on XMLType).
Unfortunatelly the type is unsupported right now.

Here is a test to do the check (used OCI8 dirver).

require_relative 'spec_helper'
describe "XMLType" do
before(:all) do
plsql.execute <<-SQL
CREATE OR REPLACE PACKAGE test_package IS
xml_variable XMLType;
END;
SQL
plsql.execute <<-SQL
CREATE OR REPLACE PACKAGE BODY test_package IS
END;
SQL
end
after(:all) do
plsql.execute "DROP PACKAGE test_package"
end
it "should set and get XMLType variable" do
plsql.test_package.xml_variable = 'abc<'
plsql.test_package.xml_variable.should == 'abc'
end

end

And here are some resources i've found about XMLType.
http://www.ruby-forum.com/topic/56982
I'm not sure if it should be vixible in Ruby as plain String or maybe an XML Docukment like here:
http://www.tutorialspoint.com/ruby/ruby_xml_xslt.htm
Do you plan to add a support for XMLType?

Regards
Jacek

Which means if you can't use default user names a lot of tests will break

Attempting to set plsql.activerecord_class to a class that inherits from ActiveRecord::Base fails if ActiveRecord::Base is not (at most) the parent class.

For instance:

class MyAppBase < ActiveRecord::Base
  self.abstract_class = true
end

class MyModel < MyAppBase
end

plsql.activerecord_class = MyModel # throws 'Wrong ActiveRecord class'

Even though ActiveRecord::Base is indeed in the inheritance chain.

>> MyModel.ancestors
MyAppBase(abstract), ActiveRecord::Base, ...

TABLE or PL/SQL TABLE type arguments are not cleared from the global temp table used to process them if an Oracle error occurs when the procedure or function is executed. If an exception is thrown in one procedure call for business reasons, the next call will more than likely fail because its table argument will also contain the values from the preceding call.

Here is the diff of spec/plsql/procedure_spec.rb to show how to cause the error and test the condition.

diff --git a/spec/plsql/procedure_spec.rb b/spec/plsql/procedure_spec.rb
index 2334f4d..20cecb4 100644
--- a/spec/plsql/procedure_spec.rb
+++ b/spec/plsql/procedure_spec.rb
@@ -905,6 +905,8 @@ describe "Parameter type mapping /" do
           TYPE t_numbers IS TABLE OF NUMBER(15);
           FUNCTION test_sum (p_numbers IN t_numbers)
             RETURN NUMBER;
+          FUNCTION test_function_failure (p_numbers IN t_numbers, p_force_failure IN varchar2 := 'N')
+            RETURN NUMBER;
           FUNCTION test_numbers (p_numbers IN t_numbers, x_numbers OUT t_numbers)
             RETURN t_numbers;
           TYPE t_employee IS RECORD(
@@ -949,6 +951,15 @@ describe "Parameter type mapping /" do
               RETURN NULL;
             END IF;
           END;
+          FUNCTION test_function_failure (p_numbers IN t_numbers, p_force_failure IN varchar2 := 'N')
+          RETURN NUMBER
+          IS
+          BEGIN
+            IF p_force_failure = 'Y' THEN
+              raise_application_error(-20000, 'Simulate business error to test clearing of temp table.');
+            END IF;
+            RETURN p_numbers.COUNT;
+          END;
           FUNCTION test_numbers (p_numbers IN t_numbers, x_numbers OUT t_numbers)
           RETURN t_numbers
           IS
@@ -1040,6 +1051,15 @@ describe "Parameter type mapping /" do
       plsql.test_collections.test_sum([1,2,3,4]).should == 10
     end
 
+    it "should clear temporary tables after executing function with table of numbers type even if an error occurs in the package" do
+      # this should work fine
+      plsql.test_collections.test_function_failure([1,2,3,4], 'N').should == 4
+      # we will force a package error here to see if things get cleaned up before the next call
+      lambda { plsql.test_collections.test_function_failure([1,2,3,4], 'Y') }.should raise_error(/Simulate business error to test clearing of temp table/)
+      # after the error in the first call temporary tables should be cleared
+      plsql.test_collections.test_function_failure([5,6,7], 'N').should == 3
+    end
+
     it "should return table of numbers type (defined inside package)" do
       plsql.test_collections.test_numbers([1,2,3,4]).should == [[1,2,3,4], {:x_numbers => [1,2,3,4]}]
     end

Here is the diff of lib/plsql/procedure_call.rb to show how the tests will pass if the global temp table is cleared before each time it is populated.

diff --git a/lib/plsql/procedure_call.rb b/lib/plsql/procedure_call.rb
index 474d19f..98e4cdb 100644
--- a/lib/plsql/procedure_call.rb
+++ b/lib/plsql/procedure_call.rb
@@ -290,6 +290,7 @@ module PLSQL
       # insert values without autocommit
       old_autocommit = @schema.connection.autocommit?
       @schema.connection.autocommit = false if old_autocommit
+      tmp_table.delete
       case argument_metadata[:element][:data_type]
       when 'PL/SQL RECORD'
         values_with_index = []

These 3 specs got failed every time. Have not found the cause of them yet. Confirmed these specs are fine at trav

$ rake spec
/home/yahonda/.rbenv/versions/2.3.1/bin/ruby -I/home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rspec-core-3.5.1/lib:/home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rspec-support-3.5.0/lib /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rspec-core-3.5.1/exe/rspec --pattern spec/\*\*\{,/\*/\*\*\}/\*_spec.rb
............................................................................................................FFF..................................................................................................................................................................................................................................................................................................................................................

Failures:

  1) Parameter type mapping / Function or procedure with XMLType parameters should process XMLType parameters
     Failure/Error: @raw_cursor.exec(*bindvars)

     OCIError:
       ORA-06550: line 2, column 12:
       PLS-00306: wrong number or types of arguments in call to 'TEST_XMLTYPE'
       ORA-06550: line 2, column 1:
       PL/SQL: Statement ignored
     # stmt.c:243:in oci8lib_230.so
     # ./lib/plsql/oci_connection.rb:109:in `exec'
     # ./lib/plsql/procedure_call.rb:27:in `exec'
     # ./lib/plsql/procedure.rb:267:in `exec'
     # ./lib/plsql/schema.rb:185:in `method_missing'
     # ./spec/plsql/procedure_spec.rb:268:in `block (3 levels) in <top (required)>'

  2) Parameter type mapping / Function or procedure with XMLType parameters should work when passing a NULL value
     Failure/Error: @raw_cursor.exec(*bindvars)

     OCIError:
       ORA-06550: line 2, column 12:
       PLS-00306: wrong number or types of arguments in call to 'TEST_XMLTYPE'
       ORA-06550: line 2, column 1:
       PL/SQL: Statement ignored
     # stmt.c:243:in oci8lib_230.so
     # ./lib/plsql/oci_connection.rb:109:in `exec'
     # ./lib/plsql/procedure_call.rb:27:in `exec'
     # ./lib/plsql/procedure.rb:267:in `exec'
     # ./lib/plsql/schema.rb:185:in `method_missing'
     # ./spec/plsql/procedure_spec.rb:273:in `block (3 levels) in <top (required)>'

  3) Parameter type mapping / Function or procedure with XMLType parameters should assign input parameter to putput parameter
     Failure/Error: @raw_cursor.exec(*bindvars)

     OCIError:
       ORA-06550: line 2, column 1:
       PLS-00306: wrong number or types of arguments in call to 'TEST_XMLTYPE2'
       ORA-06550: line 2, column 1:
       PLS-00306: wrong number or types of arguments in call to 'TEST_XMLTYPE2'
       ORA-06550: line 2, column 1:
       PL/SQL: Statement ignored
     # stmt.c:243:in oci8lib_230.so
     # ./lib/plsql/oci_connection.rb:109:in `exec'
     # ./lib/plsql/procedure_call.rb:27:in `exec'
     # ./lib/plsql/procedure.rb:267:in `exec'
     # ./lib/plsql/schema.rb:185:in `method_missing'
     # ./spec/plsql/procedure_spec.rb:279:in `block (3 levels) in <top (required)>'

Finished in 47.72 seconds (files took 0.46588 seconds to load)
449 examples, 3 failures

Failed examples:

rspec ./spec/plsql/procedure_spec.rb:266 # Parameter type mapping / Function or procedure with XMLType parameters should process XMLType parameters
rspec ./spec/plsql/procedure_spec.rb:272 # Parameter type mapping / Function or procedure with XMLType parameters should work when passing a NULL value
rspec ./spec/plsql/procedure_spec.rb:277 # Parameter type mapping / Function or procedure with XMLType parameters should assign input parameter to putput parameter

/home/yahonda/.rbenv/versions/2.3.1/bin/ruby -I/home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rspec-core-3.5.1/lib:/home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rspec-support-3.5.0/lib /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rspec-core-3.5.1/exe/rspec --pattern spec/\*\*\{,/\*/\*\*\}/\*_spec.rb failed
$

• bundle show

$ bundle show
Gems included by the bundle:
  * activemodel (4.2.7)
  * activerecord (4.2.7)
  * activerecord-oracle_enhanced-adapter (1.6.7)
  * activesupport (4.2.7)
  * addressable (2.4.0)
  * arel (6.0.3)
  * builder (3.2.2)
  * bundler (1.12.5)
  * descendants_tracker (0.0.4)
  * diff-lcs (1.2.5)
  * docile (1.1.5)
  * faraday (0.9.2)
  * git (1.3.0)
  * github_api (0.14.4)
  * hashie (3.4.4)
  * highline (1.7.8)
  * i18n (0.7.0)
  * json (1.8.3)
  * juwelier (2.1.2)
  * jwt (1.5.4)
  * mini_portile2 (2.1.0)
  * minitest (5.9.0)
  * multi_json (1.12.1)
  * multi_xml (0.5.5)
  * multipart-post (2.0.0)
  * nokogiri (1.6.8)
  * oauth2 (1.0.0)
  * pkg-config (1.1.7)
  * rack (1.6.4)
  * rake (11.2.2)
  * rdoc (4.2.2)
  * rspec (3.5.0)
  * rspec-core (3.5.1)
  * rspec-expectations (3.5.0)
  * rspec-mocks (3.5.0)
  * rspec-support (3.5.0)
  * rspec_junit_formatter (0.2.3)
  * ruby-oci8 (2.2.2)
  * semver (1.0.1)
  * simplecov (0.12.0)
  * simplecov-html (0.10.0)
  * thread_safe (0.3.5)
  * tzinfo (1.2.2)
$

• Environment

ruby 2.3.1p112 (2016-04-26 revision 54768) [x86_64-linux]
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Not sure if this is a fault of the OCI8 gem or the plsql gem, but when I attempt to connect when my password is about to expire, and get OCISuccessWithInfo (with the message stating my password is about expire), it is interpreted as an exception. This should still be a valid connection and allow for some way to retrieve that message.

Hi,

I have problems inserting a new record into a table with a timestamp column.

I'm using:
ruby-oci8 (2.0.3)
ruby-plsql (0.4.1)

The test case is:
a very simple table:
create table muc (col1 timestamp);

the problem:
irb(main):051:0> plsql.muc.insert({:col1 => Time.now})
TypeError: can't convert Time into String
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-oci8-2.0.3/lib/oci8/bindtype.rb:133:in set' from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-oci8-2.0.3/lib/oci8/bindtype.rb:133:ininitialize'
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-oci8-2.0.3/lib/oci8/bindtype.rb:133:in new' from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-oci8-2.0.3/lib/oci8/bindtype.rb:133:increate'
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-oci8-2.0.3/lib/oci8/oci8.rb:464:in make_bind_object' from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-oci8-2.0.3/lib/oci8/oci8.rb:267:inbind_param'
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.1/lib/plsql/oci_connection.rb:83:in bind_param' from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.1/lib/plsql/procedure_call.rb:16:inexec'
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.1/lib/plsql/procedure_call.rb:15:in each' from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.1/lib/plsql/procedure_call.rb:15:inexec'
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.4.1/lib/plsql/table.rb:171:in `insert'
from (irb):51
from :0

However, using OCI8, I can insert without any problems:
irb(main):048:0> oci.exec('insert into muc values(:1)', Time.new)
=> 1

Any ideas?
Thanks a lot.

With RSpec 3.4 the assertions on objects doesn't work for me any more.
The following example fails:

    expected = { type_code: plsql.dbms_types.typecode_bdouble, type_name: 'BINARY_DOUBLE', data_value: 12345 }
    result = plsql.any_data_bdouble(12345)
    expect( result ).to eq( expected )

with exception

ArgumentError: No PL/SQL procedure 'TO_ARY' found for type 'ANY_DATA_BDOUBLE' object
./spec/any_data_scalar/create_spec.rb:6:in `block (2 levels) in <top (required)>'
-e:1:in `load'
-e:1:in `<main>'

I did some digging around and I found a reason.
The method_missing should come in pair with respond_to? implementation.
http://stackoverflow.com/questions/291132/method-missing-gotchas-in-ruby

The workaround for this seems to be adding:

  class ObjectInstance < Hash #:nodoc:
...
    def respond_to?(method, include_private = false)
      super(method, include_private)
    end

so that RSpec can inspect object for methods prior to invoking them.

Hi Raymonds, I have created a self-contained testcase. It is a small tar file (30K). Let me know how I can get this across.

Steps to reproduce

$ rspec ./spec/plsql/type_spec.rb:105
Coverage may be inaccurate; set the "--debug" command line option, or do JRUBY_OPTS="--debug" or set the "debug.fullTrace=true" option in your .jrubyrc
Run options: include {:locations=>{"./spec/plsql/type_spec.rb"=>[105]}}
F

Failures:

  1) Type find should find existing type
     Failure/Error: Unable to find oracle.jdbc.driver.PhysicalConnection.rollback(oracle/jdbc/driver/PhysicalConnection.java to read failed line

     Java::JavaSql::SQLException:
       Could not rollback with auto-commit set on
     # oracle.jdbc.driver.PhysicalConnection.rollback(oracle/jdbc/driver/PhysicalConnection.java:2427)
     # java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)
     # RUBY.rollback(/home/yahonda/git/ruby-plsql/lib/plsql/jdbc_connection.rb:79)
     # RUBY.rollback(/home/yahonda/git/ruby-plsql/lib/plsql/sql_statements.rb:61)
     # RUBY.block in (root)(/home/yahonda/git/ruby-plsql/spec/plsql/type_spec.rb:100)
     # java.lang.invoke.MethodHandle.invokeWithArguments(java/lang/invoke/MethodHandle.java:627)

Finished in 3.15 seconds (files took 7.16 seconds to load)
1 example, 1 failure

Failed examples:

rspec ./spec/plsql/type_spec.rb:105 # Type find should find existing type

$

Environment:

$ ruby -v
jruby 9.1.6.0 (2.3.1) 2016-11-09 0150a76 OpenJDK 64-Bit Server VM 25.111-b16 on 1.8.0_111-b16 +jit [linux-x86_64]

$ java -version
openjdk version "1.8.0_111"
OpenJDK Runtime Environment (build 1.8.0_111-b16)
OpenJDK 64-Bit Server VM (build 25.111-b16, mixed mode)
$

$ opatch lspatches
24315824;Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
24006101;Database Patch Set Update : 12.1.0.2.161018 (24006101)

OPatch succeeded.
$

Workaround:

Setting JAVA_OPTS as follows.

$ echo $JAVA_OPTS
-Doracle.jdbc.autoCommitSpecCompliant=false
$

This is due to https://docs.oracle.com/database/121/READM/chapter12101.htm#READM316

3.24.4.3 Bug 16063217
Several changes were made to local transaction processing for compliance with the JDBC spec 4.1. If setAutoCommit(true) is called and a local transaction exists, the transaction is automatically committed (in earlier releases, no action was taken). If commit or rollback is called while in AUTOCOMMIT mode, the driver will throw an exception (again, in earlier releases, no action was taken). It is possible that your application will have these situations and it may be difficult to immediately change the software.

Workaround: 
By adding the -Doracle.jdbc.autoCommitSpecCompliant=false system property to the command line, the old behavior of no action will be preserved.

There is no public method to clear object cache for schema.
I've found method Schema.reset_instance_variables but it is private.
We need to be able to tell ruby that object definitions were changed by Spec file or any other file.
How can we do that?
Can you give me a clue or make the reset_instance_variables public?

When performing a switch on synonyms from Real tables to test tables and back, the cache is not refreshed.
What i would like to have is ability to reset cache on after(:all).
When we run multiple tests sets, some tests are working on real tables, some other tests work on test tables.

Steps to reproduce problem:

require File.expand_path(File.dirname(FILE) + '/spec_helper')

describe "Refresh Roaming Zone Set, so it represents all the Partners currently defined, not only those defined when the Roaming Zone set was created" do
before(:all) do
plsql.execute("CREATE TABLE AA( A INTEGER)")
plsql.execute("CREATE TABLE BB( A INTEGER)")
plsql.execute("CREATE SYNONYM TEST_SYN FOR AA")
plsql.aa.insert(:a => 1)

puts plsql.select(:first, "select count(1) FROM TEST_SYN").values[0]
puts plsql.TEST_SYN.count
plsql.execute("CREATE OR REPLACE SYNONYM TEST_SYN FOR BB");

end

it "should properly switch object references" do
plsql.select(:first, "select count(1) FROM TEST_SYN").values[0]
.should == plsql.TEST_SYN.count
end

after(:all) do
plsql.execute("DROP TABLE AA")
plsql.execute("DROP TABLE BB")
plsql.execute("DROP SYNONYM TEST_SYN")
end
end

Since last release there were quite few changes.
@javornikolov, you think it would be good idea to make a new release?

see http://blog.rayapps.com/2010/02/26/ruby-plsql-0-4-2-better-support-for-object-types-and-types-in-packages/#comment-160518956

This is a follow up from
I've found a nice tool for generating cheat sheets - https://github.com/Kapeli/cheatset#readme
It would be good to have someone to collaborate on this.

Is it possible to compute functions like min, max, sum or avg on a column with ruby-plsql. It would be nice to have something like

create table employee (
  name varchar2(255) 
  age integer
)

and then a call like

plsql.employee.max 'age'

or

plsql.employee.min 'age'

This one is closely related to #34

Setting is the same as with issue #34, with a single variation on the function being called, the return data set is the same, but this one returns a table indexed by varchar2:

  type t_fee_types is table of fee_types%rowtype index by varchar2(25);

  function get_fee_types return wb_types.t_fee_types is
    type t_ary_fee_types is table of fee_types%rowtype index by pls_integer;

    f     fee_types%rowtype;

    i     pls_integer;

    l_ary_fee_types  t_ary_fee_types;
    l_fee_types      wb_types.t_fee_types;

    cursor cur is 
      select * from fee_types;

  begin
    l_fee_types.delete;
    l_ary_fee_types.delete;

    -- Visit SQL
    open cur;
    fetch cur bulk collect into l_ary_fee_types;  
    close cur;

    -- Load fee types
    i := l_ary_fee_types.first;
    while i is not null loop
      f := l_ary_fee_types(i);
      l_fee_types(f.logid) := f;

      i := l_ary_fee_types.next(i);
    end loop;

    return l_fee_types;   
  end get_fee_types;

Error, with debug puts trace on is:

[2] pry(main)> plsql.wb_fee_configuration.get_fee_types
DEBUG: sql = DECLARE<br/>
i__ PLS_INTEGER;<br/>
l_return BILLING_GUI_DEV.WB_TYPES.T_FEE_TYPES;<br/>
BEGIN<br/>
l_return := BILLING_GUI_DEV.WB_FEE_CONFIGURATION.GET_FEE_TYPES();<br/>
i__ := l_return.FIRST;<br/>
LOOP<br/>
EXIT WHEN i__ IS NULL;<br/>
INSERT INTO ruby_50685_187694_50830_0 VALUES (l_return(i__).logid, l_return(i__).fee_subject, l_return(i__).comment, i__);<br/>
i__ := l_return.NEXT(i__);<br/>
END LOOP;<br/>
OPEN :return FOR SELECT * FROM ruby_50685_187694_50830_0 ORDER BY i__;<br/>
DELETE FROM ruby_50685_187694_50830_0;<br/>
END;<br/>
OCIError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6
from stmt.c:253:in oci8lib_191.so

This one looks less trivial, again, all help MUCH appreciated,

Borna

From failing tests:

1.9.3-p286 :015 > conn = OCI8.new('hr', 'hr', "//#{DATABASE_HOST}:#{DATABASE_PORT}/#{DATABASE_NAME}")
 => #<OCI8:HR> 
1.9.3-p286 :016 > conn.select_one("SELECT current_timestamp from dual").first
 => 2013-02-23 15:13:34 +0100 
1.9.3-p286 :017 > conn.exec "alter session set time_zone = 'UTC'"
 => 0 
1.9.3-p286 :018 > conn.select_one("SELECT current_timestamp from dual").first
OCIError: ORA-01805: possible error in date/time operation
        from ocidatetime.c:119:in oci8lib_191.so
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/datetime.rb:393:in `get'
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/cursor.rb:538:in `get_data'
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/cursor.rb:538:in `block in fetch_one_row_as_array'
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/cursor.rb:537:in `collect'
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/cursor.rb:537:in `fetch_one_row_as_array'
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/cursor.rb:154:in `fetch'
        from /home/bbozo/.rvm/gems/ruby-1.9.3-p286@ruby-plsql/gems/ruby-oci8-2.1.4/lib/oci8/oci8.rb:326:in `select_one'
        from (irb):18
        from /home/bbozo/.rvm/rubies/ruby-1.9.3-p286/bin/irb:13:in `<main>'

"Fix" for me was to comment out the ENV['TZ'] setter in spec_helper.rb, this breaks other tests though. I digged out older mailing list entries from oracle_enhanced that link this to a ruby-oci8 error and a problem regarding a desync of time zone files between instant client and server (I run 11.2.0.2.0 server and 11.2.0.1.0 client)

I guess the solution is to use a different Instant Client version, perhaps someone here could know which one and include it in the docs?

I guess this would be something to have in mind in the oracle_enhanced gem too, especially for production purposes?

We are using this gem to hit some stored procs for a client. We're digging in to some performance issues with the calls as they are taking 1.5 to 2 seconds. We finally got them to give us the raw sql for one of the calls and with the stored proc the average time is 1.85 seconds per call and when we run the raw sql it goes down to 1.07 on average.

Also, their dba says it creates an extra table in their db on every call we make.

Any help would be much appreciated!

Always got a segmentation fault when "should execute function with cursor parameter and return record" executed.

$ ruby -I"lib:lib:spec"  "/home/yahonda/Dropbox/git/ruby-plsql/ruby-oci8/ruby/1.8/gems/rspec-1.3.2/bin/spec" "spec/plsql/procedure_spec.rb:1546"
Segmentation fault

$ bundle update
Fetching source index for http://rubygems.org/
Using rake (0.9.2.2) 
Using activesupport (2.3.8) 
Using activerecord (2.3.8) 
Using activerecord-oracle_enhanced-adapter (1.3.1) 
Using bundler (1.0.21) 
Using git (1.2.5) 
Using jeweler (1.5.2) 
Using rspec (1.3.2) 
Using ruby-oci8 (2.0.4) 
Your bundle is updated! Use `bundle show [gemname]` to see where a bundled gem is installed.
[1.8.7@rubyplsql238] yahonda@myoel5 ~/Dropbox/git/ruby-plsql (address_rake_aborted) 

1546     it "should execute function with cursor parameter and return record" do
1547       pending "not possible from JDBC" if defined?(JRUBY_VERSION)
1548       plsql.test_cursor do |cursor|
1549         plsql.test_cursor_fetch(cursor).should == @employees[0]
1550       end
1551     end

Actually, I'm not sure how to find the cause of this segmentation fault yet.

I have to connect to multiple databases dynamically in a Rails app in order to execute a stored procedure on each. It looks like this is supported by PLSQL::Schema.find_or_new and all I should have to do is:

plsql(:db1).my_proc(...)
plsql(:db2).my_proc(...)
plsql(:db3).my_proc(...)

I can't figure out how to create these connections and their aliases though. All three are in a yml file. Any suggestions?

Given ist a package

create package p as
type mycur is rec cursor;

function foo(a in varchar2, b out varchar2, c mycur) return integer;
end;

create package body p as
function foo (
a in varchar2
,b out varchar2
,c out mycur)
return integer
is
begin
open c for
...
end;
end p;

But there is no way to call these function with ruby-plsql ;(