From: beny 18241 <beny18241@...>
Date: 2009-09-22T16:33:15+09:00
Subject: Re: ruby-oci8 function

Hi,

Thanks for help but still some problem exists, please see below:

-----
#!/usr/bin/ruby

require 'rubygems'
require 'oci8'
require 'ruby_plsql'


plsql.connection = OCI8.new("SYSTEM","orcl","//localhost/xe");
puts  plsql.get_names_f( :name => 'value1' , :sum => 'value2') ;
plsql.logoff

------

I get following error:

----
ruby repo.rb
stmt.c:539:in oci8lib.so: ORA-06550: line 2, column 12: (OCIError)
PLS-00382: expression is of wrong type
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
  from /usr/local/lib/site_ruby/1.8/oci8.rb:759:in `exec'
  from /usr/local/lib/site_ruby/1.8/oci8.rb:142:in `do_ocicall'
  from /usr/local/lib/site_ruby/1.8/oci8.rb:759:in `exec'
  from 
/var/lib/gems/1.8/gems/ruby-plsql-0.3.1/lib/plsql/oci_connection.rb:71:in 
`exec'
  from 
/var/lib/gems/1.8/gems/ruby-plsql-0.3.1/lib/plsql/procedure.rb:186:in 
`exec'
  from 
/var/lib/gems/1.8/gems/ruby-plsql-0.3.1/lib/plsql/schema.rb:117:in 
`method_missing'
  from repo.rb:14

-------

I have working function in oracle as you can see begging of function 
code:

create or replace FUNCTION                GET_NAMES_F
(
   name IN table.name%TYPE,
   sum IN table.sum%TYPE

-----

desc table to see types
NAME     NOT NULL VARCHAR2(200)
VALUE    VARCHAR2(1024)


-----


Please advice how to solve this problem

Regards
beny18241


krzysztof cierpisz wrote:
> On 21 Sep., 16:30, beny 18241 <beny18...@gmail.com> wrote:
> 
>> which dont work as i wanted any idea how make such a script ?
> 
> let's create a table:
> 
> create table test (name varchar2(10),num number);
> insert into test values ('elo',10);
> commit;
> 
> NAME                                    NUM
> ------------------------------   ----------
> elo                                      10
> 
> let's create a function:
> 
> create or replace FUNCTION FUNCTION1(p_ID test.name% TYPE)
> RETURN VARCHAR2 is result VARCHAR2(5);
> BEGIN
>     SELECT name || num into result from Test where name = p_id;
>   RETURN result;
> END FUNCTION1;
> 
> now use great ruby_plsql gem (by rsim)
> 
> require 'rubygems'
> require 'ruby_plsql'
> 
> plsql.connection = OCI8.new("scott","tiger","emeadb11");
> p plsql.function1('elo');
> plsql.logoff
> 
> chris@chris-ub:~/staging/ruby$ ruby tmp1.rb
> "elo10"
> 
> maybe that helps
> chris

-- 
Posted via http://www.ruby-forum.com/.