PL/SQL procedure, statement, and function are not available i have integrated the PlSqlParser.g4 and PlSqlLexer.g4 but its am facing the while running the code? #4351

drakshayanin · 2024-12-09T13:08:54Z

PL/SQL procedure, statement, and function are not available. Can you please provide the PL/SQL procedure and function definitions, including the following methods?"

public void enterCreate_procedure(Create_procedureContext ctx);
public void enterDeclare_statement(Declare_statementContext ctx);
public void enterEmpty_statement(Empty_statementContext ctx);
public void enterBegin_statement(Begin_statementContext ctx);
public void enterEnd_statement(End_statementContext ctx);

drakshayanin · 2024-12-09T15:56:31Z

drakshayanin · 2024-12-09T16:05:30Z

The above methods have been integrated into the PlSqlParser.g4 and PlSqlLexer.g4. The grammar files have been compiled, and all necessary files have been generated. However, while running the program, I am facing an issue with logical expressions

PlSqlParser.g4

/**

Oracle(c) PL/SQL 11g Parser
Copyright (c) 2009-2011 Alexandre Porcelli [email protected]
Copyright (c) 2015-2019 Ivan Kochurkin (KvanTTT, [email protected], Positive Technologies).
Copyright (c) 2017-2018 Mark Adams [email protected]
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// $antlr-format alignTrailingComments true, columnLimit 150, minEmptyLines 1, maxEmptyLinesToKeep 1, reflowComments false, useTab false
// $antlr-format allowShortRulesOnASingleLine false, allowShortBlocksOnASingleLine true, alignSemicolons hanging, alignColons hanging

parser grammar PlSqlParser;

options {
tokenVocab = PlSqlLexer;
superClass = PlSqlParserBase;
}

@parser::postinclude {
#include <PlSqlParserBase.h>
}

sql_script
: sql_plus_command_no_semicolon? (
(sql_plus_command | unit_statement) (SEMICOLON '/'? (sql_plus_command | unit_statement))* SEMICOLON? '/'?
) EOF
;

add_disk_clause
: ADD (
(SITE sn = id_expression)? quorum_regular? (FAILGROUP fgn = id_expression)? DISK qualified_disk_clause (
',' qualified_disk_clause
)*
)+
;

drop_disk_clause
: DROP (
quorum_regular? DISK id_expression force_noforce? (',' id_expression force_noforce?)*
| DISKS IN quorum_regular? FAILGROUP id_expression force_noforce? (
',' id_expression force_noforce?
)*
)
;

resize_disk_clause
: RESIZE ALL (SIZE size_clause)?
;

replace_disk_clause
: REPLACE DISK id_expression WITH CHAR_STRING force_noforce? (
',' id_expression WITH CHAR_STRING force_noforce?
)* (POWER numeric)? wait_nowait?
;

wait_nowait
: WAIT
| NOWAIT
;

rename_disk_clause
: RENAME (
DISK id_expression TO id_expression (',' id_expression TO id_expression)*
| DISKS ALL
)
;

disk_online_clause
: ONLINE (
(
quorum_regular? DISK id_expression (',' id_expression)*
| DISKS IN quorum_regular? FAILGROUP id_expression (',' id_expression)*
)+
| ALL
) (POWER numeric)? wait_nowait?
;

disk_offline_clause
: OFFLINE (
quorum_regular? DISK id_expression (',' id_expression)*
| DISKS IN quorum_regular? FAILGROUP id_expression (',' id_expression)*
) timeout_clause?
;

timeout_clause
: DROP AFTER numeric (M_LETTER | H_LETTER)
;

rebalance_diskgroup_clause
: REBALANCE (
((WITH | WITHOUT) phase+)? (POWER numeric) (WAIT | NOWAIT)?
| MODIFY POWER numeric?
)
;

phase
: id_expression //TODO
;

check_diskgroup_clause
: CHECK ALL? (REPAIR | NOREPAIR)? //inconsistent documentation
;

diskgroup_template_clauses
: (ADD | MODIFY) TEMPLATE id_expression qualified_template_clause (
',' id_expression qualified_template_clause
)*
| DROP TEMPLATE id_expression (',' id_expression)*
;

qualified_template_clause
: ATTRIBUTES '(' redundancy_clause? striping_clause? ')' //inconsistent documentation
;

redundancy_clause
: MIRROR
| HIGH
| UNPROTECTED
| PARITY
| DOUBLE
;

striping_clause
: FINE
| COARSE
;

force_noforce
: FORCE
| NOFORCE
;

diskgroup_directory_clauses
: ADD DIRECTORY filename (',' filename)*
| DROP DIRECTORY filename force_noforce? (',' filename force_noforce?)*
| RENAME DIRECTORY dir_name TO dir_name (',' dir_name TO dir_name)*
;

dir_name
: CHAR_STRING
;

diskgroup_alias_clauses
: ADD ALIAS CHAR_STRING FOR CHAR_STRING (',' CHAR_STRING FOR CHAR_STRING)*
| DROP ALIAS CHAR_STRING (',' CHAR_STRING)*
| RENAME ALIAS CHAR_STRING TO CHAR_STRING (',' CHAR_STRING TO CHAR_STRING)*
;

diskgroup_volume_clauses
: add_volume_clause
| modify_volume_clause
| RESIZE VOLUME id_expression SIZE size_clause
| DROP VOLUME id_expression
;

add_volume_clause
: ADD VOLUME id_expression SIZE size_clause redundancy_clause? (
STRIPE_WIDTH numeric (K_LETTER | M_LETTER)
)? (STRIPE_COLUMNS numeric)?
;

modify_volume_clause
: MODIFY VOLUME id_expression (MOUNTPATH CHAR_STRING)? (USAGE CHAR_STRING)?
;

diskgroup_attributes
: SET ATTRIBUTE CHAR_STRING '=' CHAR_STRING
;

modify_diskgroup_file
: MODIFY FILE CHAR_STRING ATTRIBUTE '(' disk_region_clause ')' (
',' CHAR_STRING ATTRIBUTE '(' disk_region_clause ')'
)*
;

disk_region_clause
:
;

drop_diskgroup_file_clause
: DROP FILE filename (',' filename)*
;

convert_redundancy_clause
: CONVERT REDUNDANCY TO FLEX
;

usergroup_clauses
: ADD USERGROUP CHAR_STRING WITH MEMBER CHAR_STRING (',' CHAR_STRING)*
| MODIFY USERGROUP CHAR_STRING (ADD | DROP) MEMBER CHAR_STRING (',' CHAR_STRING)*
| DROP USERGROUP CHAR_STRING
;

user_clauses
: ADD USER CHAR_STRING (',' CHAR_STRING)*
| DROP USER CHAR_STRING (',' CHAR_STRING)* CASCADE?
| REPLACE USER CHAR_STRING WITH CHAR_STRING (',' CHAR_STRING WITH CHAR_STRING)*
;

file_owner_clause
: SET OWNERSHIP (OWNER | GROUP) '=' CHAR_STRING (',' (OWNER | GROUP) '=' CHAR_STRING)* FOR FILE CHAR_STRING (
',' CHAR_STRING
)*
;

quotagroup_clauses
: ADD QUOTAGROUP id_expression (SET property_name '=' property_value)?
| MODIFY QUOTAGROUP id_expression SET property_name '=' property_value
| MOVE QUOTAGROUP id_expression TO id_expression
| DROP QUOTAGROUP id_expression
;

property_name
: id_expression
;

property_value
: id_expression
;

filegroup_clauses
: add_filegroup_clause
| modify_filegroup_clause
| move_to_filegroup_clause
| drop_filegroup_clause
;

add_filegroup_clause
: ADD FILEGROUP id_expression ((DATABASE | CLUSTER | VOLUME) id_expression | TEMPLATE) (
FROM TEMPLATE id_expression
)? (SET CHAR_STRING '=' CHAR_STRING)?
;

modify_filegroup_clause
: MODIFY FILEGROUP id_expression SET CHAR_STRING '=' CHAR_STRING
;

move_to_filegroup_clause
: MOVE FILE CHAR_STRING TO FILEGROUP id_expression
;

drop_filegroup_clause
: DROP FILEGROUP id_expression CASCADE?
;

quorum_regular
: QUORUM
| REGULAR
;

undrop_disk_clause
: UNDROP DISKS
;

diskgroup_availability
: MOUNT (RESTRICTED | NORMAL)? (FORCE | NOFORCE)?
| DISMOUNT (FORCE | NOFORCE)?
;

enable_disable_volume
: (ENABLE | DISABLE) VOLUME (id_expression (',' id_expression)* | ALL)
;

// DDL -> SQL Statements for Stored PL/SQL Units

// Function DDLs

drop_function
: DROP FUNCTION function_name
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-FLASHBACK-ARCHIVE.html
alter_flashback_archive
: ALTER FLASHBACK ARCHIVE fa = id_expression (
SET DEFAULT
| (ADD | MODIFY) TABLESPACE ts = id_expression flashback_archive_quota?
| REMOVE TABLESPACE rts = id_expression
| MODIFY /RETENTION/ flashback_archive_retention // inconsistent documentation
| PURGE (ALL | BEFORE (SCN expression | TIMESTAMP expression))
| NO? OPTIMIZE DATA
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-HIERARCHY.html
alter_hierarchy
: ALTER HIERARCHY (schema_name '.')? hn = id_expression (
RENAME TO nhn = id_expression
| COMPILE
)
;

alter_function
: ALTER FUNCTION function_name (
EDITIONABLE
| NONEDITIONABLE
| COMPILE DEBUG? compiler_parameters_clause* (REUSE SETTINGS)?
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-JAVA.html
alter_java
: ALTER JAVA (SOURCE | CLASS) (schema_name '.')? o = id_expression (
RESOLVER '(' ('(' match_string ','? (schema_name | '-') ')')+ ')'
)? (COMPILE | RESOLVE | invoker_rights_clause)
;

match_string
: DELIMITED_ID
| '*'
;

sql_macro_body
: SQL_MACRO IS BEGIN RETURN quoted_string SEMICOLON END
;

// Creation Function - Specific Clauses

parallel_enable_clause
: PARALLEL_ENABLE partition_by_clause?
;

partition_by_clause
: '(' PARTITION expression BY (ANY | (HASH | RANGE | LIST) paren_column_list) streaming_clause? ')'
;

result_cache_clause
: RESULT_CACHE relies_on_part? ('(' MODE (DEFAULT | FORCE ) ')')?
;

accessible_by_clause
: ACCESSIBLE BY '(' accessor (',' accessor)* ')'
;

default_collation_clause
: DEFAULT COLLATION USING_NLS_COMP
;

aggregate_clause
: AGGREGATE USING implementation_type_name
;

pipelined_using_clause
: PIPELINED ((ROW | TABLE) POLYMORPHIC)? USING implementation_type_name
;

accessor
: unitKind = (FUNCTION | PROCEDURE | PACKAGE | TRIGGER | TYPE) function_name
;

relies_on_part
: RELIES_ON '(' tableview_name (',' tableview_name)* ')'
;

streaming_clause
: (ORDER | CLUSTER) expression BY paren_column_list
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-OUTLINE.html
alter_outline
: ALTER OUTLINE (PUBLIC | PRIVATE)? o = id_expression outline_options+
;

outline_options
: REBUILD
| RENAME TO non = id_expression
| CHANGE CATEGORY TO ncn = id_expression
| ENABLE
| DISABLE
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-LOCKDOWN-PROFILE.html
alter_lockdown_profile
: ALTER LOCKDOWN PROFILE id_expression (
lockdown_feature
| lockdown_options
| lockdown_statements
) (USERS '=' (ALL | COMMON | LOCAL))?
;

lockdown_feature
: disable_enable FEATURE ('=' '(' string_list ')' | ALL (EXCEPT '=' '(' string_list ')')?)
;

lockdown_options
: disable_enable OPTION ('=' '(' string_list ')' | ALL (EXCEPT '=' '(' string_list ')')?)
;

lockdown_statements
: disable_enable STATEMENT (
'=' '(' string_list ')'
| '=' '(' CHAR_STRING ')' statement_clauses
| ALL (EXCEPT '=' '(' string_list ')')?
)
;

statement_clauses
: CLAUSE (
'=' '(' string_list ')'
| '=' '(' CHAR_STRING ')' clause_options
| ALL (EXCEPT '=' '(' string_list ')')?
)
;

clause_options
: OPTION (
'=' '(' string_list ')'
| '=' '(' CHAR_STRING ')' option_values+
| ALL (EXCEPT '=' '(' string_list ')')?
)
;

option_values
: VALUE '=' '(' string_list ')'
| (MINVALUE | MAXVALUE) '=' CHAR_STRING
;

string_list
: CHAR_STRING (',' CHAR_STRING)*
;

disable_enable
: DISABLE
| ENABLE
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-LOCKDOWN-PROFILE.html
drop_lockdown_profile
: DROP LOCKDOWN PROFILE p = id_expression
;

// Package DDLs

drop_package
: DROP PACKAGE BODY? (schema_object_name '.')? package_name
;

alter_package
: ALTER PACKAGE package_name COMPILE DEBUG? (PACKAGE | BODY | SPECIFICATION)? compiler_parameters_clause* (
REUSE SETTINGS
)?
;

create_package
: CREATE (OR REPLACE)? (EDITIONABLE | NONEDITIONABLE)? PACKAGE (schema_object_name '.')? package_name invoker_rights_clause? (
IS
| AS
) package_obj_spec* END package_name?
;

create_package_body
: CREATE (OR REPLACE)? (EDITIONABLE | NONEDITIONABLE)? PACKAGE BODY (schema_object_name '.')? package_name (
IS
| AS
) package_obj_body*? (BEGIN seq_of_statements (EXCEPTION exception_handler+)?)? END package_name?
;

// Create Package Specific Clauses

procedure_spec
: PROCEDURE identifier ('(' parameter ( ',' parameter)* ')')? (
accessible_by_clause
| PARALLEL_ENABLE
)* ';'
;

function_spec
: FUNCTION identifier ('(' parameter ( ',' parameter)* ')')? RETURN type_spec (
DETERMINISTIC
| PIPELINED
| parallel_enable_clause
| RESULT_CACHE
| streaming_clause
)* ';'
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/alter-pmem-filestore.html
alter_pmem_filestore
: ALTER PMEM FILESTORE fsn = id_expression (
RESIZE size_clause
| autoextend_clause
| MOUNT (MOUNTPOINT file_path)? (BACKINGFILE filename)? FORCE? //inconsistent documentation
| DISMOUNT
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/drop-pmem-filestore.html
drop_pmem_filestore
: DROP PMEM FILESTORE fsn = id_expression ((FORCE? INCLUDING | EXCLUDING) CONTENTS)?
;

// Procedure DDLs

drop_procedure
: DROP PROCEDURE procedure_name
;

alter_procedure
: ALTER PROCEDURE procedure_name COMPILE DEBUG? compiler_parameters_clause* (REUSE SETTINGS)?
;

function_body
: FUNCTION identifier ('(' parameter (',' parameter)* ')')? RETURN type_spec (
PIPELINED
| DETERMINISTIC
| invoker_rights_clause
| parallel_enable_clause
| result_cache_clause
| streaming_clause
// see example in section "How Table Functions Stream their Input Data" on streaming_clause in Oracle 9i: https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/08_subs.htm#20554
)* (
( (IS | AS) (DECLARE? seq_of_declare_specs? body | call_spec))
| (PIPELINED | AGGREGATE) USING implementation_type_name
) ';'
;

procedure_body
: PROCEDURE identifier ('(' parameter (',' parameter)* ')')? (
accessible_by_clause
| PARALLEL_ENABLE
)* (IS | AS) (DECLARE? seq_of_declare_specs? body | call_spec | EXTERNAL) ';'
;

create_procedure_body
: CREATE (OR REPLACE)? PROCEDURE procedure_name ('(' parameter (',' parameter)* ')')? invoker_rights_clause? PARALLEL_ENABLE? (
IS
| AS
) (DECLARE? seq_of_declare_specs? body | call_spec | EXTERNAL)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-RESOURCE-COST.html
alter_resource_cost
: ALTER RESOURCE COST (
(CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA) UNSIGNED_INTEGER
)+
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-OUTLINE.html
drop_outline
: DROP OUTLINE o = id_expression
;

// Rollback Segment DDLs

//https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2011.htm#SQLRF00816
alter_rollback_segment
: ALTER ROLLBACK SEGMENT rollback_segment_name (
ONLINE
| OFFLINE
| storage_clause
| SHRINK (TO size_clause)?
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-RESTORE-POINT.html
drop_restore_point
: DROP RESTORE POINT rp = id_expression (FOR PLUGGABLE DATABASE pdb = id_expression)?
;

drop_rollback_segment
: DROP ROLLBACK SEGMENT rollback_segment_name
;

drop_role
: DROP ROLE role_name
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/create-pmem-filestore.html
create_pmem_filestore
: CREATE PMEM FILESTORE fsn = id_expression pmem_filestore_options+
;

pmem_filestore_options
: MOUNTPOINT file_path
| BACKINGFILE filename REUSE?
| (SIZE | BLOCKSIZE) size_clause
| autoextend_clause
;

file_path
: CHAR_STRING
;

create_rollback_segment
: CREATE PUBLIC? ROLLBACK SEGMENT rollback_segment_name (
TABLESPACE tablespace
| storage_clause
)*
;

// Trigger DDLs

drop_trigger
: DROP TRIGGER trigger_name
;

alter_trigger
: ALTER TRIGGER alter_trigger_name = trigger_name (
(ENABLE | DISABLE)
| RENAME TO rename_trigger_name = trigger_name
| COMPILE DEBUG? compiler_parameters_clause* (REUSE SETTINGS)?
)
;

create_trigger
: CREATE (OR REPLACE)? TRIGGER trigger_name (
simple_dml_trigger
| compound_dml_trigger
| non_dml_trigger
) trigger_follows_clause? (ENABLE | DISABLE)? trigger_when_clause? trigger_body
;

trigger_follows_clause
: FOLLOWS trigger_name (',' trigger_name)*
;

trigger_when_clause
: WHEN '(' condition ')'
;

// Create Trigger Specific Clauses

simple_dml_trigger
: (BEFORE | AFTER | INSTEAD OF) dml_event_clause referencing_clause? for_each_row?
;

for_each_row
: FOR EACH ROW
;

compound_dml_trigger
: FOR dml_event_clause referencing_clause?
;

non_dml_trigger
: (BEFORE | AFTER) non_dml_event (OR non_dml_event)* ON (DATABASE | (schema_name '.')? SCHEMA)
;

trigger_body
: compound_trigger_block
| CALL identifier
| trigger_block
;

routine_clause
: routine_name function_argument?
;

compound_trigger_block
: COMPOUND TRIGGER seq_of_declare_specs? timing_point_section+ END trigger_name?
;

timing_point_section
: bk = BEFORE STATEMENT IS tps_block BEFORE STATEMENT ';'
| bk = BEFORE EACH ROW IS tps_block BEFORE EACH ROW ';'
| ak = AFTER STATEMENT IS tps_block AFTER STATEMENT ';'
| ak = AFTER EACH ROW IS tps_block AFTER EACH ROW ';'
;

dml_event_clause
: dml_event_element (OR dml_event_element)* ON dml_event_nested_clause? tableview_name
;

dml_event_element
: (DELETE | INSERT | UPDATE) (OF column_list)?
;

dml_event_nested_clause
: NESTED TABLE tableview_name OF
;

referencing_clause
: (REFERENCING referencing_element | REFERENCES) referencing_element*
;

referencing_element
: (NEW | OLD | PARENT) column_alias
;

// DDLs

drop_type
: DROP TYPE BODY? type_name (FORCE | VALIDATE)?
;

alter_type
: ALTER TYPE type_name (
compile_type_clause
| replace_type_clause
//TODO | {input.LT(2).getText().equalsIgnoreCase("attribute")}? alter_attribute_definition
| alter_method_spec
| alter_collection_clauses
| modifier_clause
| overriding_subprogram_spec
) dependent_handling_clause?
;

// Alter Type Specific Clauses

compile_type_clause
: COMPILE DEBUG? (SPECIFICATION | BODY)? compiler_parameters_clause* (REUSE SETTINGS)?
;

replace_type_clause
: REPLACE invoker_rights_clause? AS OBJECT '(' object_member_spec (',' object_member_spec)* ')'
;

alter_method_spec
: alter_method_element (',' alter_method_element)*
;

alter_method_element
: (ADD | DROP) (map_order_function_spec | subprogram_spec)
;

alter_attribute_definition
: (ADD | MODIFY | DROP) ATTRIBUTE (
attribute_definition
| '(' attribute_definition (',' attribute_definition)* ')'
)
;

attribute_definition
: attribute_name type_spec?
;

alter_collection_clauses
: MODIFY (LIMIT expression | ELEMENT TYPE type_spec)
;

dependent_handling_clause
: INVALIDATE
| CASCADE (CONVERT TO SUBSTITUTABLE | NOT? INCLUDING TABLE DATA)? dependent_exceptions_part?
;

dependent_exceptions_part
: FORCE? EXCEPTIONS INTO tableview_name
;

create_type
: CREATE (OR REPLACE)? (EDITIONABLE | NONEDITIONABLE)? TYPE (type_definition | type_body)
;

// Create Type Specific Clauses

type_definition
: type_name (OID CHAR_STRING)? FORCE? object_type_def?
;

object_type_def
: invoker_rights_clause? (object_as_part | object_under_part) sqlj_object_type? (
'(' object_member_spec (',' object_member_spec)* ')'
)? modifier_clause*
;

object_as_part
: (IS | AS) (OBJECT | varray_type_def | nested_table_type_def)
;

object_under_part
: UNDER type_spec
;

nested_table_type_def
: TABLE OF type_spec (NOT NULL_)?
;

sqlj_object_type
: EXTERNAL NAME expression LANGUAGE JAVA USING (SQLDATA | CUSTOMDATUM | ORADATA)
;

type_body
: BODY type_name (IS | AS) (type_body_elements)+ END
;

type_body_elements
: map_order_func_declaration
| subprog_decl_in_type
| overriding_subprogram_spec
;

map_order_func_declaration
: (MAP | ORDER) MEMBER func_decl_in_type
;

subprog_decl_in_type
: (MEMBER | STATIC) (proc_decl_in_type | func_decl_in_type | constructor_declaration)
;

proc_decl_in_type
: PROCEDURE procedure_name '(' type_elements_parameter (',' type_elements_parameter)* ')' (
IS
| AS
) (call_spec | DECLARE? seq_of_declare_specs? body ';')
;

func_decl_in_type
: FUNCTION function_name ('(' type_elements_parameter (',' type_elements_parameter)* ')')? RETURN type_spec (
IS
| AS
) (call_spec | DECLARE? seq_of_declare_specs? body ';')
;

constructor_declaration
: FINAL? INSTANTIABLE? CONSTRUCTOR FUNCTION type_spec (
'(' (SELF IN OUT type_spec ',') type_elements_parameter (',' type_elements_parameter)* ')'
)? RETURN SELF AS RESULT (IS | AS) (call_spec | DECLARE? seq_of_declare_specs? body ';')
;

// Common Type Clauses

modifier_clause
: NOT? (INSTANTIABLE | FINAL | OVERRIDING)
;

object_member_spec
: identifier type_spec sqlj_object_type_attr?
| element_spec
;

sqlj_object_type_attr
: EXTERNAL NAME expression
;

element_spec
: modifier_clause? element_spec_options+ (',' pragma_clause)?
;

element_spec_options
: subprogram_spec
| constructor_spec
| map_order_function_spec
;

subprogram_spec
: (MEMBER | STATIC) (type_procedure_spec | type_function_spec)
;

// TODO: should be refactored such as Procedure body and Function body, maybe Type_Function_Body and overriding_function_body
overriding_subprogram_spec
: OVERRIDING MEMBER overriding_function_spec
;

overriding_function_spec
: FUNCTION function_name ('(' type_elements_parameter (',' type_elements_parameter)* ')')? RETURN (
type_spec
| SELF AS RESULT
) (PIPELINED? (IS | AS) (DECLARE? seq_of_declare_specs? body))? ';'?
;

type_procedure_spec
: PROCEDURE procedure_name '(' type_elements_parameter (',' type_elements_parameter)* ')' (
(IS | AS) call_spec
)?
;

type_function_spec
: FUNCTION function_name ('(' type_elements_parameter (',' type_elements_parameter)* ')')? RETURN (
type_spec
| SELF AS RESULT
) ((IS | AS) call_spec | EXTERNAL VARIABLE? NAME expression)?
;

constructor_spec
: FINAL? INSTANTIABLE? CONSTRUCTOR FUNCTION type_spec (
'(' (SELF IN OUT type_spec ',') type_elements_parameter (',' type_elements_parameter)* ')'
)? RETURN SELF AS RESULT ((IS | AS) call_spec)?
;

map_order_function_spec
: (MAP | ORDER) MEMBER type_function_spec
;

pragma_clause
: PRAGMA RESTRICT_REFERENCES '(' pragma_elements (',' pragma_elements)* ')'
;

pragma_elements
: identifier
| DEFAULT
;

type_elements_parameter
: parameter_name type_spec
;

// Sequence DDLs

drop_sequence
: DROP SEQUENCE sequence_name
;

alter_sequence
: ALTER SEQUENCE sequence_name sequence_spec+
;

create_sequence
: CREATE SEQUENCE sequence_name (sequence_start_clause | sequence_spec)*
;

// Common Sequence

sequence_start_clause
: START WITH UNSIGNED_INTEGER
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-ANALYTIC-VIEW.html
create_analytic_view
: CREATE (OR REPLACE)? (NOFORCE | FORCE)? ANALYTIC VIEW av = id_expression (
SHARING '=' (METADATA | NONE)
)? classification_clause* cav_using_clause? dim_by_clause? measures_clause? default_measure_clause? default_aggregate_clause? cache_clause?
fact_columns_clause? qry_transform_clause?
;

classification_clause
// : (CAPTION c=quoted_string)? (DESCRIPTION d=quoted_string)? classification_item*
// to handle - 'rule contains a closure with at least one alternative that can match an empty string'
: (caption_clause description_clause? | caption_clause? description_clause) classification_item*
| caption_clause? description_clause? classification_item+
;

caption_clause
: CAPTION c = quoted_string
;

description_clause
: DESCRIPTION d = quoted_string
;

classification_item
: CLASSIFICATION cn = id_expression (VALUE cv = quoted_string)? (LANGUAGE language)?
;

language
: NULL_
| nls = id_expression
;

cav_using_clause
: USING (schema_name '.')? t = id_expression REMOTE? (AS? ta = id_expression)?
;

dim_by_clause
: DIMENSION BY '(' dim_key (',' dim_key)* ')'
;

dim_key
: dim_ref classification_clause* KEY (
'(' (a = id_expression '.')? f = column_name (',' (a = id_expression '.')? f = column_name)* ')'
| (a = id_expression '.')? f = column_name
) REFERENCES DISTINCT? ('(' attribute_name (',' attribute_name) ')' | attribute_name) HIERARCHIES '(' hier_ref (
',' hier_ref
)* ')'
;

dim_ref
: (schema_name '.')? ad = id_expression (AS? da = id_expression)?
;

hier_ref
: (schema_name '.')? h = id_expression (AS? ha = id_expression)? DEFAULT?
;

measures_clause
: MEASURES '(' av_measure (',' av_measure)* ')'
;

av_measure
: mn = id_expression (base_meas_clause | calc_meas_clause)? //classification_clause*
;

base_meas_clause
: FACT /FOR MEASURE/ bm = id_expression meas_aggregate_clause? //FIXME inconsistent documentation
;

meas_aggregate_clause
: AGGREGATE BY aggregate_function_name
;

calc_meas_clause
: /m=id_expression/ AS '(' expression ')' //FIXME inconsistent documentation
;

default_measure_clause
: DEFAULT MEASURE m = id_expression
;

default_aggregate_clause
: DEFAULT AGGREGATE BY aggregate_function_name
;

cache_clause
: CACHE cache_specification (',' cache_specification)*
;

cache_specification
: MEASURE GROUP (
ALL
| '(' id_expression (',' id_expression)* ')' levels_clause (',' levels_clause)*
)
;

levels_clause
: LEVELS '(' level_specification (',' level_specification)* ')' level_group_type
;

level_specification
: '(' ((d = id_expression '.')? h = id_expression '.')? l = id_expression ')'
;

level_group_type
: DYNAMIC
| MATERIALIZED (USING (schema_name '.')? t = id_expression)?
;

fact_columns_clause
: FACT COLUMN f = column_name (AS? fa = id_expression (',' AS? fa = id_expression)*)?
;

qry_transform_clause
: ENABLE QUERY TRANSFORM (RELY | NORELY)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-ATTRIBUTE-DIMENSION.html
create_attribute_dimension
: CREATE (OR REPLACE)? (NOFORCE | FORCE)? ATTRIBUTE DIMENSION (schema_name '.')? ad = id_expression (
SHARING '=' (METADATA | NONE)
)? classification_clause* (DIMENSION TYPE (STANDARD | TIME))? ad_using_clause attributes_clause ad_level_clause+ all_clause?
;

ad_using_clause
: USING source_clause (',' source_clause)* join_path_clause*
;

source_clause
: (schema_name '.')? ftov = id_expression REMOTE? (AS? a = id_expression)?
;

join_path_clause
: JOIN PATH jpn = id_expression ON join_condition
;

join_condition
: join_condition_item (AND join_condition_item)*
;

join_condition_item
: (a = id_expression '.')? column_name '=' (b = id_expression '.')? column_name
;

attributes_clause
: ATTRIBUTES '(' ad_attributes_clause (',' ad_attributes_clause)* ')'
;

ad_attributes_clause
: (a = id_expression '.')? column_name (AS? an = id_expression)? classification_clause*
;

key_clause
: KEY (a = id_expression | '(' id_expression (',' id_expression)* ')')
;

alternate_key_clause
: ALTERNATE key_clause
;

dim_order_clause
: a = id_expression (ASC | DESC)? (NULLS (FIRST | LAST))?
;

all_clause
: ALL MEMBER (
NAME expression (MEMBER CAPTION expression)?
| CAPTION expression (MEMBER DESCRIPTION expression)?
| DESCRIPTION expression
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-AUDIT-POLICY-Unified-Auditing.html
create_audit_policy
: CREATE AUDIT POLICY p = id_expression privilege_audit_clause? action_audit_clause? role_audit_clause? (
WHEN quoted_string EVALUATE PER (STATEMENT | SESSION | INSTANCE)
)? (ONLY TOPLEVEL)? container_clause?
;

privilege_audit_clause
: PRIVILEGES system_privilege (',' system_privilege)*
;

action_audit_clause
: (standard_actions | component_actions | system_actions)+
;

system_actions
: ACTIONS system_privilege (',' system_privilege)*
;

standard_actions
: ACTIONS actions_clause (',' actions_clause)*
;

actions_clause
: (object_action | ALL) ON (
DIRECTORY directory_name
| (MINING MODEL)? (schema_name '.')? id_expression
)
| (system_action | ALL)
;

component_action
: id_expression // SELECT name FROM auditable_system_actions WHERE component = 'Datapump';
;

role_audit_clause
: ROLES role_name (',' role_name)*
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-CONTROLFILE.html
create_controlfile
: CREATE CONTROLFILE REUSE? SET? DATABASE d = id_expression logfile_clause? (
RESETLOGS
| NORESETLOGS
) (DATAFILE file_specification (',' file_specification))? controlfile_options character_set_clause?
;

logfile_clause
: LOGFILE (GROUP? numeric)? file_specification (',' (GROUP? numeric)? file_specification)*
;

character_set_clause
: CHARACTER SET cs = id_expression
;

file_specification
: datafile_tempfile_spec
| redo_log_file_spec
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-DISKGROUP.html
create_diskgroup
: CREATE DISKGROUP id_expression (
(HIGH | NORMAL | FLEX | EXTENDED (SITE sn = id_expression)? | EXTERNAL) REDUNDANCY
)? (
quorum_regular? (FAILGROUP fg = id_expression)? DISK qualified_disk_clause (
',' qualified_disk_clause
)*
)+ (ATTRIBUTE an = CHAR_STRING '=' av = CHAR_STRING (',' CHAR_STRING '=' CHAR_STRING)*)?
;

qualified_disk_clause
: ss = CHAR_STRING (NAME dn = id_expression)? (SIZE size_clause)? force_noforce?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-EDITION.html
create_edition
: CREATE EDITION e = id_expression (AS CHILD OF pe = id_expression)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-FLASHBACK-ARCHIVE.html
create_flashback_archive
: CREATE FLASHBACK ARCHIVE DEFAULT? fa = id_expression TABLESPACE ts = id_expression flashback_archive_quota? (
NO? OPTIMIZE DATA
)? flashback_archive_retention
;

flashback_archive_quota
: QUOTA UNSIGNED_INTEGER (M_LETTER | G_LETTER | T_LETTER | P_LETTER | E_LETTER)
;

flashback_archive_retention
: RETENTION UNSIGNED_INTEGER (YEAR | MONTH | DAY)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-HIERARCHY.html
create_hierarchy
: CREATE (OR REPLACE)? (NO? FORCE)? HIERARCHY (schema_name '.')? h = id_expression (
SHARING '=' (METADATA | NONE)
)? classification_clause* hier_using_clause level_hier_clause hier_attrs_clause?
;

hier_using_clause
: USING (schema_name '.')? ad = id_expression
;

level_hier_clause
: '(' (l = id_expression (CHILD OF)?)+ ')'
;

hier_attrs_clause
: HIERARCHICAL ATTRIBUTES '(' hier_attr_clause ')'
;

hier_attr_clause
: hier_attr_name classification_clause*
;

cluster_index_clause
: CLUSTER cluster_name index_attributes?
;

cluster_name
: (id_expression '.')? id_expression
;

table_index_clause
: tableview_name table_alias? '(' index_expr (ASC | DESC)? (',' index_expr (ASC | DESC)?)* ')' index_properties?
;

bitmap_join_index_clause
: tableview_name '(' (tableview_name | table_alias)? column_name (ASC | DESC)? (
',' (tableview_name | table_alias)? column_name (ASC | DESC)?
)* ')' FROM tableview_name table_alias (',' tableview_name table_alias)* where_clause local_partitioned_index? index_attributes?
;

index_expr
: column_name
| expression
;

index_properties
: (global_partitioned_index | local_partitioned_index | index_attributes)+
| INDEXTYPE IS (domain_index_clause | xmlindex_clause)
;

domain_index_clause
: indextype local_domain_index_clause? parallel_clause? (PARAMETERS '(' odci_parameters ')')?
;

local_domain_index_clause
: LOCAL (
'(' PARTITION partition_name (PARAMETERS '(' odci_parameters ')')? (
',' PARTITION partition_name (PARAMETERS '(' odci_parameters ')')?
)* ')'
)?
;

xmlindex_clause
: (XDB '.')? XMLINDEX local_xmlindex_clause? parallel_clause? //TODO xmlindex_parameters_clause?
;

local_xmlindex_clause
: LOCAL (
'(' PARTITION partition_name (
',' PARTITION partition_name //TODO xmlindex_parameters_clause?
)* ')'
)?
;

global_partitioned_index
: GLOBAL PARTITION BY (
RANGE '(' column_name (',' column_name)* ')' '(' index_partitioning_clause (
',' index_partitioning_clause
)* ')'
| HASH '(' column_name (',' column_name)* ')' (
individual_hash_partitions
| hash_partitions_by_quantity
)
)
;

index_partitioning_clause
: PARTITION partition_name? VALUES LESS THAN '(' index_partitioning_values_list ')' segment_attributes_clause?
;

index_partitioning_values_list
: literal (',' literal)*
| TIMESTAMP literal (',' TIMESTAMP literal)*
;

local_partitioned_index
: LOCAL (
on_range_partitioned_table
| on_list_partitioned_table
| on_hash_partitioned_table
| on_comp_partitioned_table
)?
;

on_range_partitioned_table
: '(' partitioned_table (',' partitioned_table)* ')'
;

on_list_partitioned_table
: '(' partitioned_table (',' partitioned_table)* ')'
;

partitioned_table
: PARTITION partition_name? (segment_attributes_clause | key_compression)* UNUSABLE?
;

on_hash_partitioned_table
: STORE IN '(' tablespace (',' tablespace)* ')'
| '(' on_hash_partitioned_clause (',' on_hash_partitioned_clause)* ')'
;

on_hash_partitioned_clause
: PARTITION partition_name? (TABLESPACE tablespace)? key_compression? UNUSABLE?
;

on_comp_partitioned_table
: (STORE IN '(' tablespace (',' tablespace)* ')')? '(' on_comp_partitioned_clause (
',' on_comp_partitioned_clause
)* ')'
;

on_comp_partitioned_clause
: PARTITION partition_name? (segment_attributes_clause | key_compression)* UNUSABLE index_subpartition_clause?
;

index_subpartition_clause
: STORE IN '(' tablespace (',' tablespace)* ')'
| '(' index_subpartition_subclause (',' index_subpartition_subclause)* ')'
;

index_subpartition_subclause
: SUBPARTITION subpartition_name? (TABLESPACE tablespace)? key_compression? UNUSABLE?
;

odci_parameters
: CHAR_STRING
;

indextype
: (id_expression '.')? id_expression
;

//https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1010.htm#SQLRF00805
alter_index
: ALTER INDEX index_name (alter_index_ops_set1 | alter_index_ops_set2)
;

visible_or_invisible
: VISIBLE
| INVISIBLE
;

monitoring_nomonitoring
: MONITORING
| NOMONITORING
;

modify_index_default_attrs
: MODIFY DEFAULT ATTRIBUTES (FOR PARTITION partition_name)? (
physical_attributes_clause
| TABLESPACE (tablespace | DEFAULT)
| logging_clause
)
;

add_hash_index_partition
: ADD PARTITION partition_name? (TABLESPACE tablespace)? key_compression? parallel_clause?
;

coalesce_index_partition
: COALESCE PARTITION parallel_clause?
;

modify_index_partition
: MODIFY PARTITION partition_name (
modify_index_partitions_ops+
| PARAMETERS '(' odci_parameters ')'
| COALESCE
| UPDATE BLOCK REFERENCES
| UNUSABLE
)
;

modify_index_partitions_ops
: deallocate_unused_clause
| allocate_extent_clause
| physical_attributes_clause
| logging_clause
| key_compression
;

rename_index_partition
: RENAME (PARTITION partition_name | SUBPARTITION subpartition_name) TO new_partition_name
;

drop_index_partition
: DROP PARTITION partition_name
;

split_index_partition
: SPLIT PARTITION partition_name_old AT '(' literal (',' literal)* ')' (
INTO '(' index_partition_description ',' index_partition_description ')'
)? parallel_clause?
;

index_partition_description
: PARTITION (
partition_name (
(segment_attributes_clause | key_compression)+
| PARAMETERS '(' odci_parameters ')'
) UNUSABLE?
)?
;

modify_index_subpartition
: MODIFY SUBPARTITION subpartition_name (
UNUSABLE
| allocate_extent_clause
| deallocate_unused_clause
)
;

partition_name_old
: partition_name
;

new_partition_name
: partition_name
;

new_index_name
: index_name
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-INMEMORY-JOIN-GROUP.html
alter_inmemory_join_group
: ALTER INMEMORY JOIN GROUP (schema_name '.')? jg = id_expression (ADD | REMOVE) '(' (
schema_name '.'
)? t = id_expression '(' c = id_expression ')' ')'
;

drop_user
: DROP USER user_object_name (IF EXISTS)? CASCADE?
;

alter_identified_by
: identified_by (REPLACE id_expression)?
;

identified_by
: IDENTIFIED BY id_expression
;

identified_other_clause
: IDENTIFIED (EXTERNALLY | GLOBALLY) (AS quoted_string)?
;

user_tablespace_clause
: (DEFAULT | TEMPORARY) TABLESPACE id_expression
;

quota_clause
: QUOTA (size_clause | UNLIMITED) ON id_expression
;

profile_clause
: PROFILE id_expression
;

role_clause
: role_name (',' role_name)*
| ALL (EXCEPT role_name (',' role_name))
;

user_default_role_clause
: DEFAULT ROLE (NONE | role_clause)
;

password_expire_clause
: PASSWORD EXPIRE
;

user_lock_clause
: ACCOUNT (LOCK | UNLOCK)
;

user_editions_clause
: ENABLE EDITIONS
;

alter_user_editions_clause
: user_editions_clause (FOR regular_id (',' regular_id)*)? FORCE?
;

container_names
: LEFT_PAREN id_expression (',' id_expression)* RIGHT_PAREN
;

set_container_data
: SET CONTAINER_DATA EQUALS_OP (ALL | DEFAULT | container_names)
;

add_rem_container_data
: (ADD | REMOVE) CONTAINER_DATA EQUALS_OP container_names
;

container_data_clause
: set_container_data
| add_rem_container_data (FOR container_tableview_name)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ADMINISTER-KEY-MANAGEMENT.html
administer_key_management
: ADMINISTER KEY MANAGEMENT (
keystore_management_clauses
| key_management_clauses
| secret_management_clauses
| zero_downtime_software_patching_clauses
)
;

create_keystore
: CREATE (
KEYSTORE ksl = CHAR_STRING
| LOCAL? AUTO_LOGIN KEYSTORE FROM KEYSTORE ksl = CHAR_STRING
) IDENTIFIED BY keystore_password
;

open_keystore
: SET KEYSTORE OPEN force_keystore? identified_by_store container_clause?
;

force_keystore
: FORCE KEYSTORE
;

close_keystore
: SET KEYSTORE CLOSE identified_by_store? container_clause?
;

backup_keystore
: BACKUP KEYSTORE (USING bi = CHAR_STRING)? force_keystore? identified_by_store (
TO ksl = CHAR_STRING
)?
;

alter_keystore_password
: ALTER KEYSTORE PASSWORD force_keystore? IDENTIFIED BY o = keystore_password SET n = keystore_password with_backup_clause?
;

merge_into_new_keystore
: MERGE KEYSTORE ksl1 = CHAR_STRING identified_by_password_clause? AND KEYSTORE ksl2 = CHAR_STRING identified_by_password_clause? INTO NEW
KEYSTORE ksl2 = CHAR_STRING identified_by_password_clause
;

merge_into_existing_keystore
: MERGE KEYSTORE ksl1 = CHAR_STRING identified_by_password_clause? INTO EXISTING KEYSTORE ksl2 = CHAR_STRING identified_by_password_clause
with_backup_clause?
;

isolate_keystore
: FORCE? ISOLATE KEYSTORE IDENTIFIED BY i = keystore_password FROM ROOT KEYSTORE force_keystore? identified_by_store with_backup_clause?
;

unite_keystore
: UNITE KEYSTORE IDENTIFIED BY i = keystore_password WITH ROOT KEYSTORE force_keystore? identified_by_store with_backup_clause?
;

set_key
: SET ENCRYPTION? KEY ((mkid ':')? mk)? using_tag_clause? using_algorithm_clause? force_keystore? identified_by_store with_backup_clause?
container_clause?
;

create_key
: CREATE ENCRYPTION? KEY ((mkid ':')? mk)? using_tag_clause? using_algorithm_clause? force_keystore? identified_by_store with_backup_clause?
container_clause?
;

mkid
: CHAR_STRING
;

mk
: CHAR_STRING
;

use_key
: USE ENCRYPTION? KEY k = CHAR_STRING using_tag_clause? force_keystore? identified_by_store with_backup_clause?
;

set_key_tag
: SET TAG t = CHAR_STRING FOR k = CHAR_STRING force_keystore? identified_by_store with_backup_clause?
;

export_keys
: EXPORT ENCRYPTION? KEYS WITH SECRET secret TO filename force_keystore? identified_by_store (
WITH IDENTIFIER IN (CHAR_STRING (',' CHAR_STRING)* | '(' subquery ')')
)?
;

import_keys
: IMPORT ENCRYPTION? KEYS WITH SECRET secret FROM filename force_keystore? identified_by_store with_backup_clause?
;

migrate_keys
: SET ENCRYPTION? KEY IDENTIFIED BY hsm = secret force_keystore? MIGRATE USING keystore_password with_backup_clause?
;

reverse_migrate_keys
: SET ENCRYPTION? KEY IDENTIFIED BY s = secret force_keystore? REVERSE MIGRATE USING hsm = secret
;

move_keys
: MOVE ENCRYPTION? KEYS TO NEW KEYSTORE ksl1 = CHAR_STRING IDENTIFIED BY ksp1 = keystore_password FROM FORCE? KEYSTORE IDENTIFIED BY ksp =
keystore_password (WITH IDENTIFIER IN (CHAR_STRING (',' CHAR_STRING)* | subquery))? with_backup_clause?
;

identified_by_store
: IDENTIFIED BY (EXTERNAL STORE | keystore_password)
;

using_algorithm_clause
: USING ALGORITHM ea = CHAR_STRING
;

using_tag_clause
: USING TAG t = CHAR_STRING
;

secret_management_clauses
: add_update_secret
| delete_secret
| add_update_secret_seps
| delete_secret_seps
;

add_update_secret
: (ADD | UPDATE) SECRET s = CHAR_STRING FOR CLIENT ci = CHAR_STRING using_tag_clause? force_keystore? identified_by_store? with_backup_clause?
;

delete_secret
: DELETE SECRET FOR CLIENT ci = CHAR_STRING force_keystore? identified_by_store with_backup_clause?
;

add_update_secret_seps
: (ADD | UPDATE) SECRET s = CHAR_STRING FOR CLIENT ci = CHAR_STRING using_tag_clause? TO LOCAL? AUTO_LOGIN KEYSTORE directory_path
;

delete_secret_seps
: DELETE SECRET s = CHAR_STRING SQ FOR CLIENT ci = CHAR_STRING FROM LOCAL? AUTO_LOGIN KEYSTORE directory_path
;

zero_downtime_software_patching_clauses
: SWITCHOVER TO? LIBRARY path FOR ALL CONTAINERS //inconsistent documentation
;

with_backup_clause
: WITH BACKUP (USING bi = CHAR_STRING)?
;

identified_by_password_clause
: IDENTIFIED BY keystore_password
;

keystore_password
: DELIMITED_ID
;

path
: CHAR_STRING
;

secret
: DELIMITED_ID
;

// https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4005.htm#SQLRF01105
analyze
: (
ANALYZE (TABLE tableview_name | INDEX index_name) partition_extention_clause?
| ANALYZE CLUSTER cluster_name
) (validation_clauses | LIST CHAINED ROWS into_clause1? | DELETE SYSTEM? STATISTICS)
;

partition_extention_clause
: PARTITION (
'(' partition_name ')'
| FOR '(' partition_key_value (',' partition_key_value)* ')'
)
| SUBPARTITION (
'(' subpartition_name ')'
| FOR '(' subpartition_key_value (',' subpartition_key_value)* ')'
)
;

validation_clauses
: VALIDATE REF UPDATE (SET DANGLING TO NULL_)?
| VALIDATE STRUCTURE (CASCADE FAST | CASCADE online_or_offline? into_clause? | CASCADE)? online_or_offline? into_clause?
;

compute_clauses
: COMPUTE SYSTEM? STATISTICS for_clause?
;

for_clause
: FOR (
TABLE for_clause*
| ALL (INDEXED? COLUMNS (SIZE UNSIGNED_INTEGER)? for_clause* | LOCAL? INDEXES)
| COLUMNS (SIZE UNSIGNED_INTEGER)? (column_name SIZE UNSIGNED_INTEGER)+ for_clause*
)
;

online_or_offline
: OFFLINE
| ONLINE
;

into_clause1
: INTO tableview_name?
;

//Making assumption on partition ad subpartition key value clauses
partition_key_value
: literal
| TIMESTAMP quoted_string
;

subpartition_key_value
: literal
| TIMESTAMP quoted_string
;

//https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4006.htm#SQLRF01106
associate_statistics
: ASSOCIATE STATISTICS WITH (column_association | function_association) storage_table_clause?
;

column_association
: COLUMNS tableview_name '.' column_name (',' tableview_name '.' column_name)* using_statistics_type
;

function_association
: (
FUNCTIONS function_name (',' function_name)*
| PACKAGES package_name (',' package_name)*
| TYPES type_name (',' type_name)*
| INDEXES index_name (',' index_name)*
| INDEXTYPES indextype_name (',' indextype_name)*
) (
using_statistics_type
| default_cost_clause (',' default_selectivity_clause)?
| default_selectivity_clause (',' default_cost_clause)?
)
;

indextype_name
: id_expression
;

using_statistics_type
: USING (statistics_type_name | NULL_)
;

statistics_type_name
: regular_id
;

default_cost_clause
: DEFAULT COST '(' cpu_cost ',' io_cost ',' network_cost ')'
;

cpu_cost
: UNSIGNED_INTEGER
;

io_cost
: UNSIGNED_INTEGER
;

network_cost
: UNSIGNED_INTEGER
;

default_selectivity_clause
: DEFAULT SELECTIVITY default_selectivity
;

default_selectivity
: UNSIGNED_INTEGER
;

storage_table_clause
: WITH (SYSTEM | USER) MANAGED STORAGE TABLES
;

// https://docs.oracle.com/database/121/SQLRF/statements_4008.htm#SQLRF56110
unified_auditing
: {this.isVersion12()}? AUDIT (
POLICY policy_name ((BY | EXCEPT) audit_user (',' audit_user))? (WHENEVER NOT? SUCCESSFUL)?
| CONTEXT NAMESPACE oracle_namespace ATTRIBUTES attribute_name (',' attribute_name) (
BY audit_user (',' audit_user)*
)?
)
;

policy_name
: identifier
;

// https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF01107
// https://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF01107

audit_direct_path
: {this.isVersion12()}? DIRECT_PATH auditing_by_clause
;

audit_container_clause
: {this.isVersion12()}? (CONTAINER EQUALS_OP (CURRENT | ALL))
;

auditing_by_clause
: BY audit_user (',' audit_user)*
;

audit_user
: regular_id
;

audit_schema_object_clause
: (sql_operation (',' sql_operation)* | ALL) auditing_on_clause
;

auditing_on_clause
: ON (
object_name
| DIRECTORY regular_id
| MINING MODEL model_name
| {this.isVersion12()}? SQL TRANSLATION PROFILE profile_name
| DEFAULT
)
;

model_name
: (id_expression '.')? id_expression
;

object_name
: (id_expression '.')? id_expression
;

profile_name
: (id_expression '.')? id_expression
;

sql_statement_shortcut
: ALTER SYSTEM
| CLUSTER
| CONTEXT
| DATABASE LINK
| DIMENSION
| DIRECTORY
| INDEX
| MATERIALIZED VIEW
| NOT EXISTS
| OUTLINE
| {this.isVersion12()}? PLUGGABLE DATABASE
| PROCEDURE
| PROFILE
| PUBLIC DATABASE LINK
| PUBLIC SYNONYM
| ROLE
| ROLLBACK SEGMENT
| SEQUENCE
| SESSION
| SYNONYM
| SYSTEM AUDIT
| SYSTEM GRANT
| TABLE
| TABLESPACE
| TRIGGER
| TYPE
| USER
| VIEW
| ALTER SEQUENCE
| ALTER TABLE
| COMMENT TABLE
| DELETE TABLE
| EXECUTE PROCEDURE
| GRANT DIRECTORY
| GRANT PROCEDURE
| GRANT SEQUENCE
| GRANT TABLE
| GRANT TYPE
| INSERT TABLE
| LOCK TABLE
| SELECT SEQUENCE
| SELECT TABLE
| UPDATE TABLE
;

drop_index
: DROP INDEX index_name (IF EXISTS)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DISASSOCIATE-STATISTICS.html
disassociate_statistics
: DISASSOCIATE STATISTICS FROM (
COLUMNS (schema_name '.')? tb = id_expression '.' c = id_expression (
',' (schema_name '.')? tb = id_expression '.' c = id_expression
)*
| FUNCTIONS (schema_name '.')? fn = id_expression (
',' (schema_name '.')? fn = id_expression
)*
| PACKAGES (schema_name '.')? pkg = id_expression (
',' (schema_name '.')? pkg = id_expression
)*
| TYPES (schema_name '.')? t = id_expression (',' (schema_name '.')? t = id_expression)*
| INDEXES (schema_name '.')? ix = id_expression (',' (schema_name '.')? ix = id_expression)*
| INDEXTYPES (schema_name '.')? it = id_expression (
',' (schema_name '.')? it = id_expression
)*
) FORCE?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-INDEXTYPE.html
drop_indextype
: DROP INDEXTYPE (schema_name '.')? it = id_expression FORCE?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-INMEMORY-JOIN-GROUP.html
drop_inmemory_join_group
: DROP INMEMORY JOIN GROUP (schema_name '.')? jg = id_expression
;

flashback_table
: FLASHBACK TABLE tableview_name (',' tableview_name)* TO (
((SCN | TIMESTAMP) expression | RESTORE POINT restore_point) ((ENABLE | DISABLE) TRIGGERS)?
| BEFORE DROP (RENAME TO tableview_name)?
)
;

restore_point
: identifier ('.' id_expression)*
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/PURGE.html
purge_statement
: PURGE (
(TABLE | INDEX) id_expression
| TABLESPACE SET? ts = id_expression (USER u = id_expression)?
| RECYCLEBIN
| DBA_RECYCLEBIN
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/NOAUDIT-Traditional-Auditing.html
noaudit_statement
: NOAUDIT (
audit_operation_clause auditing_by_clause?
| audit_schema_object_clause
| NETWORK
| DIRECT_PATH LOAD auditing_by_clause?
) (WHENEVER NOT? SUCCESSFUL)? container_clause?
;

rename_object
: RENAME object_name TO object_name
;

grant_statement
: GRANT (','? (role_name | system_privilege | object_privilege paren_column_list?))+ (
ON grant_object_name
)? TO (grantee_name | PUBLIC) (',' (grantee_name | PUBLIC))* (WITH (ADMIN | DELEGATE) OPTION)? (
WITH HIERARCHY OPTION
)? (WITH GRANT OPTION)? container_clause?
;

container_clause
: CONTAINER EQUALS_OP (CURRENT | ALL)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/REVOKE.html
revoke_statement
: REVOKE (
(revoke_system_privilege | revoke_object_privileges) container_clause?
| revoke_roles_from_programs
)
;

revoke_system_privilege
: (system_privilege | role_name | ALL PRIVILEGES) FROM revokee_clause
;

revokee_clause
: (id_expression | PUBLIC) (',' (id_expression | PUBLIC))*
;

revoke_object_privileges
: (object_privilege | ALL PRIVILEGES?) (',' (object_privilege | ALL PRIVILEGES?))* on_object_clause FROM revokee_clause (
CASCADE CONSTRAINTS
| FORCE
)?
;

revoke_roles_from_programs
: (role_name (',' role_name)* | ALL) FROM program_unit (',' program_unit)*
;

program_unit
: (FUNCTION | PROCEDURE | PACKAGE) (schema_name '.')? id_expression
;

create_dimension
: CREATE DIMENSION identifier level_clause+ (
hierarchy_clause
| attribute_clause
| extended_attribute_clause
)+
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-DIRECTORY.html
create_directory
: CREATE (OR REPLACE)? DIRECTORY directory_name (SHARING '=' (METADATA | NONE))? AS directory_path
;

directory_name
: regular_id
;

directory_path
: CHAR_STRING
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-INMEMORY-JOIN-GROUP.html
create_inmemory_join_group
: CREATE INMEMORY JOIN GROUP (schema_name '.')? jg = id_expression '(' (schema_name '.')? t = id_expression '(' c = id_expression ')' (
',' (schema_name '.')? t = id_expression '(' c = id_expression ')'
)+ ')'
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-HIERARCHY.html
drop_hierarchy
: DROP HIERARCHY (schema_name '.')? hn = id_expression
;

// https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/alter_library.htm#LNPLS99946
// https://docs.oracle.com/database/121/LNPLS/alter_library.htm#LNPLS99946
alter_library
: ALTER LIBRARY library_name (
COMPILE library_debug? compiler_parameters_clause* (REUSE SETTINGS)?
| library_editionable
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-JAVA.html
drop_java
: DROP JAVA (SOURCE | CLASS | RESOURCE) (schema_name '.')? id_expression
;

drop_library
: DROP LIBRARY library_name
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-JAVA.html
create_java
: CREATE (OR REPLACE)? (AND (RESOLVE | COMPILE))? NOFORCE? JAVA (
(SOURCE | RESOURCE) NAMED (schema_name '.')? pn = id_expression
| CLASS (SCHEMA id_expression)?
) (SHARING '=' (METADATA | NONE))? invoker_rights_clause? (
RESOLVER '(' ('(' CHAR_STRING ','? (sn = id_expression | '-') ')')+ ')'
)? (
USING (
BFILE '(' d = id_expression ',' filename ')'
| (CLOB | BLOB | BFILE) subquery
| CHAR_STRING
)
| AS CHAR_STRING
)
;

create_library
: CREATE (OR REPLACE)? (EDITIONABLE | NONEDITIONABLE)? LIBRARY plsql_library_source
;

plsql_library_source
: library_name (IS | AS) quoted_string (IN directory_name)? (AGENT quoted_string)? (
CREDENTIAL credential_name
)?
;

credential_name
: (id_expression '.')? id_expression
;

library_editionable
: {this.isVersion12()}? (EDITIONABLE | NONEDITIONABLE)
;

library_debug
: {this.isVersion12()}? DEBUG
;

compiler_parameters_clause
: parameter_name EQUALS_OP parameter_value
;

parameter_value
: regular_id
| CHAR_STRING
;

library_name
: (regular_id '.')? regular_id
;

level_clause
: LEVEL identifier IS (
table_name '.' column_name
| '(' table_name '.' column_name (',' table_name '.' column_name)* ')'
) (SKIP_ WHEN NULL_)?
;

hierarchy_clause
: HIERARCHY identifier '(' identifier (CHILD OF identifier)+ dimension_join_clause? ')'
;

dimension_join_clause
: (JOIN KEY column_one_or_more_sub_clause REFERENCES identifier)+
;

attribute_clause
: (ATTRIBUTE identifier DETERMINES column_one_or_more_sub_clause)+
;

extended_attribute_clause
: ATTRIBUTE identifier (LEVEL identifier DETERMINES column_one_or_more_sub_clause)+
;

column_one_or_more_sub_clause
: column_name
| '(' column_name (',' column_name)* ')'
;

// https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4004.htm#SQLRF01104
// https://docs.oracle.com/database/121/SQLRF/statements_4004.htm#SQLRF01104
alter_view
: ALTER VIEW tableview_name (
ADD out_of_line_constraint
| MODIFY CONSTRAINT constraint_name (RELY | NORELY)
| DROP (
CONSTRAINT constraint_name
| PRIMARY KEY
| UNIQUE '(' column_name (',' column_name)* ')'
)
| COMPILE
| READ (ONLY | WRITE)
| alter_view_editionable?
)
;

alter_view_editionable
: {this.isVersion12()}? (EDITIONABLE | NONEDITIONABLE)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-VIEW.html
create_view
: CREATE (OR REPLACE)? (NO? FORCE)? editioning_clause? VIEW (schema_name '.')? v = id_expression (
IF NOT EXISTS
)? (SHARING '=' (METADATA | EXTENDED? DATA | NONE))? view_options? (
DEFAULT COLLATION cn = id_expression
)? (BEQUEATH (CURRENT_USER | DEFINER))? AS select_only_statement subquery_restriction_clause? (
CONTAINER_MAP
| CONTAINERS_DEFAULT
)?
;

editioning_clause
: EDITIONING
| EDITIONABLE EDITIONING?
| NONEDITIONABLE
;

view_options
: view_alias_constraint
| object_view_clause
| xmltype_view_clause
;

view_alias_constraint
: '(' (','? (table_alias inline_constraint* | out_of_line_constraint))+ ')'
;

object_view_clause
: OF (schema_name '.')? tn = id_expression (
WITH OBJECT (IDENTIFIER | ID) (DEFAULT | '(' REGULAR_ID (',' REGULAR_ID)* ')')
| UNDER (schema_name '.')? sv = id_expression
) ('(' (','? (out_of_line_constraint | REGULAR_ID inline_constraint))+ ')')*
;

inline_constraint
: (CONSTRAINT constraint_name)? (
NOT? NULL_
| UNIQUE
| PRIMARY KEY
| references_clause
| check_constraint
) constraint_state?
;

inline_ref_constraint
: SCOPE IS tableview_name
| WITH ROWID
| (CONSTRAINT constraint_name)? references_clause constraint_state?
;

out_of_line_ref_constraint
: SCOPE FOR '(' ref_col_or_attr = regular_id ')' IS tableview_name
| REF '(' ref_col_or_attr = regular_id ')' WITH ROWID
| (CONSTRAINT constraint_name)? FOREIGN KEY '(' (','? ref_col_or_attr = regular_id)+ ')' references_clause constraint_state?
;

out_of_line_constraint
: (
((CONSTRAINT | CONSTRAINTS) constraint_name)? (
UNIQUE '(' column_name (',' column_name)* ')'
| PRIMARY KEY '(' column_name (',' column_name)* ')'
| foreign_key_clause
| CHECK '(' condition ')'
)
)
constraint_state?
parallel_clause?
;

xmltype_view_clause
: OF XMLTYPE xml_schema_spec? WITH OBJECT (IDENTIFIER | ID) (
DEFAULT
| '(' expression (',' expression)* ')'
)
;

xml_schema_spec
: (XMLSCHEMA xml_schema_url)? ELEMENT (element | xml_schema_url '#' element) (
STORE ALL VARRAYS AS (LOBS | TABLES)
)? (allow_or_disallow NONSCHEMA)? (allow_or_disallow ANYSCHEMA)?
;

xml_schema_url
: DELIMITED_ID
;

element
: DELIMITED_ID
;

tablespace_logging_clauses
: logging_clause
| NO? FORCE LOGGING
;

tablespace_group_clause
: TABLESPACE GROUP (tablespace_group_name | CHAR_STRING)
;

tablespace_group_name
: regular_id
;

flashback_mode_clause
: FLASHBACK (ON | OFF)
;

new_tablespace_name
: tablespace
;

create_tablespace
: CREATE (BIGFILE | SMALLFILE)? (
permanent_tablespace_clause
| temporary_tablespace_clause
| undo_tablespace_clause
)
;

tablespace_encryption_spec
: USING encrypt_algorithm = CHAR_STRING
;

logging_clause
: LOGGING
| NOLOGGING
| FILESYSTEM_LIKE_LOGGING
;

extent_management_clause
: EXTENT MANAGEMENT LOCAL (AUTOALLOCATE | UNIFORM (SIZE size_clause)?)?
;

segment_management_clause
: SEGMENT SPACE_KEYWORD MANAGEMENT (AUTO | MANUAL)
;

temporary_tablespace_clause
: TEMPORARY TABLESPACE tablespace_name = id_expression (IF NOT EXISTS)? tempfile_specification? tablespace_group_clause? extent_management_clause?
;

undo_tablespace_clause
: UNDO TABLESPACE tablespace_name = id_expression (IF NOT EXISTS)? datafile_specification? extent_management_clause? tablespace_retention_clause?
;

tablespace_retention_clause
: RETENTION (GUARANTEE | NOGUARANTEE)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLESPACE-SET.html
create_tablespace_set
: CREATE TABLESPACE SET tss = id_expression (IN SHARDSPACE ss = id_expression)? (
USING TEMPLATE '(' (DATAFILE file_specification (',' file_specification)*)? permanent_tablespace_attrs+ ')'
)?
;

tablespace_encryption_clause
: ENCRYPTION (tablespace_encryption_spec? ENCRYPT | DECRYPT)
;

default_tablespace_params
: DEFAULT default_table_compression? default_index_compression? inmmemory_clause? ilm_clause? storage_clause?
;

default_table_compression
: TABLE (COMPRESS FOR (OLTP | QUERY low_high | ARCHIVE low_high) | NOCOMPRESS)
;

low_high
: LOW
| HIGH
;

default_index_compression
: INDEX (COMPRESS ADVANCED low_high | NOCOMPRESS)
;

inmmemory_clause
: INMEMORY inmemory_attributes? (
TEXT (
column_name (',' column_name)*
| column_name USING policy_name (',' column_name USING policy_name)*
)
)?
| NO INMEMORY
;

// asm_filename is just a charater string. Would need to parse the string
// to find diskgroup...
datafile_specification
: DATAFILE (','? datafile_tempfile_spec)
;

tempfile_specification
: TEMPFILE (','? datafile_tempfile_spec)
;

datafile_tempfile_spec
: CHAR_STRING? (SIZE size_clause)? REUSE? autoextend_clause?
;

redo_log_file_spec
: (filename | '(' filename (',' filename)* ')') (SIZE size_clause)? (BLOCKSIZE size_clause)? REUSE?
;

autoextend_clause
: AUTOEXTEND (OFF | ON (NEXT size_clause)? maxsize_clause?)
;

maxsize_clause
: MAXSIZE (UNLIMITED | size_clause)
;

build_clause
: BUILD (IMMEDIATE | DEFERRED)
;

parallel_clause
: NOPARALLEL
| PARALLEL parallel_count = UNSIGNED_INTEGER?
;

alter_mv_option1
: alter_mv_refresh
//TODO | MODIFY scoped_table_ref_constraint
;

rollback_segment
: regular_id
;

modify_mv_column_clause
: MODIFY '(' column_name (ENCRYPT encryption_spec | DECRYPT)? ')'
;

add_mv_log_column_clause
: ADD '(' column_name ')'
;

move_mv_log_clause
: MOVE segment_attributes_clause parallel_clause?
;

mv_log_augmentation
: ADD (
(OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE) ('(' column_name (',' column_name)* ')')?
| '(' column_name (',' column_name)* ')'
) new_values_clause?
;

// Should bound this to just date/time expr
datetime_expr
: expression
;

// Should bound this to just interval expr
interval_expr
: expression
;

synchronous_or_asynchronous
: SYNCHRONOUS
| ASYNCHRONOUS
;

including_or_excluding
: INCLUDING
| EXCLUDING
;

create_materialized_view_log
: CREATE MATERIALIZED VIEW LOG ON tableview_name (
(
physical_attributes_clause
| TABLESPACE tablespace_name = id_expression
| logging_clause
| (CACHE | NOCACHE)
)+
)? parallel_clause?
// table_partitioning_clauses TODO
(
WITH (','? ( OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | COMMIT SCN))* (
'(' ( ','? regular_id)+ ')' new_values_clause?
)? mv_log_purge_clause?
)*
;

new_values_clause
: (INCLUDING | EXCLUDING) NEW VALUES
;

mv_log_purge_clause
: PURGE (
IMMEDIATE (SYNCHRONOUS | ASYNCHRONOUS)?
// |START WITH CLAUSES TODO
)
;

create_materialized_zonemap
: CREATE MATERIALIZED ZONEMAP zonemap_name (LEFT_PAREN column_list RIGHT_PAREN)? zonemap_attributes? zonemap_refresh_clause? (
(ENABLE | DISABLE) PRUNING
)? (create_zonemap_on_table | create_zonemap_as_subquery)
;

drop_materialized_zonemap
: DROP MATERIALIZED ZONEMAP zonemap_name
;

zonemap_name
: identifier ('.' id_expression)?
;

operator_name
: identifier ('.' id_expression)?
;

operator_function_name
: identifier ('.' id_expression)*
;

create_zonemap_on_table
: ON tableview_name LEFT_PAREN column_list RIGHT_PAREN
;

create_zonemap_as_subquery
: AS subquery
;

alter_operator
: ALTER OPERATOR operator_name (add_binding_clause | drop_binding_clause | COMPILE)
;

drop_operator
: DROP OPERATOR operator_name FORCE?
;

create_operator
: CREATE (OR REPLACE)? OPERATOR operator_name BINDING binding_clause (COMMA binding_clause)* (
SHARING '=' (METADATA | NONE)
)?
;

binding_clause
: LEFT_PAREN datatype (COMMA datatype)* RIGHT_PAREN RETURN LEFT_PAREN? datatype RIGHT_PAREN? implementation_clause? using_function_clause
;

add_binding_clause
: ADD BINDING binding_clause
;

implementation_clause
: ANCILLARY TO primary_operator_list
| operator_context_clause
;

primary_operator_list
: primary_operator_item (COMMA primary_operator_item)*
;

primary_operator_item
: schema_object_name LEFT_PAREN datatype (COMMA datatype)* RIGHT_PAREN
;

operator_context_clause
: WITH INDEX CONTEXT COMMA SCAN CONTEXT implementation_type_name (COMPUTE ANCILLARY DATA)? (
WITH COLUMN CONTEXT
)?
;

using_function_clause
: USING operator_function_name
;

drop_binding_clause
: DROP BINDING LEFT_PAREN datatype (COMMA datatype)* RIGHT_PAREN FORCE?
;

create_materialized_view
: CREATE MATERIALIZED VIEW tableview_name (OF type_name)? (
'(' (scoped_table_ref_constraint | mv_column_alias) (
',' (scoped_table_ref_constraint | mv_column_alias)
)* ')'
)? (
ON PREBUILT TABLE ( (WITH | WITHOUT) REDUCED PRECISION)?
| physical_properties? (CACHE | NOCACHE)? parallel_clause? build_clause?
) (
USING INDEX ((physical_attributes_clause | TABLESPACE mv_tablespace = id_expression)+)*
| USING NO INDEX
)? create_mv_refresh? (FOR UPDATE)? ((DISABLE | ENABLE) QUERY REWRITE)? AS select_only_statement
;

scoped_table_ref_constraint
: SCOPE FOR '(' ref_column_or_attribute = identifier ')' IS (schema_name '.')? scope_table_name_or_c_alias = identifier
;

mv_column_alias
: (identifier | quoted_string) (ENCRYPT encryption_spec)?
;

drop_materialized_view
: DROP MATERIALIZED VIEW tableview_name (PRESERVE TABLE)?
;

create_context
: CREATE (OR REPLACE)? CONTEXT oracle_namespace USING (schema_object_name '.')? package_name (
INITIALIZED (EXTERNALLY | GLOBALLY)
| ACCESSED GLOBALLY
)?
;

oracle_namespace
: id_expression
;

//https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5001.htm#SQLRF01201
create_cluster
: CREATE CLUSTER cluster_name '(' column_name datatype SORT? (',' column_name datatype SORT?)* ')' (
physical_attributes_clause
| SIZE size_clause
| TABLESPACE tablespace
| INDEX
| (SINGLE TABLE)? HASHKEYS UNSIGNED_INTEGER (HASH IS expression)?
)* parallel_clause? (ROWDEPENDENCIES | NOROWDEPENDENCIES)? (CACHE | NOCACHE)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-PROFILE.html
create_profile
: CREATE MANDATORY? PROFILE p = id_expression LIMIT (resource_parameters | password_parameters)+ container_clause?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-LOCKDOWN-PROFILE.html
create_lockdown_profile
: CREATE LOCKDOWN PROFILE id_expression (static_base_profile | dynamic_base_profile)?
;

static_base_profile
: FROM bp = id_expression
;

dynamic_base_profile
: INCLUDING bp = id_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-OUTLINE.html
create_outline
: CREATE (OR REPLACE)? (PUBLIC | PRIVATE)? OUTLINE (o = id_expression)? (
FROM (PUBLIC | PRIVATE)? so = id_expression
)? (FOR CATEGORY c = id_expression)? (ON statement)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-RESTORE-POINT.html
create_restore_point
: CREATE CLEAN? RESTORE POINT rp = id_expression (FOR PLUGGABLE DATABASE pdb = id_expression)? (
AS OF (TIMESTAMP | SCN) expression
)? (PRESERVE | GUARANTEE FLASHBACK DATABASE)?
;

create_role
: CREATE ROLE role_name role_identified_clause? container_clause?
;

xmltype_table
: OF XMLTYPE ('(' object_properties ')')? (XMLTYPE xmltype_storage)? xmlschema_spec? xmltype_virtual_columns? (
ON COMMIT (DELETE | PRESERVE) ROWS
)? oid_clause? oid_index_clause? physical_properties? table_properties
;

xmltype_virtual_columns
: VIRTUAL COLUMNS '(' column_name AS '(' expression ')' (',' column_name AS '(' expression ')')* ')'
;

xmltype_column_properties
: XMLTYPE COLUMN? column_name xmltype_storage? xmlschema_spec?
;

xmlschema_spec
: (XMLSCHEMA DELIMITED_ID)? ELEMENT DELIMITED_ID (allow_or_disallow NONSCHEMA)? (
allow_or_disallow ANYSCHEMA
)?
;

object_table
: OF (schema_name '.')? object_type object_table_substitution? (
'(' object_properties (',' object_properties)* ')'
)? (ON COMMIT (DELETE | PRESERVE) ROWS)? oid_clause? oid_index_clause? physical_properties? table_properties
;

object_type
: regular_id
;

oid_index_clause
: OIDINDEX index_name? '(' (physical_attributes_clause | TABLESPACE tablespace)+ ')'
;

oid_clause
: OBJECT IDENTIFIER IS (SYSTEM GENERATED | PRIMARY KEY)
;

object_table_substitution
: NOT? SUBSTITUTABLE AT ALL LEVELS
;

relational_table
: ('(' relational_property (',' relational_property)* ')')? immutable_table_clauses blockchain_table_clauses? (
DEFAULT COLLATION collation_name
)? (ON COMMIT (DROP | PRESERVE) DEFINITION)? (ON COMMIT (DELETE | PRESERVE) ROWS)? physical_properties? table_properties
;

immutable_table_clauses
: immutable_table_no_drop_clause? immutable_table_no_delete_clause?
;

immutable_table_no_drop_clause
: NO DROP (UNTIL numeric DAYS IDLE)?
;

immutable_table_no_delete_clause
: NO DELETE (LOCKED? | UNTIL numeric DAYS AFTER INSERT LOCKED?)
;

blockchain_table_clauses
: blockchain_drop_table_clause blockchain_row_retention_clause blockchain_hash_and_data_format_clause
;

blockchain_drop_table_clause
: NO DROP (UNTIL numeric DAYS IDLE)?
;

blockchain_row_retention_clause
: NO DELETE (LOCKED? | UNTIL numeric DAYS AFTER INSERT LOCKED?)
;

blockchain_hash_and_data_format_clause
: HASHING USING SHA2_512_Q VERSION V1_Q
;

collation_name
: identifier
;

table_properties
: column_properties? read_only_clause? indexing_clause? table_partitioning_clauses? attribute_clustering_clause? (
CACHE
| NOCACHE
)? result_cache_clause? parallel_clause? monitoring_nomonitoring? (ROWDEPENDENCIES | NOROWDEPENDENCIES)? enable_disable_clause* row_movement_clause?
logical_replication_clause? flashback_archive_clause? physical_properties? (ROW ARCHIVAL)? (
AS select_only_statement
| FOR EXCHANGE WITH TABLE (schema_name '.')? table_name
)?
;

read_only_clause
: READ (ONLY | WRITE)
;

indexing_clause
: INDEXING (ON | OFF)
;

attribute_clustering_clause
: CLUSTERING clustering_join? cluster_clause (yes_no? ON LOAD)? (yes_no? ON DATA MOVEMENT)? zonemap_clause?
;

clustering_join
: (schema_name '.')? table_name clustering_join_item (',' clustering_join_item)*
;

clustering_join_item
: JOIN (schema_name '.')? table_name ON '(' equijoin_condition ')'
;

equijoin_condition
: expression
;

cluster_clause
: BY (LINEAR | INTERLEAVED)? ORDER clustering_columns
;

clustering_columns
: clustering_column_group
| '(' clustering_column_group (',' clustering_column_group)* ')'
;

clustering_column_group
: '(' column_name (',' column_name)* ')'
;

yes_no
: YES
| NO
;

zonemap_clause
: WITH MATERIALIZED ZONEMAP ('(' zonemap_name ')')?
| WITHOUT MATERIALIZED ZONEMAP
;

logical_replication_clause
: DISABLE LOGICAL REPLICATION
| ENABLE LOGICAL REPLICATION ((ALL | ALLOW NOVALIDATE) KEYS)?
;

table_name
: identifier
| IDENTIFIER
;

range_partitions
: PARTITION BY RANGE '(' column_name (',' column_name)* ')' (
INTERVAL '(' expression ')' (STORE IN '(' tablespace (',' tablespace)* ')')?
)? '(' PARTITION partition_name? range_values_clause table_partition_description (
',' PARTITION partition_name? range_values_clause table_partition_description
)* ')'
;

list_partitions
: PARTITION BY LIST '(' column_name ')' '(' PARTITION partition_name? list_values_clause table_partition_description (
',' PARTITION partition_name? list_values_clause table_partition_description
)* ')'
;

hash_partitions
: PARTITION BY HASH '(' column_name (',' column_name)* ')' (
individual_hash_partitions
| hash_partitions_by_quantity
)
;

individual_hash_partitions
: '(' PARTITION partition_name? partitioning_storage_clause? (
',' PARTITION partition_name? partitioning_storage_clause?
)* ')'
;

hash_partitions_by_quantity
: PARTITIONS hash_partition_quantity (STORE IN '(' tablespace (',' tablespace)* ')')? (
table_compression
| key_compression
)? (OVERFLOW STORE IN '(' tablespace (',' tablespace)* ')')?
;

hash_partition_quantity
: UNSIGNED_INTEGER
;

composite_range_partitions
: PARTITION BY RANGE '(' column_name (',' column_name)* ')' (
INTERVAL '(' expression ')' (STORE IN '(' tablespace (',' tablespace)* ')')?
)? (subpartition_by_range | subpartition_by_list | subpartition_by_hash) '(' range_partition_desc (
',' range_partition_desc
)* ')'
;

composite_list_partitions
: PARTITION BY LIST '(' column_name ')' (
subpartition_by_range
| subpartition_by_list
| subpartition_by_hash
) '(' list_partition_desc (',' list_partition_desc)* ')'
;

composite_hash_partitions
: PARTITION BY HASH '(' (',' column_name)+ ')' (
subpartition_by_range
| subpartition_by_list
| subpartition_by_hash
) (individual_hash_partitions | hash_partitions_by_quantity)
;

reference_partitioning
: PARTITION BY REFERENCE '(' regular_id ')' (
'(' reference_partition_desc (',' reference_partition_desc)* ')'
)?
;

reference_partition_desc
: PARTITION partition_name? table_partition_description
;

system_partitioning
: PARTITION BY SYSTEM (
PARTITIONS UNSIGNED_INTEGER
| reference_partition_desc (',' reference_partition_desc)*
)?
;

range_partition_desc
: PARTITION partition_name? range_values_clause? table_partition_description (
(
'(' (
range_subpartition_desc (',' range_subpartition_desc)*
| list_subpartition_desc (',' list_subpartition_desc)*
| individual_hash_subparts (',' individual_hash_subparts)*
) ')'
| hash_subparts_by_quantity
)
)?
;

list_partition_desc
: PARTITION partition_name? list_values_clause? table_partition_description (
(
'(' (
range_subpartition_desc (',' range_subpartition_desc)*
| list_subpartition_desc (',' list_subpartition_desc)*
| individual_hash_subparts (',' individual_hash_subparts)*
) ')'
| hash_subparts_by_quantity
)
)?
;

subpartition_template
: SUBPARTITION TEMPLATE (
(
'(' (
range_subpartition_desc (',' range_subpartition_desc)*
| list_subpartition_desc (',' list_subpartition_desc)*
| individual_hash_subparts (',' individual_hash_subparts)*
) ')'
| hash_subpartition_quantity
)
)
;

hash_subpartition_quantity
: UNSIGNED_INTEGER
;

subpartition_by_range
: SUBPARTITION BY RANGE '(' column_name (',' column_name)* ')' subpartition_template?
;

subpartition_by_list
: SUBPARTITION BY LIST '(' column_name ')' subpartition_template?
;

subpartition_by_hash
: SUBPARTITION BY HASH '(' column_name (',' column_name)* ')' (
SUBPARTITIONS UNSIGNED_INTEGER (STORE IN '(' tablespace (',' tablespace)* ')')?
| subpartition_template
)?
;

subpartition_name
: partition_name
;

range_subpartition_desc
: SUBPARTITION subpartition_name? range_values_clause partitioning_storage_clause?
;

list_subpartition_desc
: SUBPARTITION subpartition_name? list_values_clause partitioning_storage_clause?
;

individual_hash_subparts
: SUBPARTITION subpartition_name? partitioning_storage_clause?
;

hash_subparts_by_quantity
: SUBPARTITIONS UNSIGNED_INTEGER (STORE IN '(' tablespace (',' tablespace)* ')')?
;

range_values_clause
: VALUES LESS THAN '(' range_values_list ')'
;

range_values_list
: literal (',' literal)*
| TIMESTAMP literal (',' TIMESTAMP literal)*
;

list_values_clause
: VALUES '(' (literal (',' literal)* | TIMESTAMP literal (',' TIMESTAMP literal)* | DEFAULT) ')'
;

table_partition_description
: deferred_segment_creation? segment_attributes_clause? (table_compression | key_compression)? (
OVERFLOW segment_attributes_clause?
)? (lob_storage_clause | varray_col_properties | nested_table_col_properties)*
;

lob_partitioning_storage
: LOB '(' lob_item ')' STORE AS (BASICFILE | SECUREFILE)? (
lob_segname ('(' TABLESPACE tablespace ')')?
| '(' TABLESPACE tablespace ')'
)
;

datatype_null_enable
: column_name datatype SORT? (DEFAULT expression)? (
ENCRYPT (USING CHAR_STRING)? (IDENTIFIED BY REGULAR_ID)? CHAR_STRING? (NO? SALT)?
)? (NOT NULL_)? (ENABLE | DISABLE)?
;

// avoid to match an empty string in
inmemory_table_clause
: inmemory_column_clause+
| (INMEMORY inmemory_attributes? | NO INMEMORY) inmemory_column_clause*
;

// avoid to match an empty string in
inmemory_attributes
: inmemory_memcompress inmemory_priority? inmemory_distribute? inmemory_duplicate?
| inmemory_priority inmemory_distribute? inmemory_duplicate?
| inmemory_distribute inmemory_duplicate?
| inmemory_duplicate
;

inmemory_memcompress
: MEMCOMPRESS FOR (DML | (QUERY | CAPACITY) (LOW | HIGH)?)
| NO MEMCOMPRESS
;

inmemory_priority
: PRIORITY (NONE | LOW | MEDIUM | HIGH | CRITICAL)
;

inmemory_duplicate
: DUPLICATE ALL?
| NO DUPLICATE
;

inmemory_column_clause
: (INMEMORY inmemory_memcompress? | NO INMEMORY) '(' column_list ')'
;

deferred_segment_creation
: SEGMENT CREATION (IMMEDIATE | DEFERRED)
;

segment_attributes_clause
: (
physical_attributes_clause
| TABLESPACE (tablespace_name = id_expression | SET? identifier)
| table_compression
| logging_clause
)+
;

physical_properties
: deferred_segment_creation? segment_attributes_clause table_compression? inmemory_table_clause? ilm_clause?
| deferred_segment_creation? (
ORGANIZATION (
HEAP segment_attributes_clause? heap_org_table_clause
| INDEX segment_attributes_clause? index_org_table_clause
| EXTERNAL external_table_clause
)
| EXTERNAL PARTITION ATTRIBUTES external_table_clause (REJECT LIMIT)?
)
| CLUSTER cluster_name '(' column_name (',' column_name)* ')'
;

ilm_policy_clause
: ilm_compression_policy
| ilm_tiering_policy
| ilm_inmemory_policy
;

ilm_compression_policy
: table_compression segment_group ilm_after_on
| ((ROW | COLUMN) STORE COMPRESS (ADVANCED | FOR QUERY)) ROW AFTER ilm_time_period OF NO MODIFICATION
;

ilm_tiering_policy
: TIER TO tablespace (
segment_group? (ON function_name)?
| READ ONLY segment_group? ilm_after_on
)
;

ilm_after_on
: AFTER ilm_time_period OF (NO (ACCESS | MODIFICATION) | CREATION)
| ON function_name
;

segment_group
: SEGMENT
| GROUP
;

ilm_inmemory_policy
: (SET INMEMORY inmemory_attributes? | MODIFY INMEMORY inmemory_memcompress | NO INMEMORY) SEGMENT? ilm_after_on
;

heap_org_table_clause
: table_compression? inmemory_table_clause? ilm_clause?
;

external_table_clause
: '(' (TYPE access_driver_type)? external_table_data_props ')' (
REJECT LIMIT (numeric | UNLIMITED)
)? inmemory_table_clause?
;

access_driver_type
: ORACLE_LOADER
| ORACLE_DATAPUMP
| ORACLE_HDFS
| ORACLE_HIVE
;

external_table_data_props
: (DEFAULT DIRECTORY directory_name)? (
ACCESS PARAMETERS (
'(' CHAR_STRING ')'
| '(' opaque_format_spec ')'
| USING CLOB select_only_statement
)
)? (LOCATION '(' directory_name COLON CHAR_STRING (',' directory_name COLON CHAR_STRING)* ')')?
;

opaque_format_spec
: //TODO https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-external-tables.html
;

row_movement_clause
: (ENABLE | DISABLE)? ROW MOVEMENT
;

flashback_archive_clause
: FLASHBACK ARCHIVE fa = id_expression?
| NO FLASHBACK ARCHIVE
;

log_grp
: UNSIGNED_INTEGER
;

supplemental_table_logging
: ADD SUPPLEMENTAL LOG (supplemental_log_grp_clause | supplemental_id_key_clause) (
',' SUPPLEMENTAL LOG (supplemental_log_grp_clause | supplemental_id_key_clause)
)*
| DROP SUPPLEMENTAL LOG (supplemental_id_key_clause | GROUP log_grp) (
',' SUPPLEMENTAL LOG (supplemental_id_key_clause | GROUP log_grp)
)*
;

supplemental_log_grp_clause
: GROUP log_grp '(' regular_id (NO LOG)? (',' regular_id (NO LOG)?)* ')' ALWAYS?
;

supplemental_id_key_clause
: DATA '(' (','? ( ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY))+ ')' COLUMNS
;

allocate_extent_clause
: ALLOCATE EXTENT (
'(' (
SIZE size_clause
| DATAFILE datafile = CHAR_STRING
| INSTANCE inst_num = UNSIGNED_INTEGER
)+ ')'
)?
;

deallocate_unused_clause
: DEALLOCATE UNUSED (KEEP size_clause)?
;

shrink_clause
: SHRINK SPACE_KEYWORD COMPACT? CASCADE?
;

records_per_block_clause
: (MINIMIZE | NOMINIMIZE)? RECORDS_PER_BLOCK
;

upgrade_table_clause
: UPGRADE (NOT? INCLUDING DATA) column_properties
;

truncate_table
: TRUNCATE TABLE tableview_name PURGE?
;

drop_table
: DROP TABLE tableview_name (IF EXISTS)? PURGE?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-TABLESPACE.html
drop_tablespace
: DROP TABLESPACE ts = id_expression (IF EXISTS)? ((DROP | KEEP) QUOTA?)? including_contents_clause?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-TABLESPACE-SET.html
drop_tablespace_set
: DROP TABLESPACE SET tss = id_expression including_contents_clause?
;

including_contents_clause
: INCLUDING CONTENTS ((AND | KEEP) DATAFILES)? (CASCADE CONSTRAINTS)?
;

drop_view
: DROP VIEW tableview_name (IF EXISTS)? (CASCADE CONSTRAINT)?
;

comment_on_column
: COMMENT ON COLUMN column_name IS quoted_string
;

enable_or_disable
: ENABLE
| DISABLE
;

allow_or_disallow
: ALLOW
| DISALLOW
;

// Synonym DDL Clauses

alter_synonym
: ALTER PUBLIC? SYNONYM (schema_name '.')? synonym_name (
EDITIONABLE
| NONEDITIONABLE
| COMPILE
)
;

create_synonym
// Synonym's schema cannot be specified for public synonyms
: CREATE (OR REPLACE)? PUBLIC SYNONYM synonym_name FOR (schema_name PERIOD)? schema_object_name (
AT_SIGN link_name
)?
| CREATE (OR REPLACE)? SYNONYM (schema_name PERIOD)? synonym_name FOR (schema_name PERIOD)? schema_object_name (
AT_SIGN (schema_name PERIOD)? link_name
)?
;

drop_synonym
: DROP PUBLIC? SYNONYM (schema_name '.')? synonym_name FORCE?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-SPFILE.html
create_spfile
: CREATE SPFILE ('=' spfile_name)? FROM (PFILE ('=' pfile_name)? (AS COPY)? | MEMORY)
;

spfile_name
: CHAR_STRING
;

pfile_name
: CHAR_STRING
;

comment_on_table
: COMMENT ON TABLE tableview_name IS quoted_string
;

comment_on_materialized
: COMMENT ON MATERIALIZED VIEW tableview_name IS quoted_string
;

alter_analytic_view
: ALTER ANALYTIC VIEW (schema_name '.')? av = id_expression (
RENAME TO id_expression
| COMPILE
| alter_add_cache_clause
| alter_drop_cache_clause
)
;

alter_add_cache_clause
: ADD CACHE MEASURE GROUP '(' (ALL | measure_list)? ')' LEVELS '(' levels_item (
',' levels_item
)* ')'
;

levels_item
: ((d = id_expression '.')? h = id_expression '.')? l = id_expression
;

measure_list
: id_expression (',' id_expression)*
;

alter_drop_cache_clause
: DROP CACHE MEASURE GROUP '(' (ALL | measure_list)? ')' LEVELS '(' levels_item (
',' levels_item
)* ')'
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-ATTRIBUTE-DIMENSION.html
alter_attribute_dimension
: ALTER ATTRIBUTE DIMENSION (schema_name '.')? ad = id_expression (
RENAME TO nad = id_expression
| COMPILE
)
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-AUDIT-POLICY-Unified-Auditing.html
alter_audit_policy
: ALTER AUDIT POLICY p = id_expression ADD? (
privilege_audit_clause? action_audit_clause? role_audit_clause?
| (ONLY TOPLEVEL)?
) DROP? (privilege_audit_clause? action_audit_clause? role_audit_clause? | (ONLY TOPLEVEL)?) (
CONDITION (DROP | CHAR_STRING EVALUATE PER (STATEMENT | SESSION | INSTANCE))
)?
;

alter_cluster
: ALTER CLUSTER cluster_name (
physical_attributes_clause
| SIZE size_clause
| allocate_extent_clause
| deallocate_unused_clause
| cache_or_nocache
)+ parallel_clause?
;

drop_analytic_view
: DROP ANALYTIC VIEW (schema_name '.')? av = id_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-ATTRIBUTE-DIMENSION.html
drop_attribute_dimension
: DROP ATTRIBUTE DIMENSION (schema_name '.')? ad = id_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-AUDIT-POLICY-Unified-Auditing.html
drop_audit_policy
: DROP AUDIT POLICY p = id_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-FLASHBACK-ARCHIVE.html
drop_flashback_archive
: DROP FLASHBACK ARCHIVE fa = id_expression
;

drop_cluster
: DROP CLUSTER cluster_name (INCLUDING TABLES (CASCADE CONSTRAINTS)?)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-CONTEXT.html
drop_context
: DROP CONTEXT ns = id_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-DIRECTORY.html
drop_directory
: DROP DIRECTORY dn = id_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-DISKGROUP.html
drop_diskgroup
: DROP DISKGROUP dgn = id_expression ((FORCE? INCLUDING | EXCLUDING) CONTENTS)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-EDITION.html
drop_edition
: DROP EDITION e = id_expression CASCADE?
;

truncate_cluster
: TRUNCATE CLUSTER cluster_name ((DROP | REUSE) STORAGE)?
;

cache_or_nocache
: CACHE
| NOCACHE
;

database_name
: id_expression
;

database_clause
: PLUGGABLE? DATABASE database_name?
;

startup_clauses
: MOUNT ((STANDBY | CLONE) DATABASE)?
| OPEN (READ WRITE)? resetlogs_or_noresetlogs? upgrade_or_downgrade?
| OPEN READ ONLY
;

resetlogs_or_noresetlogs
: RESETLOGS
| NORESETLOGS
;

upgrade_or_downgrade
: UPGRADE
| DOWNGRADE
;

recovery_clauses
: general_recovery
| managed_standby_recovery
| begin_or_end BACKUP
;

begin_or_end
: BEGIN
| END
;

partial_database_recovery
: TABLESPACE tablespace (',' tablespace)*
| DATAFILE CHAR_STRING
| filenumber (',' CHAR_STRING | filenumber)*
| partial_database_recovery_10g
;

partial_database_recovery_10g
: {this.isVersion10()}? STANDBY (
TABLESPACE tablespace (',' tablespace)*
| DATAFILE CHAR_STRING
| filenumber (',' CHAR_STRING | filenumber)*
) UNTIL (CONSISTENT WITH)? CONTROLFILE
;

db_name
: regular_id
;

database_file_clauses
: RENAME FILE filename (',' filename)* TO filename
| create_datafile_clause
| alter_datafile_clause
| alter_tempfile_clause
| move_datafile_clause
;

create_datafile_clause
: CREATE DATAFILE (filename | filenumber) (',' (filename | filenumber))* (
AS (
//TODO (','? file_specification)+ |
NEW
)
)?
;

move_datafile_clause
: MOVE DATAFILE (filename | filenumber) (',' (filename | filenumber))* (TO filename)? REUSE? KEEP?
;

add_logfile_clauses
: ADD STANDBY? LOGFILE (
(INSTANCE CHAR_STRING | THREAD UNSIGNED_INTEGER)? group_redo_logfile+
| MEMBER filename REUSE? (',' filename REUSE?)* TO logfile_descriptor (
',' logfile_descriptor
)*
)
;

group_redo_logfile
: (GROUP UNSIGNED_INTEGER)? redo_log_file_spec
;

drop_logfile_clauses
: DROP STANDBY? LOGFILE (
logfile_descriptor (',' logfile_descriptor)*
| MEMBER filename (',' filename)*
)
;

switch_logfile_clause
: SWITCH ALL LOGFILES TO BLOCKSIZE UNSIGNED_INTEGER
;

supplemental_db_logging
: add_or_drop SUPPLEMENTAL LOG (DATA | supplemental_id_key_clause | supplemental_plsql_clause)
;

add_or_drop
: ADD
| DROP
;

supplemental_plsql_clause
: DATA FOR PROCEDURAL REPLICATION
;

logfile_descriptor
: GROUP UNSIGNED_INTEGER
| '(' filename (',' filename)* ')'
| filename
;

controlfile_clauses
: CREATE (LOGICAL | PHYSICAL)? STANDBY CONTROLFILE AS filename REUSE?
| BACKUP CONTROLFILE TO (filename REUSE? | trace_file_clause)
;

trace_file_clause
: TRACE (AS filename REUSE?)? (RESETLOGS | NORESETLOGS)?
;

activate_standby_db_clause
: ACTIVATE (PHYSICAL | LOGICAL)? STANDBY DATABASE (FINISH APPLY)?
;

maximize_standby_db_clause
: SET STANDBY DATABASE TO MAXIMIZE (PROTECTION | AVAILABILITY | PERFORMANCE)
;

register_logfile_clause
: REGISTER (OR REPLACE)? (PHYSICAL | LOGICAL) LOGFILE //TODO (','? file_specification)+
//TODO (FOR logminer_session_name)?
;

start_standby_clause
: START LOGICAL STANDBY APPLY IMMEDIATE? NODELAY? (
NEW PRIMARY regular_id
| INITIAL scn_value = UNSIGNED_INTEGER?
| SKIP_ FAILED TRANSACTION
| FINISH
)?
;

stop_standby_clause
: (STOP | ABORT) LOGICAL STANDBY APPLY
;

convert_database_clause
: CONVERT TO (PHYSICAL | SNAPSHOT) STANDBY
;

set_time_zone_clause
: SET TIMEZONE EQUALS_OP CHAR_STRING
;

instance_clauses
: enable_or_disable INSTANCE CHAR_STRING
;

security_clause
: GUARD (ALL | STANDBY | NONE)
;

domain
: id_expression
;

database
: id_expression
;

edition_name
: regular_id
;

filenumber
: UNSIGNED_INTEGER
;

filename
: CHAR_STRING
;

prepare_clause
: PREPARE MIRROR COPY c = id_expression (WITH (UNPROTECTED | MIRROR | HIGH) REDUNDANCY)? (
FOR DATABASE id_expression
)?
;

drop_mirror_clause
: DROP MIRROR COPY mn = id_expression
;

lost_write_protection
: (ENABLE | DISABLE | REMOVE | SUSPEND) LOST WRITE PROTECTION
;

cdb_fleet_clauses
: lead_cdb_clause
| lead_cdb_uri_clause
;

lead_cdb_clause
: SET LEAD_CDB '=' (TRUE | FALSE)
;

lead_cdb_uri_clause
: SET LEAD_CDB_URI '=' CHAR_STRING
;

property_clauses
: PROPERTY (SET | REMOVE) DEFAULT_CREDENTIAL '=' qcn = id_expression
;

replay_upgrade_clauses
: UPGRADE SYNC (ON | OFF)
;

alter_database_link
: ALTER SHARED? PUBLIC? DATABASE LINK local_link_name (
CONNECT TO user_object_name IDENTIFIED BY password_value link_authentication?
| link_authentication
)
;

password_value
: id_expression
| numeric
| VALUES CHAR_STRING
;

link_authentication
: AUTHENTICATED BY user_object_name IDENTIFIED BY password_value
;

database_logging_sub_clause
: (GROUP UNSIGNED_INTEGER)? file_specification
;

tablespace_clauses
: EXTENT MANAGEMENT LOCAL
| SYSAUX? DATAFILE file_specification (',' file_specification)*
| default_tablespace
| default_temp_tablespace
| undo_tablespace
;

enable_pluggable_database
: ENABLE PLUGGABLE DATABASE (
SEED file_name_convert? (SYSTEM tablespace_datafile_clauses)? (
SYSAUX tablespace_datafile_clauses
)?
)? undo_mode_clause?
;

file_name_convert
: FILE_NAME_CONVERT EQUALS_OP (
'(' filename_convert_sub_clause (',' filename_convert_sub_clause)* ')'
| NONE
)
;

filename_convert_sub_clause
: CHAR_STRING (',' CHAR_STRING)?
;

tablespace_datafile_clauses
: DATAFILES (SIZE size_clause | autoextend_clause)+
;

undo_mode_clause
: LOCAL UNDO (ON | OFF)
;

default_tablespace
: DEFAULT TABLESPACE tablespace (DATAFILE datafile_tempfile_spec)? extent_management_clause?
;

default_temp_tablespace
: (BIGFILE | SMALLFILE)? DEFAULT (
TEMPORARY TABLESPACE
| LOCAL TEMPORARY TABLESPACE FOR (ALL | LEAF)
) tablespace (TEMPFILE file_specification (',' file_specification)*)? extent_management_clause?
;

undo_tablespace
: (BIGFILE | SMALLFILE)? UNDO TABLESPACE tablespace (
DATAFILE file_specification (',' file_specification)*
)?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-DATABASE.html
drop_database
: DROP DATABASE (INCLUDING BACKUPS)? NOPROMPT?
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-DATABASE-LINK.html
create_database_link
: CREATE SHARED? PUBLIC? DATABASE LINK link_name (
CONNECT TO (
CURRENT_USER
| user_object_name IDENTIFIED BY password_value link_authentication?
)
| link_authentication
)* (USING CHAR_STRING)?
;

drop_database_link
: DROP PUBLIC? DATABASE LINK link_name
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-TABLESPACE-SET.html
alter_tablespace_set
: ALTER TABLESPACE SET tss = id_expression alter_tablespace_attrs
;

ts_file_name_convert
: FILE_NAME_CONVERT '=' '(' CHAR_STRING ',' CHAR_STRING (',' CHAR_STRING ',' CHAR_STRING)* ')' KEEP?
;

alter_role
: ALTER ROLE role_name role_identified_clause container_clause?
;

role_identified_clause
: NOT IDENTIFIED
| IDENTIFIED (
BY identifier
| USING identifier ('.' id_expression)?
| EXTERNALLY
| GLOBALLY (AS CHAR_STRING)?
)
;

memoptimize_read_write_clause
: NO? MEMOPTIMIZE FOR (READ | WRITE)
;

add_table_partition
: ADD (
range_partition_desc
| list_partition_desc
| PARTITION partition_name? (TABLESPACE tablespace)? key_compression? UNUSABLE?
)
;

drop_table_partition
: DROP (partition_extended_names | subpartition_extended_names) (
update_index_clauses parallel_clause?
)?
;

merge_table_partition
: MERGE PARTITION partition_name AND partition_name INTO PARTITION partition_name
;

modify_table_partition
: MODIFY (
PARTITION partition_name ((ADD | DROP) list_values_clause)? (ADD range_subpartition_desc)? (
REBUILD? UNUSABLE LOCAL INDEXES
)? shrink_clause?
| range_partitions
)
;

split_table_partition
: SPLIT partition_extended_names (
AT '(' literal (',' literal)* ')' INTO '(' range_partition_desc (',' range_partition_desc)* ')'
| INTO '(' (
range_partition_desc (',' range_partition_desc)*
| list_partition_desc (',' list_partition_desc)*
) ')'
) (update_global_index_clause | update_index_clauses)?
;

truncate_table_partition
: TRUNCATE (partition_extended_names | subpartition_extended_names) (
(DROP ALL? | REUSE)? STORAGE
)? CASCADE? (update_index_clauses parallel_clause?)?
;

exchange_table_partition
: EXCHANGE PARTITION partition_name WITH TABLE tableview_name ((INCLUDING | EXCLUDING) INDEXES)? (
(WITH | WITHOUT) VALIDATION
)?
;

coalesce_table_partition
: COALESCE PARTITION parallel_clause? (allow_or_disallow CLUSTERING)?
;

alter_interval_partition
: SET INTERVAL '(' (constant | expression)? ')'
;

partition_extended_names
: (PARTITION | PARTITIONS) (
partition_name (',' partition_name)*
| '(' partition_name (',' partition_name)* ')'
| FOR '('? partition_key_value (',' partition_key_value)* ')'?
)
;

subpartition_extended_names
: (SUBPARTITION | SUBPARTITIONS) (
partition_name (UPDATE INDEXES)?
| '(' partition_name (',' partition_name)* ')'
| FOR '('? subpartition_key_value (',' subpartition_key_value)* ')'?
)
;

alter_iot_clauses
: index_org_table_clause
| alter_overflow_clause
| alter_mapping_table_clause
| COALESCE
;

alter_mapping_table_clause
: MAPPING TABLE (allocate_extent_clause | deallocate_unused_clause)
;

alter_overflow_clause
: add_overflow_clause
| OVERFLOW (
segment_attributes_clause
| allocate_extent_clause
| shrink_clause
| deallocate_unused_clause
)+
;

add_overflow_clause
: ADD OVERFLOW segment_attributes_clause? (
'(' PARTITION segment_attributes_clause? (',' PARTITION segment_attributes_clause?)* ')'
)?
;

update_index_clauses
: update_global_index_clause
| update_all_indexes_clause
;

update_global_index_clause
: (UPDATE | INVALIDATE) GLOBAL INDEXES
;

update_all_indexes_clause
: UPDATE INDEXES ('(' update_all_indexes_index_clause ')')?
;

update_all_indexes_index_clause
: index_name '(' (update_index_partition | update_index_subpartition) ')' (
',' update_all_indexes_clause
)*
;

update_index_partition
: index_partition_description index_subpartition_clause? (',' update_index_partition)*
;

update_index_subpartition
: SUBPARTITION subpartition_name? (TABLESPACE tablespace)? (',' update_index_subpartition)*
;

using_index_clause
: USING INDEX (index_name | '(' create_index ')' | index_attributes)?
;

sort_or_nosort
: SORT
| NOSORT
;

exceptions_clause
: EXCEPTIONS INTO tableview_name
;

move_table_clause
: MOVE ONLINE? segment_attributes_clause? table_compression? index_org_table_clause? (
lob_storage_clause
| varray_col_properties
)* parallel_clause?
;

index_org_table_clause
: (mapping_table_clause | PCTTHRESHOLD UNSIGNED_INTEGER | key_compression)+ index_org_overflow_clause?
| index_org_overflow_clause // rule move_table_clause contains an optional block with at least one alternative that can match an empty string
;

mapping_table_clause
: MAPPING TABLE
| NOMAPPING
;

key_compression
: NOCOMPRESS
| COMPRESS UNSIGNED_INTEGER
;

index_org_overflow_clause
: (INCLUDING column_name)? OVERFLOW segment_attributes_clause?
;

column_clauses
: add_modify_drop_column_clauses
| rename_column_clause
| modify_collection_retrieval
| modify_lob_storage_clause
;

modify_collection_retrieval
: MODIFY NESTED TABLE collection_item RETURN AS (LOCATOR | VALUE)
;

collection_item
: tableview_name
;

rename_column_clause
: RENAME COLUMN old_column_name TO new_column_name
;

old_column_name
: column_name
;

new_column_name
: column_name
;

add_modify_drop_column_clauses
: (constraint_clauses | add_column_clause | modify_column_clauses | drop_column_clause)+
;

modify_column_clauses
: MODIFY (
'(' modify_col_properties (',' modify_col_properties)* ')'
| '(' modify_col_visibility (',' modify_col_visibility)* ')'
| modify_col_properties
| modify_col_visibility
| modify_col_substitutable
)
;

modify_col_properties
: column_name datatype? (DEFAULT expression)? (ENCRYPT encryption_spec | DECRYPT)? inline_constraint* lob_storage_clause?
//TODO alter_xmlschema_clause
;

modify_col_visibility
: column_name (VISIBLE | INVISIBLE)
;

modify_col_substitutable
: COLUMN column_name NOT? SUBSTITUTABLE AT ALL LEVELS FORCE?
;

add_column_clause
: ADD (
'(' (column_definition | virtual_column_definition) (
',' (column_definition | virtual_column_definition)
)* ')'
| ( column_definition | virtual_column_definition)
) column_properties?
//TODO (','? out_of_line_part_storage )
;

alter_varray_col_properties
: MODIFY VARRAY varray_item '(' modify_lob_parameters ')'
;

varray_col_properties
: VARRAY varray_item (
substitutable_column_clause? varray_storage_clause
| substitutable_column_clause
)
;

varray_storage_clause
: STORE AS (SECUREFILE | BASICFILE)? LOB (
lob_segname? '(' lob_storage_parameters ')'
| lob_segname
)
;

lob_segname
: regular_id
;

lob_item
: regular_id
| quoted_string
| DELIMITED_ID
;

lob_storage_parameters
: TABLESPACE tablespace_name = id_expression
| (lob_parameters storage_clause?)
| storage_clause
;

modify_lob_storage_clause
: MODIFY LOB '(' lob_item ')' '(' modify_lob_parameters ')'
;

lob_deduplicate_clause
: DEDUPLICATE
| KEEP_DUPLICATES
;

lob_compression_clause
: NOCOMPRESS
| COMPRESS (HIGH | MEDIUM | LOW)?
;

lob_retention_clause
: RETENTION (MAX | MIN UNSIGNED_INTEGER | AUTO | NONE)?
;

encryption_spec
: (USING CHAR_STRING)? (IDENTIFIED BY REGULAR_ID)? CHAR_STRING? (NO? SALT)?
;

tablespace
: id_expression
;

varray_item
: (id_expression '.')? (id_expression '.')? id_expression
;

column_properties
: (
object_type_col_properties
| nested_table_col_properties
| (varray_col_properties | lob_storage_clause) (
'(' lob_partition_storage (',' lob_partition_storage)* ')'
)? //TODO '(' ( ','? lob_partition_storage)+ ')'
| xmltype_column_properties
)+
;

period_definition
: {this.isVersion12()}? PERIOD FOR column_name ('(' start_time_column ',' end_time_column ')')?
;

start_time_column
: column_name
;

end_time_column
: column_name
;

column_definition
: column_name ((datatype | regular_id) (COLLATE column_collation_name)?)? SORT? (
VISIBLE
| INVISIBLE
)? (DEFAULT (ON NULL_)? expression | identity_clause)? (ENCRYPT encryption_spec)? (
inline_constraint+
| inline_ref_constraint
)?
;

column_collation_name
: id_expression
;

identity_clause
: GENERATED (ALWAYS | BY DEFAULT (ON NULL_)?)? AS IDENTITY identity_options_parentheses?
;

//https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6
// NOTE to identity options
// according to the SQL Reference, identity_options be nested in parentheses.
// But statements without parentheses can also be executed successfully on a oracle database.
// See this issue for more details: #3183
identity_options_parentheses
: identity_options+
| '(' identity_options+ ')'
;

virtual_column_definition
: column_name (datatype COLLATE column_collation_name)? (VISIBLE | INVISIBLE)? autogenerated_sequence_definition? VIRTUAL?
evaluation_edition_clause? (UNUSABLE BEFORE (CURRENT EDITION | EDITION edition_name))? (
UNUSABLE BEGINNING WITH ((CURRENT | NULL_) EDITION | EDITION edition_name)
)? inline_constraint*
;

autogenerated_sequence_definition
: GENERATED (ALWAYS | BY DEFAULT (ON NULL_)?)? AS IDENTITY (
'(' (sequence_start_clause | sequence_spec)* ')'
)?
;

evaluation_edition_clause
: EVALUATE USING ((CURRENT | NULL_) EDITION | EDITION edition_name)
;

out_of_line_part_storage
: PARTITION partition_name
;

nested_item
: regular_id
;

substitutable_column_clause
: ELEMENT? IS OF TYPE? '(' type_name ')'
| NOT? SUBSTITUTABLE AT ALL LEVELS
;

partition_name
: regular_id
| DELIMITED_ID
;

supplemental_logging_props
: SUPPLEMENTAL LOG (supplemental_log_grp_clause | supplemental_id_key_clause)
;

column_or_attribute
: regular_id
;

object_type_col_properties
: COLUMN column = regular_id substitutable_column_clause
;

constraint_clauses
: ADD '(' (out_of_line_constraint (',' out_of_line_constraint)* | out_of_line_ref_constraint) ')'
| ADD (out_of_line_constraint | out_of_line_ref_constraint)
| MODIFY (
CONSTRAINT constraint_name
| PRIMARY KEY
| UNIQUE '(' column_name (',' column_name)* ')'
) constraint_state CASCADE?
| RENAME CONSTRAINT old_constraint_name TO new_constraint_name
| drop_constraint_clause+
;

old_constraint_name
: constraint_name
;

new_constraint_name
: constraint_name
;

drop_constraint_clause
: DROP (
PRIMARY KEY
| UNIQUE '(' column_name (',' column_name)* ')'
| CONSTRAINT constraint_name
) CASCADE? ((KEY | DROP) INDEX)? ONLINE?
;

add_constraint
: ADD (CONSTRAINT constraint_name)? add_constraint_clause (
',' (CONSTRAINT constraint_name)? add_constraint_clause
)+
;

add_constraint_clause
: primary_key_clause
| foreign_key_clause
| unique_key_clause
| check_constraint
;

check_constraint
: CHECK '(' condition ')' DISABLE?
;

drop_constraint
: DROP CONSTRAINT constraint_name
;

enable_constraint
: ENABLE CONSTRAINT constraint_name
;

disable_constraint
: DISABLE CONSTRAINT constraint_name
;

foreign_key_clause
: FOREIGN KEY paren_column_list references_clause on_delete_clause?
;

references_clause
: REFERENCES tableview_name paren_column_list? (ON DELETE (CASCADE | SET NULL_))?
;

on_delete_clause
: ON DELETE (CASCADE | SET NULL_)
;

unique_key_clause
: UNIQUE paren_column_list using_index_clause?
;

primary_key_clause
: PRIMARY KEY paren_column_list using_index_clause?
;

// Anonymous PL/SQL code block

anonymous_block
: (DECLARE seq_of_declare_specs)? BEGIN seq_of_statements (EXCEPTION exception_handler+)? END
;

// Common DDL Clauses

invoker_rights_clause
: AUTHID (CURRENT_USER | DEFINER)
;

call_spec
: java_spec
| c_spec
;

// Call Spec Specific Clauses

java_spec
: LANGUAGE JAVA NAME CHAR_STRING
;

c_spec
: (LANGUAGE C_LETTER | EXTERNAL) (
NAME id_expression LIBRARY identifier
| LIBRARY identifier (NAME id_expression)?
) c_agent_in_clause? (WITH CONTEXT)? c_parameters_clause?
;

c_agent_in_clause
: AGENT IN '(' expressions ')'
;

c_parameters_clause
: PARAMETERS '(' c_external_parameter (',' c_external_parameter)* ')'
;

c_external_parameter
: CONTEXT
| SELF (TDO | c_property)?
| (parameter_name | RETURN) c_property? (BY REFERENCE)? external_datatype = regular_id?
;

parameter
: parameter_name (IN | OUT | INOUT | NOCOPY)* type_spec? default_value_part?
;

default_value_part
: (ASSIGN_OP | DEFAULT) expression
;

// Elements Declarations

seq_of_declare_specs
: declare_spec+
;

// incorporates constant_declaration

variable_declaration
: IDENTIFIER data_type (CONSTANT)? (NOT? NULL_)? (default_value_part)? ';'
;

subtype_declaration
: SUBTYPE identifier IS type_spec (RANGE expression '..' expression)? (NOT NULL_)? ';'
;

// cursor_declaration incorportates curscursor_body and cursor_spec

cursor_declaration
: CURSOR identifier ('(' parameter_spec (',' parameter_spec)* ')')? (RETURN type_spec)? (
IS select_statement
)? ';'
;

parameter_spec
: parameter_name (IN? type_spec)? default_value_part?
;

exception_declaration
: identifier EXCEPTION ';'
;

pragma_declaration
: PRAGMA (
SERIALLY_REUSABLE
| AUTONOMOUS_TRANSACTION
| EXCEPTION_INIT '(' exception_name ',' numeric_negative ')'
| INLINE '(' id1 = identifier ',' expression ')'
| RESTRICT_REFERENCES '(' (identifier | DEFAULT) (',' identifier)+ ')'
| DEPRECATE '(' identifier ( ',' CHAR_STRING)? ')'
) ';'
;

// Record Declaration Specific Clauses

// incorporates ref_cursor_type_definition

record_type_def
: RECORD '(' field_spec (',' field_spec)* ')'
;

field_spec
: column_name type_spec? (NOT NULL_)? default_value_part?
;

ref_cursor_type_def
: REF CURSOR (RETURN type_spec)?
;

type_declaration
: TYPE identifier IS (table_type_def | varray_type_def | record_type_def | ref_cursor_type_def) ';'
;

table_type_def
: TABLE OF type_spec (NOT NULL_)? table_indexed_by_part?
;

table_indexed_by_part
: (idx1 = INDEXED | idx2 = INDEX) BY type_spec
;

//https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/collection-variable.html#GUID-89A1863C-65A1-40CF-9392-86E9FDC21BE9
varray_type_def
: (VARRAY | VARYING? ARRAY) '(' expression ')' OF type_spec (NOT NULL_)?
;

// Statements

seq_of_statements
: (pragma_declaration* statement (';' | EOF) | label_declaration | selection_directive)+
;

label_declaration
: ltp1 = '<' '<' label_name '>' '>'
;

swallow_to_semi
: ~';'+
;

assignment_statement
: (general_element | bind_variable) ASSIGN_OP expression
;

continue_statement
: CONTINUE label_name? (WHEN condition)?
;

exit_statement
: EXIT label_name? (WHEN condition)?
;

goto_statement
: GOTO label_name
;

if_statement
: IF condition THEN seq_of_statements elsif_part* else_part? END IF
;

elsif_part
: ELSIF condition THEN seq_of_statements
;

else_part
: ELSE seq_of_statements
;

loop_statement
: label_declaration? (WHILE condition | FOR cursor_loop_param)? LOOP seq_of_statements END LOOP label_name?
;

// Loop Specific Clause

cursor_loop_param
: index_name IN REVERSE? lower_bound range_separator = '..' upper_bound
| record_name IN (cursor_name ('(' expressions? ')')? | '(' select_statement ')')
;

//https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/FORALL-statement.html#GUID-C45B8241-F9DF-4C93-8577-C840A25963DB
forall_statement
: FORALL index_name IN bounds_clause (SAVE EXCEPTIONS)? data_manipulation_language_statements
;

bounds_clause
: lower_bound '..' upper_bound
| INDICES OF general_element between_bound?
| VALUES OF index_name
;

between_bound
: BETWEEN lower_bound AND upper_bound
;

lower_bound
: concatenation
;

upper_bound
: concatenation
;

null_statement
: NULL_
;

raise_statement
: RAISE exception_name?
;

return_statement
: RETURN expression?
;

call_statement
: CALL? routine_name function_argument? ('.' routine_name function_argument?)* (
INTO bind_variable
)?
;

pipe_row_statement
: PIPE ROW '(' expression ')'
;

selection_directive
: DOLLAR_IF condition DOLLAR_THEN selection_directive_body (
DOLLAR_ELSIF selection_directive_body
)* (DOLLAR_ELSE selection_directive_body)? DOLLAR_END
;

error_directive
: DOLLAR_ERROR concatenation DOLLAR_END
;

selection_directive_body
: (
pragma_declaration? statement ';'
| variable_declaration
| error_directive
| function_body
| procedure_body
)+
;

body
: BEGIN seq_of_statements (EXCEPTION exception_handler+)? END label_name?
;

// Body Specific Clause

exception_handler
: WHEN exception_name (OR exception_name)* THEN seq_of_statements
;

trigger_block
: (DECLARE declare_spec*)? body
;

tps_block
: declare_spec* body
;

block
: (DECLARE declare_spec+ SEMICOLON)? BEGIN statement* END
;

// SQL Statements

execute_immediate
: EXECUTE IMMEDIATE expression (
into_clause using_clause?
| using_clause dynamic_returning_clause?
| dynamic_returning_clause
)?
;

// Execute Immediate Specific Clause

dynamic_returning_clause
: (RETURNING | RETURN) into_clause
;

// DML Statements

// Cursor Manipulation Statements

cursor_manipulation_statements
: close_statement
| open_statement
| fetch_statement
| open_for_statement
;

close_statement
: CLOSE cursor_name
;

open_statement
: OPEN cursor_name ('(' expressions? ')')?
;

fetch_statement
: FETCH cursor_name (
it1 = INTO variable_or_collection (',' variable_or_collection)*
| BULK COLLECT INTO variable_or_collection (',' variable_or_collection)* (
LIMIT (numeric | variable_or_collection)
)?
)
;

variable_or_collection
: variable_name
| collection_expression
;

open_for_statement
: OPEN variable_name FOR (select_statement | expression) using_clause?
;

// Transaction Control SQL Statements

transaction_control_statements
: set_transaction_command
| set_constraint_command
| commit_statement
| rollback_statement
| savepoint_statement
;

set_transaction_command
: SET TRANSACTION (
READ (ONLY | WRITE)
| ISOLATION LEVEL (SERIALIZABLE | READ COMMITTED)
| USE ROLLBACK SEGMENT rollback_segment_name
)? (NAME quoted_string)?
;

set_constraint_command
: SET (CONSTRAINT | CONSTRAINTS) (ALL | constraint_name (',' constraint_name)*) (
IMMEDIATE
| DEFERRED
)
;

// https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_4010.htm#SQLRF01110
// https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/COMMIT.html
commit_statement
: COMMIT WORK? write_clause? (
COMMENT CHAR_STRING write_clause?
| FORCE (CHAR_STRING (',' numeric)? | CORRUPT_XID CHAR_STRING | CORRUPT_XID_ALL)
)?
;

write_clause
: WRITE (WAIT | NOWAIT)? (IMMEDIATE | BATCH)?
;

rollback_statement
: ROLLBACK WORK? (TO SAVEPOINT? savepoint_name | FORCE quoted_string)?
;

savepoint_statement
: SAVEPOINT savepoint_name
;

// https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/collection-method.html#GUID-7AF1A3C4-D04B-4F91-9D7B-C92C75E3A300
collection_method_call // collection methods invocation that could be used as a statement
: expression '.' (
(DELETE | EXTEND) ('(' index += expression (',' index += expression)* ')')?
| TRIM ('(' index += expression ')')?
)
;

// Dml

/* TODO
//SHOULD BE OVERRIDEN!
compilation_unit
: seq_of_statements* EOF
;

explain_statement
: EXPLAIN PLAN (SET STATEMENT_ID '=' quoted_string)? (INTO tableview_name)? FOR (
select_statement
| update_statement
| delete_statement
| insert_statement
| merge_statement
)
;

select_only_statement
: with_clause? subquery
;

select_statement
: select_only_statement (for_update_clause | order_by_clause | offset_clause | fetch_clause)*
;

// Select Specific Clauses
with_clause
: WITH (function_body | procedure_body)* with_factoring_clause (',' with_factoring_clause)*
| WITH (function_body | procedure_body)+ (with_factoring_clause (',' with_factoring_clause)*)?
;

with_factoring_clause
: subquery_factoring_clause
| subav_factoring_clause
;

subquery_factoring_clause
: query_name paren_column_list? AS '(' subquery order_by_clause? ')' search_clause? cycle_clause?
;

search_clause
: SEARCH (DEPTH | BREADTH) FIRST BY column_name ASC? DESC? (NULLS FIRST)? (NULLS LAST)? (
',' column_name ASC? DESC? (NULLS FIRST)? (NULLS LAST)?
)* SET column_name
;

cycle_clause
: CYCLE column_list SET column_name TO expression DEFAULT expression
;

subav_factoring_clause
: subav_name = id_expression ANALYTIC VIEW AS '(' subav_clause ')'
;

subav_clause
: USING subav_name = object_name hierarchies_clause? filter_clauses? add_calcs_clause?
;

hierarchies_clause
: HIERARCHIES '(' hier_alias += object_name (',' hier_alias += object_name)* ')'
;

filter_clauses
: FILTER FACT '(' filter_clause (',' filter_clause)* ')'
;

filter_clause
: (MEASURES | hier_alias = object_name) TO condition
;

add_calcs_clause
: ADD MEASURES '(' add_calc_meas_clause (',' add_calc_meas_clause)* ')'
;

add_calc_meas_clause
: meas_name = id_expression AS '(' expression ')'
;

subquery
: subquery_basic_elements subquery_operation_part*
;

subquery_basic_elements
: query_block
| '(' subquery ')'
;

subquery_operation_part
: (UNION ALL? | INTERSECT | MINUS) subquery_basic_elements
;

query_block
: SELECT (DISTINCT | UNIQUE | ALL)? selected_list into_clause? from_clause? where_clause? (
hierarchical_query_clause
| group_by_clause
)* model_clause? order_by_clause? offset_clause? fetch_clause?
;

selected_list
: ''
| select_list_elements (',' select_list_elements)
;

from_clause
: FROM table_ref_list
;

select_list_elements
: tableview_name '.' ASTERISK
| expression column_alias?
;

table_ref_list
: table_ref (',' table_ref)*
;

// NOTE to PIVOT clause
// according the SQL reference this should not be possible
// according to he reality it is. Here we probably apply pivot/unpivot onto whole join clause
// eventhough it is not enclosed in parenthesis. See pivot examples 09,10,11

table_ref
: table_ref_aux join_clause* (pivot_clause | unpivot_clause)?
;

table_ref_aux
: table_ref_aux_internal flashback_query_clause* (/{isTableAlias()}?/ table_alias)?
;

table_ref_aux_internal
: dml_table_expression_clause (pivot_clause | unpivot_clause)? # table_ref_aux_internal_one
| '(' table_ref subquery_operation_part* ')' (pivot_clause | unpivot_clause)? # table_ref_aux_internal_two
| ONLY '(' dml_table_expression_clause ')' # table_ref_aux_internal_thre
;

join_on_part
: ON condition
;

join_using_part
: USING paren_column_list
;

outer_join_type
: (FULL | LEFT | RIGHT) OUTER?
;

query_partition_clause
: PARTITION BY (('(' (subquery | expressions)? ')') | expressions)
;

pivot_clause
: PIVOT XML? '(' pivot_element (',' pivot_element)* pivot_for_clause pivot_in_clause ')'
;

pivot_element
: (numeric_function | aggregate_function_name '(' expression ')') column_alias?
;

pivot_for_clause
: FOR (column_name | paren_column_list)
;

pivot_in_clause
: IN '(' (subquery | ANY (',' ANY)* | pivot_in_clause_element (',' pivot_in_clause_element)*) ')'
;

pivot_in_clause_element
: pivot_in_clause_elements column_alias?
;

pivot_in_clause_elements
: expression
| '(' expressions? ')'
;

unpivot_clause
: UNPIVOT ((INCLUDE | EXCLUDE) NULLS)? '(' (column_name | paren_column_list) pivot_for_clause unpivot_in_clause ')'
;

unpivot_in_clause
: IN '(' unpivot_in_elements (',' unpivot_in_elements)* ')'
;

unpivot_in_elements
: (column_name | paren_column_list) (AS (constant | '(' constant (',' constant)* ')'))?
;

hierarchical_query_clause
: CONNECT BY NOCYCLE? condition start_part?
| start_part CONNECT BY NOCYCLE? condition
;

start_part
: START WITH condition
;

group_by_clause
: GROUP BY group_by_elements (',' group_by_elements)* having_clause?
| having_clause (GROUP BY group_by_elements (',' group_by_elements)*)?
;

group_by_elements
: grouping_sets_clause
| rollup_cube_clause
| expression
;

rollup_cube_clause
: (ROLLUP | CUBE) '(' grouping_sets_elements (',' grouping_sets_elements)* ')'
;

grouping_sets_clause
: GROUPING SETS '(' grouping_sets_elements (',' grouping_sets_elements)* ')'
;

grouping_sets_elements
: rollup_cube_clause
| '(' expressions? ')'
| expression
;

having_clause
: HAVING condition
;

model_clause
: MODEL cell_reference_options* return_rows_clause? reference_model* main_model
;

cell_reference_options
: (IGNORE | KEEP) NAV
| UNIQUE (DIMENSION | SINGLE REFERENCE)
;

return_rows_clause
: RETURN (UPDATED | ALL) ROWS
;

reference_model
: REFERENCE reference_model_name ON '(' subquery ')' model_column_clauses cell_reference_options*
;

main_model
: (MAIN main_model_name)? model_column_clauses cell_reference_options* model_rules_clause
;

model_column_clauses
: model_column_partition_part? DIMENSION BY model_column_list MEASURES model_column_list
;

model_column_partition_part
: PARTITION BY model_column_list
;

model_column_list
: '(' model_column (',' model_column)* ')'
;

model_column
: (expression | query_block) column_alias?
;

model_rules_clause
: model_rules_part? '(' (model_rules_element (',' model_rules_element)*)? ')'
;

model_rules_part
: RULES (UPDATE | UPSERT ALL?)? ((AUTOMATIC | SEQUENTIAL) ORDER)? model_iterate_clause?
;

model_rules_element
: (UPDATE | UPSERT ALL?)? cell_assignment order_by_clause? '=' expression
;

cell_assignment
: model_expression
;

model_iterate_clause
: ITERATE '(' expression ')' until_part?
;

until_part
: UNTIL '(' condition ')'
;

order_by_clause
: ORDER SIBLINGS? BY order_by_elements (',' order_by_elements)*
;

order_by_elements
: expression (ASC | DESC)? (NULLS (FIRST | LAST))?
;

offset_clause
: OFFSET expression (ROW | ROWS)
;

fetch_clause
: FETCH (FIRST | NEXT) (expression PERCENT_KEYWORD?)? (ROW | ROWS) (ONLY | WITH TIES)
;

for_update_clause
: FOR UPDATE for_update_of_part? for_update_options?
;

for_update_of_part
: OF column_list
;

for_update_options
: SKIP_ LOCKED
| NOWAIT
| WAIT expression
;

update_statement
: UPDATE general_table_ref update_set_clause where_clause? static_returning_clause? error_logging_clause?
;

// Update Specific Clauses

update_set_clause
: SET (
column_based_update_set_clause (',' column_based_update_set_clause)*
| VALUE '(' identifier ')' '=' expression
)
;

column_based_update_set_clause
: column_name '=' expression
| paren_column_list '=' subquery
;

delete_statement
: DELETE FROM? general_table_ref where_clause? static_returning_clause? error_logging_clause?
;

insert_statement
: INSERT (single_table_insert | multi_table_insert)
;

// Insert Specific Clauses

single_table_insert
: insert_into_clause (values_clause static_returning_clause? | select_statement) error_logging_clause?
;

multi_table_insert
: (ALL multi_table_element+ | conditional_insert_clause) select_statement
;

multi_table_element
: insert_into_clause values_clause? error_logging_clause?
;

conditional_insert_clause
: (ALL | FIRST)? conditional_insert_when_part+ conditional_insert_else_part?
;

conditional_insert_when_part
: WHEN condition THEN multi_table_element+
;

conditional_insert_else_part
: ELSE multi_table_element+
;

insert_into_clause
: INTO general_table_ref paren_column_list?
;

values_clause
: VALUES (REGULAR_ID | '(' expressions ')' | collection_expression)
;

merge_statement
: MERGE INTO tableview_name table_alias? USING selected_tableview ON '(' condition ')' (
merge_update_clause merge_insert_clause?
| merge_insert_clause merge_update_clause?
)? error_logging_clause?
;

// Merge Specific Clauses

merge_update_clause
: WHEN MATCHED THEN UPDATE SET merge_element (',' merge_element)* where_clause? merge_update_delete_part?
;

merge_element
: column_name '=' expression
;

merge_update_delete_part
: DELETE where_clause
;

merge_insert_clause
: WHEN NOT MATCHED THEN INSERT paren_column_list? values_clause where_clause?
;

selected_tableview
: (tableview_name | '(' select_statement ')') table_alias?
;

lock_table_statement
: LOCK TABLE lock_table_element (',' lock_table_element)* IN lock_mode MODE wait_nowait_part?
;

wait_nowait_part
: WAIT expression
| NOWAIT
;

// Lock Specific Clauses

lock_table_element
: tableview_name partition_extension_clause?
;

lock_mode
: ROW SHARE
| ROW EXCLUSIVE
| SHARE UPDATE?
| SHARE ROW EXCLUSIVE
| EXCLUSIVE
;

// Common DDL Clauses

general_table_ref
: (dml_table_expression_clause | ONLY '(' dml_table_expression_clause ')') table_alias?
;

static_returning_clause
: (RETURNING | RETURN) expressions into_clause
;

error_logging_clause
: LOG ERRORS error_logging_into_part? expression? error_logging_reject_part?
;

error_logging_into_part
: INTO tableview_name
;

error_logging_reject_part
: REJECT LIMIT (UNLIMITED | expression)
;

dml_table_expression_clause
: table_collection_expression
| '(' select_statement subquery_restriction_clause? ')'
| tableview_name hierarchies_clause? sample_clause?
| json_table_clause (AS identifier)?
| LATERAL '(' subquery subquery_restriction_clause? ')'
;

table_collection_expression
: (TABLE | THE) ('(' subquery ')' | '(' expression ')' outer_join_sign?)
;

subquery_restriction_clause
: WITH (READ ONLY | CHECK OPTION (CONSTRAINT constraint_name)?)
;

sample_clause
: SAMPLE BLOCK? '(' expression (',' expression)? ')' seed_part?
;

seed_part
: SEED '(' expression ')'
;

// Expression & Condition

condition
: expression
| IDENTIFIER '=' value
| JSON_EQUAL '(' expressions ')'
;

expressions
: expression (',' expression)*
;

cursor_expression
: CURSOR '(' subquery ')'
;

relational_expression
: relational_expression relational_operator relational_expression
| compound_expression
;

relational_operator
: '='
| (NOT_EQUAL_OP | '<' '>' | '!' '=' | '^' '=')
| ('<' | '>') '='?
;

in_elements
: '(' subquery ')'
| '(' concatenation (',' concatenation)* ')'
| constant
| bind_variable
| general_element
;

between_elements
: concatenation AND concatenation
;

model_expression
: unary_expression ('[' model_expression_element ']')?
;

model_expression_element
: (ANY | expression) (',' (ANY | expression))*
| single_column_for_loop (',' single_column_for_loop)*
| multi_column_for_loop
;

single_column_for_loop
: FOR column_name (
IN '(' expressions? ')'
| (LIKE expression)? FROM fromExpr = expression TO toExpr = expression action_type = (
INCREMENT
| DECREMENT
) action_expr = expression
)
;

multi_column_for_loop
: FOR paren_column_list IN '(' (subquery | '(' expressions? ')') ')'
;

unary_expression
: ('-' | '+') unary_expression
| PRIOR unary_expression
| CONNECT_BY_ROOT unary_expression
| /TODO {input.LT(1).getText().equalsIgnoreCase("new") && !input.LT(2).getText().equals(".")}?/ NEW unary_expression
| DISTINCT unary_expression
| ALL unary_expression
| /TODO{(input.LA(1) == CASE || input.LA(2) == CASE)}?/ case_statement /[false]/
| unary_expression '.' (
(COUNT | FIRST | LAST | LIMIT)
| (EXISTS | NEXT | PRIOR) '(' index += expression ')'
)
| quantified_expression
| standard_function
| atom
| implicit_cursor_expression
;

// https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-optimization-and-tuning.html#GUID-DAF46F06-EF3F-4B1A-A518-5238B80C69FA
implicit_cursor_expression
: SQL (
PERCENT_BULK_ROWCOUNT '(' index = expression ')'
| PERCENT_BULK_EXCEPTIONS ('.' COUNT | '(' expression ')' '.' (ERROR_INDEX | ERROR_CODE))
)
;

collection_expression
: collation_name '(' expression ')' ('.' general_element_part)*
;

case_statement /TODO [boolean isStatementParameter]
TODO scope {
boolean isStatement;
}
@init {$case_statement::isStatement = $isStatementParameter;}/
: searched_case_statement
| simple_case_statement
;

// CASE

simple_case_statement
: label_declaration? ck1 = CASE expression simple_case_when_part+ case_else_part? END CASE? label_name?
;

simple_case_when_part
: WHEN expression THEN (/TODO{$case_statement::isStatement}?/ seq_of_statements | expression)
;

searched_case_statement
: label_declaration? ck1 = CASE searched_case_when_part+ case_else_part? END CASE? label_name?
;

searched_case_when_part
: WHEN condition THEN (/TODO{$case_statement::isStatement}?/ seq_of_statements | expression)
;

case_else_part
: ELSE (/{$case_statement::isStatement}?/ seq_of_statements | expression)
;

quantified_expression
: (SOME | EXISTS | ALL | ANY) (
'(' select_only_statement ')'
| '(' expression (',' expression)* ')'
)
;

standard_function
: string_function
| numeric_function_wrapper
| json_function
| other_function
;

//see as https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_ARRAY.html#GUID-46CDB3AF-5795-455B-85A8-764528CEC43B
json_function
: JSON_ARRAY '(' json_array_element (',' json_array_element)* json_on_null_clause? json_return_clause? STRICT? ')'
| JSON_ARRAYAGG '(' expression (FORMAT JSON)? order_by_clause? json_on_null_clause? json_return_clause? STRICT? ')'
| JSON_OBJECT '(' json_object_content ')'
| JSON_OBJECTAGG '(' KEY? expression VALUE expression ((NULL_ | ABSENT) ON NULL_)? (
RETURNING (VARCHAR2 ('(' UNSIGNED_INTEGER ( BYTE | CHAR)? ')')? | CLOB | BLOB)
)? STRICT? (WITH UNIQUE KEYS)? ')'
| JSON_QUERY '(' expression (FORMAT JSON)? ',' CHAR_STRING json_query_returning_clause json_query_wrapper_clause? json_query_on_error_clause?
json_query_on_empty_clause? ')'
| JSON_SERIALIZE '(' CHAR_STRING (RETURNING json_query_return_type)? PRETTY? ASCII? TRUNCATE? (
(NULL_ | ERROR | EMPTY_ (ARRAY | OBJECT)) ON ERROR
)? ')'
| JSON_TRANSFORM '(' expression ',' json_transform_op (',' json_transform_op)* ')'
| JSON_VALUE '(' expression (FORMAT JSON)? (
',' CHAR_STRING? json_value_return_clause? ((ERROR | NULL_ | DEFAULT literal)? ON ERROR)? (
(ERROR | NULL_ | DEFAULT literal)? ON EMPTY_
)? json_value_on_mismatch_clause? ')'
)?
;

json_object_content
: (json_object_entry (',' json_object_entry)* | '*') json_on_null_clause? json_return_clause? STRICT? (
WITH UNIQUE KEYS
)?
;

json_object_entry
: (KEY? expression (VALUE | IS)? expression | expression ':' expression | identifier) (
FORMAT JSON
)?
;

json_table_clause
: JSON_TABLE '(' expression (FORMAT JSON)? (',' CHAR_STRING)? ((ERROR | NULL_) ON ERROR)? (
(EMPTY_ | NULL_) ON EMPTY_
)? json_column_clause? ')'
;

json_array_element
: (expression | CHAR_STRING | NULL_ | UNSIGNED_INTEGER | json_function) (FORMAT JSON)?
;

json_on_null_clause
: (NULL_ | ABSENT) ON NULL_
;

json_return_clause
: RETURNING (VARCHAR2 ('(' UNSIGNED_INTEGER ( BYTE | CHAR)? ')')? | CLOB | BLOB)
;

json_column_clause
: COLUMNS '(' json_column_definition (',' json_column_definition)* ')'
;

json_column_definition
: expression json_value_return_type? (EXISTS? PATH CHAR_STRING | TRUNCATE (PATH CHAR_STRING)?)? json_query_on_error_clause?
json_query_on_empty_clause?
| expression json_query_return_type? TRUNCATE? FORMAT JSON json_query_wrapper_clause? PATH CHAR_STRING
| NESTED PATH? expression ('[' ASTERISK ']')? json_column_clause
| expression FOR ORDINALITY
;

json_query_returning_clause
: (RETURNING json_query_return_type)? PRETTY? ASCII?
;

json_query_return_type
: VARCHAR2 ('(' UNSIGNED_INTEGER ( BYTE | CHAR)? ')')?
| CLOB
| BLOB
;

json_query_wrapper_clause
: (WITHOUT ARRAY? WRAPPER)
| (WITH (UNCONDITIONAL | CONDITIONAL)? ARRAY? WRAPPER)
;

json_query_on_error_clause
: (ERROR | NULL_ | EMPTY_ | EMPTY_ ARRAY | EMPTY_ OBJECT)? ON ERROR
;

json_query_on_empty_clause
: (ERROR | NULL_ | EMPTY_ | EMPTY_ ARRAY | EMPTY_ OBJECT)? ON EMPTY_
;

json_value_return_clause
: RETURNING json_value_return_type? ASCII?
;

json_value_on_mismatch_clause
: (IGNORE | ERROR | NULL_) ON MISMATCH ('(' MISSING DATA | EXTRA DATA | TYPE ERROR ')')?
;

literal
: CHAR_STRING
| string_function
| numeric
| MAXVALUE
;

numeric_function_wrapper
: numeric_function (single_column_for_loop | multi_column_for_loop)?
;

listagg_overflow_clause
: ON OVERFLOW (ERROR | TRUNCATE) CHAR_STRING? ((WITH | WITHOUT) COUNT)?
;

other_function
: over_clause_keyword function_argument_analytic over_clause?
| /TODO stantard_function_enabling_using/ regular_id function_argument_modeling using_clause?
| COUNT '(' (ASTERISK | (DISTINCT | UNIQUE | ALL)? concatenation) ')' over_clause?
| (CAST | XMLCAST) '(' (MULTISET '(' subquery ')' | concatenation) AS type_spec (
DEFAULT concatenation ON CONVERSION ERROR
)? (',' quoted_string (',' quoted_string)?)? ')'
| COALESCE '(' table_element (',' (numeric | quoted_string))? ')'
| COLLECT '(' (DISTINCT | UNIQUE)? concatenation collect_order_by_part? ')'
| within_or_over_clause_keyword function_argument within_or_over_part+
| LISTAGG '(' (ALL | DISTINCT | UNIQUE)? argument (',' string_delimiter)? listagg_overflow_clause? ')' (
WITHIN GROUP '(' order_by_clause ')'
)? over_clause?
| cursor_name (PERCENT_ISOPEN | PERCENT_FOUND | PERCENT_NOTFOUND | PERCENT_ROWCOUNT)
| DECOMPOSE '(' concatenation (CANONICAL | COMPATIBILITY)? ')'
| EXTRACT '(' regular_id FROM concatenation ')'
| (FIRST_VALUE | LAST_VALUE) function_argument_analytic respect_or_ignore_nulls? over_clause
| (LEAD | LAG) function_argument_analytic respect_or_ignore_nulls? over_clause
| standard_prediction_function_keyword '(' expressions cost_matrix_clause? using_clause? ')'
| (TO_BINARY_DOUBLE | TO_BINARY_FLOAT | TO_NUMBER | TO_TIMESTAMP | TO_TIMESTAMP_TZ) '(' concatenation (
DEFAULT concatenation ON CONVERSION ERROR
)? (',' quoted_string (',' quoted_string)?)? ')'
| (TO_DSINTERVAL | TO_YMINTERVAL) '(' concatenation (DEFAULT concatenation ON CONVERSION ERROR)? ')'
| TRANSLATE '(' expression (USING (CHAR_CS | NCHAR_CS))? (',' expression)* ')'
| TREAT '(' expression AS REF? type_spec ')' ('.' general_element_part)*
| TRIM '(' ((LEADING | TRAILING | BOTH)? quoted_string? FROM)? concatenation ')'
| VALIDATE_CONVERSION '(' concatenation AS type_spec (',' quoted_string (',' quoted_string)?)? ')'
| XMLAGG '(' expression order_by_clause? ')' ('.' general_element_part)*
| (XMLCOLATTVAL | XMLFOREST) '(' xml_multiuse_expression_element (
',' xml_multiuse_expression_element
)* ')' ('.' general_element_part)*
| XMLELEMENT '(' (ENTITYESCAPING | NOENTITYESCAPING)? (NAME | EVALNAME)? expression (
/TODO{input.LT(2).getText().equalsIgnoreCase("xmlattributes")}?/ ',' xml_attributes_clause
)? (',' expression column_alias?)* ')' ('.' general_element_part)*
| XMLEXISTS '(' expression xml_passing_clause? ')'
| XMLPARSE '(' (DOCUMENT | CONTENT) concatenation WELLFORMED? ')' ('.' general_element_part)*
| XMLPI '(' (NAME identifier | EVALNAME concatenation) (',' concatenation)? ')' (
'.' general_element_part
)*
| XMLQUERY '(' concatenation xml_passing_clause? RETURNING CONTENT (NULL_ ON EMPTY_)? ')' (
'.' general_element_part
)*
| XMLROOT '(' concatenation (',' xmlroot_param_version_part)? (
',' xmlroot_param_standalone_part
)? ')' ('.' general_element_part)*
| XMLSERIALIZE '(' (DOCUMENT | CONTENT) concatenation (AS type_spec)? xmlserialize_param_enconding_part? xmlserialize_param_version_part?
xmlserialize_param_ident_part? ((HIDE | SHOW) DEFAULTS)? ')' ('.' general_element_part)?
| TIME CHAR_STRING
| xmltable
;

over_clause
: OVER '(' (
query_partition_clause? (order_by_clause windowing_clause?)?
| HIERARCHY th = id_expression OFFSET numeric (ACROSS ANCESTOR AT LEVEL id_expression)?
) ')'
;

windowing_clause
: windowing_type (BETWEEN windowing_elements AND windowing_elements | windowing_elements)
;

windowing_type
: ROWS
| RANGE
;

windowing_elements
: UNBOUNDED PRECEDING
| CURRENT ROW
| concatenation (PRECEDING | FOLLOWING)
;

using_clause
: USING (ASTERISK | using_element (',' using_element)*)
;

using_element
: (IN OUT? | OUT)? select_list_elements
;

collect_order_by_part
: ORDER BY concatenation (',' concatenation)*
;

within_or_over_part
: WITHIN GROUP '(' order_by_clause ')'
| over_clause
;

string_delimiter
: CHAR_STRING
| string_function
| string_delimiter BAR BAR string_delimiter
| '(' string_delimiter ')'
| id_expression
;

cost_matrix_clause
: COST (
MODEL AUTO?
| '(' cost_class_name (',' cost_class_name)* ')' VALUES '(' expressions? ')'
)
;

xml_passing_clause
: PASSING (BY VALUE)? expression column_alias? (',' expression column_alias?)*
;

xml_attributes_clause
: XMLATTRIBUTES '(' (ENTITYESCAPING | NOENTITYESCAPING)? (SCHEMACHECK | NOSCHEMACHECK)? xml_multiuse_expression_element (
',' xml_multiuse_expression_element
)* ')'
;

xml_namespaces_clause
: XMLNAMESPACES '(' (concatenation column_alias)? (',' concatenation column_alias)* xml_general_default_part? ')'
;

xml_table_column
: xml_column_name (FOR ORDINALITY | type_spec (PATH concatenation)? xml_general_default_part?)
;

xml_general_default_part
: DEFAULT concatenation
;

xml_multiuse_expression_element
: expression (AS (id_expression | EVALNAME concatenation))?
;

xmlroot_param_version_part
: VERSION (NO VALUE | expression)
;

xmlroot_param_standalone_part
: STANDALONE (YES | NO VALUE?)
;

xmlserialize_param_enconding_part
: ENCODING concatenation
;

xmlserialize_param_version_part
: VERSION concatenation
;

xmlserialize_param_ident_part
: NO INDENT
| INDENT (SIZE '=' concatenation)?
;

// SqlPlus

sql_plus_command_no_semicolon
: set_command
;

start_command
: START_CMD id_expression PERIOD (SQL | FILE_EXT)
;

set_command
: SET regular_id (CHAR_STRING | ON | OFF | /EXACT_NUM_LIT/ numeric | regular_id)
;

timing_command
: TIMING (START timing_text = id_expression* | SHOW | STOP)?
;

// Common

partition_extension_clause
: (SUBPARTITION | PARTITION) FOR? '(' expressions? ')'
;

column_alias
: AS? (identifier | quoted_string)
| AS
;

table_alias
: identifier
| quoted_string
;

where_clause
: WHERE (CURRENT OF cursor_name | condition | quantitative_where_stmt)
;

quantitative_where_stmt
: expression relational_operator (SOME | ALL | ANY) '(' expression (',' expression)* ')'
;

into_clause
: (BULK COLLECT)? INTO (general_element | bind_variable) (
',' (general_element | bind_variable)
)*
;

// Common Named Elements

xml_column_name
: identifier
| quoted_string
;

cost_class_name
: identifier
;

attribute_name
: identifier
;

savepoint_name
: identifier
;

rollback_segment_name
: identifier
;

table_var_name
: identifier
;

schema_name
: identifier
;

routine_name
: identifier ('.' id_expression)* ('@' link_name)?
;

package_name
: identifier
;

implementation_type_name
: identifier ('.' id_expression)?
;

parameter_name
: identifier
;

reference_model_name
: identifier
;

main_model_name
: identifier
;

container_tableview_name
: identifier ('.' id_expression)?
;

aggregate_function_name
: identifier ('.' id_expression)*
;

query_name
: identifier
;

grantee_name
: id_expression identified_by?
;

role_name
: id_expression
| CONNECT
;

constraint_name
: identifier ('.' id_expression)* ('@' link_name)?
;

label_name
: id_expression
;

type_name
: id_expression ('.' id_expression)*
;

sequence_name
: id_expression ('.' id_expression)*
;

exception_name
: identifier ('.' id_expression)*
;

function_name
: identifier ('.' id_expression)?
;

procedure_name
: identifier ('.' id_expression)?
;

trigger_name
: identifier ('.' id_expression)?
;

variable_name
: (INTRODUCER char_set_name)? id_expression ('.' id_expression)?
| bind_variable
;

index_name
: identifier ('.' id_expression)?
;

cursor_name
: general_element
| bind_variable
;

record_name
: identifier
| bind_variable
;

collection_name
: identifier ('.' id_expression)?
;

link_name
: database ('.' domain)* (AT_SIGN connection_qualifier)?
;

local_link_name
: identifier
;

connection_qualifier
: identifier
;

column_name
: identifier ('.' id_expression)*
;

tableview_name
: identifier ('.' id_expression)? (
AT_SIGN link_name
| /TODO{!(input.LA(2) == BY)}?/ partition_extension_clause
)?
| xmltable outer_join_sign?
;

xmltable
: XMLTABLE '(' (xml_namespaces_clause ',')? concatenation xml_passing_clause? (
COLUMNS xml_table_column (',' xml_table_column)*
)? ')' ('.' general_element_part)?
;

char_set_name
: id_expression ('.' id_expression)*
;

synonym_name
: identifier
;

// Represents a valid DB object name in DDL commands which are valid for several DB (or schema) objects.
// For instance, create synonym ... for , or rename to .
// Both are valid for sequences, tables, views, etc.
schema_object_name
: id_expression
;

dir_object_name
: id_expression
;

user_object_name
: id_expression
;

column_list
: column_name (',' column_name)*
;

paren_column_list
: LEFT_PAREN column_list RIGHT_PAREN
;

// PL/SQL Specs

// NOTE: In reality this applies to aggregate functions only
keep_clause
: KEEP '(' DENSE_RANK (FIRST | LAST) (query_partition_clause | order_by_clause) ')' over_clause?
;

function_argument
: '(' (argument (',' argument)*)? ')' keep_clause?
;

function_argument_analytic
: '(' (argument respect_or_ignore_nulls? (',' argument respect_or_ignore_nulls?)*)? ')' keep_clause?
;

function_argument_modeling
: '(' column_name (',' (numeric | NULL_) (',' (numeric | NULL_))?)? USING (
tableview_name '.' ASTERISK
| ASTERISK
| expression column_alias? (',' expression column_alias?)*
) ')' keep_clause?
;

respect_or_ignore_nulls
: (RESPECT | IGNORE) NULLS
;

argument
: (identifier '=' '>')? expression
;

type_spec
: datatype
| REF? type_name (PERCENT_ROWTYPE | PERCENT_TYPE)?
;

datatype
: native_datatype_element precision_part? (WITH LOCAL? TIME ZONE | CHARACTER SET char_set_name)?
| INTERVAL (YEAR | DAY) ('(' expression ')')? TO (MONTH | SECOND) ('(' expression ')')?
;

precision_part
: '(' (numeric | ASTERISK) (',' (numeric | numeric_negative))? (CHAR | BYTE)? ')'
;

bind_variable
: (BINDVAR | ':' UNSIGNED_INTEGER)
// ProC/C++ indicator variables
(INDICATOR? (BINDVAR | ':' UNSIGNED_INTEGER))? ('.' general_element_part)
;

general_element
: general_element_part
| general_element ('.' general_element_part)+
| '(' general_element ')'
;

general_element_part
: (INTRODUCER char_set_name)? id_expression ('@' link_name)? function_argument*
;

table_element
: (INTRODUCER char_set_name)? id_expression ('.' id_expression)*
;

// $>

// $<Lexer Mappings

numeric
: UNSIGNED_INTEGER '.'?
| APPROXIMATE_NUM_LIT
;

numeric_negative
: MINUS_SIGN numeric
;

quoted_string
: CHAR_STRING
//| CHAR_STRING_PERL
| NATIONAL_CHAR_STRING_LIT
;

identifier
: (INTRODUCER char_set_name)? id_expression
;

id_expression
: regular_id
| DELIMITED_ID
;

inquiry_directive
: INQUIRY_DIRECTIVE
;

outer_join_sign
: '(' '+' ')'
;

non_reserved_keywords_in_18c
: PERSISTABLE
| POLYMORPHIC
;

string_function_name
: CHR
| DECODE
| SUBSTR
| TO_CHAR
| TRIM
;

numeric_function_name
: AVG
| COUNT
| NVL
| ROUND
| SUM
;

create_procedure
: CREATE PROCEDURE IDENTIFIER (parameter_list)? IS? block
;

declare_statement
: DECLARE variable_declaration+ BEGIN statement* END
;

empty_statement
: SEMICOLON
;

begin_statement
: BEGIN statement* END
;

end_statement
: END SEMICOLON
;

expression_statement
: IDENTIFIER '=' expression SEMICOLON
;

parameter_list
: parameter (',' parameter)*
;

data_type
: IDENTIFIER
;

value_list
: value (',' value)*
;

value
: STRING_LITERAL | NUMBER | IDENTIFIER
;

column_value_pair
: IDENTIFIER '=' value
;

expression_list
: expression (',' expression)*
;

// Parser rules
logical_expression
: unary_logical_expression
| logical_expression AND logical_expression
| logical_expression OR logical_expression
;

//unary_logical_expression
// : NOT unary_logical_expression // NOT followed by another unary logical expression
// | IDENTIFIER // An identifier (variable)
//| NUMBER // A number
// | '(' logical_expression ')' // Parenthesized expression
// ;

//unary_logical_expression
// : NOT? multiset_expression unary_logical_operation?
// ;

unary_logical_expression
: IDENTIFIER // Variable
| NUMBER // Number
| NOT unary_logical_expression // NOT operation
| '(' logical_expression ')' // Parenthesized expression
;

PlSqlLexer.g4

IDENTIFIER : 'IDENTIFIER';

//fragment LETTER : [a-zA-Z]; // Only letters, no underscore
//DIGIT : [0-9];
//IDENTIFIER : LETTER (LETTER | DIGIT | '')*;
//IDENTIFIER : [a-zA-Z][a-zA-Z0-9_]*;

CREATE : 'CREATE';
PROCEDURE : 'PROCEDURE';
DECLARE : 'DECLARE';
BEGIN : 'BEGIN';
END : 'END';
IS : 'IS';
SELECT : 'SELECT';
FROM : 'FROM';
UPDATE : 'UPDATE';
SET : 'SET';
DELETE : 'DELETE';
INSERT : 'INSERT';
IN : 'IN';
OUT : 'OUT';
INOUT : 'INOUT';
WHERE : 'WHERE';
VALUES : 'VALUES';
SEMICOLON : ';';
COMMA : ',';
NUMBER : [0-9]+;
STRING_LITERAL : ''' (~'''|''' ''')* '''; // String literal
INTO : 'INTO';
WS : [ \t\r\n]+ -> skip; // Whitespace
COMMENT : '--' ~[\r\n]* -> skip; // Single-line comment
BLOCK_COMMENT : '/' .? '*/' -> skip; // Block commen

