ruby-oci8 function

B

beny 18241

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
 
K

krzysztof cierpisz

which dont work as i wanted any idea how make such a script ?
Click to expand...

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
 
B

beny 18241

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
 
K

krzysztof cierpisz

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)
Click to expand...

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
 
B

beny 18241

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
 
K

krzysztof cierpisz

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
Click to expand...

please paste the full function creation statement
get_devices_f

chris
 
B

beny 18241

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

pragma autonomous_transaction;

v_returned_cursor types.ref_cursor;

v_name vmpolicy_values.name%TYPE;
v_zal_child VMRELATIONS.CHILD%TYPE;
v_zal_parent VMRELATIONS.PARENT%TYPE;
v_zal_level NUMBER ;
v_pol_sup vmpolicy_values.VALUE%TYPE;
v_create_temp_tb VARCHAR2(2000);
v_input_to_temp VARCHAR2(2000);
v_output_from_temp VARCHAR2(2000);
v_temp_val VARCHAR2(30);
i INTEGER := 0;
j INTEGER := 0;
k INTEGER := 0;
z INTEGER := 0;

t1 NUMBER(10);
t2 NUMBER(10);

TYPE W_VARRAY IS TABLE OF VARCHAR2(20) NOT NULL;

lista1 W_VARRAY := W_VARRAY();
lista2 W_VARRAY := W_VARRAY();
lista3 W_VARRAY := W_VARRAY();
lista4 W_VARRAY := W_VARRAY();

CURSOR cur2 IS
select name
from vmpolicy_values
where policy=v_query_policy_name
and value=v_query_policy_value;

CURSOR cur3 IS
select child, parent, level
from VMRELATIONS
connect by prior child=parent
start with child=v_name;

CURSOR cur4 IS
SELECT value
FROM vmpolicy_values
WHERE policy=v_query_policy_name and name=v_zal_child;

BEGIN
DBMS_OUTPUT.ENABLE(90000000);
t1 := dbms_utility.get_time;
OPEN cur2;
LOOP
FETCH cur2 INTO v_name;
EXIT WHEN cur2%NOTFOUND;

OPEN cur3;
LOOP

FETCH cur3 INTO v_zal_child, v_zal_parent, v_zal_level;
EXIT WHEN cur3%NOTFOUND;
OPEN cur4;
FETCH cur4 INTO v_pol_sup;
IF cur4%NOTFOUND
THEN v_pol_sup := 'NULL';
END IF;
CLOSE cur4;

IF v_pol_sup = v_query_policy_value
THEN
lista1.EXTEND;
i := i + 1;
lista1(i) := v_zal_child;
ELSIF v_pol_sup = 'NULL'
THEN
lista1.EXTEND;
i := i + 1;
lista1(i) := v_zal_child;
ELSE
lista2.EXTEND;
j := j + 1;
lista2(j) := v_zal_child;
END IF;

END LOOP;
CLOSE cur3;

END LOOP;
CLOSE cur2;

FOR m IN 1..lista2.COUNT LOOP

v_name := lista2(m);

OPEN cur3;
LOOP
FETCH cur3 INTO v_zal_child, v_zal_parent, v_zal_level;
EXIT WHEN cur3%NOTFOUND;

lista3.EXTEND;
k := k + 1;
lista3(k) := v_zal_child;
END LOOP;
CLOSE cur3;

END LOOP;

lista4 := lista1 MULTISET EXCEPT lista3;
lista4 := SET(lista4);
DBMS_OUTPUT.PUT_LINE('Total:' || lista4.COUNT); -- number of devices,
comment out this line if needed
FOR n IN 1..lista4.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(lista4(n));
INSERT INTO vmtemp_ruby (name) values (lista4(n));

END LOOP;



OPEN v_returned_cursor FOR
SELECT name
FROM vmtemp_ruby;


t2 := dbms_utility.get_time;

dbms_output.put_line('Total execution time = ' || round((t2-t1)/100,2)
||'ms');


RETURN v_returned_cursor;

END GET_DEVICES_F;
 
K

krzysztof cierpisz

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

pragma autonomous_transaction;

v_returned_cursor types.ref_cursor;

   v_name vmpolicy_values.name%TYPE;
   v_zal_child VMRELATIONS.CHILD%TYPE;
   v_zal_parent VMRELATIONS.PARENT%TYPE;
   v_zal_level  NUMBER ;
   v_pol_sup vmpolicy_values.VALUE%TYPE;
   v_create_temp_tb  VARCHAR2(2000);
   v_input_to_temp  VARCHAR2(2000);
   v_output_from_temp  VARCHAR2(2000);
   v_temp_val VARCHAR2(30);
   i INTEGER := 0;
   j INTEGER := 0;
   k INTEGER := 0;
   z INTEGER := 0;

   t1 NUMBER(10);
   t2 NUMBER(10);

   TYPE W_VARRAY IS TABLE OF VARCHAR2(20) NOT NULL;

   lista1 W_VARRAY := W_VARRAY();
   lista2 W_VARRAY := W_VARRAY();
   lista3 W_VARRAY := W_VARRAY();
   lista4 W_VARRAY := W_VARRAY();

   CURSOR cur2 IS
   select name
   from vmpolicy_values
   where policy=v_query_policy_name
   and value=v_query_policy_value;

   CURSOR cur3 IS
   select child, parent, level
   from VMRELATIONS
   connect by prior child=parent
   start with child=v_name;

   CURSOR cur4 IS
   SELECT value
   FROM vmpolicy_values
   WHERE policy=v_query_policy_name and name=v_zal_child;

