create or replace package statement_classifier is

--Copyright (C) 2015 Jon Heller. This program is licensed under the LGPLv3.

procedure classify(

p_tokens in token_table,

p_category out varchar2,

p_statement_type out varchar2,

p_command_name out varchar2,

p_command_type out number,

p_lex_sqlcode out number,

p_lex_sqlerrm out varchar2,

p_start_index in number default 1

);

--Variations on classify that may be more convenient if you don't need all the details.

function get_category(p_source in clob) return varchar2;

function get_statement_type(p_source in clob) return varchar2;

function get_command_name(p_source in clob) return varchar2;

function get_command_type(p_source in clob) return varchar2;

--Helper functions useful for further classifying statements.

function has_plsql_declaration(

p_tokens token_table,

p_token_start_index in number default 1

) return boolean;

procedure get_trigger_type_body_index (

p_tokens in token_table,

p_trigger_type out number,

p_trigger_body_start_index out number);

--Constants useful for classifying triggers.

C_TRIGGER_TYPE_REGULAR constant number := 1;

C_TRIGGER_TYPE_COMPOUND constant number := 2;

C_TRIGGER_TYPE_CALL constant number := 3;

/*

== Purpose ==

Statement classifier is a PL/SQL package to determine the category, statement

type, command name, and command type (id) for a single SQL or PL/SQL statement.

This information can help programs decide how to handle different statements.

For example, a program may need to decide to:

- Inspect the results if it is a SELECT statement.

- Look at SQL%ROWCOUNT and COMMIT the transaction if it is a DML statement.

- Prevent the code from running if it is a PL/SQL statement.

== Example ==

declare

v_category varchar2(100);

v_statement_type varchar2(100);

v_command_name varchar2(64);

v_command_type number;

v_lex_sqlcode number;

v_lex_sqlerrm varchar2(4000);

begin

statement_classifier.classify(

plsql_lexer.lex('(with test as (select * from dual) select * from test))'),

v_category,v_statement_type,v_command_name,v_command_type,v_lex_sqlcode,v_lex_sqlerrm

);

dbms_output.put_line('Category : '||v_category);

dbms_output.put_line('Statement Type: '||v_statement_type);

dbms_output.put_line('Command Name : '||v_command_name);

dbms_output.put_line('Command Type : '||v_command_type);

dbms_output.put_line('Lex SQLCODE : '||v_lex_sqlcode);

dbms_output.put_line('Lex SQLERRM : '||v_lex_sqlerrm);

end;

Results:

Category : DML

Statement Type: SELECT

Command Name : SELECT

Command Type : 3

Lex SQLCODE :

Lex SQLERRM :

== Parameters ==

- p_tokens (IN): The tokens for a statement, probably generated by plsql_lexer.

- p_category (OUT): One of DDL, DML, Transaction Control, Session Control,

System Control, PL/SQL, Nothing, or Invalid.

- p_statement_type (OUT): More descriptive than category. For example, INSERT,

SELECT, or CREATE.

Here are the possible combinations of P_CATEGORY and P_STATEMENT_TYPE.

These combinations are defined by the manual, except for "PL/SQL", "Invalid"

(a string that cannot be classified, probably because of a typo like

'seelct ...'.), and "Nothing" (a string with only whitespace or comments).

DDL

ADMINISTER KEY MANAGEMENT, ALTER (except ALTER SESSION and ALTER SYSTEM),

ANALYZE,ASSOCIATE STATISTICS,AUDIT,COMMENT,CREATE,DISASSOCIATE STATISTICS,

DROP,FLASHBACK,GRANT,NOAUDIT,PURGE,RENAME,REVOKE,TRUNCATE

DML

CALL,DELETE,EXPLAIN PLAN,EXPLAIN WORK,INSERT,LOCK TABLE,MERGE,SELECT,UPDATE

Transaction Control

COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION,SET CONSTRAINT

Session Control

ALTER SESSION,SET ROLE

System Control

ALTER SYSTEM

PL/SQL

BLOCK

Invalid

Invalid

Nothing

Nothing

- p_command_name (OUT): This is usually more specific than the statement type

and is based on V$SQLCOMMAND. For example, "CREATE PLUGGABLE DATABASE".

Also includes the custom values Invalid and Nothing.

- p_command_type (OUT): This is an ID number assigned to each command name,

as defind by V$SQLCOMMAND.

Also includes custom values -1 (Invalid) and -2 (Nothing).

Also includes these made-up values because they don't exist in V$SQLCOMMAND:

(See Bug 27629117 : V$SQLCOMMAND VIEW MISSES SOME NEW COMMAND TYPES)

-201 (ALTER TABLESPACE SET)

-202 (CREATE TABLESPACE SET)

-203 (DROP TABLESPACE SET)

-204 (PURGE TABLESPACE SET)

- p_lex_sqlcode (OUT): The first SQL error code generated by scanning the

statement. This package does *NOT* fully parse the statement. An error

code implies there is a serious syntax error, such as a non-terminated

string. The statement is still classified but likely will not run.

- p_lex_sqlerrm (OUT): The SQLERRM associated with the SQLCODE above.

== References ==

The category names and associations are based on the Oracle 12c "Types of SQL

Statements" chapter of "Database SQL Language Reference", as well as the

"Blocks" chapter of the "Database PL/SQL Language Reference".

SQL: http://docs.oracle.com/database/121/SQLRF/statements_1001.htm#SQLRF30001

PL/SQL: http://docs.oracle.com/database/121/LNPLS/block.htm#LNPLS01303

Command name and command type are based on V$SQLCOMMAND. The COMMAND_TYPE

column can be found in V$SQL, DBA_HIST_SQLTEXT, etc.

The SQLCODE and SQLERRM are based on the SQL errors thrown by serious

syntax issues that prevent code from running. For example, "select /* from dual;"

would generate "ORA-01742: comment not terminated properly".

== Lexer details ==

Classifying statements does not require a full parser. Many shortcuts are

possible because only the first four (non-optional) keywords are usually

needed. However, the lexer still tokenizes everything and may catch some

serious syntax errors, such as a non-terminated string.

A simple review of the SQL Language Reference shows that 99% of the time

a statement type can be identified simply by matching with the first N keywords.

For example, "CREATE" must start with keyword CREATE. Some notable exceptions

are SELECT can start with "WITH" or "(", and a PL/SQL BLOCK can start with "<<",

"DECLARE", or "BEGIN".

See PLSQL_LEXER for more details on possible tokens.

*/

end;

/

create or replace package body statement_classifier is

--Constants

C_DDL constant varchar2(100) := 'DDL';

C_DML constant varchar2(100) := 'DML';

C_Transaction_Control constant varchar2(100) := 'Transaction Control';

C_Session_Control constant varchar2(100) := 'Session Control';

C_System_Control constant varchar2(100) := 'System Control';

C_PLSQL constant varchar2(100) := 'PL/SQL';

C_Invalid constant varchar2(100) := 'Invalid';

C_Nothing constant varchar2(100) := 'Nothing';

--------------------------------------------------------------------------------

