January 4, 2010

ruby-plsql 0.4.1 – support for package variables, views, dbms_output and more

Filed under: oracle,pl/sql,ruby — Raimonds Simanovskis @ 11:40 pm

Based on feedback from using ruby-plsql for PL/SQL unit testing I have release new version 0.4.1 with several new features. You can read about initial versions of ruby-plsql in previous blog posts.

Package variables

When you call methods on “plsql” Ruby object then ruby-plsql uses all_procedures and all_arguments data dictionary views to search for procedures and their argument metadata to construct corresponding PL/SQL block for execution. Unfortunately there are no corresponding data dictionary views for package variables (sometimes called “global variables”) that are defined in package specifications. Therefore there was no support for package variables in initial ruby-plsql versions.

But as there is quite frequent need in PL/SQL tests to set and get package variable values then I created the following solution for accessing package variables. I assume that typically package variables are defined in one line in package specifications and I scan PL/SQL package specification source in all_source data dictionary view for potential package variable definitions.

As a result if you have the following example of package specification:

  varchar2_variable VARCHAR2(50);
  number_variable NUMBER(15,2);
  string_constant CONSTANT  VARCHAR2(10) := 'constant';
  integer_constant CONSTANT INTEGER := 1;

then you can access these package variables in the same way as procedures:

plsql.test_package.varchar2_variable = 'test'
plsql.test_package.number_variable = 123
plsql.test_package.varchar2_variable # => 'test'
plsql.test_package.number_variable # => 123
plsql.test_package.string_constant # => 'constant'
plsql.test_package.integer_constant # => 1

Other basic data types as well as %ROWTYPE, %TYPE and schema object types are also supported for package variables. Only custom types defined in package specification are not supported (they are not supported for procedure parameters as well). As there are no data dictionary views for types defined in package specifications I don’t feel very enthusiastic about parsing package sources from all_source to get information about types defined inside packages :)


In previous post I described how to use ruby-plsql to perform basic table operations. Now these operations can be performed also with views:


insert_values method

Additional insert_values method is added for tables and views which can be helpful in PL/SQL tests for test data preparation. You can specify with more compact syntax which data you would like to insert into table or view:

plsql.employees.insert_values [:employee_id, :first_name, :last_name],
    [1, 'First', 'Last'],
    [2, 'Second', 'Last']

# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'First', 'Last')
# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (2, 'Second', 'Last')


If you use DBMS_OUTPUT.PUT_LINE in your PL/SQL procedures to log some debug messages then you can use plsql.dbms_output_stream= method to set where these messages should be displayed. Use the following to display DBMS_OUTPUT messages in standard output:

plsql.dbms_output_stream = STDOUT

Or write DBMS_OUTPUT messages to file:

plsql.dbms_output_stream = File.new('debug.log', 'w')

STANDARD package procedures

Procedures from SYS.STANDARD package can be called without sys.standard prefix, e.g.:


Other improvements

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

