SQL Window - Query Not Completing

[Jenean]

I have a query that returns the first three records and then hangs. The only way to resolve the issue is to close the window and choose to terminate the query.

The same query in SQL/Plus returns all 57 records in 2 seconds.

This query is using object types and calling a couple of constructors.

I did turn on the debugger while the query was executed. Here is information from the Debug.txt:

4/10/2008 8:34:51 AM AddNavigationBookmark starts
4/10/2008 8:34:51 AM AddNavigationBookmark GNV00004
4/10/2008 8:34:51 AM NavigationBookmarks.EnableButtons(Rebuild)
4/10/2008 8:34:51 AM NavigationBookmarks: Backward=4, Forward=0
4/10/2008 8:34:51 AM AddNavigationBookmark ends
4/10/2008 8:34:51 AM NavigationBookmarks.EnableButtons(Rebuild)
4/10/2008 8:34:51 AM NavigationBookmarks: Backward=4, Forward=0
4/10/2008 8:34:54 AM SQLForm Initializing...
4/10/2008 8:34:54 AM SetConnected starts
4/10/2008 8:34:54 AM SetConnected: Connect starts
4/10/2008 8:34:54 AM TOracleSession $016C3610 Start Session.LogOn as cisd@DEV
4/10/2008 8:34:55 AM TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
4/10/2008 8:34:55 AM TOracleQuery Query End (1 record processed)
Duration = 0
4/10/2008 8:34:55 AM TOracleSession $016C3610 End
Duration = 1.062
4/10/2008 8:34:55 AM SetConnected: (Dis)connect ends
4/10/2008 8:34:55 AM SetSessionAction starts: SQL Window - SELECT CIS_Consultant_Firm_Obj( CCF_Consultant_Id, CIS_Access_Obj( 324397396, 328079246)) FROM C ...
4/10/2008 8:34:55 AM TOracleQuery $02AD2E20 Start Query.Execute
SQL = select null from dual
4/10/2008 8:34:55 AM TOracleQuery $02AD2E20 End (1 record processed)
Duration = 0
4/10/2008 8:34:55 AM TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select length(chr(2000000000)) l4, length(chr(2000000)) l3, length(chr(20000)) l2, 'c' c1 from dual
4/10/2008 8:34:55 AM TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.016
4/10/2008 8:34:55 AM TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select lengthb(nchr(20000)), nchr(20) from dual
4/10/2008 8:34:55 AM TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0
4/10/2008 8:34:55 AM TOracleQuery $02AD2E20 Start Query.Execute
SQL = begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end;
:ACTION = SQL Window - SELECT CIS_Consultant_Firm_Obj( CCF_Consultant_Id, CIS_Access_Obj( 324397396, 328079246)) FROM C ...
4/10/2008 8:34:55 AM TOracleQuery $02AD2E20 End
Duration = 0
4/10/2008 8:34:55 AM SetSessionAction ends
4/10/2008 8:34:55 AM ExecuteConnectScript starts
4/10/2008 8:34:55 AM ExecuteConnectScript ends
4/10/2008 8:34:55 AM TOracleQuery $02AD2E20 Start Query.Execute
SQL = select sid, serial# from v$session where audsid = userenv('SESSIONID')
4/10/2008 8:34:55 AM TOracleQuery $02AD2E20 End (1 record processed)
Duration = 0
4/10/2008 8:34:55 AM EnableOutput starts
4/10/2008 8:34:55 AM TOracleQuery PLSQLDevForm.EnableOutputQuery Start Query.Execute
SQL = begin
if :enable = 0 then
sys.dbms_output.disable;
else
sys.dbms_output.enablesize);
end if;
end;
:ENABLE = 1
:SIZE = 1000000
4/10/2008 8:34:55 AM TOracleQuery PLSQLDevForm.EnableOutputQuery End
Duration = 0
4/10/2008 8:34:55 AM EnableOutput done
4/10/2008 8:34:55 AM InitStatistics starts
4/10/2008 8:34:55 AM TOracleQuery PLSQLDevForm.StatQuery Start Query.Execute
SQL = select value from v$sesstat where sid = :sid order by statistic#
:SID = 144
4/10/2008 8:34:55 AM TOracleQuery PLSQLDevForm.StatQuery End (258 records processed)
Duration = 0.015
4/10/2008 8:34:55 AM InitStatistics ends
4/10/2008 8:34:55 AM NavigationBookmarks.EnableButtons(Rebuild)
4/10/2008 8:34:55 AM NavigationBookmarks: Backward=4, Forward=0
4/10/2008 8:34:55 AM SQLForm Executing...
4/10/2008 8:34:55 AM SQLForm Executing...
4/10/2008 8:34:55 AM TSQLThread SQLThread.Execute
4/10/2008 8:34:55 AM TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
4/10/2008 8:34:55 AM TOracleQuery Query End
Duration = 0
4/10/2008 8:34:55 AM TOracleQuery $02AD3500 Start Query.Execute
SQL = SELECT
CIS_Consultant_Firm_Obj(
CCF_Consultant_Id,
CIS_Access_Obj( 324397396, 328079246))
FROM
CIS_Consultant_Firm
WHERE
CCF_Consultant_Id >= 800
4/10/2008 8:34:57 AM TOracleQuery $02AD3500 End (57 records processed)
Duration = 2.235
4/10/2008 8:34:57 AM TOracleQuery SQLForm.TestQuery Start Query.Describe
SQL = SELECT * FROM CIS_Consultant_Firm
4/10/2008 8:34:57 AM TOracleQuery SQLForm.TestQuery End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_CONSULTANT_FIRM_OBJ', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_STATUS_NTBL', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_STATUS_NTBL', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_STATUS_NTBL', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_STATUS_NTBL', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_STATUS_NTBL', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:34:57 AM TOracleObject Start Object.Create('CISD.CIS_STATUS_NTBL', '')
4/10/2008 8:34:57 AM TOracleObject End
Duration = 0
4/10/2008 8:37:34 AM TOracleQuery Query Start Query.Execute
SQL = select * from v$version
4/10/2008 8:37:34 AM TOracleQuery Query End (5 records processed)
Duration = 0
4/10/2008 8:37:38 AM TOracleQuery Query Start Query.Execute
SQL = select * from v$version
4/10/2008 8:37:38 AM TOracleQuery Query End (5 records processed)
Duration = 0
4/10/2008 8:37:38 AM TOracleQuery $02AD2A00 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
4/10/2008 8:37:38 AM TOracleQuery $02AD2A00 End (2 records processed)
Duration = 0
4/10/2008 8:38:39 AM AddPlugInPopups Start 1
4/10/2008 8:38:39 AM PL/SQL Documentation (plsqldoc)
4/10/2008 8:38:39 AM VIEW+
4/10/2008 8:38:39 AM MATERIALIZED VIEW+
4/10/2008 8:38:39 AM PACKAGE+
4/10/2008 8:38:39 AM PACKAGE BODY+
4/10/2008 8:38:39 AM TABLE+
4/10/2008 8:38:39 AM TYPE+
4/10/2008 8:38:39 AM TYPE BODY+
4/10/2008 8:38:39 AM FUNCTION+
4/10/2008 8:38:39 AM PROCEDURE+
4/10/2008 8:38:39 AM TRIGGER+
4/10/2008 8:38:39 AM VIEW+
4/10/2008 8:38:39 AM MATERIALIZED VIEW+
4/10/2008 8:38:39 AM PACKAGE+
4/10/2008 8:38:39 AM PACKAGE BODY+
4/10/2008 8:38:39 AM TABLE+
4/10/2008 8:38:39 AM TYPE+
4/10/2008 8:38:39 AM TYPE BODY+
4/10/2008 8:38:39 AM FUNCTION+
4/10/2008 8:38:39 AM PROCEDURE+
4/10/2008 8:38:39 AM TRIGGER+
4/10/2008 8:38:39 AM PROGRAMWINDOW
4/10/2008 8:38:39 AM AddPlugInPopups End
4/10/2008 8:38:39 AM TOracleQuery $02AD2A00 Start Query.Execute
SQL = select sys_context('userenv', 'current_schema') from dual
4/10/2008 8:38:39 AM TOracleQuery $02AD2A00 End (1 record processed)
Duration = 0
4/10/2008 8:38:39 AM TOracleQuery PLSQLDevForm.ResolveSymbolQuery Start Query.Execute
SQL = declare

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

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

