Query Oracle 26ai Object Tables: Practical SQL Examples

An Oracle object table stores rows whose declared structure comes from a SQL object type. You can select its scalar attributes with familiar SQL: name the attributes in SELECT, identify the table in FROM, and restrict the rows with WHERE. You do not need a special object-navigation operator simply to retrieve a name or identifier.

This lesson develops that first querying technique in Oracle AI Database 26ai. It uses a small animal dataset so every query has defined prerequisites and a predictable purpose. The same pattern applies to a customer object table whose type declares cust_id, first_name and last_name. Those attribute names must actually exist in the type; SQL does not infer them from the table's name.

Keep the result shape in mind. Selecting three scalar attributes returns three ordinary result columns. Selecting an entire object is a different projection, introduced briefly at the end. A relational table can also contain an object-valued column, which adds a column name to the attribute path. Recognizing these two storage arrangements prevents many confusing dot-notation errors.

SELECT a.animal_id, a.name, a.breed
FROM animal a
WHERE a.breed = 'ALLIGATOR'
ORDER BY a.animal_id;

Read this as a complete query, not as a sequence of procedural instructions to the database. The optimizer chooses an execution plan that preserves SQL semantics. The aliases and predicates describe the result you want, rather than promising a particular access path or the order in which Oracle evaluates expressions.

Run the following definitions in a practice schema where these names are unused. You need privileges to create types and tables, plus appropriate tablespace quota. These examples and their expected results were checked against Oracle documentation, but were not executed against an Oracle instance. Do not run CREATE OR REPLACE indiscriminately against shared types with dependent tables.

CREATE TYPE animal_location_ty AS OBJECT (
  city       VARCHAR2(40 CHAR),
  enclosure  VARCHAR2(20 CHAR)
);
/
CREATE TYPE animal_ty AS OBJECT (
  animal_id  NUMBER,
  name       VARCHAR2(30 CHAR),
  breed      VARCHAR2(30 CHAR),
  location   animal_location_ty
);
/
CREATE TABLE animal OF animal_ty (
  animal_id PRIMARY KEY
);

INSERT INTO animal VALUES (
  animal_ty(1, 'FRANCES', 'CAT',
            animal_location_ty('ORLANDO', 'A1'))
);
INSERT INTO animal VALUES (
  animal_ty(2, 'BENJI', 'DOG',
            animal_location_ty('ORLANDO', 'B1'))
);
INSERT INTO animal VALUES (
  animal_ty(3, 'LYLE', 'ALLIGATOR',
            animal_location_ty('MIAMI', 'C1'))
);
COMMIT;

The location type is created first because animal_ty uses it as an attribute datatype. The table then stores row objects of animal_ty. Each constructor supplies values in the attribute order declared by its type. An object type defines structure; its definition alone neither stores these three animals nor creates the table.

The slash submits a type definition in script-oriented clients such as SQL*Plus. It is a client command, not part of the CREATE TYPE SQL sent through a database driver. Follow your client's execution mode when running the script. The INSERT statements use SQL constructors and the final COMMIT makes the inserted practice data permanent in this transaction.

The primary key belongs to the table definition and makes animal_id unique and non-null there. Other attribute declarations do not establish additional business rules automatically. For example, the type's breed attribute permits a null value unless the table has an applicable constraint. Decide which integrity rules belong in a real application rather than inferring them from descriptive attribute names.

SELECT a.name
FROM animal a
WHERE a.breed = 'ALLIGATOR'
ORDER BY a.name, a.animal_id;

The expected name is LYLE. A string literal belongs in single quotes. For an application parameter, bind the supplied breed value instead of building SQL by concatenating user input. A parameterized version can use WHERE a.breed = :wanted_breed when the execution environment supplies that bind variable.

Bind syntax is not a complete standalone script: the client or application must define and supply the bind. The literal query above remains the self-contained teaching example. Separating these two forms avoids presenting an unexplained placeholder as if it were a value already stored in the database.

WHERE and SELECT answer different questions. You can filter on breed while returning only name. You can likewise order by animal_id without including it in this simple SELECT list. For grouped or distinct queries, inspect the applicable SQL restrictions rather than transferring every rule from a basic projection without checking.

Without ORDER BY, the apparent insertion order of these rows is not a reporting guarantee. A different execution plan or additional data can change the order you observe. For stable pagination or reproducible exercises, choose a complete ordering with a unique tie-breaker. This is a SQL result requirement, not a special property of objects.

A client SELECT returns a result set. A scalar SELECT INTO in PL/SQL instead assigns one row to variables. Its contract matters: zero rows raises NO_DATA_FOUND, and multiple rows raises TOO_MANY_ROWS. Handle those outcomes according to the application's requirement rather than treating them as ordinary empty result sets.

SET SERVEROUTPUT ON
DECLARE
  l_name animal.name%TYPE;
BEGIN
  SELECT a.name INTO l_name
  FROM animal a
  WHERE a.animal_id = 3;

  DBMS_OUTPUT.PUT_LINE('Animal: ' || l_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No animal has that identifier.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('The lookup returned multiple animals.');
END;
/

The practice data illustrates Animal: LYLE. Changing the identifier to 999 exercises the missing-row branch. The primary key prevents multiple matches for this particular identifier lookup; the second handler shows the separate cardinality outcome if a future query uses a nonunique criterion.

There is no need to test SQL%FOUND after this successful scalar lookup. If it returned normally, one row was assigned. If it found no row, execution already transferred to the exception handler. Adding IF SQL%NOTFOUND immediately after the SELECT does not stop that exception. The handler should reflect a business outcome; unexpected database errors should not be disguised as a missing animal.

Process several rows with a cursor FOR loop

BEGIN
  FOR r IN (
    SELECT a.animal_id, a.name
    FROM animal a
    ORDER BY a.animal_id
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.animal_id || ': ' || r.name);
  END LOOP;
END;
/

The loop illustrates three output lines in identifier order. It manages the query cursor and supplies a record for each iteration. An empty result executes the body zero times. Use this shape when several rows are expected, rather than weakening a scalar lookup until its multiple-row exception disappears.

For a task expressible as one SQL statement, a procedural loop is not automatically the best implementation. This example teaches row processing and output, not a performance recommendation. Do not use SQL%ROWCOUNT to count the loop's query rows; count iterations explicitly if the application needs that total.

Read implicit cursor status after object-table DML

Before any relevant implicit operation, FOUND, NOTFOUND and ROWCOUNT are null. Subsequent SQL, including SQL executed inside a called routine, can replace the status being observed. Capture what you need immediately after the target statement. Explicit named cursors have their own cursor_name%attribute interface.

DECLARE
  l_rows PLS_INTEGER;
BEGIN
  UPDATE animal a
  SET a.breed = 'CROCODILIAN'
  WHERE a.animal_id = 3;
  l_rows := SQL%ROWCOUNT;

  IF l_rows = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No matching animal.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || l_rows);
  END IF;
END;
/
ROLLBACK;

This optional continuation illustrates one updated row. The rollback restores the committed ALLIGATOR value, provided the exercise runs in the same session without intervening commits. Rows affected are not evidence that a transaction committed. An update can count a matching row even if the assigned value equals its existing value.

After a multiple-row scalar SELECT INTO error, SQL%ROWCOUNT is not the number of matching rows. Do not use it to diagnose the dataset's cardinality. Choose a separate query or a multiple-row processing interface when that count is part of the requirement.