2

I am using PL/SQL Developer 9.0.6, working on an Oracle 11g database.

I have a procedure in a package that creates some dynamic SQL to be run with execute immediate. The variable vSQL is declared as varchar2(4000). My procedure builds and runs vSQL correctly, and returns a ref cursor through an out paramter.

However, while debugging my procedure, something unexpected happened: I set a watch on vSQL, and when the length vSQL gets to 1000 or greater, the value in the Watch window changes from the contents of vSQL to the literal '(Long Value)' and I can no longer view the contents of vSQL.

Why does this happen? Is there any way that I can cast vSQL so that I can still view it while debugging my procedure?

Welton v3.62
  • 2,210
  • 7
  • 29
  • 46
  • 3
    There's a difference between an Oracle `LONG` and the client displaying the text 'Long value'. There are some forum posts that suggest this is an 'Oracle debugger' problem - e.g. [here](http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=13779&PHPSESSID=2e3c1b028a66500ae8a1730abc88b993), [here](http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=27694&PHPSESSID=2e3c1b028a66500ae8a1730abc88b993) and [here](http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=23444&PHPSESSID=2e3c1b028a66500ae8a1730abc88b993). – Alex Poole Feb 12 '13 at 19:05

2 Answers2

2

As others have pointed out, PL/SQL Developer cannot display 4000 characters in the debugger. This is probably a limitation of the Oracle package DBMS_DEBUG.

As a workaround, you can use Oracle SQL Developer, which uses a different debugger, DBMS_DEBUG_JDWP. In general, PL/SQL Developer is a thousand times better than Oracle SQL Developer. But if you need to debug large strings or collections then Oracle SQL Developer works better.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
1

For debugging purpose you can have array of varchar2 having length of 1000 each element and max to 4 elements in it so it will overcome problem of exceeding 1000 char limit for debuging and still you can monitor the varchar2(4000)'s value

Rahul Vasantrao Kamble
  • 251
  • 1
  • 4
  • 15