DBA Hub

📋Steps in this guide1/3

Oracle ANYDATA Type

This article presents an overview of the ANYDATA type.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

SQL

The type includes a number of member functions to manipulate the contents from SQL and PL/SQL. Create the following table to run the examples below. The type includes constructor functions for the majority of Oracle data types that can be accessed from SQL. The code below uses some of them, but a full list can be found here . The function displays the type of data contained in a specific type. This can be used to determine how to process the data. The following example uses a combination of the and functions to process and output the data stored in the table. A full list of supported functions can be found here .

Code/Command (click line numbers to comment):

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
DROP TABLE anydata_test;

CREATE TABLE anydata_test (
  id      NUMBER,
  content SYS.ANYDATA
);

INSERT INTO anydata_test (id, content) VALUES (1, SYS.ANYDATA.convertVarchar2('This is some data'));
INSERT INTO anydata_test (id, content) VALUES (2, SYS.ANYDATA.convertNumber(1234567890));
INSERT INTO anydata_test (id, content) VALUES (3, SYS.ANYDATA.convertDate(TO_DATE('01-JAN-2012','DD-MON-YYYY')));
COMMIT;

COLUMN type_name FORMAT A20

SELECT id,
       SYS.ANYDATA.getTypeName(content) AS type_name
FROM   anydata_test
ORDER BY id;

        ID TYPE_NAME
---------- --------------------
         1 SYS.VARCHAR2
         2 SYS.NUMBER
         3 SYS.DATE

3 rows selected.

SQL>

COLUMN content FORMAT A20

SELECT id,
       (CASE SYS.ANYDATA.getTypeName(content)
          WHEN 'SYS.VARCHAR2' THEN SYS.ANYDATA.accessVarchar2(content)
          WHEN 'SYS.NUMBER'   THEN TO_CHAR(SYS.ANYDATA.accessNumber(content))
          WHEN 'SYS.DATE'     THEN TO_CHAR(SYS.ANYDATA.accessDate(content), 'DD-MON-YYYY')
        END) AS content
FROM   anydata_test;

        ID CONTENT
---------- --------------------
         1 This is some data
         2 1234567890
         3 01-JAN-2012

3 rows selected.

SQL>
2

PL/SQL

The , and functions are also available from PL/SQL. Since the following example doesn't persist anything to the database, it also includes BLOB and CLOB conversions. From PL/SQL we can also use the functions to pull the data out of an type into a variable. The full list of available functions can be found here .

Code/Command (click line numbers to comment):

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
SET SERVEROUTPUT ON

DECLARE
  l_varchar2 VARCHAR2(50) := 'This is some data';
  l_number   NUMBER       := 1234567890;
  l_date     DATE         := TO_DATE('01-JAN-2012','DD-MON-YYYY');
  l_clob     CLOB         := 'This is some CLOB data';
  l_blob     BLOB         := UTL_RAW.cast_to_raw('This is some BLOB data');
  l_anydata  SYS.ANYDATA;
BEGIN
  -- Convert VARCHAR2 to ANYDATA and back.
  l_anydata  := SYS.ANYDATA.convertVarchar2(l_varchar2);
  l_varchar2 := SYS.ANYDATA.accessVarchar2(l_anydata);
  DBMS_OUTPUT.put_line('VARCHAR2: ' || l_varchar2);

  -- Convert NUMBER to ANYDATA and back.
  l_anydata  := SYS.ANYDATA.convertNumber(l_number);
  l_number   := SYS.ANYDATA.accessNumber(l_anydata);
  DBMS_OUTPUT.put_line('NUMBER  : ' || TO_CHAR(l_number));

  -- Convert DATE to ANYDATA and back.
  l_anydata  := SYS.ANYDATA.convertDate(l_date);
  l_date     := SYS.ANYDATA.accessDate(l_anydata);
  DBMS_OUTPUT.put_line('DATE    : ' || TO_CHAR(l_date, 'DD-MON-YYYY'));

  -- Convert ANYDATA to CLOB
  l_anydata  := SYS.ANYDATA.convertClob(l_clob);
  l_clob     := SYS.ANYDATA.accessClob(l_anydata);
  DBMS_OUTPUT.put_line('CLOB    : ' || l_clob);

  -- Convert ANYDATA to BLOB
  l_anydata  := SYS.ANYDATA.convertBlob(l_blob);
  l_blob     := SYS.ANYDATA.accessBlob(l_anydata);
  DBMS_OUTPUT.put_line('BLOB    : ' || UTL_RAW.cast_to_varchar2(l_blob));
END;
/
VARCHAR2: This is some data
NUMBER  : 1234567890
DATE    : 01-JAN-2012
CLOB    : This is some CLOB data
BLOB    : This is some BLOB data

PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON

DECLARE
  l_varchar2 VARCHAR2(50) := 'This is some data';
  l_anydata  SYS.ANYDATA;
BEGIN
  -- Convert VARCHAR2 to ANYDATA and back.
  l_anydata  := SYS.ANYDATA.convertVarchar2(l_varchar2);
  
  IF l_anydata.getVarchar2(l_varchar2) = DBMS_TYPES.SUCCESS THEN
    DBMS_OUTPUT.put_line('VARCHAR2: ' || l_varchar2);
  END IF;
END;
/
VARCHAR2: This is some data

PL/SQL procedure successfully completed.

SQL>
3

Complex Types

Complex object types and user-defined objects types can also be serialized as types. This is done using the and functions. For more information see: - ANYDATA TYPE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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
CREATE OR REPLACE TYPE t_my_type AS OBJECT (
  g_val1 VARCHAR2(10),
  g_val2 VARCHAR2(10)
);
/

SET SERVEROUTPUT ON

DECLARE
  l_obj     t_my_type := t_my_type('1111111111', '2222222222');
  l_anydata SYS.ANYDATA;
BEGIN
  -- Convert Object to ANYDATA and back.
  l_anydata := SYS.ANYDATA.convertObject(l_obj);
  
  IF l_anydata.getObject(l_obj) = DBMS_TYPES.SUCCESS THEN
    DBMS_OUTPUT.put_line('T_MY_TYPE : ' || l_obj.g_val1 || ' : ' || l_obj.g_val2);
  END IF;
END;
/
T_MY_TYPE : 1111111111 : 2222222222

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!