BEGIN
   DBMS_OUTPUT.ENABLE(90000000);
t1 := dbms_utility.get_time;
   OPEN cur2;
   LOOP
       FETCH cur2 INTO v_name;
       EXIT WHEN cur2%NOTFOUND;

       OPEN cur3;
       LOOP

           FETCH cur3 INTO v_zal_child, v_zal_parent, v_zal_level;
           EXIT WHEN cur3%NOTFOUND;
               OPEN cur4;
               FETCH cur4 INTO v_pol_sup;
               IF cur4%NOTFOUND
               THEN v_pol_sup := 'NULL';
               END IF;
               CLOSE cur4;

           IF v_pol_sup = v_query_policy_value
           THEN
               lista1.EXTEND;
               i := i + 1;
               lista1(i) := v_zal_child;
           ELSIF v_pol_sup = 'NULL'
           THEN
               lista1.EXTEND;
               i := i + 1;
               lista1(i) := v_zal_child;
           ELSE
               lista2.EXTEND;
               j := j + 1;
               lista2(j) := v_zal_child;
           END IF;

       END LOOP;
       CLOSE cur3;

   END LOOP;
   CLOSE cur2;

   FOR m IN 1..lista2.COUNT LOOP

       v_name := lista2(m);

       OPEN cur3;
       LOOP
           FETCH cur3 INTO v_zal_child, v_zal_parent, v_zal_level;
           EXIT WHEN cur3%NOTFOUND;

           lista3.EXTEND;
           k := k + 1;
           lista3(k) := v_zal_child;
       END LOOP;
       CLOSE cur3;

   END LOOP;

lista4 := lista1 MULTISET EXCEPT lista3;
lista4 := SET(lista4);
   DBMS_OUTPUT.PUT_LINE('Total:' || lista4.COUNT); -- number of devices,
comment out this line if needed
   FOR n IN 1..lista4.COUNT LOOP
   DBMS_OUTPUT.PUT_LINE(lista4(n));
   INSERT INTO vmtemp_ruby (name) values (lista4(n));

   END LOOP;

OPEN v_returned_cursor FOR
SELECT name
FROM vmtemp_ruby;

 t2 := dbms_utility.get_time;

 dbms_output.put_line('Total execution time = ' || round((t2-t1)/100,2)
||'ms');

 RETURN v_returned_cursor;

END GET_DEVICES_F;

----------

cheers
beny18241



please paste the full function creation statement
get_devices_f
Click to expand...
chris
Click to expand...
Click to expand...

based on this blog:
http://blog.rayapps.com/2008/03/15/ruby-plsql-gem-simple-ruby-api-for-plsql-procedures/

Current limitation is that this API support just NUMBER, VARCHAR2,
DATE and TIMESTAMP types for PL/SQL procedures which are dinamically
mapped to Ruby Fixnum/Bignum/Float, String, DateTime and Time types.

you cannot return cursor then.

chris
 
K

KUBO Takehiro

Hi,

This is whole function which i wanted to call by ruby script:

please write how scrip[t should =A0look like to call this function :


-----

create or replace FUNCTION =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0GET_DEVICES_F
(
=A0 v_query_policy_name IN vmpolicy_values.policy%TYPE,
=A0 v_query_policy_value IN vmpolicy_values.value%TYPE
)
RETURN types.ref_cursor AS
Click to expand...

conn =3D OCI8.new(user, password)
cursor1 =3D conn.parse("begin :cursor :=3D 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 =3D cursor1[':cursor'] # Gets the returned cursor.
while row =3D 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 =3D 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] !=3D 0 # non-zero indicates the end of lines.
puts plsql[1]
end

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

beny 18241

Hi,


I verified your code , i need to admin it's ..... awsome :D

Works perfectly as i wanted, many thanks

Regards
(e-mail address removed)


Takehiro said:
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
Click to expand...

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...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

[ANN] ruby-oci8 2.0.3 and 1.0.7 1
[ANN] ruby-oci8 1.0.1 4
[ANN] ruby-oci8 2.0.4 0
[ANN] ruby-oci8 1.0.6 and 2.0.2 2
Confused about OCI8 7
[ANN] ruby-oci8 0.1.10 4
oracle oci8 utf8 corruption 1
ruby problem connecting to oracle using oci8 0

Members online

No members online now.
Total: 210 (members: 0, guests: 210)
Robots: 127

Forum statistics

Threads
474,261
Messages
2,571,040
Members
48,769
Latest member
Clifft

Latest Threads

Top