create or replace package statement_terminator is

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

function remove_semicolon(

p_tokens in token_table

) return token_table;

function remove_sqlplus_delimiter(

p_tokens in token_table,

p_sqlplus_delimiter in varchar2 default '/'

) return token_table;

function remove_sqlplus_del_and_semi(

p_tokens in token_table,

p_sqlplus_delimiter in varchar2 default '/'

) return token_table;

/*

== PURPOSE ==

Statement Terminator removes terminating semicolons and SQL*Plus delimiters from

the end of a statement. This is helpful when the statement will be executed as

dynamic SQL.

Dynamic SQL requires some commands must include a terminating semicolon and

other commands must not include a semicolon. For example, "create table ..."

must not have a semicolon, but "create procedure ..." must end with a semicolon.

Only the last semicolon is removed. Just like with SQL*Plus, statements

that end with two semicolons will not work correctly.

Semicolons may not be removed if the statement has a serious parsing error, like

an unclosed comment.

== EXAMPLE ==

select statement_semicolon_remover.remove(plsql_lexer.lex('select * from dual;'))

from dual;

Results:

select * from dual

== PARAMETERS ==

- p_tokens (IN): Tokens for a statement, probably generated by PLSQL_LEXER.

- p_statement (IN): A SQL statement.

- p_sqlplus_delimiter (IN): A SQL*Plus delimiter, usually "/". Remember that

SQL*Plus delimiters must be on a line with only

whitespace.

*/

end;

/

create or replace package body statement_terminator is

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

--Build the statement excluding the last semicolon, if any.

function build_statement_wo_semicolon(p_abstract_tokens token_table)

return token_table is

v_tokens token_table := token_table();

v_semicolon_index number := -1;

v_semicolon_line_number number;

begin

--Find the index of the last semicolon token.

--Only count it if it's the last concrete token.

for i in reverse 1 .. p_abstract_tokens.count loop

--Record index if it's a semicolon.

if p_abstract_tokens(i).type = ';' then

v_semicolon_index := i;

v_semicolon_line_number := p_abstract_tokens(i).line_number;

--Quit the loop if another concrete token is found.

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

exit;

end if;

end loop;

--Put together string, excluding the semicolon.

for i in 1 .. p_abstract_tokens.count loop

--Concatenate two whitespaces if it was after the removed semicolon.

if i = v_semicolon_index + 1 and p_abstract_tokens(i).type = plsql_lexer.c_whitespace then

--Concatenate values.

v_tokens(i-2).value := v_tokens(i-2).value || p_abstract_tokens(i).value;

--Adjust metadata.

v_tokens(i-2).last_char_position := p_abstract_tokens(i).last_char_position - 1;

--Add token if it's not the semicolon.

elsif i <> v_semicolon_index then

v_tokens.extend;

v_tokens(v_tokens.count) := p_abstract_tokens(i);

end if;

end loop;

--Fix metadata by going through all tokens on or after it's old spot.

--LINE_NUMBER - nothing to fix

--COLUMN_NUMBER - Shrink by 1 if after semicolon but on same line.

--FIRST_CHAR_POSITION - Shrink by 1 if after semicolon.

--LAST_CHAR_POSITION - Shrink by 1 if after semicolon

if v_semicolon_index <> -1 then

for i in v_semicolon_index .. v_tokens.count loop

if v_tokens(i).line_number = v_semicolon_line_number then

v_tokens(i).column_number := v_tokens(i).column_number - 1;

end if;

v_tokens(i).first_char_position := v_tokens(i).first_char_position - 1;

v_tokens(i).last_char_position := v_tokens(i).last_char_position - 1;

end loop;

end if;

return v_tokens;

end build_statement_wo_semicolon;

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

--Remove extra semicolons, if any, to prepare for dynamic execution.

function remove_semicolon(p_tokens in token_table) return token_table is

v_command_name varchar2(4000);

v_lex_sqlcode number;

v_lex_sqlerrm varchar2(4000);

