NAME
DBIO::Oracle::DDL - Generate Oracle DDL from DBIO Result classes
VERSION
version 0.900007
DESCRIPTION
DBIO::Oracle::DDL generates Oracle DDL from the DBIO schema class hierarchy. It is what DBIO::Oracle::Deploy runs as its install.
my $ddl = DBIO::Oracle::DDL->install_ddl($schema);
# CREATE TABLE ...; CREATE SEQUENCE ...; CREATE INDEX ...;
METHODS
install_ddl
my $ddl = DBIO::Oracle::DDL->install_ddl($schema);
Returns the full installation DDL as a single string. $schema may be a connected schema instance or a schema class name.
declared_objects
my $objects = DBIO::Oracle::DDL->declared_objects($schema);
# { table => [...], view => [...], sequence => [...], trigger => [...],
# index => [ [ name, table ], ... ],
# schema => { OWNX => { table => [...], sequence => [...], ... } } }
The names, as the dictionary holds them, of the objects "install_ddl" writes for $schema, taken while the statements are built, so that a generated name (the shortened sequence and trigger names) is the one in the statements and not a second computation of it. A name written unquoted (a literal table name, \'cd') is given in upper case, as Oracle folds it. Throws what "install_ddl" throws for a declaration it refuses. The caller that needs to know what an install created (the cleanup of DBIO::Oracle::Test::Conformance) reads it here.
The objects of a table in another schema (a source OWNX.item) are not in the lists above but under schema, by schema and then by type, with the bare names (the index pairs hold the bare table too).
foreign_schemas
my @schemas = DBIO::Oracle::DDL->foreign_schemas($schema);
The schemas (users) that the table names of the install qualify, as they spell them, sorted: the OWNX of a source OWNX.item. Empty when no table or view is schema-qualified. "install" in DBIO::Oracle::Deploy asks the server for each of them before the first statement. Throws what "install_ddl" throws for a declaration it refuses.
INDEX DECLARATIONS
The canonical index declarations of a result source (DBIO::Cake idx, indices, ->indices of DBIO::ResultSourceProxy; ADR 0061 in core) are written as CREATE [UNIQUE] INDEX name ON table (columns); after the table. The declared name is used as given. A declaration of the same name as a unique constraint, or over exactly the columns of one (in that order), is refused with an exception naming both, because Oracle holds one index there (ORA-01408) and neither would be the declared one; declare the key once. A function-based index (expression) is not on the columns.
The oracle block takes one key: expression, a function-based index, whose text stands in the parentheses instead of the column list. It is the user's own SQL and is not quoted for them: a column spelled in lower case has to be written "quoted" in it (LOWER("title")), because the table is created with quoted names. There is no where: Oracle has no partial index, and the key is refused as unknown. A type other than unique and any options are refused with an exception, never written as a plainer index.
IDENTIFIERS, AUTO-INCREMENT, FOREIGN KEYS
Every table, column, sequence, trigger, constraint and index name is written double-quoted, with the spelling of the result class. The storage quotes with sql_quote_char " and so asks the dictionary for that spelling; an unquoted name would have been folded to upper case, and the schema that was just installed could not read it (ORA-00942). A table given as a literal scalar reference (\'cd') is the user's own SQL and stays as given, as the storage writes it.
Because the names are quoted, a view_definition and any literal SQL (a function-based index expression, a literal table name) must quote a lower-case or mixed-case name itself: SELECT "title" FROM "cd" -- unquoted Oracle reads the name as upper case and finds nothing (ORA-00942).
A column marked is_auto_increment gets CREATE SEQUENCE table_column_seq and a BEFORE INSERT trigger table_column_trg, WHEN (new.col IS NULL), that fills the column with NEXTVAL (an explicit value is left alone). The storage finds the sequence of such a column by reading exactly that trigger in ALL_TRIGGERS, so create without RETURNING can read the key back. The CREATE TRIGGER text ends in END;; core's splitter, told the dialect oracle by _ddl_sql_dialect of DBIO::Oracle::Storage, keeps that semicolon when the statement is run.
Foreign keys come last, one ALTER TABLE ... ADD CONSTRAINT fk_table_columns_target FOREIGN KEY (...) REFERENCES ... each, after every CREATE TABLE (a table may refer to one that sorts after it, or to itself). They are read from the relationships marked is_foreign_key_constraint (belongs_to sets it by default); ON DELETE and DEFERRABLE follow them (below). A relationship whose condition is not plain column pairs, whose target is not a table of the deploy, or whose target columns are neither the target's primary key nor one of its unique constraints yields no constraint. Names are shortened to Oracle's 30 characters like every generated name.
ON DELETE, ON UPDATE, DEFERRABLE
The on_delete, on_update and is_deferrable attributes of the belongs_to are written as declared, as the other drivers do:
__PACKAGE__->belongs_to(owner => 'MyApp::DB::Result::Owner', 'owner_id',
{ on_delete => 'SET NULL', is_deferrable => 1 });
# ... FOREIGN KEY ("owner_id") REFERENCES "owner" ("id") ON DELETE SET NULL DEFERRABLE
An
on_deletethat thebelongs_todoes not declare at all is detected as DBIO::Relationship::Base describes it: when the target has ahas_manyback over the same columns withcascade_delete(its default), the key isON DELETE CASCADE. Without that the ORM, which deletes the parent first and the children after, runs into ORA-02292 on every cascading$row->delete.on_delete => undefor''switches this off. Acascade_copydoes not turn intoON UPDATE CASCADE.Oracle knows
ON DELETE CASCADEandON DELETE SET NULLonly.NO ACTIONandRESTRICTare Oracle's behaviour already and write no clause; any otheron_delete(SET DEFAULT) is refused with an exception.Oracle has no
ON UPDATE. A declaredon_updateother thanNO ACTIONorRESTRICTis refused with an exception, never left out silently.A true
is_deferrablewritesDEFERRABLE(initially immediate), whatwith_deferred_fk_checksneeds.Two relationships to the same target over the same columns with different actions would need one constraint name twice and are refused.
TABLES IN ANOTHER SCHEMA
A source named schema.table (__PACKAGE__->table('OWNX.item')) is a table in the schema OWNX; on Oracle a schema is a user. The text before the first dot is the schema, the rest the table, and each is quoted exactly as spelled, like every name here: "OWNX"."item". The schema is the name the dictionary holds, so a user made unquoted is written in upper case. Every statement of the table carries it: CREATE TABLE, the sequence and the trigger of an auto-increment column ("OWNX"."item_id_seq", "OWNX"."item_id_trg", the trigger reading "OWNX"."item_id_seq".NEXTVAL), the indexes (CREATE INDEX "OWNX"."idx" ON "OWNX"."item") and the foreign keys, which may point from one schema to another in either direction (REFERENCES "OWNX"."parent"). Generated names are made from the bare table name. A unique constraint lives in the table and is not qualified.
The schema is not created: the user has to exist, which "install" in DBIO::Oracle::Deploy checks before the first statement and refuses with a DBIO exception naming the user when it does not. This class only writes the statements. The connected user needs the ANY privileges to write into another user's schema (CREATE ANY TABLE, ALTER ANY TABLE for the foreign keys, CREATE ANY INDEX, CREATE ANY SEQUENCE, CREATE ANY TRIGGER; the server answers ORA-01031 without them). A foreign key to a table of another schema also needs the REFERENCES privilege of the owner of the referencing table on the parent; that is the server's business, not checked here.
REFUSALS
What Oracle cannot hold is refused with a DBIO exception before any statement is built (core ADR 0060), naming the table and, where there is one, the column (or, for a relationship, the relationship) of the declaration; the text says "refus" (refuses, refused). The server would answer each of them with an error of its own, or, for the dot, create something nobody can reach.
a column of type
enumorset-- Oracle has no such type;a column of type
bit(bit varying,varbit) -- Oracle has no BIT type. The type is compared as it means, not as it is spelled: letter case, blanks and a trailing(n)do not matter (' ENUM',bit(8),bit varying (8));a table or column name with a double quote or a NUL character -- Oracle takes any other character in a quoted name, a space included, but never these (ORA-25716);
a schema-qualified table name with an empty half (
.t,x.) -- a name with a dot isschema.table(see "TABLES IN ANOTHER SCHEMA"), and neither half may be empty. A literal table name (\'other.t') is the user's own SQL and not checked;an index declaration over exactly the columns of a unique constraint, or of its name (see "INDEX DECLARATIONS");
an index of a type other than
unique, an index withoptions, anon_deleteoron_updateOracle does not have, two relationships to one key with other actions (see "ON DELETE, ON UPDATE, DEFERRABLE"), and afloatof a precision other than a whole number from 1 to 126.
A view definition is the user's own SQL and is not read by the driver: it is sent as it is, and a table named in it unquoted, which Oracle reads in upper case, is answered by the server with ORA-00942 (see "IDENTIFIERS, AUTO-INCREMENT, FOREIGN KEYS"). The definition therefore quotes the table names it uses, as the driver created them.
The double base type is BINARY_DOUBLE, as double precision is; the blanks of double precision do not matter.
DECLARED OBJECTS
"declared_objects" names what an install writes. The objects of a case can be told from the others of the same user by it, which is what the cleanup of DBIO::Oracle::Test::Conformance relies on.
UNIQUE CONSTRAINTS
Every entry of $source->unique_constraints but primary (add_unique_constraint, Cake uniq) is written as CONSTRAINT name UNIQUE (columns) inside the CREATE TABLE, after the primary key; Oracle creates the unique index of the same name. The name is shortened to 30 characters like every generated name. A constraint over the same columns as the primary key or as an earlier constraint is not written again, because Oracle refuses it (ORA-02261).
SEE ALSO
DBIO::Oracle - schema component
DBIO::Oracle::Deploy - uses this to generate DDL for deployment
DBIO::PostgreSQL::DDL - reference implementation
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.