How to get the query statement of LOV in Oracle Form

1. Make sure value of Profile "FND: Diagnostics" is Yes. if not, modify it.

 

2. Open your form and navigate to "HELP -> About Oracle Applications", get the Session SID of your current form.

 

 

3. Navigate to the LOV window and keep it open.

 

4. Execute below code to get the query statement using the Session ID.

declare
  cursor cur_lov_sql(p_pre_sql_addr varchar2) is
   select t.SQL_TEXT
   from v$sqltext_with_newlines t
   where t.ADDRESS = p_pre_sql_addr
   order by t.PIECE;
  l_lov_sql  varchar2(2400);
  l_prev_sql_addr varchar2(200);
begin
  begin
    select v.PREV_SQL_ADDR
    into   l_prev_sql_addr
    from v$session v
    where v.SID = &sid;
    exception
      when others then
       dbms_output.put_line('get prev sql addr error,'||sqlcode||':'||sqlerrm);
  end;
  for rec in cur_lov_sql(l_prev_sql_addr) loop
      l_lov_sql := nvl(l_lov_sql,'')||rec.sql_text;
  end loop;
  dbms_output.put_line('Query statement of LOV is:');
  dbms_output.put_line(l_lov_sql);
end;