you are not logged in

Navigation

User login

Oracle SQL dialect (PL/SQL)

Oracle (PL/SQL) dialect

Several statement types will be observed in this article. Create, insert and update statements will be evaluated on possible syntax usage.

The insert statement

INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...)
    VALUES (expression)

INSERT [hint] INTO [schema.] table
    [[SUB]PARTITION (ptn_name)] [t_alias] (column, column,...)
    VALUES (expression)

INSERT [hint] INTO subquery
    WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ]
    [t_alias] (column, column,...)
    VALUES (expression)

VALUES(expression) can be expanded to

VALUES ([expr, expr...] [subquery])
    [RETURNING expr, expr... INTO host_variable|plsql_variable]

The create table statement

CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (tbl_defs,...)
  [ON COMMIT {DELETE|PRESERVE} ROWS]
  [storage_options | CLUSTER cluster_name (col1, col2,... )
  | ORGANIZATION {HEAP [storage_options] | INDEX idx_organized_tbl_clause}]
  [LOB_storage_clause][varray_clause][nested_storage_clause]
             partitioning_options
             [[NO]CACHE] [[NO]MONITORING] [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
             [AS subquery]

tbl_defs:
   column datatype [DEFAULT expr] [column_constraint(s)]
   table_constraint
   table_ref_constraint

storage_options:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   [LOGGING|NOLOGGING]

idx_organized_tbl_clause:
   storage_option(s) [PCTTHRESHOLD int]
     [COMPRESS int|NOCOMPRESS]
         [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

nested_storage_clause:
   NESTED TABLE nested_item STORE AS storage_table
   [RETURN AS {LOCATOR|VALUE}]

The alter table statement

ALTER TABLE [schema.]table RENAME TO new_table_name 

ALTER TABLE [schema.]table
      [ [NO]MINIMISE RECORDS PER BLOCK ]
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

 ALTER TABLE [schema.]table
      iot_overflow_clause
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

   ALTER TABLE [schema.]table
      partitioning_clause
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

   ALTER TABLE [schema.]table
      tbl_defs,...
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

tbl_defs:
   ADD [column datatype] [DEFAULT expr] [column_constraint(s)]
     [table_constraint] [table_ref_constraint]

   MODIFY [column datatype] [DEFAULT expr] [column_constraint(s)] 

   MODIFY [table_constraint] 

   drop_column_clause

   DROP drop_constraint_clause 

   [PCTFREE int][PCTUSED int][INITTRANS int]
      [MAXTRANS int][STORAGE storage_clause]

   extent_options
   MOVE [ONLINE] storage_options INDEX index_organized_tbl_clause

      [LOB_storage_clause][varray_clause]

   LOGGING|NOLOGGING 

   MODIFY NESTED TABLE collection_item RETURN AS {LOCATOR|VALUE

   MODIFY LOB [LOB_storage_clause] 

   MODIFY VARRAY [varray_clause] 

   CACHE | NOCACHE 

   MONITORING | NOMONITORING 

storage_options:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   [LOGGING|NOLOGGING]
 

extent_options:
   ALLOCATE EXTENT [( [size int K | M ]
      [DATAFILE 'filename' ] [INSTANCE int] )]
   DEALLOCATE UNUSED [KEEP int K | M ] 
 

index_organized_tbl_clause:
   storage_option(s) [PCTTHRESHOLD int]
     [COMPRESS int|NOCOMPRESS]
         [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]
 

iot_overflow_clause:
   {PCTTHRESHOLD int | INCLUDING column} |
       OVERFLOW overflow_storage_clause
         
          ADD OVERFLOW [storage_options] [(PARTITION storage_options)]

overflow_storage_clause:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   extent_options

  STORAGE storage_clause
   [LOGGING|NOLOGGING]

nested_storage_clause:
   NESTED TABLE nested_item STORE AS storage_table
      [RETURN AS {LOCATOR|VALUE
}] 

drop_column_clause:
   SET UNUSED (column,...)
      [CASCADE CONSTRAINTS][INVALIDATE]

   DROP COLUMN (column,...)
      [CASCADE CONSTRAINTS][INVALIDATE] CHECKPOINT int

   DROP {UNUSED COLUMNS|COLUMNS CONTINUE} [CHECKPOINT int]