February 26, 2010

ruby-plsql 0.4.2 – better support for object types and types in packages

Filed under: oracle,pl/sql,ruby — Raimonds Simanovskis @ 6:43 pm

I just released ruby-plsql version 0.4.2 which mainly adds support for more PL/SQL procedure parameter types. See change history file for more detailed list of changes.

Object types and object methods

Now you can use ruby-plsql to construct PL/SQL objects and call methods on these object. For example, if you have the following type defined:

  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50),
  CONSTRUCTOR FUNCTION t_address(p_full_address VARCHAR2)
  MEMBER FUNCTION display_address(p_separator VARCHAR2 DEFAULT ',') RETURN VARCHAR2,
  MEMBER PROCEDURE set_country(p_country VARCHAR2),
  STATIC FUNCTION create_address(p_full_address VARCHAR2) RETURN t_address

Then you can construct PL/SQL objects and call methods on them:

# call default constructor with named parameters
address = plsql.t_address(:street => 'Street', :city => 'City', :country => 'Country')
# call default constructor with sequential parameters
address = plsql.t_address('Street', 'City', 'Country')
# call custom constructor
address = plsql.t_address('Street, City, Country')
address = plsql.t_address(:p_full_address => 'Street, City, Country')

# returned PL/SQL object is Hash object in Ruby
address == {:street => 'Street', :city => 'City', :country => 'Country'}

# but in addition you can call PL/SQL methods on it
address.display_address == 'Street, City, Country'
address.set_country('Other') == {:street => 'Street', :city => 'City', :country => 'Other'}

# or you can call object member methods also with explicit self parameter
plsql.t_address.display_address(:self => {:street => 'Street', :city => 'City', :country => 'Other'}, :p_separator => ',') == 'Street, City, Country'

# or you can call static methods of type
plsql.t_address.create_address('Street, City, Country') == {:street => 'Street', :city => 'City', :country => 'Country'}

Record types and table of record types inside packages

Now you can call Pl/SQL procedures with parameters which have record or table of record type that is defined inside PL/SQL package. For example if you have the following package:

  TYPE t_employee IS RECORD(
    employee_id   NUMBER(15),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    hire_date     DATE
  TYPE t_employees IS TABLE OF t_employee;
  TYPE t_employees2 IS TABLE OF t_employee
  FUNCTION test_employee (p_employee IN t_employee)
    RETURN t_employee;
  FUNCTION test_employees (p_employees IN t_employees)
    RETURN t_employees;
  FUNCTION test_employees2 (p_employees IN t_employees2)
    RETURN t_employees2;

Then you can call these package functions from Ruby:

employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2010,2,26)
# PL/SQL record corresponds to Ruby Hash
plsql.test_records.test_employee(employee) == employee
# PL/SQL table corresponds to Ruby Array
plsql.test_records.test_employees([employee, employee]) == [employee, employee]
# PL/SQL index-by table corresponds to Ruby Hash
plsql.test_records.test_employees({1 => employee, 2 => employee}) == {1 => employee, 2 => employee}

If you will use table types defined inside PL/SQL packages then ruby-plsql will dynamically create session specific temporary tables which will be used to pass and get table parameter values. To ensure that these session specific temporary tables will be dropped you need to explicitly call plsql.logoff to close connection. For example, if you use ruby-plsql-spec for PL/SQL unit testing then in spec_helper.rb include

at_exit do

to ensure that connection will be closed with plsql.logoff before Ruby script will exit. But in case of some script failure if this was not executed and you notice that there are temporary tables with RUBY_ prefix in your schema then you can call plsql.connection.drop_all_ruby_temporary_tables to drop all temporary tables.

Establish new connection

Now there is simpler connect! method how to establish new ruby-plsql connection when you need a new connection just for ruby-plsql needs. You can do it in several ways:

plsql.connect! username, password, database_tns_alias
plsql.connect! username, password, :host => host, :port => port, :database => database
plsql.connect! :username => username, :password => password, :database => database_tns_alias
plsql.connect! :username => username, :password => password, :host => host, :port => port, :database => database

And the good thing is that this method will work both with MRI 1.8 or 1.9 or with JRuby – you do not need to change the way how you are establishing connection to database.


Now there is simpler way how to define savepoints and how to rollback to savepoint:

plsql.savepoint "before_something"
plsql.rollback_to "before_something"

Check validity of database objects

Now ruby-plsql will check if referenced database object is valid before trying to call it. And if it will not be valid then corresponding compilation error will be displayed. For example, if you have invalid database object:

  l_dummy invalid_table.invalid_column%TYPE;
  RETURN p_dummy;

then when trying to call it


you will get the following error message:

ArgumentError: Database object 'HR.TEST_INVALID_FUNCTION' is not in valid status
Error on line    2:   l_dummy invalid_table.invalid_column%TYPE;
     position   11: PLS-00201: identifier 'INVALID_TABLE.INVALID_COLUMN' must be declared
     position   11: PL/SQL: Item ignored

