1

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?

2
  • I'm only seeing that if I get the ref cursor back as part of a select list; if I assign the function value to a refcursor variable and then print that it displays as expected. Which are you doing? If you are selecting, as a workaround, can you just use a column command with a large size, which would be easier if you can alias the column in the query? Commented Nov 18, 2014 at 23:51
  • Aliasing the column is definitely an option, so I'll try using the column formatting. I thought that was a SQLPlus command, so hadn't tried it. Commented Nov 22, 2014 at 10:29

1 Answer 1

0

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:

enter image description here

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the options. I'll give them a shot when next able. I had read on other pages about the "output variables" tab, but could never seem to get it to appear, apparently using the code editor view is the key. Hopefully that works easily, as the anonymous block / exec method takes more memorization of the syntax, making it less likely to be recalled on-the-fly for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.