CODEWITHSUNDEEP
oracle-databaseoracle11gplsqldeveloper
Welton v3.62
Welton v3.62

Reputation: 2230

Why is my VARCHAR2 being converted to a LONG in my PL/SQL?

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?

Upvotes: 2

Views: 2915

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 2

Rahul Vasantrao Kamble
Rahul Vasantrao Kamble

Reputation: 251

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

Upvotes: 1

Related Questions