procedure classify(

p_tokens in token_table,

p_category out varchar2,

p_statement_type out varchar2,

p_command_name out varchar2,

p_command_type out number,

p_lex_sqlcode out number,

p_lex_sqlerrm out varchar2,

p_start_index in number default 1

) is

v_concrete_tokens token_table := token_table();

type string_table is table of varchar2(4000) index by pls_integer;

v_types string_table;

v_values string_table;

v_words_1 varchar2(4000);

v_words_1_to_2 varchar2(4000);

v_words_1_to_3 varchar2(4000);

v_words_1_to_4 varchar2(4000);

--Return TRUE if there is at least one concrete token, else return FALSE.

function has_concrete_tokens return boolean is

begin

for i in p_start_index .. p_tokens.count loop

if p_tokens(i).type not in ('whitespace', 'comment', 'EOF') then

return true;

end if;

end loop;

return false;

end;

begin

--DEBUG:

--dbms_output.put_line(print_tokens(v_tokens_with_whitespace));

--Find the first plsql_lexer error.

--Lexing errors are so serious that there will rarely be more than one anyway.

for i in p_start_index .. p_tokens.count loop

if p_tokens(i).sqlcode is not null then

p_lex_sqlcode := p_tokens(i).sqlcode;

p_lex_sqlerrm := p_tokens(i).sqlerrm;

exit;

end if;

end loop;

--Remove whitespace, comments, and optional keywords.

--Optional keywords in the SQL Language Reference are not used for determining

--the category, statement type, command name, or command type.

--For example, ALTER [SHARED|PUBLIC]? DATABASE LINK is DDL/ALTER/ALTER DATABASE LINK/225.

--The keywords SHARED and PUBLIC can be discarded. Luckily, *almost all* of those

--optional keywords can *always* be discarded, leaving a nice linear path.

--The only exception is "COMPILE", which is occasionally necessary to classify.

/*

List of all optional keywords:

ALTER [SHARED|PUBLIC]? DATABASE LINK

ALTER [PUBLIC]? SYNONYM

CREATE [OR REPLACE]? CONTEXT

CREATE [SHARED|PUBLIC]? DATABASE LINK

CREATE [OR REPLACE]? DIRECTORY

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? FUNCTION

CREATE [UNIQUE|BITMAP]? INDEX

CREATE [OR REPLACE]? INDEXTYPE

CREATE [OR REPLACE]? [AND (RESOLVE|COMPILE)]? [NOFORCE]? JAVA

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? LIBRARY

CREATE [OR REPLACE]? OPERATOR

CREATE [OR REPLACE]? [PUBLIC|PRIVATE]? OUTLINE

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? PACKAGE

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? PACKAGE BODY

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? PROCEDURE

CREATE [PUBLIC]? ROLLBACK SEGMENT

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? [PUBLIC]? SYNONYM

CREATE [GLOBAL TEMPORARY]? TABLE

CREATE [BIGFILE|SMALLFILE]? [TEMPORARY|UNDO]? TABLESPACE

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? TRIGGER

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? TYPE

CREATE [OR REPLACE]? [EDITIONABLE|NONEDITIONABLE]? TYPE BODY

CREATE [OR REPLACE]? [NO FORCE|FORCE]? [EDITIONING|EDITIONABLE|EDITIONABLE EDITIONING|NONEDITIONABLE]? VIEW

DROP [PUBLIC]? DATABASE LINK

DROP [PUBLIC]? SYNONYM

FLASHBACK [STANDBY]? DATABASE

SET [CONSTRAINT|CONSTRAINTS]* (AMBIGUOUS - HOW DO WE NAME THESE?)

*/

for i in p_start_index .. p_tokens.count loop

if p_tokens(i).type not in ('whitespace', 'comment')

and not

(

p_tokens(i).type = 'word'

and upper(p_tokens(i).value) in

(

--Removed COMPILE

'AND','BIGFILE','BITMAP','EDITIONABLE','EDITIONING','FORCE','FORCE','GLOBAL',

'NO','NOFORCE','NONEDITIONABLE','OR','PRIVATE','PUBLIC','REPLACE','RESOLVE',

'SHARED','SMALLFILE','STANDBY','TEMPORARY','UNDO','UNIQUE'

)

)

then

v_concrete_tokens.extend;

v_concrete_tokens(v_concrete_tokens.count) := p_tokens(i);

end if;

end loop;

--Get up to 8 tokens.

--The first 4 tokens are sufficient 99% of the time.

--8 tokens may be required for "alter package body".

for i in 1 .. least(v_concrete_tokens.count, 8) loop

if v_concrete_tokens.exists(i) then

v_types(i) := v_concrete_tokens(i).type;

v_values(i) := upper(v_concrete_tokens(i).value);

if v_types(i) = 'word' and i = 1 then

v_words_1 := upper(v_concrete_tokens(i).value);

elsif v_types(i) = 'word' and i = 2 then

v_words_1_to_2 := upper(v_words_1 || ' ' || v_concrete_tokens(i).value);

elsif v_types(i) = 'word' and i = 3 then

v_words_1_to_3 := upper(v_words_1_to_2 || ' ' || v_concrete_tokens(i).value);

elsif v_types(i) = 'word' and i = 4 then

v_words_1_to_4 := upper(v_words_1_to_3 || ' ' || v_concrete_tokens(i).value);

end if;

end if;

end loop;

--See the description in the package specification for details of this.

--

--This SQL generates most of the code for classification of tokens.

--The category and statement types are modified, and some exceptions are commented.

--The real list has a few exceptions (such as "SELECT", which can start with a

--"(" or a "WITH"), and some differences in order. For example,

--"ALTER DATABASE LINK" is more specific than "ALTER DATABASE" and is listed first.

--

/*

select v$sqlcommand.*,

' elsif '||

--TRIM and REPLACE remove some extra spaces.

case regexp_count(trim(replace(command_name, ' ', ' ')), ' ')

when 0 then 'v_words_1'

when 1 then 'v_words_1_to_2'

when 2 then 'v_words_1_to_3'

when 3 then 'v_words_1_to_4'

else '????'

end||' = '''||command_name||''' then

p_category := ''DDL''; p_statement_type := '''||

regexp_substr(command_name, '^[^ ]*') --Statement type is usually just the first word.

||'''; p_command_name := '''||command_name||'''; p_command_type := '||command_type||';'

v_plsql

from v$sqlcommand

order by command_name;

*/

if not has_concrete_tokens then

p_category := C_Nothing; p_statement_type := C_Nothing; p_command_name := C_Nothing; p_command_type := -2;

elsif v_words_1_to_3 = 'ADMINISTER KEY MANAGEMENT' then --Not in "Types of SQL Statements".

p_category := C_DDL; p_statement_type := 'ADMINISTER KEY MANAGEMENT'; p_command_name := 'ADMINISTER KEY MANAGEMENT'; p_command_type := 238;

elsif v_words_1_to_3 = 'ALTER ANALYTIC VIEW' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER ANALYTIC VIEW'; p_command_type := 250;

elsif v_words_1_to_2 = 'ALTER ASSEMBLY' then --I don't think this is a real command.

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER ASSEMBLY'; p_command_type := 217;

