4 Replies Latest reply: Dec 2, 2007 8:02 PM by 605073 RSS

    RoR Calling Oracle Function/Procedure

    39779
      Hello,

      Has anyone got experience of calling an Oracle Function or Procedure from RoR?

      thanks

      David
        • 1. Re: RoR Calling Oracle Function/Procedure
          589551
          Hi,

          Ruby/RoR both use Ruby-OCI8 to interact with Oracle database, following information is from Ruby-OCI8 document,

          In case of PL/SQL statement:

          conn = OCI8.new('scott', 'tiger')
          conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123)
          # => ["0123", 123]
          conn.logoff

          Above example uses two bind variables which names are :str and :num. These initial values are "the string whose width is 4 and whose value is 'ABCD'" and "the number whose value is 123". This method returns the array of these bind variables, which may modified by PL/SQL statement. The order of array is same with that of bind variables.

          More information can be found at: http://ruby-oci8.rubyforge.org/en/api_OCI8.html
          • 2. Re: RoR Calling Oracle Function/Procedure
            608539
            Hello for calling function/procedure at RoR in Oracle I write muself classes and use they.
            Module:
            module SQLStatement

            class NoTypeResult < StandardError
            end # class

            class SQLProc

            attr_accessor :name, :arguments

            def initialize( args = nil )
            @name = args[:name] unless args[:name].nil?
            @arguments = args[:arguments] unless args[:arguments].nil?
            @conn = ActiveRecord::Base.connection.raw_connection
            end # def initialize

            def exec( args = nil )
            unless args.nil? || ( !args.is_a? Hash )
            args.each { |k, v| @arguments[k.to_s.intern] = args[k] }
            end # unless
            func_args = ''
            @arguments.each { |k, v| func_args += "#{func_args == '' ? '' : ', '}#{k.to_s} => :#{k.to_s}"}
            sql = "BEGIN {#@name}( #{func_args} );END;"
            cursor = @conn.parse( sql )
            @arguments.each do |k, v|
            cursor.bind_param( ":#{k.to_s}", v )
            end # each
            cursor.exec()
            end # def exec

            end # class SQLProc

            class SQLFunc < SQLProc

            attr_reader :result
            attr_accessor :result_type

            def initialize( args = nil )
            @result_type = args[:result_type] unless args[:result_type].nil?
            super( args )
            end # def

            def exec( args = nil )
            if @result_type.nil?
            raise NoTypeResult, 'No type for result setting', caller
            end # if
            unless args.nil? || ( !args.is_a? Hash )
            args.each { |k, v| @arguments[k.to_s.intern] = args[k] }
            end # unless
            func_args = ''
            @arguments.each { |k, v| func_args += "#{func_args == '' ? '' : ', '}#{k.to_s} => :#{k.to_s}"}
            sql = "BEGIN :result := #{@name}( #{func_args} );END;"
            cursor = @conn.parse( sql )
            @arguments.each do |k, v|
            cursor.bind_param( ":#{k.to_s}", v )
            end # each
            cursor.bind_param( ":result", nil, @result_type )
            cursor.exec()
            @result = cursor[':result']
            cursor
            end # def exec

            end # class

            end # module SQLStatment

            Example of use:

            function = SQLStatement::SQLFunc.new(
            :name => 'f_test_ins',
            :result_type => Fixnum,
            :arguments => {
            :login =>'asdf',
            :password => 'asdf',
            :email => 'Alik@ukr.net'
            }
            )
            function.exec
            raise function.result.inspect
            • 3. Re: RoR Calling Oracle Function/Procedure
              dvohra21
              Please refer
              http://wiki.rubyonrails.org/rails/pages/OracleStoredProceduresAsDataSource
              • 4. Re: RoR Calling Oracle Function/Procedure
                605073
                Thanks for this - very useful!

                I found that the SQLProc class wrapped the name of the procedure with curly braces but once they were removed it worked fine.

                It would be great if you could you post your solution on the Rails wiki. http://wiki.rubyonrails.org/rails/pages/OracleStoredProceduresAsDataSource