v_throwaway varchar2(4000);

begin

--Classify the tokens/statement.

statement_classifier.classify(

p_tokens => p_tokens,

p_category => v_throwaway,

p_statement_type => v_throwaway,

p_command_name => v_command_name,

p_command_type => v_throwaway,

p_lex_sqlcode => v_lex_sqlcode,

p_lex_sqlerrm => v_lex_sqlerrm

);

--Do nothing if there's a serious parsing error:

if v_lex_sqlcode is not null or v_lex_sqlerrm is not null then

return p_tokens;

--Triggers sometimes need semicolons and sometimes do not.

elsif v_command_name = 'CREATE TRIGGER' then

declare

v_not_needed number;

v_trigger_type number;

begin

--Get trigger type.

statement_classifier.get_trigger_type_body_index(p_tokens, p_trigger_type => v_trigger_type, p_trigger_body_start_index => v_not_needed);

--CALL triggers need semicolons removed.

if v_trigger_type = statement_classifier.C_TRIGGER_TYPE_CALL then

return build_statement_wo_semicolon(p_tokens);

--All other triggers need to keep the semicolon.

else

return p_tokens;

end if;

end;

--Remove semicolons from these:

elsif v_command_name in (

'ADMINISTER KEY MANAGEMENT','ALTER ASSEMBLY','ALTER AUDIT POLICY','ALTER CLUSTER','ALTER DATABASE',

'ALTER DATABASE LINK','ALTER DIMENSION','ALTER DISK GROUP','ALTER EDITION','ALTER FLASHBACK ARCHIVE',

'ALTER FUNCTION','ALTER INDEX','ALTER INDEXTYPE','ALTER JAVA','ALTER LIBRARY','ALTER MATERIALIZED VIEW ',

'ALTER MATERIALIZED VIEW LOG','ALTER MATERIALIZED ZONEMAP','ALTER OPERATOR','ALTER OUTLINE',

'ALTER PACKAGE','ALTER PACKAGE BODY','ALTER PLUGGABLE DATABASE','ALTER PROCEDURE','ALTER PROFILE',

'ALTER RESOURCE COST',/*'ALTER REWRITE EQUIVALENCE',*/'ALTER ROLE','ALTER ROLLBACK SEGMENT',

'ALTER SEQUENCE','ALTER SESSION',/*'ALTER SUMMARY',*/'ALTER SYNONYM','ALTER SYSTEM','ALTER TABLE',

'ALTER TABLESPACE','ALTER TRACING','ALTER TRIGGER','ALTER TYPE','ALTER TYPE BODY','ALTER USER',

'ALTER VIEW','ANALYZE CLUSTER','ANALYZE INDEX','ANALYZE TABLE','ASSOCIATE STATISTICS','AUDIT OBJECT',

'CALL METHOD',/*'CHANGE PASSWORD',*/'COMMENT','COMMIT',/*'CREATE ASSEMBLY',*/'CREATE AUDIT POLICY',

/*'CREATE BITMAPFILE',*/'CREATE CLUSTER','CREATE CONTEXT','CREATE CONTROL FILE','CREATE DATABASE',

'CREATE DATABASE LINK','CREATE DIMENSION','CREATE DIRECTORY','CREATE DISK GROUP','CREATE EDITION',

'CREATE FLASHBACK ARCHIVE',/*'CREATE FUNCTION',*/'CREATE INDEX','CREATE INDEXTYPE',/*'CREATE JAVA',*/

/*'CREATE LIBRARY',*/'CREATE MATERIALIZED VIEW ','CREATE MATERIALIZED VIEW LOG','CREATE MATERIALIZED ZONEMAP',

'CREATE OPERATOR','CREATE OUTLINE',/*'CREATE PACKAGE',*//*'CREATE PACKAGE BODY',*/'CREATE PFILE',

'CREATE PLUGGABLE DATABASE',/*'CREATE PROCEDURE',*/'CREATE PROFILE','CREATE RESTORE POINT','CREATE ROLE',

'CREATE ROLLBACK SEGMENT','CREATE SCHEMA','CREATE SCHEMA SYNONYM','CREATE SEQUENCE','CREATE SPFILE',

/*'CREATE SUMMARY',*/'CREATE SYNONYM','CREATE TABLE','CREATE TABLESPACE',/*'CREATE TRIGGER',*//*'CREATE TYPE',*/

/*'CREATE TYPE BODY',*/'CREATE USER','CREATE VIEW',/*'DECLARE REWRITE EQUIVALENCE',*/'DELETE',

'DISASSOCIATE STATISTICS','DROP ASSEMBLY','DROP AUDIT POLICY',/*'DROP BITMAPFILE',*/'DROP CLUSTER',

'DROP CONTEXT','DROP DATABASE','DROP DATABASE LINK','DROP DIMENSION','DROP DIRECTORY','DROP DISK GROUP',

'DROP EDITION','DROP FLASHBACK ARCHIVE','DROP FUNCTION','DROP INDEX','DROP INDEXTYPE','DROP JAVA',

'DROP LIBRARY','DROP MATERIALIZED VIEW ','DROP MATERIALIZED VIEW LOG','DROP MATERIALIZED ZONEMAP',

'DROP OPERATOR','DROP OUTLINE','DROP PACKAGE','DROP PACKAGE BODY','DROP PLUGGABLE DATABASE',

'DROP PROCEDURE','DROP PROFILE','DROP RESTORE POINT',/*'DROP REWRITE EQUIVALENCE',*/'DROP ROLE',

'DROP ROLLBACK SEGMENT','DROP SCHEMA SYNONYM','DROP SEQUENCE',/*'DROP SUMMARY',*/'DROP SYNONYM',

'DROP TABLE','DROP TABLESPACE','DROP TRIGGER','DROP TYPE','DROP TYPE BODY','DROP USER','DROP VIEW',

/*'Do not use 184',*//*'Do not use 185',*//*'Do not use 186',*/'EXPLAIN','FLASHBACK DATABASE',

'FLASHBACK TABLE','GRANT OBJECT','INSERT','LOCK TABLE',/*'NO-OP',*/'NOAUDIT OBJECT',/*'PL/SQL EXECUTE',*/

'PURGE DBA RECYCLEBIN','PURGE INDEX','PURGE TABLE','PURGE TABLESPACE','PURGE USER RECYCLEBIN','RENAME',

'REVOKE OBJECT','ROLLBACK','SAVEPOINT','SELECT','SET CONSTRAINTS','SET ROLE','SET TRANSACTION',

'TRUNCATE CLUSTER','TRUNCATE TABLE',/*'UNDROP OBJECT',*/'UPDATE',/*'UPDATE INDEXES',*/

/*'UPDATE JOIN INDEX',*/'UPSERT'/*'VALIDATE INDEX',*/

) then