elsif v_words_1_to_3 = 'ALTER ATTRIBUTE DIMENSION' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER ATTRIBUTE DIMENSION'; p_command_type := 244;

elsif v_words_1_to_3 = 'ALTER AUDIT POLICY' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER AUDIT POLICY'; p_command_type := 230;

elsif v_words_1_to_2 = 'ALTER CLUSTER' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER CLUSTER'; p_command_type := 5;

elsif v_words_1_to_3 = 'ALTER DATABASE LINK' then --Moved above "ALTER DATABASE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER DATABASE LINK'; p_command_type := 225;

elsif v_words_1_to_3 = 'ALTER DATABASE DICTIONARY' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER DATABASE DICTIONARY'; p_command_type := 252;

elsif v_words_1_to_2 = 'ALTER DATABASE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER DATABASE'; p_command_type := 35;

elsif v_words_1_to_2 = 'ALTER DIMENSION' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER DIMENSION'; p_command_type := 175;

elsif v_words_1_to_2 = 'ALTER DISKGROUP' then --The real command is "DISKGROUP", not "DISK GROUP".

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER DISK GROUP'; p_command_type := 193;

--Not sure if this is real. It's not documented, and doesn't work on my 12.1.0.2.

--But it's mentioned in some respectable websites. Guess it doesn't hurt to include it.

elsif v_words_1_to_2 = 'ALTER EDITION' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER EDITION'; p_command_type := 213;

elsif v_words_1_to_3 = 'ALTER FLASHBACK ARCHIVE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER FLASHBACK ARCHIVE'; p_command_type := 219;

elsif v_words_1_to_2 = 'ALTER FUNCTION' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER FUNCTION'; p_command_type := 92;

elsif v_words_1_to_2 = 'ALTER HIERARCHY' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER HIERARCHY'; p_command_type := 247;

elsif v_words_1_to_2 = 'ALTER INDEX' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER INDEX'; p_command_type := 11;

elsif v_words_1_to_2 = 'ALTER INDEXTYPE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER INDEXTYPE'; p_command_type := 166;

elsif v_words_1_to_4 = 'ALTER INMEMORY JOIN GROUP' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER INMEMORY JOIN GROUP'; p_command_type := 254;

elsif v_words_1_to_2 = 'ALTER JAVA' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER JAVA'; p_command_type := 161;

elsif v_words_1_to_2 = 'ALTER LIBRARY' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER LIBRARY'; p_command_type := 196;

elsif v_words_1_to_3 = 'ALTER LOCKDOWN PROFILE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER LOCKDOWN PROFILE'; p_command_type := 236;

--"SNAPSHOT" is old syntax for "MATERIALIZED VIEW", but is still supported.

elsif v_words_1_to_4 = 'ALTER MATERIALIZED VIEW LOG' or v_words_1_to_3 = 'ALTER SNAPSHOT LOG' then --Moved above "ALTER MATERIALIZED VIEW" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER MATERIALIZED VIEW LOG'; p_command_type := 72;

elsif v_words_1_to_3 = 'ALTER MATERIALIZED VIEW' or v_words_1_to_2 = 'ALTER SNAPSHOT' then --The command name has an extra space at the end.

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER MATERIALIZED VIEW '; p_command_type := 75;

elsif v_words_1_to_3 = 'ALTER MATERIALIZED ZONEMAP' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER MATERIALIZED ZONEMAP'; p_command_type := 240;

elsif v_words_1_to_2 = 'ALTER OPERATOR' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER OPERATOR'; p_command_type := 183;

elsif v_words_1_to_2 = 'ALTER OUTLINE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER OUTLINE'; p_command_type := 179;

elsif v_words_1_to_2 = 'ALTER PACKAGE' then

--There's only a few ways to be a "ALTER PACKAGE BODY":

--alter package (schema .)? package_name compile debug? body

if

--Example: alter package test_package compile body

(

(v_types(3) = 'word' and v_types(4) = 'word' and v_types(5) = 'word')

and

(v_values(3) like '%' and v_values(4) = 'COMPILE' and v_values(5) = 'BODY')

)

or

--Example: alter package jheller.test_package compile body

(

(v_types(3) = 'word' and v_types(4) = '.' and v_types(5) = 'word' and v_types(6) = 'word' and v_types(7) = 'word')

and

(v_values(3) like '%' and v_values(4) = '.' and v_values(5) like '%' and v_values(6) = 'COMPILE' and v_values(7) = 'BODY')

)

or

--Example: alter package test_package compile debug body

(

(v_types(3) = 'word' and v_types(4) = 'word' and v_types(5) = 'word' and v_types(6) = 'word')

and

(v_values(3) like '%' and v_values(4) = 'COMPILE' and v_values(5) = 'DEBUG' and v_values(6) = 'BODY')

)

or

--Example: alter package jheller.test_package compile debug body

(

(v_types(3) = 'word' and v_types(4) = '.' and v_types(5) = 'word' and v_types(6) = 'word' and v_types(7) = 'word' and v_types(8) = 'word')

and

(v_values(3) like '%' and v_values(4) = '.' and v_values(5) like '%' and v_values(6) = 'COMPILE' and v_values(7) = 'DEBUG' and v_values(8) = 'BODY')

) then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER PACKAGE BODY'; p_command_type := 98;

--Anything else is an "ALTER PACKAGE".

else

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER PACKAGE'; p_command_type := 95;

end if;

elsif v_words_1_to_3 = 'ALTER PLUGGABLE DATABASE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER PLUGGABLE DATABASE'; p_command_type := 227;

elsif v_words_1_to_2 = 'ALTER PROCEDURE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER PROCEDURE'; p_command_type := 25;

elsif v_words_1_to_2 = 'ALTER PROFILE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER PROFILE'; p_command_type := 67;

elsif v_words_1_to_3 = 'ALTER RESOURCE COST' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER RESOURCE COST'; p_command_type := 70;

--I don't think this is a real command.

--elsif v_words_1_to_3 = 'ALTER REWRITE EQUIVALENCE' then

-- p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER REWRITE EQUIVALENCE'; p_command_type := 210;

elsif v_words_1_to_2 = 'ALTER ROLE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER ROLE'; p_command_type := 79;

elsif v_words_1_to_3 = 'ALTER ROLLBACK SEGMENT' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER ROLLBACK SEGMENT'; p_command_type := 37;

elsif v_words_1_to_2 = 'ALTER SEQUENCE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER SEQUENCE'; p_command_type := 14;

elsif v_words_1_to_2 = 'ALTER SESSION' then --Different than other ALTERs.

p_category := C_Session_Control; p_statement_type := 'ALTER SESSION'; p_command_name := 'ALTER SESSION'; p_command_type := 42;

--An old version of "ALTER SNAPSHOT"? This is not supported in 11gR2+.

--elsif v_words_1_to_2 = 'ALTER SUMMARY' then

-- p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER SUMMARY'; p_command_type := 172;

elsif v_words_1_to_2 = 'ALTER SYNONYM' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER SYNONYM'; p_command_type := 192;

