Never been to TextSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

About this user

« Newer Snippets
Older Snippets »
1 total  XML / RSS feed 

ruby stored procedures

There are alot of reasons to use stored procedures. One of the most compelling reasons is that a system has already been built with stored procedures and interfacing with it cannot be by raw SQL. This situation is mainly caused by DBA’s whose job it is to control the flow of information into and out of the database. In “enterprise” situations this is the norm.


  @connection = OCI8.new("db_user", "db_password", "db_name")

  plsql = @connection.parse("BEGIN P_MYPROCEDURE.my_method(:out); END;")
  plsql.bind_param(':out', OCI8::Cursor) 
  plsql.exec

  cursor = plsql[':out']
  plsql.close

  x = ''
  while r = cursor.fetch()
    x = x + r.join(', ') + '
' end @out = x @connection.logout


This code assumes you are using Rails 1.1 as an engine and that you have setup the database.yml for Oracle (10g in this post). The OCI8 object instantiated on the first line is in the OCI8 library which is loaded by Rails, specified in database.yml.

Interfacing with Oracle and its stored procedures pretty much requires some knowledge about Oracle which has some very specific concepts that something like MySQL does not.

First, to pass local variables to the stored procedure’s “in” and “out” variables, you must explicitly “bind” them.

Second is the notion of “cursor”.

Author: hksintl on http://blog.hksintl.com/
« Newer Snippets
Older Snippets »
1 total  XML / RSS feed