begin
:sub_object := null;
if :deep != 0 then
begin
if art2 is null then
select constraint_type, owner, constraint_name
into bject_type, bject_owner, bject_name
from sys.all_constraints c
where c.constraint_name = art1 and c.owner = :cur_schema
and rownum = 1;
else
select constraint_type, owner, constraint_name, art3
into bject_type, bject_owner, bject_name, :sub_object
from sys.all_constraints c
where c.constraint_name = art2 and c.owner = art1
and rownum = 1;
end if;
if bject_type = 'P' then bject_type := 'PRIMARY KEY'; end if;
if bject_type = 'U' then bject_type := 'UNIQUE KEY'; end if;
if bject_type = 'R' then bject_type := 'FOREIGN KEY'; end if;
if bject_type = 'C' then bject_type := 'CHECK CONSTRAINT'; end if;
return;
exception
when no_data_found then null;
end;
end if;
:sub_object := art2;
if part2 is null) or part1 != :cur_schema) then
begin
select object_type, :cur_schema, art1
into bject_type, bject_owner, bject_name
from sys.all_objects
where owner = :cur_schema
and object_name = art1
and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
and rownum = 1;
if bject_type = 'SYNONYM' then
select s.table_owner, s.table_name
into t_owner, t_name
from all_synonyms s
where s.synonym_name = art1
and s.owner = :cur_schema
and rownum = 1;
select o.object_type, o.owner, o.object_name
into bject_type, bject_owner, bject_name
from sys.all_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 := art2;
if art3 is not null then
:sub_object := :sub_object || '.' || art3;
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 all_synonyms s
where s.synonym_name = art1
and s.owner = 'PUBLIC'
and rownum = 1;
select o.object_type, o.owner, o.object_name
into bject_type, bject_owner, bject_name
from sys.all_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 := art3;
begin
select o.object_type, o.owner, o.object_name
into bject_type, bject_owner, bject_name
from sys.all_objects o
where o.owner = art1
and o.object_name = art2
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 art2 is null and art3 is null
then
select 'USER', null, art1
into bject_type, bject_owner, bject_name
from sys.all_users u
where u.username = art1
and rownum = 1;
return;
end if;
exception
when no_data_found then null;
end;
begin
if art2 is null and art3 is null and :deep != 0
then
select 'ROLE', null, art1
into bject_type, bject_owner, bject_name
from sys.session_roles r
where r.role = art1
and rownum = 1;
return;
end if;
exception
when no_data_found then null;
end;
bject_owner := null;
bject_type := null;
bject_name := null;
:sub_object := null;
end;
ART1 = WHERE
:OBJECT_TYPE = TABLE
:OBJECT_OWNER = CIS
:OBJECT_NAME = CIS_CONSULTANT_FIRM
ART2 =
:SUB_OBJECT = Null
ART3 =
EEP = 1
:CUR_SCHEMA = CISD
4/10/2008 8:38:39 AM TOracleQuery PLSQLDevForm.ResolveSymbolQuery End
Duration = 0.015
4/10/2008 8:38:39 AM AddPlugInPopups Start 1
4/10/2008 8:38:39 AM PL/SQL Documentation (plsqldoc)
4/10/2008 8:38:39 AM VIEW+
4/10/2008 8:38:39 AM MATERIALIZED VIEW+
4/10/2008 8:38:39 AM PACKAGE+
4/10/2008 8:38:39 AM PACKAGE BODY+
4/10/2008 8:38:39 AM TABLE+
4/10/2008 8:38:39 AM TYPE+
4/10/2008 8:38:39 AM TYPE BODY+
4/10/2008 8:38:39 AM FUNCTION+
4/10/2008 8:38:39 AM PROCEDURE+
4/10/2008 8:38:39 AM TRIGGER+
4/10/2008 8:38:39 AM VIEW+
4/10/2008 8:38:39 AM MATERIALIZED VIEW+
4/10/2008 8:38:39 AM PACKAGE+
4/10/2008 8:38:39 AM PACKAGE BODY+
4/10/2008 8:38:39 AM TABLE+
4/10/2008 8:38:39 AM TYPE+
4/10/2008 8:38:39 AM TYPE BODY+
4/10/2008 8:38:39 AM FUNCTION+
4/10/2008 8:38:39 AM PROCEDURE+
4/10/2008 8:38:39 AM TRIGGER+
4/10/2008 8:38:39 AM PROGRAMWINDOW
4/10/2008 8:38:39 AM AddPlugInPopups End
4/10/2008 8:38:43 AM Aborting thread
4/10/2008 8:38:45 AM TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
4/10/2008 8:38:45 AM TOracleQuery Query End
Duration = 0
4/10/2008 8:38:45 AM NavigationBookmarks.EnableButtons(Rebuild)
4/10/2008 8:38:45 AM NavigationBookmarks: Backward=4, Forward=0