elsif v_words_1_to_2 = 'ALTER SYSTEM' then --Different than other ALTERs.

p_category := C_System_Control; p_statement_type := 'ALTER SYSTEM'; p_command_name := 'ALTER SYSTEM'; p_command_type := 49;

elsif v_words_1_to_2 = 'ALTER TABLE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TABLE'; p_command_type := 15;

elsif v_words_1_to_3 = 'ALTER TABLESPACE SET' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TABLESPACE SET'; p_command_type := -201;

elsif v_words_1_to_2 = 'ALTER TABLESPACE' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TABLESPACE'; p_command_type := 40;

--Undocumented by still runs in 12.1.0.2.

elsif v_words_1_to_2 = 'ALTER TRACING' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TRACING'; p_command_type := 58;

elsif v_words_1_to_2 = 'ALTER TRIGGER' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TRIGGER'; p_command_type := 60;

--This is very similar to "ALTER PACKAGE".

elsif v_words_1_to_2 = 'ALTER TYPE' then

--There's only a few ways to be a "ALTER TYPE BODY":

--alter type (schema .)? type_name compile debug? body

if

--Example: alter type test_type compile body

(

(v_types(3) = 'word' and v_types(4) = 'word' and v_types(5) = 'word')

and

(v_values(3) like '%' and v_values(4) = 'COMPILE' and v_values(5) = 'BODY')

)

or

--Example: alter type jheller.test_type compile body

(

(v_types(3) = 'word' and v_types(4) = '.' and v_types(5) = 'word' and v_types(6) = 'word' and v_types(7) = 'word')

and

(v_values(3) like '%' and v_values(4) = '.' and v_values(5) like '%' and v_values(6) = 'COMPILE' and v_values(7) = 'BODY')

)

or

--Example: alter type test_type compile debug body

(

(v_types(3) = 'word' and v_types(4) = 'word' and v_types(5) = 'word' and v_types(6) = 'word')

and

(v_values(3) like '%' and v_values(4) = 'COMPILE' and v_values(5) = 'DEBUG' and v_values(6) = 'BODY')

)

or

--Example: alter type jheller.test_type compile debug body

(

(v_types(3) = 'word' and v_types(4) = '.' and v_types(5) = 'word' and v_types(6) = 'word' and v_types(7) = 'word' and v_types(8) = 'word')

and

(v_values(3) like '%' and v_values(4) = '.' and v_values(5) like '%' and v_values(6) = 'COMPILE' and v_values(7) = 'DEBUG' and v_values(8) = 'BODY')

) then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TYPE BODY'; p_command_type := 82;

--Anything else is an "ALTER TYPE".

else

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER TYPE'; p_command_type := 80;

end if;

elsif v_words_1_to_2 = 'ALTER USER' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER USER'; p_command_type := 43;

elsif v_words_1_to_2 = 'ALTER VIEW' then

p_category := C_DDL; p_statement_type := 'ALTER'; p_command_name := 'ALTER VIEW'; p_command_type := 88;

elsif v_words_1_to_2 = 'ANALYZE CLUSTER' then --Syntax diagram is incorrect, statement must start with "ANALYZE".

p_category := C_DDL; p_statement_type := 'ANALYZE'; p_command_name := 'ANALYZE CLUSTER'; p_command_type := 64;

elsif v_words_1_to_2 = 'ANALYZE INDEX' then

p_category := C_DDL; p_statement_type := 'ANALYZE'; p_command_name := 'ANALYZE INDEX'; p_command_type := 63;

elsif v_words_1_to_2 = 'ANALYZE TABLE' then

p_category := C_DDL; p_statement_type := 'ANALYZE'; p_command_name := 'ANALYZE TABLE'; p_command_type := 62;

elsif v_words_1_to_2 = 'ASSOCIATE STATISTICS' then

p_category := C_DDL; p_statement_type := 'ASSOCIATE STATISTICS'; p_command_name := 'ASSOCIATE STATISTICS'; p_command_type := 168;

elsif v_words_1 = 'AUDIT' then --The command name is more specific than the command.

p_category := C_DDL; p_statement_type := 'AUDIT'; p_command_name := 'AUDIT OBJECT'; p_command_type := 30;

elsif v_words_1 = 'CALL' then --The command name is more specific than the command.

p_category := C_DML; p_statement_type := 'CALL'; p_command_name := 'CALL METHOD'; p_command_type := 170;

--This is not a real command.

--elsif v_words_1_to_2 = 'CHANGE PASSWORD' then

-- p_category := C_DDL; p_statement_type := 'CHANGE'; p_command_name := 'CHANGE PASSWORD'; p_command_type := 190;

elsif v_words_1 = 'COMMENT' then

p_category := C_DDL; p_statement_type := 'COMMENT'; p_command_name := 'COMMENT'; p_command_type := 29;

elsif v_words_1 = 'COMMIT' then

p_category := C_Transaction_Control; p_statement_type := 'COMMIT'; p_command_name := 'COMMIT'; p_command_type := 44;

elsif v_words_1_to_3 = 'CREATE ANALYTIC VIEW' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE ANALYTIC VIEW'; p_command_type := 249;

elsif v_words_1_to_2 = 'CREATE ASSEMBLY' then --I don't think this is a real command.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE ASSEMBLY'; p_command_type := 216;

elsif v_words_1_to_3 = 'CREATE ATTRIBUTE DIMENSION' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE ATTRIBUTE DIMENSION'; p_command_type := 243;

elsif v_words_1_to_3 = 'CREATE AUDIT POLICY' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE AUDIT POLICY'; p_command_type := 229;

--This is not a real command.

--elsif v_words_1_to_2 = 'CREATE BITMAPFILE' then --I don't think this is a real command.

-- p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE BITMAPFILE'; p_command_type := 87;

elsif v_words_1_to_2 = 'CREATE CLUSTER' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE CLUSTER'; p_command_type := 4;

elsif v_words_1_to_2 = 'CREATE CONTEXT' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE CONTEXT'; p_command_type := 177;

--Command name has an extra space.

elsif v_words_1_to_2 = 'CREATE CONTROLFILE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE CONTROL FILE'; p_command_type := 57;

elsif v_words_1_to_3 = 'CREATE DATABASE LINK' then --Moved above "CREATE DATABASE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE DATABASE LINK'; p_command_type := 32;

elsif v_words_1_to_2 = 'CREATE DATABASE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE DATABASE'; p_command_type := 34;

elsif v_words_1_to_2 = 'CREATE DIMENSION' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE DIMENSION'; p_command_type := 174;

elsif v_words_1_to_2 = 'CREATE DIRECTORY' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE DIRECTORY'; p_command_type := 157;

elsif v_words_1_to_2 = 'CREATE DISKGROUP' then --Command name has extra space, real command is "DISKGROUP".

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE DISK GROUP'; p_command_type := 194;

elsif v_words_1_to_2 = 'CREATE EDITION' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE EDITION'; p_command_type := 212;

elsif v_words_1_to_3 = 'CREATE FLASHBACK ARCHIVE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE FLASHBACK ARCHIVE'; p_command_type := 218;

