ruby-oci8 function

Prev: Writing to STDOUT after closing controlling terminal
Next: Ordering XML Attributes with Hpricot?

From: krzysztof cierpisz on 22 Sep 2009 06:46

---

On Sep 22, 11:46 am, beny 18241 <beny18...(a)gmail.com> wrote:
> 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

please paste the full function creation statement
get_devices_f

chris

From: beny 18241 on 22 Sep 2009 06:58

---

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


krzysztof cierpisz wrote:
> On Sep 22, 11:46�am, beny 18241 <beny18...(a)gmail.com> wrote:
>> � � � � VQPN := 'MY_NAME';
>> � � � � VQPV := 'MY_SUM';
>>
>> � � � VC := get_devices_f(
>> � � � name �=> VQPN,
>> � � � sum => VQPV);
>>
>> � DBMS_OUTPUT.PUT_LINE(vqpn || vqpv);
>> END;
>>
>> please help
>
> please paste the full function creation statement
> get_devices_f
>
> chris

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

From: krzysztof cierpisz on 22 Sep 2009 07:29

---

On Sep 22, 12:58 pm, beny 18241 <beny18...(a)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
>
> 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
>
>
>
> krzysztof cierpisz wrote:
> > On Sep 22, 11:46 am, beny 18241 <beny18...(a)gmail.com> wrote:
> >> VQPN := 'MY_NAME';
> >> VQPV := 'MY_SUM';
>
> >> VC := get_devices_f(
> >> name => VQPN,
> >> sum => VQPV);
>
> >> DBMS_OUTPUT.PUT_LINE(vqpn || vqpv);
> >> END;
>
> >> please help
>
> > please paste the full function creation statement
> > get_devices_f
>
> > chris
>
> --
> Posted viahttp://www.ruby-forum.com/.

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

From: KUBO Takehiro on 22 Sep 2009 08:53

---

On Tue, Sep 22, 2009 at 7:58 PM, beny 18241 <beny18241(a)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...

From: beny 18241 on 22 Sep 2009 10:02

---

Hi,


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

Works perfectly as i wanted, many thanks

Regards
beny18241(a)gmail.com


Takehiro Kubo wrote:
> On Tue, Sep 22, 2009 at 7:58 PM, beny 18241 <beny18241(a)gmail.com> wrote:
>> 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...

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

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