kaby76 · 2024-12-09T18:57:15Z

The above methods have been integrated into the PlSqlParser.g4 and PlSqlLexer.g4. The grammar files have been compiled, and all necessary files have been generated. However, while running the program, I am facing an issue with logical expressions

PlSqlParser.g4

/**

Oracle(c) PL/SQL 11g Parser

Copyright (c) 2009-2011 Alexandre Porcelli
...

Is there a reason why you copy and paste all 9726 lines of the grammar file when a permalink to the files would have sufficed? E.g., https://github.com/antlr/grammars-v4/blob/b411201e8375764663ec758ba15f9bf40b852013/sql/plsql/PlSqlParser.g4 or even just https://github.com/antlr/grammars-v4/tree/b411201e8375764663ec758ba15f9bf40b852013/sql/plsql
Please do not post pictures, as in the previous comment. The image is unavailable.
Please do post some sample input strings. You just say something is not parsed. I have no idea what input you are referring to.

drakshayanin · 2024-12-10T05:35:26Z

Sample input:CREATE OR REPLACE PROCEDURE get_employee_name(p_employee_id IN NUMBER)
IS
v_employee_name VARCHAR2(100);
BEGIN
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME:' || v_employee_name);
END get_employee_name;

I am not able to chunk the code with the existing listener method; I am getting the entire script.

expected output:
procedure:
CREATE OR REPLACE PROCEDURE get_employee_name(p_employee_id IN NUMBER)
IS
v_employee_name VARCHAR2(100);

begin:
BEGIN
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME:' || v_employee_name);

end:
END get_employee_name;

kaby76 · 2024-12-10T09:09:29Z

Thanks.

The input (x.txt) parses fine. The parse tree in Antlr's toStringTree() format is:

(sql_script (unit_statement (create_procedure_body CREATE OR REPLACE PROCEDURE (procedure_name (identifier (id_expression (regular_id get_employee_name)))) ( (parameter (parameter_name (identifier (id_expression (regular_id p_employee_id)))) IN (type_spec (datatype (native_datatype_element NUMBER)))) ) IS (seq_of_declare_specs (declare_spec (variable_declaration (identifier (id_expression (regular_id v_employee_name))) (type_spec (datatype (native_datatype_element VARCHAR2) (precision_part ( (numeric 100) )))) ;))) (body BEGIN (seq_of_statements (statement (sql_statement (data_manipulation_language_statements (select_statement (select_only_statement (subquery (subquery_basic_elements (query_block SELECT (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (general_element (general_element_part (id_expression (regular_id employee_name)))))))))))))))) (into_clause INTO (general_element (general_element_part (id_expression (regular_id v_employee_name))))) (from_clause FROM (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id employees)))))))))) (where_clause WHERE (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (general_element (general_element_part (id_expression (regular_id employee_id)))))))))) (relational_operator =) (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (general_element (general_element_part (id_expression (regular_id p_employee_id))))))))))))))))))))))))) ; (statement (call_statement (routine_name (identifier (id_expression (regular_id DBMS_OUTPUT))) . (id_expression (regular_id PUT_LINE))) (function_argument ( (argument (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (concatenation (model_expression (unary_expression (atom (constant (quoted_string 'EMPLOYEE NAME:')))))) | | (concatenation (model_expression (unary_expression (atom (general_element (general_element_part (id_expression (regular_id v_employee_name)))))))))))))))) )))) ;) END (label_name (id_expression (regular_id get_employee_name)))))) ; <EOF>)

You should be able to get the name of the procedure from the parse tree. There's nothing wrong per se with the grammar or the generated parser.

For example, using Trash, I can get the name using trparse, trxgrep, and an XPath expression to point to the sub-tree with the name, and get the text for that node.

12/10-04:01:32 ~/issues/g4-current/sql/plsql/Generated-CSharp-full
$ trparse x.txt | trxgrep ' //procedure_name//REGULAR_ID/text()'
CSharp 0 x.txt success 1.0985372
get_employee_name
12/10-04:02:06 ~/issues/g4-current/sql/plsql/Generated-CSharp-full
$

And, you should be able to extract the two statements:

12/10-04:02:06 ~/issues/g4-current/sql/plsql/Generated-CSharp-full
$ trparse x.txt | trxgrep ' //statement' | trtext
CSharp 0 x.txt success 1.0538105

SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = p_employee_id

DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME:' || v_employee_name)
12/10-04:04:20 ~/issues/g4-current/sql/plsql/Generated-CSharp-full
$ trparse x.txt | trxgrep ' //statement' | trtext -c
CSharp 0 x.txt success 1.2186509
2
12/10-04:04:46 ~/issues/g4-current/sql/plsql/Generated-CSharp-full
$

It's likely that your visitor or listener code is not working. But, as far as the grammar, it looks like the parser is working just fine.

drakshayanin · 2024-12-10T12:06:55Z

@OverRide
public void enterUnit_statement(PlSqlParser.Unit_statementContext ctx) {}
This function is processing the entire script, but I need to break down the code into its components. Can you please guide me on how to break down the code in Java?

kaby76 · 2024-12-10T13:21:04Z

I don't think you will want to create an override function for enterUnit_statement(PlSqlParser.Unit_statementContext ctx). Instead, you're going to want to have an override for each component you want to extract.

For example, to get the procedure_name, write a listener that overrides the enter method for regular_id. Then, given the node in the parse tree, traverse up the tree to verify that the node is contained in a "procedure_name" (look at the parse tree print out). If so, get the interval for the node and print out the text.

MyList.java


import org.antlr.v4.runtime.*;
import org.antlr.v4.runtime.tree.*;
import org.antlr.v4.runtime.misc.*;

public class MyList extends PlSqlParserBaseListener
{
    @Override public void enterRegular_id(PlSqlParser.Regular_idContext ctx)
    {
	// Verify that the node fits the pattern
	// '//procedure_name//regular_id' or more accurately,
	// '../ancestor::procedure_name'. In other words, make sure
	// some ancestor up the parent chain is a 'procedure_name'
	// node.
	ParserRuleContext p = ctx;
	for (; p != null; p = p.getParent())
	{
	    if (p instanceof PlSqlParser.Procedure_nameContext)
	    {
		Token start = ctx.getStart();
		int start_index = start.getStartIndex();
		Token stop = ctx.getStop();
		int stop_index = stop.getStopIndex();
		CharStream stream = start.getInputStream();
		System.out.println("Got " + stream.getText(new Interval(start_index, stop_index)));
	    }
	}
    }
}

You're going to need to call the tree walker in your driver.

....
	ParseTreeWalker walker = new ParseTreeWalker();
	MyList mylist = new MyList();
	walker.walk(mylist, tree);
....

drakshayanin · 2024-12-11T06:56:29Z

Not recognizing the listener for these two methods, so unable to chunk the code .Can you please guide me how to proceed ?

@OverRide public void enterBegin_or_end(PlSqlParser.Begin_or_endContext ctx) {
}

@OverRide public void enterProcedure_body(PlSqlParser.Procedure_bodyContext ctx) {

}

kaby76 · 2024-12-11T12:58:28Z

Well, that's because your example doesn't contain any begin_or_end or procedure_body parse tree nodes. Try examples/alter_tablespaces.sql for begin_or_end. That is the only example in the testsuite with that node type. There are plenty of examples with procedure_body, e.g., examples/block01.sql.

12/11-07:48:11 ~/issues/g4-4351/sql/plsql/Generated-CSharp-full
$ trparse ../examples/*.sql | trxgrep ' //(begin_or_end | procedure_body)' | trtext -l
CSharp 0 ../examples/administer_key_management.sql success 0.8776836
CSharp 0 ../examples/aggregate_functions.sql success 0.3025803
....
../examples/alter_tablespaces.sql
../examples/block01.sql
../examples/concatenation-double_asterisk.sql
../examples/concatenation-mod.sql
../examples/create_package02.sql
../examples/create_package03.sql
../examples/fetch_statement.sql
../examples/forall_statement_and_exception_handling.sql
../examples/general_element_part.sql
../examples/package_body.sql
../examples/values_clause.sql
../examples/varray_type_def.sql
12/11-07:49:29 ~/issues/g4-4351/sql/plsql/Generated-CSharp-full
$ pushd ../Generated-Java-full/
~/issues/g4-4351/sql/plsql/Generated-Java-full ~/issues/g4-4351/sql/plsql/Generated-CSharp-full
12/11-07:51:08 ~/issues/g4-4351/sql/plsql/Generated-Java-full
$ bash run.sh ../examples/block01.sql
enterProcedure_body
Java 0 ../examples/block01.sql success 0.484
Total Time: 1.354
12/11-07:51:30 ~/issues/g4-4351/sql/plsql/Generated-Java-full
$ pushd
~/issues/g4-4351/sql/plsql/Generated-CSharp-full ~/issues/g4-4351/sql/plsql/Generated-Java-full
12/11-07:55:36 ~/issues/g4-4351/sql/plsql/Generated-CSharp-full
$ trparse ../examples/block01.sql | trxgrep ' //procedure_body' | trtree -i
CSharp 0 ../examples/block01.sql success 0.8050003
procedure_body
 Attribute SPACES Value '\n' chnl:HIDDEN
 Attribute SINGLE_LINE_COMMENT Value '-- Package body\n' chnl:HIDDEN
 Attribute SINGLE_LINE_COMMENT Value '-- This test case compiles and runs fine on 'Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production'\n' chnl:HIDDEN
 Attribute SPACES Value '\n    ' chnl:HIDDEN
 PROCEDURE
  "PROCEDURE"
 identifier
  id_expression
   regular_id
    Attribute SPACES Value ' ' chnl:HIDDEN
    REGULAR_ID
     "block_test"
 Attribute SPACES Value '\n    ' chnl:HIDDEN
 IS
  "IS"
 body
  Attribute SPACES Value '\n    ' chnl:HIDDEN
  BEGIN
   "BEGIN"
  seq_of_statements
   statement
    block
     Attribute SPACES Value '\n\n        ' chnl:HIDDEN
     DECLARE
      "DECLARE"
     declare_spec
      variable_declaration
       identifier
        id_expression
         regular_id
          non_reserved_keywords_in_12c
           Attribute SPACES Value '\n            ' chnl:HIDDEN
           V1
            "v1"
       type_spec
        datatype
         native_datatype_element
          Attribute SPACES Value ' ' chnl:HIDDEN
          PLS_INTEGER
           "PLS_INTEGER"
       default_value_part
        Attribute SPACES Value ' ' chnl:HIDDEN
        ASSIGN_OP
         ":="
        expression
         logical_expression
          unary_logical_expression
           multiset_expression
            relational_expression
             compound_expression
              concatenation
               model_expression
                unary_expression
                 atom
                  constant
                   numeric
                    Attribute SPACES Value ' ' chnl:HIDDEN
                    UNSIGNED_INTEGER
                     "1"
       SEMICOLON
        ";"
     body
      Attribute SPACES Value '\n        ' chnl:HIDDEN
      BEGIN
       "BEGIN"
      seq_of_statements
       statement
        call_statement
         routine_name
          identifier
           id_expression
            regular_id
             Attribute SPACES Value '\n            ' chnl:HIDDEN
             REGULAR_ID
              "dbms_output"
          PERIOD
           "."
          id_expression
           regular_id
            REGULAR_ID
             "put_line"
         function_argument
          LEFT_PAREN
           "("
          argument
           expression
            logical_expression
             unary_logical_expression
              multiset_expression
               relational_expression
                compound_expression
                 concatenation
                  concatenation
                   model_expression
                    unary_expression
                     atom
                      constant
                       quoted_string
                        CHAR_STRING
                         "'v1='"
                  Attribute SPACES Value ' ' chnl:HIDDEN
                  BAR
                   "|"
                  BAR
                   "|"
                  concatenation
                   model_expression
                    unary_expression
                     atom
                      general_element
                       general_element_part
                        id_expression
                         regular_id
                          non_reserved_keywords_in_12c
                           Attribute SPACES Value ' ' chnl:HIDDEN
                           V1
                            "v1"
          RIGHT_PAREN
           ")"
       SEMICOLON
        ";"
      Attribute SPACES Value '\n        ' chnl:HIDDEN
      END
       "END"
   SEMICOLON
    ";"
   statement
    block
     Attribute SPACES Value '\n\n        ' chnl:HIDDEN
     DECLARE
      "DECLARE"
     body
      Attribute SPACES Value '\n        ' chnl:HIDDEN
      BEGIN
       "BEGIN"
      seq_of_statements
       statement
        call_statement
         routine_name
          identifier
           id_expression
            regular_id
             Attribute SPACES Value '\n            ' chnl:HIDDEN
             REGULAR_ID
              "dbms_output"
          PERIOD
           "."
          id_expression
           regular_id
            REGULAR_ID
             "put_line"
         function_argument
          LEFT_PAREN
           "("
          argument
           expression
            logical_expression
             unary_logical_expression
              multiset_expression
               relational_expression
                compound_expression
                 concatenation
                  model_expression
                   unary_expression
                    atom
                     constant
                      quoted_string
                       CHAR_STRING
                        "'empty declare_spec is ok'"
          RIGHT_PAREN
           ")"
       SEMICOLON
        ";"
      Attribute SPACES Value '\n        ' chnl:HIDDEN
      END
       "END"
   SEMICOLON
    ";"
   statement
    body
     Attribute SPACES Value '\n\n        ' chnl:HIDDEN
     BEGIN
      "BEGIN"
     seq_of_statements
      statement
       call_statement
        routine_name
         identifier
          id_expression
           regular_id
            Attribute SPACES Value '\n            ' chnl:HIDDEN
            REGULAR_ID
             "dbms_output"
         PERIOD
          "."
         id_expression
          regular_id
           REGULAR_ID
            "put_line"
        function_argument
         LEFT_PAREN
          "("
         argument
          expression
           logical_expression
            unary_logical_expression
             multiset_expression
              relational_expression
               compound_expression
                concatenation
                 model_expression
                  unary_expression
                   atom
                    constant
                     quoted_string
                      CHAR_STRING
                       "'no DECLARE no declare_spec is ok too'"
         RIGHT_PAREN
          ")"
      SEMICOLON
       ";"
     Attribute SPACES Value '\n        ' chnl:HIDDEN
     END
      "END"
   SEMICOLON
    ";"
  Attribute SPACES Value '\n\n        ' chnl:HIDDEN
  Attribute SINGLE_LINE_COMMENT Value '-- the block below is invalid\n' chnl:HIDDEN
  Attribute SPACES Value '        ' chnl:HIDDEN
  Attribute SINGLE_LINE_COMMENT Value '-- v2 PLS_INTEGER := 1;\n' chnl:HIDDEN
  Attribute SPACES Value '        ' chnl:HIDDEN
  Attribute SINGLE_LINE_COMMENT Value '-- BEGIN\n' chnl:HIDDEN
  Attribute SPACES Value '        ' chnl:HIDDEN
  Attribute SINGLE_LINE_COMMENT Value '--     dbms_output.put_line('');\n' chnl:HIDDEN
  Attribute SPACES Value '        ' chnl:HIDDEN
  Attribute SINGLE_LINE_COMMENT Value '-- END;\n' chnl:HIDDEN
  Attribute SPACES Value '\n    ' chnl:HIDDEN
  END
   "END"
  label_name
   id_expression
    regular_id
     Attribute SPACES Value ' ' chnl:HIDDEN
     REGULAR_ID
      "block_test"
 SEMICOLON
  ";"


12/11-07:56:06 ~/issues/g4-4351/sql/plsql/Generated-CSharp-full
$

Please make sure to use block comments when giving out code that contains @-signs. When you used @override, it messaged @OverRide on GitHub.

drakshayanin · 2024-12-12T08:20:02Z

In the case of alter_tablespaces.sql, the enterBegin_or_end() function is used, but it only returns the keyword BEGIN and not the actual BEGIN and END statements.
On the other hand, in block01.sql, the enterProcedure_body() function returns the actual BEGIN and END statements

My example has BEGIN and END, but even though it is not working, it uses enterBegin_or_end() and enterProcedure_body()

CREATE OR REPLACE PROCEDURE get_employee_name(p_employee_id IN NUMBER)
IS
v_employee_name VARCHAR2(100);
BEGIN
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME:' || v_employee_name);
END get_employee_name;

Expected Output:
The above example aims to break down the PL/SQL code into the following format

Procedure:
CREATE OR REPLACE PROCEDURE get_employee_name(p_employee_id IN NUMBER)

Declaration:
IS v_employee_name VARCHAR2(100);

Begin:
BEGIN
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME:' || v_employee_name);

End:
END get_employee_name;
Can you please guide me how to proceed ?

kaby76 · 2024-12-12T11:31:07Z

Please use block comments around your SQL code.

In the case of alter_tablespaces.sql, the enterBegin_or_end() function is used, but it only returns the keyword BEGIN and not the actual BEGIN and END statements.

Please create a file in.sql with this input.

ALTER TABLESPACE tbs_01 
    BEGIN BACKUP; 
ALTER TABLESPACE tbs_01
   END BACKUP;

And, do the parse, and print out the tree:

sql_script
├── unit_statement
│   └── alter_tablespace
│       ├── ALTER
│       │   └── "ALTER"
│       ├── Attribute SPACES Value ' ' chnl:HIDDEN
│       ├── TABLESPACE
│       │   └── "TABLESPACE"
│       ├── tablespace
│       │   └── id_expression
│       │       └── regular_id
│       │           ├── Attribute SPACES Value ' ' chnl:HIDDEN
│       │           └── REGULAR_ID
│       │               └── "tbs_01"
│       ├── begin_or_end
│       │   ├── Attribute SPACES Value ' \r\n    ' chnl:HIDDEN
│       │   └── BEGIN
│       │       └── "BEGIN"
│       ├── Attribute SPACES Value ' ' chnl:HIDDEN
│       └── BACKUP
│           └── "BACKUP"
├── SEMICOLON
│   └── ";"
├── unit_statement
│   └── alter_tablespace
│       ├── Attribute SPACES Value ' \r\n' chnl:HIDDEN
│       ├── ALTER
│       │   └── "ALTER"
│       ├── Attribute SPACES Value ' ' chnl:HIDDEN
│       ├── TABLESPACE
│       │   └── "TABLESPACE"
│       ├── tablespace
│       │   └── id_expression
│       │       └── regular_id
│       │           ├── Attribute SPACES Value ' ' chnl:HIDDEN
│       │           └── REGULAR_ID
│       │               └── "tbs_01"
│       ├── begin_or_end
│       │   ├── Attribute SPACES Value '\r\n   ' chnl:HIDDEN
│       │   └── END
│       │       └── "END"
│       ├── Attribute SPACES Value ' ' chnl:HIDDEN
│       └── BACKUP
│           └── "BACKUP"
├── SEMICOLON
│   └── ";"
├── Attribute SPACES Value '\r\n' chnl:HIDDEN
└── EOF
    └── ""

Then, please go to https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/ALTER-TABLESPACE.html, and read the railroad diagram for alter_tablespace_attrs. Examine sub-graph containing (BEGIN | END) BACKUP. There is no "label" for the string. There is no folding into another rule. The Antlr grammar does what the official PL/SQL grammar is written, but refactored in two ways.

First, the alter_tablespace_attrs unfolded into alter_tablespace. Second, (BEGIN | END) was folded into a new rule called begin_or_end.

I do not know why these refactorings were done. The person who wrote this part of the gramar is madams51703, who is a senior developer at MS.

You will need to choose what to do:

extract the information you want from the current parse tree;
write a PR to undo the these refactorings;
or alternatively adds # labels.

drakshayanin · 2024-12-13T09:31:14Z

I have used the PL/SQL grammar file

https://github.com/antlr/grammars-v4/blob/451d59c5ef5ba954ffff24afdf2d367497fb9098/sql/plsql/PlSqlLexer.g4
https://github.com/antlr/grammars-v4/blob/451d59c5ef5ba954ffff24afdf2d367497fb9098/sql/plsql/PlSqlParser.g4

There are several listener methods that can be used to extract the respective parts of the parsed input. In my case, I want to extract the PROCEDURE, BEGIN, END, and EXCEPTION statements separately. So, I tried to override the following listener method:

@OverRide
public void enterUnit_statement(plsqlParser.Unit_statementContext ctx) { }

@OverRide
public void enterCreate_procedure_body(PlSqlParser.Create_procedure_bodyContext ctx) {}

However, I noticed that the entire script is getting captured and I am unable to capture each statement separately using this listener method.

As you mentioned in the previous comment, what I understood is that we cannot extract these statements separately using the available listener methods generated from the PL/SQL grammar files. If we need to extract them separately, we would have to write custom logic on top of it to achieve that. Please correct me if I'm wrong.

kaby76 · 2024-12-13T11:41:39Z

However, I noticed that the entire script is getting captured and I am unable to capture each statement separately using this listener method.
...
If we need to extract them separately, we would have to write custom logic on top of it to achieve that.

Yes, this is correct. Antlr parsers produces concrete syntax trees, not abstract syntax trees, so sometimes the information that you want may span several parse tree nodes. But, it is not hard to get the information, just tedious.

You can use the Antlr Visitor or Listener to get an interval of the tokens and text of anything you are interested in.

For example, suppose we want to extract out the string BEGIN BACKUP from the parse input file alter_tablespaces.sql. To do that, you have to choose which node best works for you. For BEGIN BACKUP, the information is all contained in the immediate children of the alter_tablespace node of the parse tree. Assuming an Antlr Listener implementation, we override @Override public void enterAlter_tablespace(PlSqlParser.Alter_tablespaceContext ctx) { } and have code to get the interval of the text for the statement.


import org.antlr.v4.runtime.*;
import org.antlr.v4.runtime.tree.*;
import org.antlr.v4.runtime.misc.*;

public class MyList extends PlSqlParserBaseListener
{
    CommonTokenStream _token_stream;
    
    public MyList(CommonTokenStream token_stream)
    {
        super();
        _token_stream = token_stream;
    }
    
    @Override public void enterAlter_tablespace(PlSqlParser.Alter_tablespaceContext ctx)
    {
        var begin_node = ctx.begin_or_end();
        if (begin_node == null) return;
        if (begin_node.getText().equals("BEGIN"))
        {
            System.out.println("Got a begin");
        }
        var last_node = ctx.getChild(ctx.getChildCount() - 1);
        if (last_node == null) return;
        Token start = _token_stream.get(begin_node.getSourceInterval().a);
        int start_index = start.getStartIndex();
        int stop_index = _token_stream.get(last_node.getSourceInterval().b).getStopIndex();
        CharStream char_stream = start.getInputStream();
        System.out.println("The statement is: " + char_stream.getText(new Interval(start_index, stop_index)));
    }
}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

PL/SQL procedure, statement, and function are not available i have integrated the PlSqlParser.g4 and PlSqlLexer.g4 but its am facing the while running the code? #4351

PL/SQL procedure, statement, and function are not available i have integrated the PlSqlParser.g4 and PlSqlLexer.g4 but its am facing the while running the code? #4351

drakshayanin commented Dec 9, 2024

drakshayanin commented Dec 9, 2024

drakshayanin commented Dec 9, 2024

kaby76 commented Dec 9, 2024 •

edited

Loading

drakshayanin commented Dec 10, 2024

kaby76 commented Dec 10, 2024

drakshayanin commented Dec 10, 2024

kaby76 commented Dec 10, 2024

drakshayanin commented Dec 11, 2024

kaby76 commented Dec 11, 2024

drakshayanin commented Dec 12, 2024

kaby76 commented Dec 12, 2024

drakshayanin commented Dec 13, 2024

kaby76 commented Dec 13, 2024 •

edited

Loading

PL/SQL procedure, statement, and function are not available i have integrated the PlSqlParser.g4 and PlSqlLexer.g4 but its am facing the while running the code? #4351

PL/SQL procedure, statement, and function are not available i have integrated the PlSqlParser.g4 and PlSqlLexer.g4 but its am facing the while running the code? #4351

Comments

drakshayanin commented Dec 9, 2024

drakshayanin commented Dec 9, 2024

drakshayanin commented Dec 9, 2024

kaby76 commented Dec 9, 2024 • edited Loading

drakshayanin commented Dec 10, 2024

kaby76 commented Dec 10, 2024

drakshayanin commented Dec 10, 2024

kaby76 commented Dec 10, 2024

drakshayanin commented Dec 11, 2024

kaby76 commented Dec 11, 2024

drakshayanin commented Dec 12, 2024

kaby76 commented Dec 12, 2024

drakshayanin commented Dec 13, 2024

kaby76 commented Dec 13, 2024 • edited Loading

kaby76 commented Dec 9, 2024 •

edited

Loading

kaby76 commented Dec 13, 2024 •

edited

Loading