return build_statement_wo_semicolon(p_tokens);

--Do nothing for these, they can end with a semicolon:

elsif v_command_name in (

'CREATE ASSEMBLY',

'CREATE FUNCTION',

'CREATE JAVA',

'CREATE LIBRARY',

'CREATE PACKAGE',

'CREATE PACKAGE BODY',

'CREATE PROCEDURE',

'CREATE TYPE',

'CREATE TYPE BODY',

'PL/SQL EXECUTE',

'Invalid',

'Nothing'

) then

return p_tokens;

else

raise_application_error(-20000, 'Cannot determine if statement needs a semicolon.'||

' The command name "'||v_command_name||'" is not recognized.');

end if;

end remove_semicolon;

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

function remove_sqlplus_delimiter(

p_tokens in token_table,

p_sqlplus_delimiter in varchar2 default '/'

) return token_table

is

v_delimiter_begin_index number;

v_delimiter_end_index number;

v_2_token_before_delimiter token;

v_1_token_before_delimiter token;

v_potential_delimiter clob;

v_1_token_after_delimiter token;

v_2_token_after_delimiter token;

v_tokens_without_delimiter token_table := token_table();

begin

--Special cases.

--

--Throw an error if the delimiter is null.

if p_sqlplus_delimiter is null then

raise_application_error(-20000, 'The SQL*Plus delimiter cannot be NULL.');

