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 [email protected] 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
