PL/SQL script: Check a Oracle View

This PL/SQL script can be used to find issues with Oracle Views. For example it shows data type conversions being done that could result in an sub optimal plan.

Overview

I recently had an issue where an index was not being used because of implicit data type conversions taking place. When using PL/SQL objects (like procedures, functions, …) and we enable to show PL/SQL warnings with:

alter session set plsql_warnings='ENABLE:ALL';

We get a nice warning which gives a hint towards this issue:

SQL> alter procedure p1 compile;

Procedure P1 altered.

SQL> show errors

Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/41     PLW-07204: conversion away from column type may result in sub-optimal
         query plan
SQL>

Now when using a view I couldn’t find a way to detect these issues. The view just compiles without an error although we use different data types (varchar2 and nvarchar2):

SQL> create table t1 (a varchar2(10));

Table T1 created.

SQL> create table t2 (a nvarchar2(10));

Table T2 created.

SQL> create view v1 as (select t1.a from t1, t2 where t1.a=t2.a);

View V1 created.

SQL> show errors
No errors.
SQL> alter view v1 compile;

View V1 altered.

SQL> show errors
No errors.
SQL>

Since I didn’t find a convenient way to detect these issues in views with other tools I created a PL/SQL script that creates a procedure based on the underlying SQL statement of a view and shows the errors/warnings after its creation/compilation. The procedure is named p_view_test and created in the schema of the view owner. Here it is:

Script

set serverout on
create global temporary table gtt_view_test (view_text clob);
declare
  l_view_owner varchar2(128) := 'user1';  -- enter the owner of the view
  l_view_name varchar2(128) := 'v1';      -- enter the view name
  l_drop_proc boolean := true;            -- drop the created procedure at the end
  l_view_text clob;
  l_stmt varchar2(200);
  l_cnt integer;
begin
  l_view_owner := upper(l_view_owner);
  l_view_name := upper(l_view_name);
  insert into gtt_view_test select to_lob(text) from all_views where owner=l_view_owner and view_name=l_view_name;
  select view_text into l_view_text from gtt_view_test;
  
  execute immediate 'create procedure '||l_view_owner||'.p_view_test
is
  l_cnt integer;
begin
  select count(*) into l_cnt from ('||l_view_text||');
  dbms_output.put_line(l_cnt);
end;';

  dbms_output.put_line('LINE/COL  ERROR');
  dbms_output.put_line('--------- -------------------------------------------------------------');
  for l_rec in (select line, position, text from all_errors where owner=l_view_owner and name='P_VIEW_TEST' group by line, position, text order by line, position) loop
    dbms_output.put_line(rpad(l_rec.line||'/'||l_rec.position,9)||' '||l_rec.text);
  end loop;
  
  if l_drop_proc then
    execute immediate 'drop procedure '||l_view_owner||'.p_view_test';
  end if;

  exception
    when others then
      dbms_output.put_line('The following error occured: '||SQLERRM);
      raise;
end;
/
drop table gtt_view_test;

Usage

• Connect as a database user with proper privileges
• Adjust the owner and the name of the view to check
• Optional: You can set the parameter l_drop_proc to false. This will not drop the created procedure at the end. In that way you can further check the compiler warnings and make adjustments to the procedure.

Global temporary TABLE created.

LINE/COL  ERROR
--------- -------------------------------------------------------------
1/1       PLW-05018: unit P_VIEW_TEST omitted optional AUTHID clause; default value DEFINER used
5/67      PLW-07204: conversion away from column type may result in sub-optimal query plan


PL/SQL procedure successfully completed.


Table GTT_VIEW_TEST dropped.