Other improvements

See History.txt file for other new features and improvements and see RSpec tests in spec directory for more usage examples.



  1. Raimonds,

    I’m not sure if it’s kind of bug in this version or I’m doing something wrong with configuration however I’ve faced with problem. I’m running your examples and getting same error for each spec:

    ORA-00904: “SUBPROGRAM_ID”: invalid identifier

    I’ve tried to change name of function that is called in example just to understand how it will affect execution and got expected error:

    No database object ‘REMOVE_ROOMS_BY_NAME1′ found

    So it looks like something wrong with calling of function inside DB. However as I’ve just start playing with your library it might be some misconfiguration on my side. Did you have such kind of problems in past?

    Example trace from Remove rooms by name example:

    stmt.c:306:in oci8lib.bundle
    /Library/Ruby/Gems/1.8/gems/ruby-oci8-2.0.4/lib/oci8/oci8.rb:287 :in `exec’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/oci_connection.rb:97 :in `exec’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/oci_connection.rb:82 :in `new_from_query’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/oci_connection.rb:132 :in `cursor_from_query’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/connection.rb:114 :in `select_all’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/sql_statements.rb:10 :in `select_all’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/procedure.rb:87 :in `get_argument_metadata’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/procedure.rb:241 :in `initialize’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/schema.rb:213 :in `new’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/schema.rb:213 :in `find_database_object’
    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/schema.rb:179 :in `method_missing’
    /Users/oiavorskyi/Projects/Current/PLSQL Tests/spec/remove_rooms_by_name_spec.rb:31

    Comment by Oleg — March 5, 2010 @ 10:50 pm

    • Can you tell which database version are you using? I am selecting subprogram_id column from all_arguments view but only if database version is 10.2 or more (as in previous releases this field was missing).

      You can try to execute plsql.connection.database_version – this is how I detect database version.

      Comment by Raimonds Simanovskis — March 7, 2010 @ 3:55 pm

  2. Hi Raimonds,
    I have just installed ruby-plsql 0.4.2.
    In PL/SQL I use both function overloading and named parameter calls so that I can limit the number of packaged functions (and consequently the amount of application code).
    A typical call from Ruby would be:

    rv = plsql.etl_translations_xfn.store(:p_table_column => table_column, :p_language_code => language_code, :p_source => source, :p_translation => translation)

    where there are two versions of etl_translations_xfn.store: a version which takes a rowtype (record) and a version with 20-odd parameters as used above.
    If I restrict the etl_translations.store() function with 20-odd parameters to just the four parameters above, everything is OK.
    If I use the original function I get the following message:

    /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/procedure_call.rb:101:in `get_overload_from_arguments_list’: Wrong number or types of arguments passed to overloaded PL/SQL procedure (ArgumentError)
    from /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/procedure_call.rb:10:in `initialize’
    from /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/procedure.rb:245:in `new’
    from /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/procedure.rb:245:in `exec’
    from /Library/Ruby/Gems/1.8/gems/ruby-plsql-0.4.2/lib/plsql/package.rb:52:in `method_missing’
    from ./import_translations.rb:34:in `import’
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/csv.rb:312:in `open_reader’
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/csv.rb:532:in `parse’
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/csv.rb:560:in `each’
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/csv.rb:531:in `parse’
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/csv.rb:311:in `open_reader’
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/csv.rb:85:in `open’
    from ./import_translations.rb:27:in `import’
    from ./import_translations.rb:66

    It seems that the combination of overloading and named parameters is not a happy one.
    Otherwise I am quite happy with ruby-plsql!

    Comment by Hubert — March 15, 2010 @ 7:49 pm

  3. Raimonds,
    I created a tar file with a self contained testcase (30K). Please let me know how I can upload it to you.
    Rgds Hubert

    Comment by Hubert — March 16, 2010 @ 3:37 pm

  4. Hi,

    Does ruby-plsql support connection pooling? In web apps, connection reusing significantly reduces request execution time due to lack of overhead for opening a new session with Oracle.


    Comment by Robert — May 29, 2010 @ 3:33 pm

    • When I use ruby-plsql in web apps then I use Ruby on Rails web framework and Ruby on Rails supports permanent database connections and connection pooling. And in this case ruby-plsql is instructed to use existing Ruby on Rails (more precisely ActiveRecord) database connection.

      Comment by Raimonds Simanovskis — May 29, 2010 @ 10:49 pm

      • Still… for example for Sinatra apps we are somehow left outside. It would certainly help in those scenarios.

        Comment by Robert — May 31, 2010 @ 10:18 pm

  5. Excellent stuff. DHH may hate stored procedures, but I depend on them!

    Comment by JulesLt — July 25, 2010 @ 4:33 pm

RSS feed for comments on this post.

Blog at WordPress.com.

%d bloggers like this: