MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Use an object in a query?

with 2 comments

Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:

SELECT	 instance.get_type()
         *
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier

The problem is how Oracle treats runtime views, which appears to me as a casting error. Somewhat like the ORDER BY clause irregularity that I noted in July, the trick is complete versus incomplete syntax. The following query fails and generates the foregoing error:

1
2
3
4

SELECT instance.get_type() AS object_type
,      instance.to_string() AS object_content
FROM  (SELECT dependent()AS instance
       FROM   dual);

If you add a table alias, or name, to the runtime view on line 4, it works fine:

1
2
3
4

SELECT cte.instance.get_type() AS object_type
,      cte.instance.to_string() AS object_content
FROM  (SELECT dependent() AS instance
       FROM   dual) cte;

That is the trick. You use an alias for the query, which assigns the alias like a table reference. The reference lets you access instance methods in the scope of a query. Different columns in the query’s SELECT-list may return different results from different methods from the same instance of the object type.

You can also raise an exception if you forget the open and close parentheses for a method call to a UDT, which differs from how Oracle treats no argument functions and procedures. That type of error would look like this:

SELECT cte.instance.get_type AS object_type
       *
ERROR AT line 1:
ORA-00904: : invalid identifier

It is an invalid identifier because there’s no public variable get_type, and a method is only found by using the parenthesis and a list of parameters where they’re required.

The object source code is visible by clicking on the expandable label below.

Setup Object Types ↓

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

DROP TYPE dependent_t;
DROP TYPE base_t;
 
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Create an object.
CREATE OR REPLACE TYPE base_t IS OBJECT
( TYPE       VARCHAR2(20)
, CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT
, MEMBER FUNCTION get_type RETURN VARCHAR2
, MEMBER FUNCTION to_string RETURN VARCHAR2) 
INSTANTIABLE NOT FINAL;
/
 
-- Create an object body.
CREATE OR REPLACE TYPE BODY base_t IS
CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT IS
BEGIN
  RETURN;
END base_t;
MEMBER FUNCTION get_type RETURN VARCHAR2 IS
BEGIN
  RETURN self.TYPE;
END;
MEMBER FUNCTION to_string RETURN VARCHAR2 IS
BEGIN
  RETURN self.TYPE;
END to_string;
END;
/
 
-- Create a subtype.
CREATE OR REPLACE TYPE dependent UNDER base_t
( child  VARCHAR2(40)
, CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION dependent
  ( child  VARCHAR2 ) RETURN SELF AS RESULT
, OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2
, OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) 
INSTANTIABLE NOT FINAL;
/
 
-- Create a subtype body.
CREATE OR REPLACE TYPE BODY dependent IS
  CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT IS
  BEGIN
    /* Assign subtype name to type. */
    self.TYPE := 'DEPENDENT';
    RETURN;
  END dependent;
  CONSTRUCTOR FUNCTION dependent
  ( child  VARCHAR2 ) RETURN SELF AS RESULT IS
    lv_dependent DEPENDENT := dependent();
  BEGIN
    /* Assign default constructor to self instance. */
    self := lv_dependent;
    /* Assign parameters to object instance. */
    self.TYPE  := 'DEPENDENT';
    self.child := child;
    RETURN;
  END dependent;
  OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2 IS
  BEGIN
    RETURN self.TYPE;
  END;
  OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN self.TYPE;
  END to_string;
END;
/

As always, I hope this helps those solving problems.

Written by maclochlainn

August 22nd, 2015 at 5:23 pm

Posted in Objects,Oracle,Oracle 11g,Oracle 12c,Oracle XE,pl/sql,sql

Tagged with ,

«
»