Some BUGs (or only newbie)

[Thorsten Kohl (IBS)]

Hi, I'm new working with PLSQL Developer. I've found some problems:

1)In Test-Window I can't use Debugger, if I use an Oracle 10g CLIENT, with an Oracle 9i Client it works. The version of the Oracle Server doesn't matter. Debugger in a program window works, so it couldn't be a privileg problem.

2) If I have more than one Statement in a SQL-Window and have switched Autoselect Statement on, it won't work with anonymous PLSQL-Blocks. I have to select the block manually and have to press the execute button (because Ctrl+Enter deletes the selected statement!).

3) In Testwindow I can't add a breakpoint, nothing happens.

I've to add the info, that I switched Execute accelerator from F8 to common Ctrl+Enter.

Thanks for your help!
Thorsten

 

[Marco Kalter]

1)In Test-Window I can't use Debugger, if I use an Oracle 10g CLIENT, with an Oracle 9i Client it works. The version of the Oracle Server doesn't matter. Debugger in a program window works, so it couldn't be a privileg problem.

What exactly goes wrong?

2) If I have more than one Statement in a SQL-Window and have switched Autoselect Statement on, it won't work with anonymous PLSQL-Blocks. I have to select the block manually and have to press the execute button (because Ctrl+Enter deletes the selected statement!).

That is correct. This is enhanced for version 7.0 though.

3) In Testwindow I can't add a breakpoint, nothing happens.

You can only view/set variables and set breakpoints in stored program units, compiled in the database. In the anonymous PL/SQL Block of the Test Script, these things are unfortunately not possible. You can create a simple "debug" stored procedure that can act as a placeholder for the anonymous PL/SQL that you want to debug, and create a standard test script to run it. This way you can make the process a little bit more efficient.

 

[Thorsten Kohl (IBS)]

Thanks for the answers.

The Test-Window executes the PLSQL-Block without going through it step by step. It happens on all PCs with active Oracle 10g Client (WinXPSP2). Some has additional installed a 9i Client. If you switch your home to 9i, everything works fine. On PCs with 9i Version (no 10g version parallel installed) it works also fine.

 

[Marco Kalter]

Can you also let me know the database version?

 

[Thorsten Kohl (IBS)]

It seems to be independend from the Server-Versions - we have tested 9i (9.2.0.1.0) and 10g (10.1.0.2.0) Servers. It depends only on the client version.

 

[Marco Kalter]

Strange. Could it be that you are connected as SYS?

 

[Thorsten Kohl (IBS)]

I'm connected as "normal" and the user has the following roles: CONNECT, DBA, EXECUTE_CATALOG_ROLE, RESOURCE, SELECT_CATALOG_ROLE

I've seen that there is a new patch for PLSQL-Developer, I'll test it with the new version.

 

[Thorsten Kohl (IBS)]

The new patch changed nothing - debugging in Testwindow with 10g (=>10.1) Client isn't working.

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the debug parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debug

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.

 

[Dean Lamper]

We have the same problem: debugging via test window doesn't work.

PL/SQL Developer
Version 6.0.6.947 (MBCS)
01.27546 - 20 user license
Windows XP version 6,0 (build 2600) Service Pack 2

Physical memory : 523.248 kB (123.676 available)
Paging file : 1.277.324 kB (632.772 available)
Virtual memory : 2.097.024 kB (2.001.584 available)

Parameters
C:\Program Files\Allround Automations\PLSQL Developer 5\PLSQLDev.exe

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

Plug-Ins
*Version Control Interface 1.2 (C:\Program Files\Allround Automations\PLSQL Developer 5\PlugIns\VCS.dll)
(* is Active)

Aliases
ACDP
ACDP.ixtlan-team.si
ACDR
ACDR.ixtlan-team.si
ACDR9
...

Homes
DEFAULT_HOME (C:\Oracle\73_80)
Local805 (C:\Oracle\805)
Local817 (C:\Oracle\817)
Local90 (C:\Oracle\90)
Local92 (C:\Oracle\92)

DLLs
C:\Oracle\817\bin\oci.dll

TNS File
Q:\Ixtlan\Razvoj\ORA_TNS_NAMES\tnsnames.ora

Using
Home: Local817
DLL: C:\Oracle\817\bin\oci.dll
OCI: Version 8.1
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Character size: 1 byte(s)

 

[Dean Lamper]

We solved it. 10g 2 needs new debug priviledges:
- DEBUG ANY PROCEDURE
- DEBUG CONNECT SESSION

 

[Thorsten Kohl (IBS)]

