Home > Enterprise >  Increase default size for OUT VARCHAR2 variables in "Run PL/SQL" window
Increase default size for OUT VARCHAR2 variables in "Run PL/SQL" window

Time:03-18

In the Oracle SQL Developer "Run PL/SQL" window, is there a way to increase the default variable size from VARCHAR2(200) to VARCHAR2(4000)?

For example, if you create a procedure like this:

create or replace procedure test_proc(p_out in out varchar2) is
begin
    p_out := lpad('A', 1000, 'A');
end;
/

And then right-click on the procedure and select "Run...":

enter image description here

The "Run PL/SQL" windows opens and creates an anonymous block for running the procedure and retrieving the output variables.

enter image description here

Unfortunately, the default size of VARCHAR2(200) is not large enough, and running the procedure generates the error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

We can manually change the variables size from 200 to 4000, but it's annoying to have to do this every time, for every procedure. The window does temporarily remember the change, but it resets after the program is reopened.

And to make the problem even more annoying, the error actually gets generated inside the procedure. Which means that our logging system records these errors, and it's not trivial to separate them from legitimate errors.

Is there some way to change that default size? I don't see any option in the Preferences window in Version 21.4.1.349.

CodePudding user response:

Sorry, there's no option for this.

I'll investigate an ER to increase this to 4000.

As a workaround you could have scripts for testing your procedures, and then run those exactly as you please in the SQL worksheet using the Execute as Script (F5) button.

  • Related