end if;

--Throw an error if the delimiter contains whitespace.

for i in 1 .. lengthc(p_sqlplus_delimiter) loop

if plsql_lexer.is_lexical_whitespace(substrc(p_sqlplus_delimiter, i, 1)) then

raise_application_error(-20001, 'The SQL*Plus delimiter cannot contain whitespace.');

end if;

end loop;

--Return an empty string if the string is NULL.

if p_tokens is null then

return null;

end if;

--Gather tokens before and after, and delimiter.

--

--Loop through all tokens in reverse order.

for token_index in reverse 1 .. p_tokens.count loop

--Look for the last non-whitespace/comment/EOF

if p_tokens(token_index).type not in (plsql_lexer.c_whitespace, plsql_lexer.c_comment, plsql_lexer.c_eof) then

v_delimiter_end_index := token_index;

--Get tokens after delimiter.

v_1_token_after_delimiter := p_tokens(token_index + 1);

if token_index + 2 <= p_tokens.count then

v_2_token_after_delimiter := p_tokens(token_index + 2);

end if;

--Get potential delimiter - go until whitespace or comment found.

for delimiter_index in reverse 1 .. token_index loop

--Build delimiter.

if p_tokens(delimiter_index).type not in (plsql_lexer.c_whitespace, plsql_lexer.c_comment) then

v_delimiter_begin_index := delimiter_index;

v_potential_delimiter := p_tokens(delimiter_index).value || v_potential_delimiter;

--If something else found, get tokens before delimiter and quit.

else

v_delimiter_begin_index := delimiter_index + 1;

v_1_token_before_delimiter := p_tokens(delimiter_index);

if delimiter_index - 1 >= 1 then

v_2_token_before_delimiter := p_tokens(delimiter_index - 1);

end if;

exit;

end if;

end loop;

--Quit outer loop.

exit;

end if;

end loop;

--Return the original statement if these conditions do not match:

if

--Delimiters must match.

(p_sqlplus_delimiter = v_potential_delimiter) and

--Before the delimiter, if anything, must be whitespace.

(v_1_token_before_delimiter is null or v_1_token_before_delimiter.type = plsql_lexer.c_whitespace) and

--If there are two tokens before then the first token before must have a newline.

(v_2_token_before_delimiter is null or instr(v_1_token_before_delimiter.value, chr(10)) >= 1) and

--After the delimiter, if anything, must be whitespace or EOF.

(v_1_token_after_delimiter is null or v_1_token_after_delimiter.type in (plsql_lexer.c_whitespace, plsql_lexer.c_eof)) and

--There is only one token after, or the second token after is EOF, or the first token after has a newline.

(v_2_token_after_delimiter is null or v_2_token_after_delimiter.type = plsql_lexer.c_eof

or

(

instr(v_1_token_after_delimiter.value, chr(10)) >= 1

)

)

then

null;

else

return p_tokens;

end if;

--Put the string back together without the delimiter.

if v_delimiter_begin_index is null then

v_tokens_without_delimiter := p_tokens;

else

for i in 1 .. p_tokens.count loop

if i not between v_delimiter_begin_index and v_delimiter_end_index then

v_tokens_without_delimiter.extend;

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

end if;

end loop;

end if;

return v_tokens_without_delimiter;

end remove_sqlplus_delimiter;

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

function remove_sqlplus_del_and_semi(

p_tokens in token_table,

p_sqlplus_delimiter in varchar2 default '/'

) return token_table

is

begin

return remove_semicolon(remove_sqlplus_delimiter(p_tokens, p_sqlplus_delimiter));

end remove_sqlplus_del_and_semi;

end;

/