PL/SQL Developer Info:
PL/SQL Developer
Version 7.1.3.1381
01.44401 - Unlimited user license
Windows XP Professional 5.1 Build 2600 (Service Pack 2)

Physical memory : 1,038,404 kB (518,492 available)
Paging file : 2,512,108 kB (2,117,868 available)
Virtual memory : 2,097,024 kB (2,001,272 available)

Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe
debugsql

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Preference Files
C:\Program Files\PLSQL Developer\Preferences\Default\Default.ini
C:\Documents and Settings\A0278\Application Data\PLSQL Developer\Preferences\a0278\default.ini

Plug-Ins
*PL/SQL Documentation (plsqldoc) (C:\Program Files\PLSQL Developer\PlugIns\plsqldoc.dll)
(* is Active)

Aliases
brdgms
comm
dbadm
dev
dev9ir2
...

Homes
DEFAULT_HOME (C:\orant)
Des9i (C:\Des9i)
Ora10g (C:\Ora10g)
ora9i (c:\orant\ora9i)

DLLs
c:\orant\ora9i\bin\oci.dll

TNS File
c:\orant\ora9i\Network\Admin\tnsnames.ora

Using
Home: ora9i
DLL: c:\orant\ora9i\bin\oci.dll
OCI: version 9.2
Oracle9i Enterprise Edition Release 9.2.0.7.0

Character Sets
Character size: 1 byte(s)
CharSetID: 1
NCharSetID: 2000
Unicode Support: True
NLS_LANG: AMERICAN_AMERICA.WE8MSWIN1252
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: US7ASCII

Any help would be appreciated.

Thanks,
Jenean Spencer
JS Programming, LLC

 

[Marco Kalter]

Did you try this with c:\orant\ora9i\bin\sqlplus.exe?

 

[Jenean]

Yes and the query complete just fine.

It returns 57 rows in 2 seconds.

In fact I am using sqlplus.exe as my work around.

Jenean