elsif v_words_1_to_2 = 'CREATE FUNCTION' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE FUNCTION'; p_command_type := 91;

elsif v_words_1_to_2 = 'CREATE HIERARCHY' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE HIERARCHY'; p_command_type := 246;

elsif v_words_1_to_2 = 'CREATE INDEX' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE INDEX'; p_command_type := 9;

elsif v_words_1_to_3 = 'CREATE SEARCH INDEX' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE INDEX'; p_command_type := 9;

elsif v_words_1_to_2 = 'CREATE INDEXTYPE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE INDEXTYPE'; p_command_type := 164;

elsif v_words_1_to_4 = 'CREATE INMEMORY JOIN GROUP' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE INMEMORY JOIN GROUP'; p_command_type := 253;

--COMPILE is optional here, but not elsewhere.

--Since it's not always thrown out, it must be handled here.

elsif v_words_1_to_2 = 'CREATE JAVA' or v_words_1_to_3 = 'CREATE COMPILE JAVA' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE JAVA'; p_command_type := 160;

elsif v_words_1_to_2 = 'CREATE LIBRARY' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE LIBRARY'; p_command_type := 159;

elsif v_words_1_to_3 = 'CREATE LOCKDOWN PROFILE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE LOCKDOWN PROFILE'; p_command_type := 234;

elsif v_words_1_to_4 = 'CREATE MATERIALIZED VIEW LOG' or v_words_1_to_3 = 'CREATE SNAPSHOT LOG' then --Moved above "CREATE MATERIALIZED VIEW" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE MATERIALIZED VIEW LOG'; p_command_type := 71;

elsif v_words_1_to_3 = 'CREATE MATERIALIZED VIEW' or v_words_1_to_2 = 'CREATE SNAPSHOT' then --Extra space in command name.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE MATERIALIZED VIEW '; p_command_type := 74;

elsif v_words_1_to_3 = 'CREATE MATERIALIZED ZONEMAP' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE MATERIALIZED ZONEMAP'; p_command_type := 239;

elsif v_words_1_to_2 = 'CREATE OPERATOR' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE OPERATOR'; p_command_type := 163;

elsif v_words_1_to_2 = 'CREATE OUTLINE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE OUTLINE'; p_command_type := 180;

elsif v_words_1_to_3 = 'CREATE PACKAGE BODY' then --Moved above "CREATE PACKAGE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE PACKAGE BODY'; p_command_type := 97;

elsif v_words_1_to_2 = 'CREATE PACKAGE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE PACKAGE'; p_command_type := 94;

elsif v_words_1_to_2 = 'CREATE PFILE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE PFILE'; p_command_type := 188;

elsif v_words_1_to_3 = 'CREATE PLUGGABLE DATABASE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE PLUGGABLE DATABASE'; p_command_type := 226;

elsif v_words_1_to_2 = 'CREATE PROCEDURE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE PROCEDURE'; p_command_type := 24;

elsif v_words_1_to_2 = 'CREATE PROFILE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE PROFILE'; p_command_type := 65;

elsif v_words_1_to_4 = 'CREATE CLEAN RESTORE POINT' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE RESTORE POINT'; p_command_type := 206;

elsif v_words_1_to_3 = 'CREATE RESTORE POINT' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE RESTORE POINT'; p_command_type := 206;

elsif v_words_1_to_2 = 'CREATE ROLE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE ROLE'; p_command_type := 52;

elsif v_words_1_to_3 = 'CREATE ROLLBACK SEGMENT' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE ROLLBACK SEGMENT'; p_command_type := 36;

elsif v_words_1_to_3 = 'CREATE SCHEMA SYNONYM' then --Undocumented feature. Moved above "CREATE SCHEMA" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE SCHEMA SYNONYM'; p_command_type := 222;

elsif v_words_1_to_2 = 'CREATE SCHEMA' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE SCHEMA'; p_command_type := 56;

elsif v_words_1_to_2 = 'CREATE SEQUENCE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE SEQUENCE'; p_command_type := 13;

elsif v_words_1_to_2 = 'CREATE SPFILE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE SPFILE'; p_command_type := 187;

--An old version of "CREATE SNAPSHOT"? This is not supported in 11gR2+.

--elsif v_words_1_to_2 = 'CREATE SUMMARY' then --Not a real command, I think this is an old version of "SNAPSHOT".

-- p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE SUMMARY'; p_command_type := 171;

elsif v_words_1_to_2 = 'CREATE SYNONYM' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE SYNONYM'; p_command_type := 19;

elsif v_words_1_to_2 = 'CREATE TABLE' or v_words_1_to_3 in ('CREATE SHARDED TABLE', 'CREATE DUPLICATED TABLE') then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE TABLE'; p_command_type := 1;

elsif v_words_1_to_3 = 'CREATE TABLESPACE SET' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE TABLESPACE SET'; p_command_type := -202;

elsif v_words_1_to_2 = 'CREATE TABLESPACE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE TABLESPACE'; p_command_type := 39;

elsif v_words_1_to_2 = 'CREATE TRIGGER' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE TRIGGER'; p_command_type := 59;

elsif v_words_1_to_3 = 'CREATE TYPE BODY' then --Moved above "CREATE TYPE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE TYPE BODY'; p_command_type := 81;

elsif v_words_1_to_2 = 'CREATE TYPE' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE TYPE'; p_command_type := 77;

elsif v_words_1_to_2 = 'CREATE USER' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE USER'; p_command_type := 51;

elsif v_words_1_to_2 = 'CREATE VIEW' then

p_category := C_DDL; p_statement_type := 'CREATE'; p_command_name := 'CREATE VIEW'; p_command_type := 21;

/*

elsif v_words_1_to_3 = 'DECLARE REWRITE EQUIVALENCE' then --Not a real command and could interfere with PL/SQL parsing.

p_category := C_DDL; p_statement_type := 'DECLARE'; p_command_name := 'DECLARE REWRITE EQUIVALENCE'; p_command_type := 209;

*/

elsif v_words_1 = 'DELETE' then

p_category := C_DML; p_statement_type := 'DELETE'; p_command_name := 'DELETE'; p_command_type := 7;

elsif v_words_1_to_2 = 'DISASSOCIATE STATISTICS' then

p_category := C_DDL; p_statement_type := 'DISASSOCIATE STATISTICS'; p_command_name := 'DISASSOCIATE STATISTICS'; p_command_type := 169;

elsif v_words_1_to_3 = 'DROP ANALYTIC VIEW' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP ANALYTIC VIEW'; p_command_type := 251;

elsif v_words_1_to_2 = 'DROP ASSEMBLY' then --I don't think this is a real command.

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP ASSEMBLY'; p_command_type := 215;

elsif v_words_1_to_3 = 'DROP ATTRIBUTE DIMENSION' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP ATTRIBUTE DIMENSION'; p_command_type := 245;

elsif v_words_1_to_3 = 'DROP AUDIT POLICY' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP AUDIT POLICY'; p_command_type := 231;

--This is not a real command.

--elsif v_words_1_to_2 = 'DROP BITMAPFILE' then --I don't think this is a real command.

