From: krzysztof cierpisz <ciapecki@...>
Date: 2009-09-22T20:30:06+09:00
Subject: Re: ruby-oci8 function

On Sep 22, 12:58 pm, beny 18241 <beny18...@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...@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