Print Thread
Create table issue in sql window
#47229 06/07/13 01:30 PM
Joined: Apr 2010
Posts: 3
F
Member
OP Offline
Member
F
Joined: Apr 2010
Posts: 3
Good morning, i have found an issue using pl/sql developer.

I launch a sql windows with the following statement.

create table TABLE1
as
select *.....(very long query).

During the process, if i right click on the "TABLE1" string, the program freeze and sometimes crash, making me lose all the work of the others windows). I would suggest a timeout while gathering information about table.

Thanks.

Flavio

Re: Create table issue in sql window
flavio20002 #47241 06/08/13 10:01 AM
Joined: Aug 1999
Posts: 22,173
Member
Offline
Member
Joined: Aug 1999
Posts: 22,173
Can you let me know your Session Mode preference and your exact PL/SQL Developer version?


Marco Kalter
Allround Automations
Re: Create table issue in sql window
Marco Kalter #47249 06/10/13 07:14 AM
Joined: Apr 2010
Posts: 3
F
Member
OP Offline
Member
F
Joined: Apr 2010
Posts: 3
The version is 9.0.6.1665. I have "Multi session" mode, without "Allow multiple connection"

Re: Create table issue in sql window
flavio20002 #47255 06/10/13 10:12 AM
Joined: Aug 1999
Posts: 22,173
Member
Offline
Member
Joined: Aug 1999
Posts: 22,173
Can you try the same with the current 10.0.4 release?


Marco Kalter
Allround Automations
Re: Create table issue in sql window
Marco Kalter #47260 06/10/13 02:04 PM
Joined: Apr 2010
Posts: 3
F
Member
OP Offline
Member
F
Joined: Apr 2010
Posts: 3
There is the same issue.

Re: Create table issue in sql window
flavio20002 #47265 06/11/13 04:36 AM
Joined: Feb 2013
Posts: 251
UTC+10
I
Member
Offline
Member
I
Joined: Feb 2013
Posts: 251
UTC+10
It locks with event "library cache lock" (obviously)

Running the following query:
Code
declare
  c integer := 0;
  p1 varchar2(500);
  p2 varchar2(500);
  expr varchar2(500);
  dblink varchar2(500);
  part1_type integer;
  object_number integer;
  dp integer;
  guard_error exception;
  pragma exception_init(guard_error, -16224);
begin
  :object_type := null;
  :object_owner := null;
  :object_name := null;
  :sub_object := null;
  expr := :part1;
  if :part2 is not null then expr := expr || '.' || :part2; end if;
  if :part3 is not null then expr := expr || '.' || :part3; end if;
  loop
    begin
      sys.dbms_utility.name_resolve(name => expr,
                                    context => c,
                                    schema => :object_owner,
                                    part1 => p1,
                                    part2 => p2,
                                    dblink => dblink,
                                    part1_type => part1_type,
                                    object_number => object_number);
      if part1_type = 1 then :object_type := 'INDEX'; end if;
      if part1_type = 2 then :object_type := 'TABLE'; end if;
      if part1_type = 4 then :object_type := 'VIEW'; end if;
      if part1_type = 5 then :object_type := 'SYNONYM'; end if;
      if part1_type = 6 then :object_type := 'SEQUENCE'; end if;
      if part1_type = 7 then :object_type := 'PROCEDURE'; end if;
      if part1_type = 8 then :object_type := 'FUNCTION'; end if;
      if part1_type = 9 then :object_type := 'PACKAGE'; end if;
      if part1_type = 12 then :object_type := 'TRIGGER'; end if;
      if part1_type = 13 then :object_type := 'TYPE'; end if;
      if part1_type = 28 then :object_type := 'JAVA SOURCE'; end if;
      if part1_type = 29 then :object_type := 'JAVA CLASS'; end if;
      if :object_type is null then
        select object_type into :object_type
          from sys.dba_objects
         where object_id = object_number;
      end if;
    exception
      when guard_error then
        raise;
      when others then
        null;
    end;
    c := c + 1;
    if c > 9 then
      dp := instr(expr, '.', -1);
      if dp > 0 then
        if :sub_object is not null then
          :sub_object := '.' || :sub_object;
        end if;
        :sub_object := upper(substr(expr, dp + 1)) || :sub_object;
        expr := substr(expr, 1, dp - 1);
        c := 0;
      end if; 
    end if;
    exit when (:object_type is not null) or (c > 9);
  end loop;
  if :object_type is not null then
    if p1 is null then
      :object_name := p2;
    elsif p2 is null then
      :object_name := p1;
      if :object_name = :part1 and :part2 is not null then
        :sub_object := :part2;
      end if;
      if :object_name = :part2 and :part3 is not null then
        :sub_object := :part3;
      end if;   
    else
      :object_name := p1;
      :sub_object := p2;
    end if;
    return;
  end if;
  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;  
  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;
end;

IMO, it should include some quick timeout.


Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.029s Queries: 15 (0.007s) Memory: 2.5305 MB (Peak: 3.0394 MB) Data Comp: Off Server Time: 2024-03-29 05:48:01 UTC
Valid HTML 5 and Valid CSS