-- p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP BITMAPFILE'; p_command_type := 89;

elsif v_words_1_to_2 = 'DROP CLUSTER' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP CLUSTER'; p_command_type := 8;

elsif v_words_1_to_2 = 'DROP CONTEXT' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP CONTEXT'; p_command_type := 178;

elsif v_words_1_to_3 = 'DROP DATABASE LINK' then --Moved above "DROP DATABASE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP DATABASE LINK'; p_command_type := 33;

elsif v_words_1_to_2 = 'DROP DATABASE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP DATABASE'; p_command_type := 203;

elsif v_words_1_to_2 = 'DROP DIMENSION' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP DIMENSION'; p_command_type := 176;

elsif v_words_1_to_2 = 'DROP DIRECTORY' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP DIRECTORY'; p_command_type := 158;

elsif v_words_1_to_2 = 'DROP DISKGROUP' then --Command has an extra space in the name, it should be "DISKGROUP".

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP DISK GROUP'; p_command_type := 195;

elsif v_words_1_to_2 = 'DROP EDITION' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP EDITION'; p_command_type := 214;

elsif v_words_1_to_3 = 'DROP FLASHBACK ARCHIVE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP FLASHBACK ARCHIVE'; p_command_type := 220;

elsif v_words_1_to_2 = 'DROP FUNCTION' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP FUNCTION'; p_command_type := 93;

elsif v_words_1_to_2 = 'DROP HIERARCHY' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP HIERARCHY'; p_command_type := 248;

elsif v_words_1_to_2 = 'DROP INDEX' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP INDEX'; p_command_type := 10;

elsif v_words_1_to_2 = 'DROP INDEXTYPE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP INDEXTYPE'; p_command_type := 165;

elsif v_words_1_to_4 = 'DROP INMEMORY JOIN GROUP' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP INMEMORY JOIN GROUP'; p_command_type := 255;

elsif v_words_1_to_2 = 'DROP JAVA' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP JAVA'; p_command_type := 162;

elsif v_words_1_to_2 = 'DROP LIBRARY' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP LIBRARY'; p_command_type := 84;

elsif v_words_1_to_3 = 'DROP LOCKDOWN PROFILE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP LOCKDOWN PROFILE'; p_command_type := 235;

elsif v_words_1_to_4 = 'DROP MATERIALIZED VIEW LOG' or v_words_1_to_3 = 'DROP SNAPSHOT LOG' then --Command has an extra space in the name. Moved above "DROP MATERIALIZED VIEW" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP MATERIALIZED VIEW LOG'; p_command_type := 73;

elsif v_words_1_to_3 = 'DROP MATERIALIZED VIEW' or v_words_1_to_2 = 'DROP SNAPSHOT' then --Command has an extra space at the end.

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP MATERIALIZED VIEW '; p_command_type := 76;

elsif v_words_1_to_3 = 'DROP MATERIALIZED ZONEMAP' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP MATERIALIZED ZONEMAP'; p_command_type := 241;

elsif v_words_1_to_2 = 'DROP OPERATOR' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP OPERATOR'; p_command_type := 167;

elsif v_words_1_to_2 = 'DROP OUTLINE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP OUTLINE'; p_command_type := 181;

elsif v_words_1_to_3 = 'DROP PACKAGE BODY' then --Moved above "DROP PACKAGE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP PACKAGE BODY'; p_command_type := 99;

elsif v_words_1_to_2 = 'DROP PACKAGE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP PACKAGE'; p_command_type := 96;

elsif v_words_1_to_3 = 'DROP PLUGGABLE DATABASE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP PLUGGABLE DATABASE'; p_command_type := 228;

elsif v_words_1_to_2 = 'DROP PROCEDURE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP PROCEDURE'; p_command_type := 68;

elsif v_words_1_to_2 = 'DROP PROFILE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP PROFILE'; p_command_type := 66;

elsif v_words_1_to_3 = 'DROP RESTORE POINT' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP RESTORE POINT'; p_command_type := 207;

--This is not a real command.

--elsif v_words_1_to_3 = 'DROP REWRITE EQUIVALENCE' then

-- p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP REWRITE EQUIVALENCE'; p_command_type := 211;

elsif v_words_1_to_2 = 'DROP ROLE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP ROLE'; p_command_type := 54;

elsif v_words_1_to_3 = 'DROP ROLLBACK SEGMENT' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP ROLLBACK SEGMENT'; p_command_type := 38;

elsif v_words_1_to_3 = 'DROP SCHEMA SYNONYM' then --Undocumented feature

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP SCHEMA SYNONYM'; p_command_type := 224;

elsif v_words_1_to_2 = 'DROP SEQUENCE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP SEQUENCE'; p_command_type := 16;

--An old version of "DROP SNAPSHOT"? This is not supported in 11gR2+.

--elsif v_words_1_to_2 = 'DROP SUMMARY' then --I think this is an old version of "SNAPSHOT".

-- p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP SUMMARY'; p_command_type := 173;

elsif v_words_1_to_2 = 'DROP SYNONYM' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP SYNONYM'; p_command_type := 20;

elsif v_words_1_to_2 = 'DROP TABLE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP TABLE'; p_command_type := 12;

elsif v_words_1_to_3 = 'DROP TABLESPACE SET' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP TABLESPACE SET'; p_command_type := -203;

elsif v_words_1_to_2 = 'DROP TABLESPACE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP TABLESPACE'; p_command_type := 41;

elsif v_words_1_to_2 = 'DROP TRIGGER' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP TRIGGER'; p_command_type := 61;

elsif v_words_1_to_3 = 'DROP TYPE BODY' then --Moved above "DROP TYPE" to capture more specific case first.

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP TYPE BODY'; p_command_type := 83;

elsif v_words_1_to_2 = 'DROP TYPE' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP TYPE'; p_command_type := 78;

elsif v_words_1_to_2 = 'DROP USER' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP USER'; p_command_type := 53;

elsif v_words_1_to_2 = 'DROP VIEW' then

p_category := C_DDL; p_statement_type := 'DROP'; p_command_name := 'DROP VIEW'; p_command_type := 22;

/*

elsif v_words_1_to_4 = 'Do not use 184' then

p_category := C_DDL; p_statement_type := 'Do'; p_command_name := 'Do not use 184'; p_command_type := 184;

elsif v_words_1_to_4 = 'Do not use 185' then

p_category := C_DDL; p_statement_type := 'Do'; p_command_name := 'Do not use 185'; p_command_type := 185;

elsif v_words_1_to_4 = 'Do not use 186' then

p_category := C_DDL; p_statement_type := 'Do'; p_command_name := 'Do not use 186'; p_command_type := 186;

*/

--EXPLAIN is odd. The statement type is more specific than the command name.

--And EXPLAIN PLAN and EXPLAIN WORK share the same command name and ID.

--That doesn't really make sense but it probably doesn't matter - EXPLAIN WORK can

--only be run as SYSASM and will probably never be used in real life.

elsif v_words_1_to_2 = 'EXPLAIN PLAN' then --Statement type is more specific than command name.

