Repository navigation
Expand file tree
/
Copy pathanyObject.sql
More file actions
144 lines (128 loc) · 4.38 KB
/
Copy pathanyObject.sql
File metadata and controls
144 lines (128 loc) · 4.38 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
/*
| IN THIS SCRIPT:
| create_a_type -- returns a transient object type
| create_an_instance -- returns an instance of the type
| grok_anydata -- shows how one might interpret the instance
| anon block -- demo of these pieces working together
*/
/*
| Function create_a_type creates a simple transient type with a couple
| of attributes. It returns a type descriptor object of type ANYTYPE.
| Note: Oracle9i Release 1 uses SYS.ANYTYPE and SYS.ANYDATA, but
| Release 2 supports synonyms on object types, so we can drop the "SYS"
| prefix.
*/
CREATE OR REPLACE FUNCTION create_a_type
RETURN ANYTYPE
AS
myany ANYDATA;
mytype ANYTYPE;
BEGIN
/* Create (anonymous) transient type with two attributes: number, date */
ANYTYPE.BeginCreate(typecode => DBMS_TYPES.TYPECODE_OBJECT, atype => mytype);
mytype.AddAttr(typecode => DBMS_TYPES.TYPECODE_NUMBER, aname => 'just_a_number',
prec => 38, scale => 0, len => NULL, csid => NULL, csfrm => NULL);
mytype.AddAttr(typecode => DBMS_TYPES.TYPECODE_DATE, aname => 'just_a_date',
prec => NULL, scale => NULL, len => NULL, csid => NULL, csfrm => NULL);
mytype.EndCreate;
RETURN mytype;
END;
/
SHOW ERRORS
/*
| Here is an example of how to invoke create_a_type and instantiate a
| corresponding ANYDATA. This assumes that you happen to know the
| attributes of the transient type output by create_a_type.
*/
CREATE OR REPLACE FUNCTION create_an_instance (which_type IN ANYTYPE,
att1 IN NUMBER DEFAULT 0, att2 IN DATE DEFAULT SYSDATE)
RETURN ANYDATA
AS
l_type ANYTYPE := which_type;
l_any ANYDATA;
BEGIN
ANYDATA.BeginCreate(dtype => l_type, adata => l_any);
l_any.SetNumber(num => att1);
l_any.SetDate(dat => att2);
l_any.EndCreate;
RETURN l_any;
END;
/
SHOW ERRORS
/*
| If you have an ANYDATA and its ANYTYPE, you could do something like this
| next procedure, grok_anydata, to attempt to interpret the data.
*/
CREATE OR REPLACE PROCEDURE grok_anydata (which_type IN ANYTYPE,
which_data IN ANYDATA)
AS
ltype ANYTYPE := which_type;
lany ANYDATA := which_data;
typeid PLS_INTEGER;
attr_typeid PLS_INTEGER;
lattr_elt_type ANYTYPE;
lprec PLS_INTEGER;
lscale PLS_INTEGER;
llen PLS_INTEGER;
lcsid PLS_INTEGER;
lcsfrm PLS_INTEGER;
lschema_name VARCHAR2(30);
ltype_name VARCHAR2(30);
lversion VARCHAR2(30);
lcount PLS_INTEGER;
laname VARCHAR2(30);
result_code PLS_INTEGER;
some_number NUMBER;
some_string VARCHAR2(32767);
some_date DATE;
BEGIN
/* Discover the type code of a transient object */
typeid := lany.GetType(typ => ltype);
/* For an object type, lcount will give the number of attrs */
typeid := ltype.GetInfo (lprec, lscale, llen, lcsid, lcsfrm, lschema_name,
ltype_name, lversion, lcount);
lany.PieceWise;
FOR pos IN 1..lcount
LOOP
attr_typeid := ltype.GetAttrElemInfo(pos, lprec, lscale, llen, lcsid,
lcsfrm, lattr_elt_type, laname);
DBMS_OUTPUT.PUT_LINE('Attribute ' || pos || ': ' || laname
|| ' (type ' || attr_typeid || ')');
/* This CASE statement is incomplete -- need to deal with a lot more
|| types, possibly collections and object types
*/
CASE attr_typeid
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
result_code := lany.GetNumber(some_number);
DBMS_OUTPUT.PUT_LINE(some_number);
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
result_code := lany.GetVarchar2(some_string);
DBMS_OUTPUT.PUT_LINE(some_string);
WHEN DBMS_TYPES.TYPECODE_DATE THEN
result_code := lany.GetDate(some_date);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(some_date, 'YYYY-MM-DD hh24:mi:ss'));
ELSE
NULL;
END CASE;
END LOOP;
END;
/
SHOW ERR
/*
| So, finally, here is what it might look like in action. Well, it's really
| a contrived example, because obviously I know the structure of the type
| in advance. But you get the idea!
*/
DECLARE
l_type ANYTYPE := create_a_type;
l_any ANYDATA := create_an_instance(l_type, 3.14159, SYSDATE);
BEGIN
grok_anydata(l_type, l_any);
END;
/
/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc.
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/