SQL Developer script output truncates sys_refcursor width

Question

I have a function defined that returns a sys_refcursor containing 5 text columns. Sorry, I cannot post the contents of it here for business reasons. The columns are casted to varchar2 with specific widths (9,4,10,10,10). The casting allows me to ensure column widths are constant.

In SQL Developer 1.5, printing script output (using F5 from SQL worksheet) displays the contents of the cursor nicely as:

MY_FUNCTION(input1,input2,input3)
---------------------------------
COLUMN1   COLU COLUMN3    COLUMN4    COLUMN5
--------- ---- ---------- ---------- ----------
18-NOV-14 TEXT SOME_DATA1 SOME_DATA2 SOME_DATA3
18-NOV-14 TEXT SOME_DATA1 SOME_DATA2 SOME_DATA3
18-NOV-14 TEXT SOME_DATA1 SOME_DATA2 SOME_DATA3
18-NOV-14 TEXT SOME_DATA1 SOME_DATA2 SOME_DATA3

However, in SQL Developer 4.0, the output is wrapped (similar to linesize argument for SQLPlus) at the width of the function call:

MY_FUNCTION(input1,input2,input3)
---------------------------------
COLUMN1   COLU COLUMN3    COLUMN4
    COLUMN5
--------- ---- ---------- -------
--- ----------
18-NOV-14 TEXT SOME_DATA1 SOME_DA
TA2 SOME_DATA3
18-NOV-14 TEXT SOME_DATA1 SOME_DA
TA2 SOME_DATA3
18-NOV-14 TEXT SOME_DATA1 SOME_DA
TA2 SOME_DATA3
18-NOV-14 TEXT SOME_DATA1 SOME_DA
TA2 SOME_DATA3

Database version is 11g. I have tried using the "set linesize 1000" command, but the output still wraps at the same length. However, setting linesize 5 will cause it to wrap at 5 characters.

Output appears normal in SQL*Plus without using any "set" commands.

Is there a way to prevent the wrapping of the cursor contents in 4.0?

Answer 1

Not directly answering the question - I don't know why the behaviour is different or how to change it when calling the function from a query, other than with a column command to set the width, using an alias here to simplify things slightly:

set lines 250
column rc format a250
select my_function(input1,input2,input3) as rc from dual;

But you can also get the output you want by calling the function differently; with a variable and an anonymous block (or execute as shorthand):

variable rc refcursor;
exec :rc := MY_FUNCTION(input1,input2,input3);
print rc

Well, almost as you want it; the first line of the output is the variable name rather than the function/parameter list; but the cursor columns are not wrapped:

anonymous block completed
RC
---------------------------------------------------------------------------
COLUMN1                   COLUMN2 COLUMN3    COLUMN4    COLUMN5    
------------------------- ------- ---------- ---------- ---------- 
18-NOV-14                 text    some_data1 some_data2 some_data3 

You can also run your function from the code editor (rather than the worksheet), which generates an anonymous block for you; when you click the run arrow (or hit control-F10) you get a dialog like this:

And after running it you get an 'output variables' tab at the bottom of the main window which shows the cursor output in a grid.

You can also see the grid view when you run select my_function(...) from dual. The cursor goes into the normal 'query result' window but not in a very friendly form, appearing as something like:

{<COLUMN1=19-NOV-14,COLUMN2=text,COLUMN3=some_data1,COLUMN4=some_data2,COLUMN5=some_data3>,}

But if you double-click a value then you see a yellow pencil symbol at the far right of the column, and clicking that shows that cursor in its own grid.

Personally I prefer the print option in the script output but I rarely use the gird displays anyway.