p_category := C_DML; p_statement_type := 'EXPLAIN PLAN'; p_command_name := 'EXPLAIN'; p_command_type := 50;

elsif v_words_1_to_2 = 'EXPLAIN WORK' then --Statement type is more specific than command name.

p_category := C_DML; p_statement_type := 'EXPLAIN WORK'; p_command_name := 'EXPLAIN'; p_command_type := 50;

elsif v_words_1_to_2 = 'FLASHBACK DATABASE' or v_words_1_to_3 = 'FLASHBACK PLUGGABLE DATABASE' then

p_category := C_DDL; p_statement_type := 'FLASHBACK'; p_command_name := 'FLASHBACK DATABASE'; p_command_type := 204;

elsif v_words_1_to_2 = 'FLASHBACK TABLE' then

p_category := C_DDL; p_statement_type := 'FLASHBACK'; p_command_name := 'FLASHBACK TABLE'; p_command_type := 205;

elsif v_words_1 = 'GRANT' then --Command name has an extra "OBJECT".

p_category := C_DDL; p_statement_type := 'GRANT'; p_command_name := 'GRANT OBJECT'; p_command_type := 17;

elsif v_words_1 = 'INSERT' then

p_category := C_DML; p_statement_type := 'INSERT'; p_command_name := 'INSERT'; p_command_type := 2;

elsif v_words_1_to_2 = 'LOCK TABLE' then

p_category := C_DML; p_statement_type := 'LOCK TABLE'; p_command_name := 'LOCK TABLE'; p_command_type := 26;

--This is not a real command.

--elsif v_words_1 = 'NO-OP' then

-- p_category := C_DDL; p_statement_type := 'NO-OP'; p_command_name := 'NO-OP'; p_command_type := 27;

elsif v_words_1 = 'NOAUDIT' then --Command name is more specific than statement type.

p_category := C_DDL; p_statement_type := 'NOAUDIT'; p_command_name := 'NOAUDIT OBJECT'; p_command_type := 31;

elsif (v_types(1) = '<<') --PL/SQL is custom.

or v_types(1)='word' and v_values(1) in ('DECLARE', 'BEGIN') then

p_category := C_PLSQL; p_statement_type := 'BLOCK'; p_command_name := 'PL/SQL EXECUTE'; p_command_type := 47;

elsif v_words_1_to_2 = 'PURGE DBA_RECYCLEBIN' then --Command name has space instead of underscore.

p_category := C_DDL; p_statement_type := 'PURGE'; p_command_name := 'PURGE DBA RECYCLEBIN'; p_command_type := 198;

elsif v_words_1_to_2 = 'PURGE INDEX' then

p_category := C_DDL; p_statement_type := 'PURGE'; p_command_name := 'PURGE INDEX'; p_command_type := 201;

elsif v_words_1_to_2 = 'PURGE TABLE' then

p_category := C_DDL; p_statement_type := 'PURGE'; p_command_name := 'PURGE TABLE'; p_command_type := 200;

elsif v_words_1_to_3 = 'PURGE TABLESPACE SET' then

p_category := C_DDL; p_statement_type := 'PURGE'; p_command_name := 'PURGE TABLESPACE SET'; p_command_type := -204;

elsif v_words_1_to_2 = 'PURGE TABLESPACE' then

p_category := C_DDL; p_statement_type := 'PURGE'; p_command_name := 'PURGE TABLESPACE'; p_command_type := 199;

elsif v_words_1_to_2 = 'PURGE RECYCLEBIN' then --Command name has extra "USER".

p_category := C_DDL; p_statement_type := 'PURGE'; p_command_name := 'PURGE USER RECYCLEBIN'; p_command_type := 197;

elsif v_words_1 = 'RENAME' then

p_category := C_DDL; p_statement_type := 'RENAME'; p_command_name := 'RENAME'; p_command_type := 28;

elsif v_words_1 = 'REVOKE' then --Command has extra "OBJECT".

p_category := C_DDL; p_statement_type := 'REVOKE'; p_command_name := 'REVOKE OBJECT'; p_command_type := 18;

elsif v_words_1 = 'ROLLBACK' then

p_category := C_Transaction_Control; p_statement_type := 'ROLLBACK'; p_command_name := 'ROLLBACK'; p_command_type := 45;

elsif v_words_1 = 'SAVEPOINT' then

p_category := C_Transaction_Control; p_statement_type := 'SAVEPOINT'; p_command_name := 'SAVEPOINT'; p_command_type := 46;

elsif v_types(1) = '(' or v_words_1 in ('SELECT', 'WITH') then --SELECT is custom.

p_category := C_DML; p_statement_type := 'SELECT'; p_command_name := 'SELECT'; p_command_type := 3;

elsif v_words_1_to_2 in ('SET CONSTRAINTS', 'SET CONSTRAINT') then --Custom, there are two forms.

p_category := C_Transaction_Control; p_statement_type := 'SET CONSTRAINT'; p_command_name := 'SET CONSTRAINTS'; p_command_type := 90;

elsif v_words_1_to_2 = 'SET ROLE' then

p_category := C_Session_Control; p_statement_type := 'SET ROLE'; p_command_name := 'SET ROLE'; p_command_type := 55;

elsif v_words_1_to_2 = 'SET TRANSACTION' then

p_category := C_Transaction_Control; p_statement_type := 'SET TRANSACTION'; p_command_name := 'SET TRANSACTION'; p_command_type := 48;

elsif v_words_1_to_2 = 'TRUNCATE CLUSTER' then

p_category := C_DDL; p_statement_type := 'TRUNCATE'; p_command_name := 'TRUNCATE CLUSTER'; p_command_type := 86;

elsif v_words_1_to_2 = 'TRUNCATE TABLE' then

p_category := C_DDL; p_statement_type := 'TRUNCATE'; p_command_name := 'TRUNCATE TABLE'; p_command_type := 85;

--This is not a real command.

--elsif v_words_1_to_2 = 'UNDROP OBJECT' then

-- p_category := C_DDL; p_statement_type := 'UNDROP'; p_command_name := 'UNDROP OBJECT'; p_command_type := 202;

elsif v_words_1 = 'UPDATE' then

p_category := C_DML; p_statement_type := 'UPDATE'; p_command_name := 'UPDATE'; p_command_type := 6;

--These are not real commands (they are part of alter table) and they could be ambiguous with an UPDATE statement

--if there was a table named "INDEXES" or "JOIN".

--elsif v_words_1_to_2 = 'UPDATE INDEXES' then

-- p_category := C_DDL; p_statement_type := 'UPDATE'; p_command_name := 'UPDATE INDEXES'; p_command_type := 182;

--elsif v_words_1_to_3 = 'UPDATE JOIN INDEX' then --Not a real command as far as I can tell.

-- p_category := C_DDL; p_statement_type := 'UPDATE'; p_command_name := 'UPDATE JOIN INDEX'; p_command_type := 191;

elsif v_words_1 = 'MERGE' then --Command name is different than real name.

p_category := C_DML; p_statement_type := 'MERGE'; p_command_name := 'UPSERT'; p_command_type := 189;

--Not a real command, this is part of ANALYZE.

