create or replace package body plsql_parser is

--See PLSQL_PARSER.SPC for documentation on this package.

-- _____ ____ _ _ ____ _______ _ _ _____ ______ __ ________ _______

-- | __ \ / __ \ | \ | |/ __ \__ __| | | | |/ ____| ____| \ \ / / ____|__ __|

-- | | | | | | | | \| | | | | | | | | | | (___ | |__ \ \_/ /| |__ | |

-- | | | | | | | | . ` | | | | | | | | | |\___ \| __| \ / | __| | |

-- | |__| | |__| | | |\ | |__| | | | | |__| |____) | |____ | | | |____ | |

-- |_____/ \____/ |_| \_|\____/ |_| \____/|_____/|______| |_| |______| |_|

--

--This package is experimental and does not work yet.

type number_table is table of number;

type string_table is table of varchar2(32767);

g_nodes node_table := node_table();

g_ast_tokens token_table; --AST = abstract syntax tree.

g_ast_token_index number;

g_optional boolean; --Holds return value of optional functions.

g_parse_tree_tokens token_table;

g_map_between_parse_and_ast number_table := number_table();

g_reserved_words string_table;

type parse_context is record

(

new_node_id number,

ast_token_index_before number

);

--Temporary constants for ambiguous intermediate nodes that must be resolved later.

--

--One of: cluster,column,function,materialized view,operator,package,procedure,pseudocolumn,query,schema,table,type,view (synonyms are resolved)

--Used in expressions.

C_AMBIG_CCFMOPPPQSTTV constant varchar2(100) := 'C_AMBIG_ccfmopppqsttv';

--One of: Cluster, materialized view, table, table_alias, view (synonyms are resolved)

--Used in for_update_clause_item.

C_AMBIG_c_mv_t_ta_v constant varchar2(100) := 'C_AMBIG_c_mv_t_ta_v';

--One of: cluster,materialized view,query_name,table,view (synonyms are resolved)

--Used in query_table_expression

C_AMBIG_CMQTV constant varchar2(100) := 'C_AMBIG_cmqtv';

--One of: cluster,materialized view,table,view (synonyms are resolved)

--Used in query_table_expression

C_AMBIG_CMTV constant varchar2(100) := 'C_AMBIG_cmtv';

C_AMBIG_expression constant varchar2(100) := 'C_AMBIG_expression';

C_AMBIG_func_agg_or_analytic constant varchar2(100) := 'C_AMBIG_func_agg_or_analytic';

--One of : query_name, cluster, table, view, materialized view, alias (synonyms are resolved)

--These are things in select_list that can have a ".*"

C_AMBIG_qn_c_t_v_mv_alias constant varchar2(100) := 'C_AMBIG_qn_t_v_mv_alias';

C_AMBIG_rowcount_or_percent constant varchar2(100) := 'C_AMBIG_rowcount_or_percent';

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

--Helper functions

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

--Puprose: Create a new node and return the node ID.

function push(p_node_type in varchar2, p_parent_id in number) return parse_context is

v_parse_context parse_context;

begin

g_nodes.extend;

g_nodes(g_nodes.count) := node(id => g_nodes.count, type => p_node_type, parent_id => p_parent_id, lexer_token => g_ast_tokens(g_ast_token_index), child_ids => null);

v_parse_context.new_node_id := g_nodes.count;

v_parse_context.ast_token_index_before := g_ast_token_index;

return v_parse_context;

exception

when subscript_beyond_count then

v_parse_context.new_node_id := null;

v_parse_context.ast_token_index_before := g_ast_token_index;

return v_parse_context;

end push;

function pop(p_parse_context parse_context) return boolean is

begin

for i in 1 .. g_nodes.count - (nvl(p_parse_context.new_node_id, g_nodes.count) - 1) loop

g_nodes.trim;

end loop;

g_ast_token_index := p_parse_context.ast_token_index_before;

return false;

end pop;

function current_value return clob is begin

begin

return upper(g_ast_tokens(g_ast_token_index).value);

exception when subscript_beyond_count then

return null;

end;

end current_value;

function current_type return varchar2 is begin

begin

return g_ast_tokens(g_ast_token_index).type;

exception when subscript_beyond_count then

return null;

end;

end current_type;

procedure disambig_agg_or_analytic(p_node_type varchar2, p_node_id number) is

begin

g_nodes(p_node_id).type := p_node_type;

end disambig_agg_or_analytic;

procedure increment(p_increment number default 1) is begin

g_ast_token_index := g_ast_token_index + p_increment;

end increment;

--Compound expressions and conditions are left-recursive and are found after the parsing.

--To fix this, add 2 nodes in the middle of the tree and shift others down.

--That is, convert:

-- expr|cond

-- simple_expression|condition

-- ...

--to:

-- expr|cond (OLD)

-- compound_expression|condition (NEW)

-- expr|condition (NEW)

-- simple_expression|condition (OLD)

-- ...

--

--Return the compound_* node, since that will be a new parent.

function insert_compound_expr_or_cond(p_node_id number, p_compound_name varchar2, p_name varchar2) return number is

begin

g_nodes.extend;

g_nodes.extend;

--Shift nodes down, increase parent_id by 2.

for i in reverse p_node_id + 1 .. g_nodes.count loop

g_nodes(i) := node(

id => g_nodes(i-2).id + 2,

type => g_nodes(i-2).type,

parent_id => g_nodes(i-2).parent_id + 2,

lexer_token => g_nodes(i-2).lexer_token,

child_ids => g_nodes(i-2).child_ids

);

end loop;

--Create new compound_expression|condition and expr|cond nodes.

g_nodes(p_node_id + 1) := node(

id => p_node_id + 1,

type => p_compound_name,

parent_id => p_node_id,

lexer_token => g_nodes(p_node_id + 2).lexer_token,

child_ids => g_nodes(p_node_id + 2).child_ids

);

g_nodes(p_node_id + 2) := node(

id => p_node_id + 2,

type => p_name,

parent_id => p_node_id + 1,

lexer_token => g_nodes(p_node_id + 3).lexer_token,

child_ids => g_nodes(p_node_id + 3).child_ids

);

return p_node_id + 1;

end insert_compound_expr_or_cond;

function match_terminal(p_value varchar2, p_parent_id in number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(p_value, p_parent_id);

if current_value = p_value then

increment;

return true;

else

return pop(v_parse_context);

end if;

end match_terminal;

function match_terminal_or_list(p_values string_table, p_parent_id in number) return boolean is

begin

for i in 1 .. p_values.count loop

if match_terminal(p_values(i), p_parent_id) then

return true;

end if;

end loop;

return false;

end match_terminal_or_list;

function next_value(p_increment number default 1) return clob is begin

begin

return upper(g_ast_tokens(g_ast_token_index+p_increment).value);

exception when subscript_beyond_count then

return null;

end;

end next_value;

function next_type(p_increment number default 1) return varchar2 is begin

begin

return g_ast_tokens(g_ast_token_index+p_increment).type;

exception when subscript_beyond_count then

return null;

end;

end next_type;

function previous_value(p_decrement number) return clob is begin

begin

if g_ast_token_index - p_decrement <= 0 then

return null;

else

return upper(g_ast_tokens(g_ast_token_index - p_decrement).value);

end if;

exception when subscript_beyond_count then

null;

end;

end previous_value;

--Purpose: Determine which reserved words are truly reserved.

--V$RESERVED_WORD.RESERVED is not reliable so we must use dynamic SQL and catch

--errors to build a list of reserved words.

function get_reserved_words return string_table is

v_dummy varchar2(1);

v_reserved_words string_table := string_table();

v_potential_reserved_words string_table;

c_base_reserved_words constant string_table := string_table(

'!','!=','$','&','(',')','*','+',',','-','.','/',':',';','<','<<','<=','=','=>',

'>','>=','?','@','ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT',

'BETWEEN','BY','CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT',

'CREATE','CURRENT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP',

'ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP','HAVING',

'IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL','INSERT','INTEGER',

'INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG','MAXEXTENTS','MINUS',

'MLSLABEL','MODE','MODIFY','NOAUDIT','NOCOMPRESS','NOT','NOWAIT','NULL','NUMBER',

'OF','OFFLINE','ON','ONLINE','OPTION','OR','ORDER','PCTFREE','PRIOR','PUBLIC',

'RAW','RENAME','RESOURCE','REVOKE','ROW','ROWID','ROWNUM','ROWS','SELECT',

'SESSION','SET','SHARE','SIZE','SMALLINT','START','SUCCESSFUL','SYNONYM',

'SYSDATE','TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER',

'VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH','[',

']','^','{','|','}');

begin

--Use pre-generated list for specific versions.

if dbms_db_version.version||'.'||dbms_db_version.release = '12.1' then

v_reserved_words := c_base_reserved_words;

elsif dbms_db_version.version||'.'||dbms_db_version.release = '12.2' then

v_reserved_words := c_base_reserved_words;

v_reserved_words.extend;

v_reserved_words(v_reserved_words.count) := 'HIERARCHIES';

--TODO: Pre-generate for 11.2

--Otherwise dynamically determine list.

else

execute immediate 'select keyword from v$reserved_words order by keyword'

bulk collect into v_potential_reserved_words;

for i in 1 .. v_potential_reserved_words.count loop

begin

execute immediate 'select dummy from dual '||v_potential_reserved_words(i) into v_dummy;

exception when others then

v_reserved_words.extend;

v_reserved_words(v_reserved_words.count) := v_potential_reserved_words(i);

--For testing.

--dbms_output.put_line('Reserved: '||v_potential_reserved_words(i));

end;

end loop;

end if;

return v_reserved_words;

end get_reserved_words;

--Purpose: Remove the SUBQUERY node, re-number descendents to fill in gap, return parent id.

--ASSUMPTIONS:

function remove_extra_subquery(v_subquery_node_id number) return number is

v_new_nodes node_table := node_table();

begin

--Copy nodes up until the subquery node.

for i in 1 .. v_subquery_node_id - 1 loop

v_new_nodes.extend;

v_new_nodes(v_new_nodes.count) := g_nodes(i);

end loop;

--Copy nodes after subquery until the end.

for i in v_subquery_node_id + 1 .. g_nodes.count loop

v_new_nodes.extend;

--Shrink ID and PARENT_ID by 1 to fill in gap.

v_new_nodes(v_new_nodes.count) := node(

id => g_nodes(i).id - 1,

type => g_nodes(i).type,

parent_id => g_nodes(i).parent_id - 1,

lexer_token => g_nodes(i).lexer_token,

child_ids => g_nodes(i).child_ids

);

end loop;

--Switcheroo

g_nodes := v_new_nodes;

return v_subquery_node_id - 1;

end remove_extra_subquery;

--Purpose: Get the line up to a specific token.

function get_line_up_until_error(p_tokens token_table, p_token_error_index number) return varchar2 is

v_newline_position number;

v_line clob;

--DBMS_INSTR does not allow negative positions so we must loop through to find the last.

function find_last_newline_position(p_clob in clob) return number is

v_nth number := 1;

v_new_newline_position number;

v_previous_newline_position number;

begin

v_previous_newline_position := dbms_lob.instr(lob_loc => p_clob, pattern => chr(10), nth => v_nth);

loop

v_nth := v_nth + 1;

v_new_newline_position := dbms_lob.instr(lob_loc => p_clob, pattern => chr(10), nth => v_nth);

if v_new_newline_position = 0 then

return v_previous_newline_position;

else

v_previous_newline_position := v_new_newline_position;

end if;

end loop;

end find_last_newline_position;

begin

--Get text before index token and after previous newline.

for i in reverse 1 .. p_token_error_index loop

--Look for the last newline.

v_newline_position := find_last_newline_position(p_tokens(i).value);

--Get everything after newline if there is one, and exit.

if v_newline_position > 0 then

--(If the last character is a newline, the +1 will return null, which is what we want anyway.)

v_line := dbms_lob.substr(lob_loc => p_tokens(i).value, offset => v_newline_position + 1) || v_line;

exit;

--Add entire string to the line if there was no newline.

else

v_line := p_tokens(i).value || v_line;

end if;

end loop;

--Only return the first 4K bytes of data, to fit in SQL varchar2(4000).

return substrb(cast(substr(v_line, 1, 4000) as varchar2), 1, 4000);

end get_line_up_until_error;

--Purpose: Raise exception with information about the error.

--ASSUMES: All production rules are coded as functions on a line like: function%

procedure parse_error(p_error_expected_items varchar2, p_line_number number) is

v_production_rule varchar2(4000);

v_parse_tree_token_index number;

begin

--Find the production rule the error line occurred on.

select production_rule

into v_production_rule

from

(

--Find the production rule based on the function name.

--ASSUMES a consistent coding style.

--(Irony alert - this is exactly the kind of hack this program is built to avoid.)

select

row_number() over (order by line desc) last_when_1,

replace(regexp_replace(text, 'function ([^\(]+).*', '\1'), chr(10)) production_rule

from user_source

where name = $$plsql_unit

and type = 'PACKAGE BODY'

and line <= p_line_number

--Assumes coding style.

and lower(text) like 'function%'

) function_names

where last_when_1 = 1;

--Find the last token examined.

begin

v_parse_tree_token_index := g_map_between_parse_and_ast(g_ast_token_index);

exception when subscript_beyond_count then

v_parse_tree_token_index := g_map_between_parse_and_ast(g_ast_token_index-1);

end;

--Raise an error with some information about the rule.

raise_application_error(-20123,

'Error in line '||g_nodes(g_nodes.count).lexer_token.line_number||', '||

'column '||to_char(g_nodes(g_nodes.count).lexer_token.last_char_position+1)||':'||chr(10)||

get_line_up_until_error(g_parse_tree_tokens, v_parse_tree_token_index)||'<-- ERROR HERE'||chr(10)||

'Error in '||v_production_rule||', expected one of: '||p_error_expected_items

);

--Just in case a function cannot be found.

exception when no_data_found then

raise_application_error(-20000, 'Could not find function for line number '||p_line_number||'.');

end parse_error;

function match_unreserved_word(node_type varchar2, p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(node_type, p_parent_id);

if current_type = plsql_lexer.c_word and current_value not member of g_reserved_words then

increment;

return true;

else

return pop(v_parse_context);

end if;

end match_unreserved_word;

function is_unreserved_word(p_increment in number) return boolean is

begin

if next_type(p_increment) = plsql_lexer.c_word and next_value(p_increment) not member of g_reserved_words then

return true;

else

return false;

end if;

end is_unreserved_word;

--Purpose: Resolve nodes that are ambiguous offline or at the beginning of parsing.

--For example, "select a.* ..." - the "a" can be multiple things, such as a

--table alias, query name, table, view, or a materialized view.

procedure resolve_ambiguous_nodes(p_user varchar2) is

function is_query_name_from_cte(p_index number) return boolean is

v_ambig_cmqstv clob := g_nodes(p_index).lexer_token.value;

v_ancestor_query_block node;

v_with_clause node;

v_subquery_factoring_clause node;

v_subquery_factoring_list node;

v_subquery_factoring_items node_table;

begin

--Ancestor query_name.

v_ancestor_query_block := syntax_tree.get_first_ancest_node_by_type(g_nodes, p_index, c_query_block);

loop

--Stop looping when no more query_blocks are found.

if v_ancestor_query_block is null then

exit;

else

--Child with_clause.

v_with_clause := syntax_tree.get_child_node_by_type(g_nodes, v_ancestor_query_block.id, C_WITH_CLAUSE, 1);

if v_with_clause is not null then

--Child subquery_factoring_clause and list

v_subquery_factoring_clause := syntax_tree.get_child_node_by_type(g_nodes, v_with_clause.id, C_SUBQUERY_FACTORING_CLAUSE, 1);

v_subquery_factoring_list := syntax_tree.get_child_node_by_type(g_nodes, v_subquery_factoring_clause.id, C_SUBQUERY_FACTORING_LIST, 1);

if v_subquery_factoring_list is not null then

--Children subquery_factoring_item

v_subquery_factoring_items := syntax_tree.get_children_node_by_type(g_nodes, v_subquery_factoring_list.id, C_SUBQUERY_FACTORING_ITEM);

--Look for matching name.

for i in 1 .. v_subquery_factoring_items.count loop

if syntax_tree.are_names_equal(v_ambig_cmqstv, v_subquery_factoring_items(i).lexer_token.value) then

return true;

end if;

end loop;

end if;

end if;

end if;

--If not found, go up another query_block and try again.

v_ancestor_query_block := syntax_tree.get_first_ancest_node_by_type(g_nodes, v_ancestor_query_block.parent_id, c_query_block);

end loop;

--Nothing found.

return false;

end is_query_name_from_cte;

--Replace C_AMBIG_CMQTV with "query_name" if the name is from a CTE.

procedure resolve_query_name is

begin

--Loop through all the nodes.

for i in 1 .. g_nodes.count loop

--Look for this type of abmiguity.

if g_nodes(i).type = C_AMBIG_CMQTV then

if is_query_name_from_cte(i) then

g_nodes(i).type := c_query_name;

end if;

end if;

end loop;

end resolve_query_name;

--Purpose: Create definer's rights function to simplify name resolution.

procedure create_get_object_type_func(p_user varchar2) is

v_name_already_exists exception;

pragma exception_init(v_name_already_exists, -955);

pragma autonomous_transaction;

begin

execute immediate replace(q'[

create function "$$P_USER$$".temp_get_object_type_for_parse

--This temporary function was created by PLSQL_PARSER to resolve objects.

--It is completley safe to drop this function.

--It was supposed to have been dropped as part of the parsing functions, something

--must have went wrong.

--

--Returns: OBJECT_TYPE, and only the most relevant one if there are duplicates.

-- For example: MView trumps table, cluster trumps table, table trumps table partition.

-- Will return NULL if nothing is found.

(

p_owner varchar2,

p_object_name varchar2,

p_is_owner_implicit boolean,

p_dblink varchar2 default null

) return varchar2 authid current_user is

v_object_type varchar2(4000);

v_public varchar2(30);

begin

--Search for "PUBLIC" if schema name was implicit.

if p_is_owner_implicit then

v_public := 'PUBLIC';

end if;

--Resolve duplicates. Clusters and materialized views also create a table but

--should be counted only as a cluster or materialized view.

select coalesce(is_cluster, is_mv, is_synonym, is_table, is_view) object_type

into v_object_type

from

(

--Get simple object type.

select

max(case when object_type = 'CLUSTER' then 'CLUSTER' end) is_cluster,

max(case when object_type = 'MATERIALIZED VIEW' then 'MATERIALIZED VIEW' end) is_mv,

max(case when object_type = 'SYNONYM' then 'SYNONYM' end) is_synonym,

max(case when object_type = 'TABLE' then 'TABLE' end) is_table,

max(case when object_type = 'VIEW' then 'TABLE' end) is_view

from all_objects

where owner in (p_owner, v_public)

and object_name = p_object_name

);

--TODO: Recursive synonym resolution.

--TODO: Database link support.

return v_object_type;

end;

]', '$$P_USER$$', p_user);

--Use existing function if it already exists.

exception when v_name_already_exists then

null;

end create_get_object_type_func;

--Resolved cluster, materialized view, table, or view from either C_AMBIG_CMQTV or C_AMBIG_CMTV.

procedure resolve_cmtv(p_user varchar2) is

v_explicit_schema_name varchar2(32767);

v_object_type varchar2(4000);

v_query_table_expression node;

v_schema node;

begin

--Loop through all the nodes.

for i in 1 .. g_nodes.count loop

--Look for this type of abmiguity.

if g_nodes(i).type in (C_AMBIG_CMQTV, C_AMBIG_CMTV) then

--Find explicit schema name.

v_query_table_expression := syntax_tree.get_first_ancest_node_by_type(g_nodes, i, C_QUERY_TABLE_EXPRESSION);

v_schema := syntax_tree.get_child_node_by_type(g_nodes, v_query_table_expression.id, C_SCHEMA);

v_explicit_schema_name := v_schema.lexer_token.value;

--Implicit schema name.

if v_explicit_schema_name is null then

execute immediate replace(q'[

begin

:v_object_type := "$$P_USER$$".temp_get_object_type_for_parse(:p_owner, :p_object_name, p_is_owner_implicit => true);

end;

]', '$$P_USER$$', p_user)

using

out v_object_type,

p_user,

syntax_tree.get_data_dictionary_case(g_nodes(i).lexer_token.value);

--Throw exception if object cannot be resolved.

if v_object_type is null then

raise_application_error(-20942, 'table or view does not exist. Could not resolve '||

syntax_tree.get_data_dictionary_case(g_nodes(i).lexer_token.value));

end if;

--Explicit schema name.

else

execute immediate replace(q'[

begin

:v_object_type := "$$P_USER$$".temp_get_object_type_for_parse(:p_owner, :p_object_name, p_is_owner_implicit => false);

end;

]', '$$P_USER$$', p_user)

using

out v_object_type,

syntax_tree.get_data_dictionary_case(v_explicit_schema_name),

syntax_tree.get_data_dictionary_case(g_nodes(i).lexer_token.value);

--Throw exception if object cannot be resolved.

if v_object_type is null then

raise_application_error(-20942, 'table or view does not exist. Could not resolve '||

syntax_tree.get_data_dictionary_case(v_explicit_schema_name)||'.'||

syntax_tree.get_data_dictionary_case(g_nodes(i).lexer_token.value)||'.');

end if;

end if;

--Set the type.

g_nodes(i).type := lower(v_object_type);

end if;

end loop;

end resolve_cmtv;

--Purpose: Resolve row_limiting_clause rowcount or percent ambiguity.

procedure p_AMBIG_rowcount_or_percent is

v_row_limiting_clause_node node;

v_ambiguous_node node;

begin

--Loop through all the nodes.

for i in 1 .. g_nodes.count loop

--Look for this type of abmiguity.

if g_nodes(i).type = C_AMBIG_rowcount_or_percent then

--Find the parent of the ambiguous nodes.

v_row_limiting_clause_node := syntax_tree.get_first_ancest_node_by_type(g_nodes, i, C_ROW_LIMITING_CLAUSE);

--Set to "percent" node if there is a "PERCENT" terminal.

if syntax_tree.get_children_node_by_type(g_nodes, v_row_limiting_clause_node.id, 'PERCENT').count >= 1 then

g_nodes(i).type := c_percent;

--Set to a "rowcount" node otherwise.

else

g_nodes(i).type := c_rowcount;

end if;

end if;

end loop;

end p_AMBIG_rowcount_or_percent;

begin

--C_AMBIG_CMQTV or C_AMBIG_CMTV:

resolve_query_name;

create_get_object_type_func(p_user);

--TODO: Should we drop the function? Leave it and pollute schema?

--drop_get_object_type_func;

resolve_cmtv(p_user);

p_AMBIG_rowcount_or_percent;

--This must come towards the end, it depends on table aliases: C_AMBIG_c_mv_t_ta_v

--TODO - other ambiguities.

end resolve_ambiguous_nodes;

--Return the value after the matching parens.

--ASSUMPTION: The current_type is pointing to a "(".

function value_after_matching_parens return clob is

v_paren_counter number := 1;

begin

--Only process if starting at '('.

if next_type(0) = '(' then

--Loop until a matching ")" is found.

for token_index in 1 .. (g_ast_tokens.count - g_ast_token_index) loop

--Increment or decrement counter.

if next_type(token_index) = '(' then

v_paren_counter := v_paren_counter + 1;

elsif next_type(token_index) = ')' then

v_paren_counter := v_paren_counter - 1;

end if;

--Return a value if the counter is 0.

if v_paren_counter = 0 then

--If it's the last token, return null;

if token_index + g_ast_token_index = g_ast_tokens.count then

return null;

--Else return the next token type.

else

return next_type(token_index+1);

end if;

end if;

end loop;

--Return null, nothing found

return null;

else

return null;

end if;

end value_after_matching_parens;

--Ensure the user exists. Get the poentially case-sensitive username if necessary.

function verify_user_get_real_name(p_user varchar2) return varchar2 is

v_username varchar2(4000);

begin

--Verify case if the username has quotation marks.

if trim(p_user) like '"%"' then

execute immediate 'select username from dba_users where username = '''||trim('"' from p_user)||''''

into v_username;

else

execute immediate 'select username from dba_users where username = '''||upper(p_user)||''''

into v_username;

end if;

dbms_output.put_line('User: '||v_username);

return v_username;

exception when no_data_found then

raise_application_error(-20000, 'Could not find this user: '||p_user||'. If the '||

'username is case-sensitive then you must add quotation marks around the name.');

end verify_user_get_real_name;

--Print a more user-friendly error message of the path. Useful for debugging.

procedure print_path(p_error in varchar2) is

v_previous_newline number := 1;

v_next_newline number;

v_line varchar2(32767);

v_line_number number;

v_object_name varchar2(128);

v_path varchar2(32767);

begin

--Split, get line number, then convert into object name.

for i in 1 .. regexp_count(p_error, chr(10)) + 1 loop

--Get the error line.

v_next_newline := instr(p_error, chr(10), 1, i);

if v_next_newline = 0 then

v_next_newline := length(p_error) + 1;

end if;

v_line := replace(substr(p_error, v_previous_newline, v_next_newline - v_previous_newline), chr(10));

--test

--dbms_output.put_line(v_line);

v_previous_newline := v_next_newline;

--Get the line number from the error message.

--Ignore the last "line 1".

if v_line like 'ORA-06512%' and v_line not like '%at line 1' then

v_line_number := regexp_replace(v_line, '.* ');

--test

--dbms_output.put_line(v_line_number);

--Parse the parser... how meta.

--Convert into function names.

--This assumes that functions and procedures always starte like this: function|procedure name ...

select object_name

into v_object_name

from

(

select

user_source.*, replace(regexp_replace(regexp_replace(replace(replace(text, 'function '), 'procedure '), '\(.*'), ' .*'), chr(10)) object_name

,row_number() over (order by line desc) last_when_1

from user_source

where name = 'PLSQL_PARSER'

and type = 'PACKAGE BODY'

and (text like 'function %' or text like 'procedure %')

and line <= v_line_number

order by line

)

where last_when_1 = 1;

v_path := v_object_name || '->' || v_path;

--test

--dbms_output.put_line(v_object_name);

end if;

end loop;

--Remove the last "->" and print everything.

dbms_output.put_line(substr(v_path, 1, length(v_path)-2));

dbms_output.put_line(p_error);

end;

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

--Production Rules.

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

--Forward declarations so functions can be placed in alphabetical order.

function argument(p_parent_id number) return boolean;

function between_condition(p_parent_id number) return boolean;

function collection_expression(p_parent_id number) return boolean;

function comparison_condition(p_parent_id number) return boolean;

function compound_condition_1(p_parent_id number) return boolean;

function condition(p_parent_id number) return boolean;

function containers_clause(p_parent_id number) return boolean;

function cross_outer_apply_clause(p_parent_id number) return boolean;

function dblink(p_parent_id number) return boolean;

function else_clause(p_parent_id number) return boolean;

function else_expr(p_parent_id number) return boolean;

function exists_condition(p_parent_id number) return boolean;

function explain_plan(p_parent_id number) return boolean;

function expr(p_parent_id number) return boolean;

function expr_by_another_name(p_type varchar2, p_parent_id number) return boolean;

function expression_list(p_parent_id number) return boolean;

function expressions(p_parent_id number) return boolean;

function flashback_query_clause(p_parent_id number) return boolean;

function for_update_clause(p_parent_id number) return boolean;

function floating_point_condition(p_parent_id number) return boolean;

function function_expression_1(p_parent_id number) return boolean;

function group_by_clause(p_parent_id number) return boolean;

function group_by_list(p_parent_id number) return boolean;

function group_comparison_condition(p_parent_id number) return boolean;

function hierarchical_query_clause(p_parent_id number) return boolean;

function hint(p_parent_id number) return boolean;

function in_condition(p_parent_id number) return boolean;

function inner_cross_join_clause(p_parent_id number) return boolean;

function integer_rule(p_parent_id number) return boolean;

function interval_expression(p_parent_id number) return boolean;

function is_of_type_condition(p_parent_id number) return boolean;

function join_clause(p_parent_id number) return boolean;

function JSON_condition(p_parent_id number) return boolean;

function model_condition(p_parent_id number) return boolean;

function model_clause(p_parent_id number) return boolean;

function model_expression(p_parent_id number) return boolean;

function multiset_condition(p_parent_id number) return boolean;

function null_condition(p_parent_id number) return boolean;

function number_by_another_name(p_type varchar2, p_parent_id number) return boolean;

function object_access_expression_1(p_parent_id number) return boolean;

function order_by_clause(p_parent_id number) return boolean;

function outer_join_clause(p_parent_id number) return boolean;

function outer_join_type(p_parent_id number) return boolean;

function pattern_matching_condition(p_parent_id number) return boolean;

function placeholder_expression(p_parent_id number) return boolean;

function plsql_declarations(p_parent_id number) return boolean;

function pseudocolumn_1(p_parent_id number) return boolean;

function query_block(p_parent_id number) return boolean;

function query_partition_clause(p_parent_id number) return boolean;

function query_table_expression(p_parent_id number) return boolean;

function return_expr(p_parent_id number) return boolean;

function row_limiting_clause(p_parent_id number) return boolean;

function sample_clause(p_parent_id number) return boolean;

function searched_case_expression(p_parent_id number) return boolean;

function search_clause(p_parent_id number) return boolean;

function select_clause(p_parent_id number) return boolean;

function select_list(p_parent_id number) return boolean;

function select_statement(p_parent_id number) return boolean;

function simple_case_expression(p_parent_id number) return boolean;

function simple_expression_1(p_parent_id number) return boolean;

function scalar_subquery_expression(p_parent_id number) return boolean;

function simple_comparison_condition(p_parent_id number) return boolean;

function statement(p_parent_id number) return boolean;

function string(p_parent_id number) return boolean;

function subquery(p_parent_id number) return boolean;

function subquery_factoring_clause(p_parent_id number) return boolean;

function subquery_restriction_clause(p_parent_id number) return boolean;

function t_alias(p_parent_id number) return boolean;

function table_collection_expression(p_parent_id number) return boolean;

function table_reference(p_parent_id number) return boolean;

function type_constructor_expression_1(p_parent_id number) return boolean;

function where_clause(p_parent_id number) return boolean;

function windowing_clause(p_parent_id number) return boolean;

function with_clause(p_parent_id number) return boolean;

function words_dots_parens_links(p_parse_context parse_context) return boolean;

function unpivot_clause(p_parent_id number) return boolean;

function XML_condition(p_parent_id number) return boolean;

--This can be a lot of different expressions.

function ambiguous_expression(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_AMBIG_expression, p_parent_id);

if words_dots_parens_links(v_parse_context) then

return true;

else

return pop(v_parse_context);

end if;

end ambiguous_expression;

--Assumption: This is only called where it is required.

--This function always returns true - analytic clauses can be empty.

--For example: select count(*) over () from dual;

function analytic_clause(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_ANALYTIC_CLAUSE, p_parent_id);

g_optional := query_partition_clause(v_parse_context.new_node_id);

if order_by_clause(v_parse_context.new_node_id) then

g_optional := windowing_clause(v_parse_context.new_node_id);

end if;

return true;

end analytic_clause;

function argument(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_ARGUMENT, p_parent_id);

--TODO: Should this be some similar name for different contexts?

--Sometimes it's ARGUMENT, sometimes it's just expression, etc.

if expr(v_parse_context.new_node_id) then

return true;

else

return pop(v_parse_context);

end if;

end argument;

--Not a standard production rule. No push/pop.

--Assumption: This was called right after a "(".

procedure arguments(p_parse_context parse_context) is

begin

if argument(p_parse_context.new_node_id) then

loop

if match_terminal(',', p_parse_context.new_node_id) then

if argument(p_parse_context.new_node_id) then

null;

else

parse_error('argument', $$plsql_line);

end if;

else

exit;

end if;

end loop;

elsif match_terminal(')', p_parse_context.new_node_id) then

null;

else

parse_error('argument or ")"', $$plsql_line);

end if;

end arguments;

function between_condition(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_BETWEEN_CONDITION, p_parent_id);

--TODO

return pop(v_parse_context);

end between_condition;

function case_expression(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_CASE_EXPRESSION, p_parent_id);

if match_terminal('CASE', v_parse_context.new_node_id) then

--Put searched first, even though it's listed second on docs. It's easier to find a "WHEN".

if searched_case_expression(v_parse_context.new_node_id) or simple_case_expression(v_parse_context.new_node_id) then

g_optional := else_clause(v_parse_context.new_node_id);

if match_terminal('END', v_parse_context.new_node_id) then

return true;

else

parse_error('END', $$plsql_line);

end if;

else

parse_error('simple_case_expression or searched_case_expression', $$plsql_line);

end if;

else

return pop(v_parse_context);

end if;

end case_expression;

--TODO: I'm not sure how to handle this. This is really just a subset of expr but

--I don't want to re-define that.

function collection_expression(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_COLLECTION_EXPRESSION, p_parent_id);

if expr(v_parse_context.new_node_id) then

return true;

else

return pop(v_parse_context);

end if;

end collection_expression;

function comparison_condition(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_COMPARISON_CONDITION, p_parent_id);

if

--Group is more specific and should come first.

group_comparison_condition(v_parse_context.new_node_id) or

simple_comparison_condition(v_parse_context.new_node_id)

then

return true;

else

return pop(v_parse_context);

end if;

end comparison_condition;

function comparison_expr(p_parent_id number) return boolean is

v_parse_context parse_context;

begin

v_parse_context := push(C_COMPARISON_EXPR, p_parent_id);

if expr(v_parse_context.new_node_id) then

return true;

else

return pop(v_parse_context);

end if;

end comparison_expr;