sql and test window behavior when typing in PSD12

[Albert]

Hello!
Suugest that we have a plsql block in sql (or test) window. For example:

declare
  v_tr_file_name V$DIAG_INFO.NAME%type;
begin
  select i.value into v_tr_file_name FROM V$DIAG_INFO i WHERE NAME = 'Default Trace File';
end;

When we type one character (for example changing variable "v_tr_file_name" in "select" statement) we can see that PSD executes such statements. But it do this work for EVERY typed character

declare

  t_owner varchar2(30);
  t_name  varchar2(30);

  procedure check_mview is
    dummy integer;
  begin
    if :object_type = 'TABLE' then
      select 1 into dummy
      from sys.dba_objects
      where owner = :object_owner
      and object_name = :object_name
      and object_type = 'MATERIALIZED VIEW'
      and rownum = 1;
      :object_type := 'MATERIALIZED VIEW';
    end if;
  exception
    when others then null;
  end;

begin
  :sub_object := null;
  if :deep != 0 then
    begin
      if :part2 is null then
        select constraint_type, owner, constraint_name
          into :object_type, :object_owner, :object_name
          from sys.dba_constraints c
         where c.constraint_name = :part1 and c.owner = :cur_schema
           and rownum = 1;
      else
        select constraint_type, owner, constraint_name, :part3
          into :object_type, :object_owner, :object_name, :sub_object
          from sys.dba_constraints c
         where c.constraint_name = :part2 and c.owner = :part1
           and rownum = 1;
      end if;
      if :object_type = 'P' then :object_type := 'PRIMARY KEY'; end if;
      if :object_type = 'U' then :object_type := 'UNIQUE KEY'; end if;
      if :object_type = 'R' then :object_type := 'FOREIGN KEY'; end if;
      if :object_type = 'C' then :object_type := 'CHECK CONSTRAINT'; end if;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  :sub_object := :part2;
  if (:part2 is null) or (:part1 != :cur_schema) then
    begin
      select object_type, :cur_schema, :part1
      into :object_type, :object_owner, :object_name
      from sys.dba_objects
      where owner = :cur_schema
      and object_name = :part1
      and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
      and rownum = 1;
      if :object_type = 'SYNONYM' then
        select s.table_owner, s.table_name
          into t_owner, t_name
          from dba_synonyms s
         where s.synonym_name = :part1
           and s.owner = :cur_schema
           and rownum = 1;
        select o.object_type, o.owner, o.object_name
          into :object_type, :object_owner, :object_name
          from sys.dba_objects o
         where o.owner = t_owner
           and o.object_name = t_name
           and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
           and rownum = 1;
      end if;
      :sub_object := :part2;
      if :part3 is not null then
        :sub_object := :sub_object || '.' || :part3;
      end if;
      check_mview;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  begin
    select s.table_owner, s.table_name
      into t_owner, t_name
      from dba_synonyms s
     where s.synonym_name = :part1
       and s.owner = 'PUBLIC'
       and rownum = 1;
    select o.object_type, o.owner, o.object_name
      into :object_type, :object_owner, :object_name
      from sys.dba_objects o
     where o.owner = t_owner
       and o.object_name = t_name
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  :sub_object := :part3;
  begin
    select o.object_type, o.owner, o.object_name
      into :object_type, :object_owner, :object_name
      from sys.dba_objects o
     where o.owner = :part1
       and o.object_name = :part2
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null
    then
      select 'USER', null, :part1
      into :object_type, :object_owner, :object_name
      from sys.dba_users u
      where u.username = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null and :deep != 0
    then
      select 'ROLE', null, :part1
      into :object_type, :object_owner, :object_name
      from sys.session_roles r
      where r.role = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  :object_owner := null;
  :object_type := null;
  :object_name := null;
  :sub_object := null;
end;

:PART1=['V$DIAG_INFO']
:OBJECT_TYPE=['VIEW']
:OBJECT_OWNER=['SYS']
:OBJECT_NAME=['V_$DIAG_INFO']
:PART2=['']
:SUB_OBJECT=[NULL]
:PART3=['']
:DEEP=[0]
:CUR_SCHEMA=['SYS']

select column_name, nullable, data_type, data_type_mod, data_type_owner, data_length, data_precision, data_scale, char_used, char_length
from sys.dba_tab_columns
where owner = 'SYS'
and table_name = 'V_$DIAG_INFO'
order by column_id

select comments from sys.dba_tab_comments
 where owner      = :object_owner
   and table_name = :object_name

Is it normal?

My preferences for code assistant:

describe users=true
describe context (3 chars)=true
include columns from dml=true
describe standart functions =true
show in popup=false

Thank you

 

[Marco Kalter]

I think this is most likely caused by the "Include columns from DML statements" option of the Code Assistant. Can you disable it and try again?

However, the code above should only execute once when typing within the context of a DML statement. We'll look into this.