--elsif v_words_1_to_2 = 'VALIDATE INDEX' then

-- p_category := C_DDL; p_statement_type := 'VALIDATE'; p_command_name := 'VALIDATE INDEX'; p_command_type := 23;

--Invalid.

else

p_category := C_Invalid; p_statement_type := C_Invalid; p_command_name := C_Invalid; p_command_type := -1;

end if;

end classify;

--------------------------------------------------------------------------------

/*

Purpose: Convenient variations of classify that only return one variable.

*/

function get_only_one_variable(p_source in clob, p_return_type in varchar2) return varchar2 is

v_category varchar2(32767);

v_statement_type varchar2(32767);

v_command_name varchar2(32767);

v_command_type varchar2(32767);

v_lex_sqlcode number;

v_lex_sqlerrm varchar2(32767);

begin

classify(

p_tokens => plsql_lexer.lex(p_source),

p_category => v_category,

p_statement_type => v_statement_type,

p_command_name => v_command_name,

p_command_type => v_command_type,

p_lex_sqlcode => v_lex_sqlcode,

p_lex_sqlerrm => v_lex_sqlerrm

);

if p_return_type = 'CATEGORY' then

return v_category;

elsif p_return_type = 'STATEMENT_TYPE' then

return v_statement_type;

elsif p_return_type = 'COMMAND_NAME' then

return v_command_name;

elsif p_return_type = 'COMMAND_TYPE' then

return to_char(v_command_type);

else

raise_application_error(-20000, 'Unexpected return type.');

end if;

end;

function get_category (p_source in clob) return varchar2 is begin return get_only_one_variable(p_source, 'CATEGORY' ); end;

function get_statement_type(p_source in clob) return varchar2 is begin return get_only_one_variable(p_source, 'STATEMENT_TYPE' ); end;

function get_command_name (p_source in clob) return varchar2 is begin return get_only_one_variable(p_source, 'COMMAND_NAME' ); end;

function get_command_type (p_source in clob) return varchar2 is begin return to_char(get_only_one_variable(p_source, 'COMMAND_TYPE' )); end;

--------------------------------------------------------------------------------

/*

Purpose: Detect PLSQL_DECLARATION, a new 12c feature that allows PL/SQL in SQL.

Description:

A PL/SQL Declaration must have this pattern before the first ";":

(null or not "START") "WITH" ("FUNCTION"|"PROCEDURE") (neither "(" nor "AS")

This was discovered by analyzing all "with" strings in the Oracle documentation

text descriptions. That is, download the library and run a command like this:

C:\E50529_01\SQLRF\img_text> findstr /s /i "with" *.*

There are a lot of potential ambiguities as SQL does not have many fully

reserved words. And the pattern "with" "function" can be found in 2 cases:the following:

1. Hierarchical queries. Exclude them by looking for "start" before "with".

select *

from

(

select 1 function from dual

)

connect by function = 1

start with function = 1;

Note: "start" cannot be the name of a table, no need to worry about DML

statements like `insert into start with ...`.

2. Subquery factoring that uses "function" as a name. Stupid, but possible.

with function as (select 1 a from dual) select * from function;

with function(a) as (select 1 a from dual) select * from function;

*/

function has_plsql_declaration(p_tokens token_table, p_token_start_index in number default 1) return boolean is

v_previous_concrete_token_1 token := token(null, null, null, null, null, null, null, null);

v_previous_concrete_token_2 token := token(null, null, null, null, null, null, null, null);

v_previous_concrete_token_3 token := token(null, null, null, null, null, null, null, null);

begin

--Return false if there are no tokens.

if p_tokens is null or p_tokens.count = 0 then

return false;

--Else loop through tokens.

else

for i in p_token_start_index .. p_tokens.count loop

--Return true if PL/SQL Declaration found.

if

--For performance, check types first, instead of potentially large values.

(

p_tokens(i).type = plsql_lexer.c_word and

v_previous_concrete_token_1.type = plsql_lexer.c_word and

v_previous_concrete_token_2.type = plsql_lexer.c_word and

(v_previous_concrete_token_3.type = plsql_lexer.c_word or v_previous_concrete_token_3.type is null)

)

and

(

lower(p_tokens(i).value) <> 'as' and

lower(v_previous_concrete_token_1.value) in ('function', 'procedure') and

lower(v_previous_concrete_token_2.value) = 'with' and

(lower(v_previous_concrete_token_3.value) <> 'start' or v_previous_concrete_token_3.value is null)

) then

return true;

--Return false if ';' is found.

elsif p_tokens(i).type = ';' then

return false;

--Shift tokens if it is not a whitespace, comment, or EOF.

elsif p_tokens(i).type not in (plsql_lexer.c_whitespace, plsql_lexer.c_comment, plsql_lexer.c_eof) then

v_previous_concrete_token_3 := v_previous_concrete_token_2;

v_previous_concrete_token_2 := v_previous_concrete_token_1;

v_previous_concrete_token_1 := p_tokens(i);

end if;

end loop;

end if;

--Return false is nothing found.

return false;

end has_plsql_declaration;

--------------------------------------------------------------------------------

/*

Purpose: Get the trigger type and the token index for the beginning of the trigger_body.

The trigger_body token index helps identify when to start counting BEGINs and ENDs.

Before that point, it's easier to exclude than to include because this is the only

PL/SQL BEGIN that can start after many different keywords.

For lexing and parsing there are 3 important different types of triggers:

regular triggers, compound triggers, and CALL triggers.

Trigger type is determined by which keywords are found first:

1. Regular - DECLARE, <<, or BEGIN (e.g. something that begins a PL/SQL body.)

2. Compound - COMPOUND TRIGGER

3. Call - CALL

The tricky part with 1 and 3 is that DECLARE, BEGIN, or CALL can be used as

names for other objects. Based on the trigger syntax diagrams, the "real"

keywords are found when these conditions are true:

1. It is not found after ('trigger', '.', 'of', ',', 'on', 'as', 'follows', 'precedes', 'table')

2. It is not inside 'when ( condition )'

*/

procedure get_trigger_type_body_index (

p_tokens in token_table,

p_trigger_type out number,

p_trigger_body_start_index out number

) is

v_previous_concrete_token_1 token := token(null, null, null, null, null, null, null, null);

v_previous_concrete_token_2 token := token(null, null, null, null, null, null, null, null);

v_when_condition_paren_counter number := 0;

begin

--Loop through all the tokens until a type is found.

for i in 1 .. p_tokens.count loop

--Check for new WHEN ( condition ).

if

(

v_when_condition_paren_counter = 0

and

p_tokens(i).type = '('

and

lower(v_previous_concrete_token_1.value) = 'when'

) then

v_when_condition_paren_counter := 1;

--Only count parenthese if inside WHEN (condition).

elsif v_when_condition_paren_counter >= 1 then

if p_tokens(i).type = '(' then

v_when_condition_paren_counter := v_when_condition_paren_counter + 1;

elsif p_tokens(i).type = ')' then

v_when_condition_paren_counter := v_when_condition_paren_counter - 1;

end if;

--Compound Trigger check.

elsif

(