And also this version of ruby-plsql requires ruby-oci8 gem latest version 2.0.3 (if you use MRI / standard Ruby interpreter 1.8.6, 1.8.7 or 1.9.1) so please upgrade it as well if you do not have it. But as previously you can use ruby-plsql with JRuby and Oracle JDBC driver as well.



  1. Two questions:

    1. Does the insert_values method call shown here actually perform two distinct INSERT INTO statements (as the comment implies)? The context of my question is the network latency problem solved in the case study within http://method-r.com/downloads/doc_details/10-for-developers-making-friends-with-the-oracle-database-cary-millsap.

    2. I hope I’m taking those two “# =>” comment lines too literally (pun intended): does the insert_values actually submit the literal values being inserted? Or does it bind the values into placeholders within a prepared cursor?

    Thank you,

    Cary Millsap
    Method R Corporation

    Comment by Cary Millsap — January 5, 2010 @ 12:38 am

    • 1. It will perform two distinct INSERT INTO statements. As currently it is mostly meant for unit test data preparation I do not worry too much about network latency. When you will run your tests in continuous integration then anyway it is preferred to run it from machine which have fast network connection to database.

      2. ruby-plsql will use bind variables with prepared cursor both for table/view operations as well as for procedure calls with parameters. (It has different implementation compared to ActiveRecord oracle_enhanced adapter which always generates all variables inside SQL string as literals – but that’s different topic).

      Comment by Raimonds Simanovskis — January 5, 2010 @ 1:25 am

    • Actually currently insert_values will create a separate prepared statement for each row – so probably I will improve it in future to use just of prepared statement to enhance performance for insertion of many rows. In this case I could also use setExecuteBatch JDBC method to reduce network roundtrips (need to find if I can do something similar using OCI C library as well). So thanks for bringing my attention to it :)

      Comment by Raimonds Simanovskis — January 5, 2010 @ 1:42 am

  2. It’s good that you’re going to attend to it.

    You won’t have a problem finding the OCI support for what you’re looking for. Search http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10646/oci04sql.htm#g454129 for the string “Batch Error Mode” for part of what you’ll need.

    Good luck!


    Comment by Cary Millsap — January 5, 2010 @ 2:58 am

  3. P.S.: Please don’t overlook the end-to-end diagnostics features of the OCI that allow application developers using your drivers to instrument their code. When developers can instrument code paths with service, module, action, and client information (exposed as OCI_ATTR_SERVICE, OCI_ATTR_MODULE, OCI_ATTR_ACTION, OCI_ATTR_CLIENT_IDENTIFIER, OCI_ATTR_CLIENT_INFO, etc. in the OCI model), then they have a *much* easier time measuring and improving the performance of their applications throughout the software life cycle.


    Comment by Cary Millsap — January 5, 2010 @ 3:11 am

  4. Looks like another great release Raimonds! I’ve held off upgrading to these newer versions because of the OCI8 > v2.0 dependency. I haven’t seen anything from the OCI8 developer to suggest that the 2.0 line is production ready. I can only assume though that you are using it very regularly. Do you feel like the 2.0 library is as production ready as the existing 1.0 product?

    Comment by DeLynn Berry — January 5, 2010 @ 8:30 am

    • Yes, ruby-oci8 2.0.3 core features are as stable as in ruby-oci8 1.x, there are just some bugs in new complex data type support features. I plan to support just ruby-oci8 2.0.3 or later in future ruby-plsql and oracle_enhanced versions as I start to use many ruby-oci8 2.x features and it is too complicated to maintain backwards compatibility with ruby-oci8 1.x :)

      Comment by Raimonds Simanovskis — January 5, 2010 @ 11:02 am

  5. Is there any specific code to access global package variable defined as VARCHAR2(50 CHAR)?

    varchar2_variable VARCHAR2(50);
    varchar2char_variable VARCHAR2(50 CHAR);

    plsql.test_package.varchar2_variable = ‘test’ — OK
    plsql.test_package.varchar2char_variable = ‘test’ — NOK (No PL/SQL procedure or variable ‘varchar2char_variable’ found)

    Comment by GZI — January 13, 2010 @ 2:36 pm

    • Currently VARCHAR2(n CHAR) type is not supported – I am parsing all_source data dictionary view to find out variable definitions and didn’t include pattern for recognizing CHAR modifier for VARCHAR2 length. I will fix it in next version of ruby-plsql.

      Comment by Raimonds Simanovskis — January 17, 2010 @ 11:45 pm

  6. I’m using ruby-plsql with rails. When putting

    require ‘ruby-plsql’
    plsql.activerecord_class = ActiveRecord::Base
    plsql.connection.autocommit = false

    at the end of environment.db, I could not start webrick anymore.
    Wenn putting the same statements in front of a
    class models/plsql_processor.rb, I have no problems, everything works.

    Any ideas what the best place to put these statements is? Seems to depend
    on rails’ initialization progress…

    Comment by Martin — February 19, 2010 @ 2:38 pm

RSS feed for comments on this post.

Create a free website or blog at WordPress.com.

%d bloggers like this: