It is possible to create anydatas and anytypes in Oracle. Oracle has also some powerful object oriented features. With this OO features, you may not need to use anytypes.
A simple example shows how to use:
DECLARE
n NUMBER;
v VARCHAR2(16);
d DATE;
TYPE any_data_array_typ IS TABLE OF anydata;
any_data_array any_data_array_typ := any_data_array_typ();
temp_varchar2 VARCHAR2(16);
temp_number NUMBER;
temp_date DATE;
value_result PLS_INTEGER;
type_code PLS_INTEGER;
type_name VARCHAR2(32);
any_type anytype;
BEGIN
n := 1;
v := 'OneTwoThree';
d := SYSDATE;
any_data_array.EXTEND(3);
any_data_array(1) := anydata.ConvertNumber(n);
any_data_array(2) := anydata.ConvertVarchar2(v);
any_data_array(3) := anydata.ConvertDate(d);
FOR i IN any_data_array.FIRST .. any_data_array.LAST LOOP
type_name := any_data_array(i).GetTypeName();
type_code := any_data_array(i).GetType(any_type);
dbms_output.put_line('Iteration #' || i);
dbms_output.put_line('Type Name is ' || type_name);
dbms_output.put_line('Type Code is ' || type_code);
IF type_code = dbms_types.TYPECODE_VARCHAR2 THEN
value_result := any_data_array(i).GetVarchar2(temp_varchar2);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_varchar2);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
ELSIF type_code = dbms_types.TYPECODE_NUMBER THEN
value_result := any_data_array(i).GetNumber(temp_number);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_number);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
ELSIF type_code = dbms_types.TYPECODE_DATE THEN
value_result := any_data_array(i).GetDate(temp_date);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_date);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
END IF;
END LOOP;
END;
Result is:
Iteration #1
Type Name is SYS.NUMBER
Type Code is 2
Value is 1
Iteration #2
Type Name is SYS.VARCHAR2
Type Code is 9
Value is OneTwoThree
Iteration #3
Type Name is SYS.DATE
Type Code is 12
Value is 02/12/2006
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> DECLARE
2 n NUMBER;
3 v VARCHAR2(16);
4 d DATE;
5 TYPE any_data_array_typ IS TABLE OF anydata;
6 any_data_array any_data_array_typ := any_data_array_typ();
7
8 temp_varchar2 VARCHAR2(16);
9 temp_number NUMBER;
10 temp_date DATE;
11 value_result PLS_INTEGER;
12
13 type_code PLS_INTEGER;
14 type_name VARCHAR2(32);
15 any_type anytype;
16 BEGIN
17 n := 1;
18 v := 'OneTwoThree';
19 d := SYSDATE;
20 any_data_array.EXTEND(3);
21 any_data_array(1) := anydata.ConvertNumber(n);
22 any_data_array(2) := anydata.ConvertVarchar2(v);
23 any_data_array(3) := anydata.ConvertDate(d);
24
25 FOR i IN any_data_array.FIRST .. any_data_array.LAST LOOP
26 type_name := any_data_array(i).GetTypeName();
27 type_code := any_data_array(i).GetType(any_type);
28
29 dbms_output.put_line('Iteration #' || i);
30 dbms_output.put_line('Type Name is ' || type_name);
31 dbms_output.put_line('Type Code is ' || type_code);
32
33 IF type_code = dbms_types.TYPECODE_VARCHAR2 THEN
34 value_result := any_data_array(i).GetVarchar2(temp_varchar2);
35 IF value_result = dbms_types.SUCCESS THEN
36 dbms_output.put_line('Value is ' || temp_varchar2);
37 ELSIF value_result = dbms_types.NO_DATA THEN
38 dbms_output.put_line('No value!');
39 END IF;
40 ELSIF type_code = dbms_types.TYPECODE_NUMBER THEN
41 value_result := any_data_array(i).GetNumber(temp_number);
42 IF value_result = dbms_types.SUCCESS THEN
43 dbms_output.put_line('Value is ' || temp_number);
44 ELSIF value_result = dbms_types.NO_DATA THEN
45 dbms_output.put_line('No value!');
46 END IF;
47 ELSIF type_code = dbms_types.TYPECODE_DATE THEN
48 value_result := any_data_array(i).GetDate(temp_date);
49 IF value_result = dbms_types.SUCCESS THEN
50 dbms_output.put_line('Value is ' || temp_date);
51 ELSIF value_result = dbms_types.NO_DATA THEN
52 dbms_output.put_line('No value!');
53 END IF;
54 END IF;
55
56 END LOOP;
57
58 END;
59 /
Iteration #1
Type Name is SYS.NUMBER
Type Code is 2
Value is 1
Iteration #2
Type Name is SYS.VARCHAR2
Type Code is 9
Value is OneTwoThree
Iteration #3
Type Name is SYS.DATE
Type Code is 12
Value is 04/12/2006
PL/SQL procedure successfully completed
SQL>
A simple example shows how to use:
DECLARE
n NUMBER;
v VARCHAR2(16);
d DATE;
TYPE any_data_array_typ IS TABLE OF anydata;
any_data_array any_data_array_typ := any_data_array_typ();
temp_varchar2 VARCHAR2(16);
temp_number NUMBER;
temp_date DATE;
value_result PLS_INTEGER;
type_code PLS_INTEGER;
type_name VARCHAR2(32);
any_type anytype;
BEGIN
n := 1;
v := 'OneTwoThree';
d := SYSDATE;
any_data_array.EXTEND(3);
any_data_array(1) := anydata.ConvertNumber(n);
any_data_array(2) := anydata.ConvertVarchar2(v);
any_data_array(3) := anydata.ConvertDate(d);
FOR i IN any_data_array.FIRST .. any_data_array.LAST LOOP
type_name := any_data_array(i).GetTypeName();
type_code := any_data_array(i).GetType(any_type);
dbms_output.put_line('Iteration #' || i);
dbms_output.put_line('Type Name is ' || type_name);
dbms_output.put_line('Type Code is ' || type_code);
IF type_code = dbms_types.TYPECODE_VARCHAR2 THEN
value_result := any_data_array(i).GetVarchar2(temp_varchar2);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_varchar2);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
ELSIF type_code = dbms_types.TYPECODE_NUMBER THEN
value_result := any_data_array(i).GetNumber(temp_number);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_number);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
ELSIF type_code = dbms_types.TYPECODE_DATE THEN
value_result := any_data_array(i).GetDate(temp_date);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_date);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
END IF;
END LOOP;
END;
Result is:
Iteration #1
Type Name is SYS.NUMBER
Type Code is 2
Value is 1
Iteration #2
Type Name is SYS.VARCHAR2
Type Code is 9
Value is OneTwoThree
Iteration #3
Type Name is SYS.DATE
Type Code is 12
Value is 02/12/2006
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> DECLARE
2 n NUMBER;
3 v VARCHAR2(16);
4 d DATE;
5 TYPE any_data_array_typ IS TABLE OF anydata;
6 any_data_array any_data_array_typ := any_data_array_typ();
7
8 temp_varchar2 VARCHAR2(16);
9 temp_number NUMBER;
10 temp_date DATE;
11 value_result PLS_INTEGER;
12
13 type_code PLS_INTEGER;
14 type_name VARCHAR2(32);
15 any_type anytype;
16 BEGIN
17 n := 1;
18 v := 'OneTwoThree';
19 d := SYSDATE;
20 any_data_array.EXTEND(3);
21 any_data_array(1) := anydata.ConvertNumber(n);
22 any_data_array(2) := anydata.ConvertVarchar2(v);
23 any_data_array(3) := anydata.ConvertDate(d);
24
25 FOR i IN any_data_array.FIRST .. any_data_array.LAST LOOP
26 type_name := any_data_array(i).GetTypeName();
27 type_code := any_data_array(i).GetType(any_type);
28
29 dbms_output.put_line('Iteration #' || i);
30 dbms_output.put_line('Type Name is ' || type_name);
31 dbms_output.put_line('Type Code is ' || type_code);
32
33 IF type_code = dbms_types.TYPECODE_VARCHAR2 THEN
34 value_result := any_data_array(i).GetVarchar2(temp_varchar2);
35 IF value_result = dbms_types.SUCCESS THEN
36 dbms_output.put_line('Value is ' || temp_varchar2);
37 ELSIF value_result = dbms_types.NO_DATA THEN
38 dbms_output.put_line('No value!');
39 END IF;
40 ELSIF type_code = dbms_types.TYPECODE_NUMBER THEN
41 value_result := any_data_array(i).GetNumber(temp_number);
42 IF value_result = dbms_types.SUCCESS THEN
43 dbms_output.put_line('Value is ' || temp_number);
44 ELSIF value_result = dbms_types.NO_DATA THEN
45 dbms_output.put_line('No value!');
46 END IF;
47 ELSIF type_code = dbms_types.TYPECODE_DATE THEN
48 value_result := any_data_array(i).GetDate(temp_date);
49 IF value_result = dbms_types.SUCCESS THEN
50 dbms_output.put_line('Value is ' || temp_date);
51 ELSIF value_result = dbms_types.NO_DATA THEN
52 dbms_output.put_line('No value!');
53 END IF;
54 END IF;
55
56 END LOOP;
57
58 END;
59 /
Iteration #1
Type Name is SYS.NUMBER
Type Code is 2
Value is 1
Iteration #2
Type Name is SYS.VARCHAR2
Type Code is 9
Value is OneTwoThree
Iteration #3
Type Name is SYS.DATE
Type Code is 12
Value is 04/12/2006
PL/SQL procedure successfully completed
SQL>
No comments:
Post a Comment