All table, sequence and index scripts using their own syntax, which is precisely explained and illustrated by an example in the following sections.
Declarations in curly brackets are mandatory details. In some cases, there is a default value, which will be used, if there is no declaration. Default values are characterized by underlining. If there is no default value, you have to use a value from the curly brackets. Options are separated by a vertical stroke. Variable declarations, like object names, are written in italics.
Declarations in square brackets are optional details. Values enclosed in quotation marks have to be written with quotation marks.
Deviations from SQL Standard
- Only compact notations will be supported, in which all constraints and further specifications are included in the create table statement.
- Keywords will only be supported in lower case.
- Indizes will be expected in a create table statement, too. In this process,
create index index_name on tabellen_name ...
will be transformed to
index index_name ...
- The order of constraints is defined as follows:
- primary key
- check constraint
- unique key
- foreign key
Index statements can be mixed with unique keys (e.g. to create a unique key which uses an explicitly created index).
- More reserved terms: quite every static term from the SQL syntax is reserved (e.g. “table”, “create”, “varchar2”). All reserved terms are not allowed to occur as a name. E.g. A column called timestamp is possible in SQl, but not in Orcas. You can easily avoid this limitation, by capitalize these terms (e.g. “TIMESTAMP”). Of course, terms reserved by SQL are not possible with this.
The following data types are supported in columns:
- varchar2 - Usage of char or byte for length possible
- timestamp - Usage of length and/or time zone possible
- long raw
You have to enclose default values in double quotation marks.
You have to enclose the actual condition in double quotation marks. A constraint name has to be specified, too. But it is also possible to write an Extension, which automatically generates these names.
An index can be specified within a table (recommended) or outside of the table definition (like SQL Standard). Column-indizes should not be mixed with function-based-indizes. Error are possible.
Index example (internally)
To ensure, not to create a new index with every build, you have to define a function-based-index in capital letters!
Index Beispiel (extern)
With foreign keys you have the opportunity to simplify the syntax by using Extensions (e.g. omit a column specification, if you can define it by name conventions).
With sequences you can only specify the sequence name. In addition to this you can use a select, which return the highest value. A check on which value the sequence currently is will be performed and the sequence will be incremented if necessary.
For commenting out content of table scripts, the usual syntax (/* and +/) of Java and PL/SQL can be used.
Materialized view log purge clause: Normally you have to use
start with, when using
Complete syntax definition
The exact definition of syntax is described in xText. The BNF-similar definition is right hier: xText Syntax Definition