I found out, that the following combination doesn't work also at one PC: Server 10g (10.1.0.2.0) and Client 9i (9.2.0.1.0). It doesn't depend on the grants DEBUG ANY PROCEDURE
and DEBUG CONNECT SESSION. Here the debug information (hope that's the interesting part of the file):

03.11.2005 14:59:57 SetSessionAction starts: Test-Fenster - Neu
03.11.2005 14:59:57 SetSessionAction ends
03.11.2005 14:59:57 InitHTPOutput starts
03.11.2005 14:59:57 InitHTPOutput finished
03.11.2005 14:59:57 EnableOutput starts
03.11.2005 14:59:57 EnableOutput done
03.11.2005 14:59:57 InitializeTargetSession starts
03.11.2005 14:59:57 SetSessionAction starts: Debug Test-Fenster - Neu
03.11.2005 14:59:57 SetSessionAction ends
03.11.2005 14:59:57 ExecuteConnectScript starts
03.11.2005 14:59:57 ExecuteConnectScript ends
03.11.2005 14:59:57 InitializeTargetSession ends
03.11.2005 14:59:57 InitStatistics starts
03.11.2005 14:59:57 TStatistics.Init starts
03.11.2005 14:59:57 StatQuery.Describe starts
03.11.2005 14:59:57 StatQuery.Describe ends
03.11.2005 14:59:57 TStatistics.Init ends
03.11.2005 14:59:57 InitStatistics ends
03.11.2005 14:59:57 Debugger.Attach starts
03.11.2005 14:59:57 TSQLThread SQLThread.Execute
03.11.2005 14:59:57 Debugger.Attach ends with 0
03.11.2005 14:59:57 Continue starts(12,0) .[] - L: T:0 B: S: I:1 R:2
03.11.2005 14:59:57 FinalizeTargetSession starts
03.11.2005 14:59:57 Continue ends with 0 .[] - L: T:1 B: S: I:0 R:25
03.11.2005 14:59:57 Continue starts(2,1) .[] - L: T:1 B: S: I:0 R:25
03.11.2005 14:59:57 FinalizeTargetSession ends
03.11.2005 14:59:57 TSQLThread SQLThread Suspend
03.11.2005 14:59:57 Continue ends with 0 .[] - L: T:1 B: S: I:0 R:25
03.11.2005 14:59:57 AddDBSource starts
03.11.2005 14:59:57 AddDBSource finished
03.11.2005 14:59:57 Detach starts
03.11.2005 14:59:57 Detach ends
03.11.2005 14:59:57 GetHTPOutput starts
03.11.2005 14:59:57 Trying htp.get_line
03.11.2005 14:59:57 No lines retrieved
03.11.2005 14:59:57 GetHTPOutput finished

That's the result, when it's working:

03.11.2005 15:03:32 SetSessionAction starts: Test-Fenster - Neu
03.11.2005 15:03:32 SetSessionAction ends
03.11.2005 15:03:32 InitHTPOutput starts
03.11.2005 15:03:32 InitHTPOutput finished
03.11.2005 15:03:32 EnableOutput starts
03.11.2005 15:03:32 EnableOutput done
03.11.2005 15:03:32 InitializeTargetSession starts
03.11.2005 15:03:32 SetSessionAction starts: Debug Test-Fenster - Neu
03.11.2005 15:03:32 SetSessionAction ends
03.11.2005 15:03:32 ExecuteConnectScript starts
03.11.2005 15:03:32 ExecuteConnectScript ends
03.11.2005 15:03:32 InitializeTargetSession ends
03.11.2005 15:03:32 InitStatistics starts
03.11.2005 15:03:32 TStatistics.Init starts
03.11.2005 15:03:32 InitStatNames starts
03.11.2005 15:03:32 select name from v$statname order by statistic#
03.11.2005 15:03:32 InitStatNames ends
03.11.2005 15:03:32 StatQuery.Describe starts
03.11.2005 15:03:32 StatQuery.Describe ends
03.11.2005 15:03:32 TStatistics.Init ends
03.11.2005 15:03:32 InitStatistics ends
03.11.2005 15:03:32 TSQLThread SQLThread Suspend ended
03.11.2005 15:03:32 Debugger.Attach starts
03.11.2005 15:03:32 Debugger.Attach ends with 0
03.11.2005 15:03:32 Continue starts(12,0) .[] - L: T:0 B: S: I:1 R:2
03.11.2005 15:03:32 Continue ends with 0 .[] - L:7 T: B:0 S:1 I: R:6
03.11.2005 15:03:33 AddDBSource starts
03.11.2005 15:03:33 AddDBSource finished
03.11.2005 15:03:33 GetCallStack starts
03.11.2005 15:03:33 GetCallStack ends

 

[Thorsten Kohl (IBS)]

Additional information, that's the test block I want to "step" through:

-- Created on 03.11.2005 by KOHL
declare
-- Local variables here
i integer;
begin
-- Test statements here
i := 5;
dbms_output.put_line(TO_CHAR(i));
end;

 

[Marco Kalter]

On Oracle10g you can indeed not step through the PL/SQL in the anonymous block of the Test Script. You can only step into program units that are compiled with debug information.

 

[Thorsten Kohl (IBS)]

Originally posted by Marco Kalter:
On Oracle10g you can indeed not step through the PL/SQL in the anonymous block of the Test Script. You can only step into program units that are compiled with debug information.

What does this mean? Is it a problem of PLSQL Developer?

 

[IanN]

Marco, is this limitation on not being able to step through the top level anonymous block an Oracle restriction or a PLSQLDev restriction? I can not find any mention of this restriction in the Oracle doc or an Oracle bug.
We have thousands of tests and are not impressed that we can no longer step through them when we need find out the cause of failures. (These tests are our unit tests, run through a modified variant of jUnit)

 

[Marco Kalter]

It's an Oracle Server restriction. PL/SQL Developer does not treat the Oracle Debug API any different on Oracle10g or earlier versions.

 

[PaulB]

The simple answer to debugging anonymous PL/SQL blocks is to cast your complex block in a CREATE PROCEDURE statement and pass your bind values as parameters. Then the stored procedure is compilable and debuggable. The generic PL/SQL block is not intended to do anything but pass values from the API back and forth to your stored procedures you are testing with the Oracle debugger API.

 

[Thorsten Kohl (IBS)]

Originally posted by PaulB:
The simple answer to debugging anonymous PL/SQL blocks is to cast your complex block in a CREATE PROCEDURE statement and pass your bind values as parameters...

Sorry, but we can't live with this solution. We're in hot standby and have no chance to create stored procedures or packages in the customers database. So we need this functionality (or we have to work with dbms_output ).

I'm not sure, if this is really a restriction of Oracle 10g (see answer Dean Lamper).