From: KUBO Takehiro <kubo@...>
Date: 2009-09-22T21:53:48+09:00
Subject: Re: ruby-oci8 function

On Tue, Sep 22, 2009 at 7:58 PM, beny 18241 <beny18241@gmail.com> wrote:
>
> Hi,
>
> This is whole function which i wanted to call by ruby script:
>
> please write how scrip[t should  look like to call this function :
>
>
> -----
>
> create or replace FUNCTION                GET_DEVICES_F
> (
>   v_query_policy_name IN vmpolicy_values.policy%TYPE,
>   v_query_policy_value IN vmpolicy_values.value%TYPE
> )
> RETURN types.ref_cursor AS

  conn = OCI8.new(user, password)
  cursor1 = conn.parse("begin :cursor := GET_DEVICES_F(:name, :value); end;")
  cursor1.bind_param(':cursor', nil, OCI8::Cursor)
  cursor1.bind_param(':name', 'MY_NAME')
  cursor1.bind_param(':value', 'MY_VALUE')
  cursor1.exec
  cursor2 = cursor1[':cursor'] # Gets the returned cursor.
  while row = cursor2.fetch
    puts row.join(',')
  end
  cursor2.close
  cursor1.close

You can get the dbms_output contents putted by GET_DEVICES_F
as follows:

  plsql = conn.parse("BEGIN DBMS_OUTPUT.GET_LINE(:1, :2); END;')
  plsql.bind_param(1, nil, String, 255)
  plsql.bind_param(2, nil, Integer)
  while true
     plsql.exec
     break if plsql[2] != 0 # non-zero indicates the end of lines.
     puts plsql[1]
  end

Note that I have not verified whether these scripts work...