Prev: Writing to STDOUT after closing controlling terminal
Next: Ordering XML Attributes with Hpricot?
From: beny 18241 on 21 Sep 2009 10:30
Hi,

I have few oracle functions for example:


-----
create or replace FUNCTION FUNCTION1(p_ID test.name% TYPE)
RETURN VARCHAR2 is result VARCHAR2(5);
BEGIN
SELECT name || number into result from Test where name = p_id;
RETURN result;
END FUNCTION1;
------

i have ruby-oci8 (2.0.2).

I wanted to write script which call this function.


-----
#!/usr/bin/ruby
require 'oci8'

OCI8.new("user", "password", '//localhost/xe').exec("DECLARE
P_ID VARCHAR2(4000);
v_Return VARCHAR2(200);
BEGIN
P_ID := 'my value';

v_Return := FUNCTION1(
P_ID => P_ID
);
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
")do |r| puts r.join(",");end
------


which dont work as i wanted any idea how make such a script ?


Please help
beny18241
--
Posted via http://www.ruby-forum.com/.

From: krzysztof cierpisz on 21 Sep 2009 10:58
On 21 Sep., 16:30, beny 18241 <beny18...(a)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(a)chris-ub:~/staging/ruby$ ruby tmp1.rb
"elo10"

maybe that helps
chris
From: beny 18241 on 22 Sep 2009 03:33
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...(a)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(a)chris-ub:~/staging/ruby$ ruby tmp1.rb
> "elo10"
>
> maybe that helps
> chris

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

From: krzysztof cierpisz on 22 Sep 2009 05:13
On Sep 22, 9:33 am, beny 18241 <beny18...(a)gmail.com> wrote:
> 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)
>
> -----
>

does this work when you're logged into your database?

select get_names_f('value1' , 'value2') from dual;

if yes please paste the full function creation statement

chris
From: beny 18241 on 22 Sep 2009 05:46
Hi,

its workiong when i run this way


SET SERVEROUTPUT ON
DECLARE
VQPN VARCHAR(200);
VQPV VARCHAR2(1024);
VC types.ref_cursor;
BEGIN
VQPN := 'MY_NAME';
VQPV := 'MY_SUM';

VC := get_devices_f(
name => VQPN,
sum => VQPV);

DBMS_OUTPUT.PUT_LINE(vqpn || vqpv);
END;


please help


krzysztof cierpisz wrote:
> On Sep 22, 9:33�am, beny 18241 <beny18...(a)gmail.com> wrote:
>>
>> stmt.c:539:in oci8lib.so: ORA-06550: line 2, column 12: (OCIError)
>> /var/lib/gems/1.8/gems/ruby-plsql-0.3.1/lib/plsql/procedure.rb:186:in
>>
>>
>> -----
>>
>
> does this work when you're logged into your database?
>
> select get_names_f('value1' , 'value2') from dual;
>
> if yes please paste the full function creation statement
>
> chris

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

 |  Next  |  Last
Pages: 1 2
Prev: Writing to STDOUT after closing controlling terminal
Next: Ordering XML Attributes with Hpricot?