From: beny 18241 <beny18241@...>
Date: 2009-09-22T19:58:28+09:00
Subject: Re: ruby-oci8 function


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 via http://www.ruby-forum.com/.