Popup values in Debugger

[Ramam Pullella]

When using the debugger under the test window, I don't see the time part in the date program variables?

Is there way to see that ?

 

[TenGor]

Before you run your test Procedure/Function just add this line:

-- Modify the nls_date_format for the session of the test script
  dbms_session.set_nls('nls_date_format','''DD.MM.YYYY HH24:MI:SS''');

I made a little template for this, so i can drag&drop this in a test-window.

 

[Ivan C.]

Can't this be done by configuring a "Tool Configuration"?

I tried creating a "Tool Configuration" with the following settings:

General
-------------------------------------------------
Description = nls_date_format_with_time
Type = Session
Executable/Script = C:\temp\alter_session_nls_date_format_with_time.sql

Menu
-------------------------------------------------
Main Menu = Session

Options
-------------------------------------------------
Requirements = Active Connection
Window Types = Test Window

The content of the alter_session_nls_date_format_with_time.sql script is the following:

alter session set NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

With all this done, when I open a Test Window, and pick the nls_date_format_with_time option from the Session menu, the date format is still the default one (DD-MON-YY) while I'm running in debugg mode.

Could someone suggest a solution on how to configure the "Configuration".

 

[Marco Kalter]

I think TenGor's solution is your best option. You can also change the NLS_DATE_FORMAT at a global level for all sessions, but this may not be what you want.

 

[TenGor]

It would be very nice if there would be an option in Preferences - Test window or Date/Time like "prefered date format in Test"

in both options there is enough room for it ;-)

 

[Marco Kalter]

That should indeed be possible. I have added it to the list of enhancement requests.

 

[Ivan C.]

May I suggest you change PL/SQL Developer to apply its own date format to the debugger's pop-up, because changing it to modify session's NLS_DATE_FORMAT will trigger failures when the code has use of TO_DATE functions without format specified.

I'll give an example:

CREATE OR REPLACE
FUNCTION db_max_time IS
BEGIN
   RETURN TO_DATE ('31-DEC-9999');
END db_max_time;

I agree that we shouldn't write code like that, but that would be a different topic...

If I now run

dbms_session.set_nls('nls_date_format','''MM/DD/YYYY HH24:MI:SS''');

I'll get an error when calling the db_max_time function.

If you add the "date format" option in the debugger's preferences, you should modify PL/SQL Developer to set the debugger's pop-up for dates as TO_CHAR (date_variable, "date format"), so it doesn't trigger any errors during execution...

But then again, you would have caught that during your testing session, before releasing the enhancement, so please forgive me if I'm stating something that's absolutely obvious

Thank you beforehand.
Ivan C.

 

[Marco Kalter]

That is unfortunately not possible. The Oracle Debug API returns a date string formatted according to the NLS_DATE_FORMAT.

 

[Ivan C.]

I may not be very familiar with the inner works of the DBMS_DEBUG package, but isn't that what the "format" parameter in the GET_VALUE function is for?

The online documentation says, "format - Optional date format to use, if meaningful."

Thank you.
Ivan C.

 

[Marco Kalter]

Good find, but unfortunately it doesn't work as documented .

 

[Ivan C.]

What if we try another solution...

If we know that the variable is of DATE data type, we can convert the value returned from the DBMS_DEBUG.GET_VALUE function into a DATE using the date format defined in the NLS_DATE_FORMAT, then convert it back into a VARCHAR2 using the date format specified in the Debugger Preferences in PL/SQL Developer.

Would that work?

Thank you.
Ivan C.

 

[Marco Kalter]

If the NLS_DATE_FORMAT does not include the time fraction, then you will always lose that information.

 

[Ivan C.]

Very good point, Marco!

I'm surprised I missed it. I guess it's because I was concentrating on seeing the year as a 4-digit number (our NVL functions often use years like 1000, 1900 or 9999, so the debugger shows 00 or 99).

In any case, thank you very much for your input.

Regards,
Ivan C.