sql - Oracle Cursor has_value Used But Doesn't Exist -

i have giant query uses text in variables have no idea , can't figure out. aren't global or defined anywhere else in oracle package. in particular below variable(or whatever is) called "has_value" confusing because it's used in multiple cases queries across lot of tables.

procedure assemble_default_where(     i_search_id    in     search_table.search_id%type,     o_where_clause out    varchar2,     o_from_clause  out    varchar2,     o_error_number out    error_message.error_number%type)     l_base varchar2(30) := 'd';      cursor c_where_clause       select   decode                  (sl.parameter_name,                   'track location', join_operator || ' ' || open_brackets || ' ' || not_operator || ' exists(select 1 track_locations loc ' || l_base                    || '.plan_number = loc.plan_number , ' || l_base || '.order_id = loc.order_id , loc.t_id = nvl('''                    || track_location_rsect_id(has_value) || ''', loc.t_id) , loc.tstatus = nvl(''' || track_tstatus_id(has_value)      search_lines sl          search_id = i_search_id       order line_no; 

i have left out bit of query because it's not relevant question.

i want know join_operator, has_value , open_brackets come , are???

there several options:

1. variable - defined in outer block.
2. variable - defined in package body.
3. variable - defined in package specification.
4. column - same column names in many tables.
5. function - in invoker's or definer's schema.
6. library - in invoker's or definer's schema.
7. operator - in invoker's or definer's schema.
8. synonym - function, operator, or library.

in practice have noticed if #1, #2, #3, or #4. , #6 , #7 rare. guess function or synonym function.

to rule out variables, search through code either in ide or sql:

select * dba_source lower(text) '%join_operator%'; 

to rule out objects, search though objects sql:

select * dba_objects object_name = 'join_operator'; 

---

update

pl/sql , dba_dependencies can identify objects.

sample schema

alter session set plscope_settings = 'identifiers:all';  create table search_lines(     search_id number, line_no number, parameter_name varchar2(100));  create or replace function join_operator_function return varchar2 begin     return 'asdf'; end; /  create synonym join_operator join_operator_function;  create or replace procedure assemble_default_where     cursor c_where_clause     select decode(sl.parameter_name, 'track location', join_operator)         search_lines sl     order line_no; begin     null; end; / 

pl/scope example

with v (   select    line,             col,             initcap(name) name,             lower(type)   type,             lower(usage)  usage,             usage_id,             usage_context_id     user_identifiers       object_name = 'assemble_default_where'         , object_type = 'procedure' ) select rpad(lpad(' ', 2*(level-1)) ||                  name, 30, '.')||' '||                  rpad(type, 30)||                  rpad(usage, 30)                  identifier_usage_contexts   v   start usage_context_id = 0   connect prior usage_id = usage_context_id   order siblings line, col /  assemble_default_where........ procedure                     declaration   assemble_default_where...... procedure                     definition     c_where_clause............ cursor                        declaration       join_operator_function.. function                      call 

dba_dependencies example

select referenced_owner, referenced_name, referenced_type dba_dependencies owner = user     , name = 'assemble_default_where';         referenced_owner    referenced_name                 referenced_type ----------------    ---------------                 --------------- sys                 standard                        package sys                 sys_stub_for_purity_analysis    package jheller_dba         join_operator                   synonym jheller_dba         join_operator_function          function jheller_dba         search_lines                    table