SQL Developer脚本输出截断sys_refcursor宽度 [英] SQL Developer script output truncates sys_refcursor width

问题描述

我定义了一个函数，该函数返回包含5个文本列的sys_refcursor.抱歉，由于业务原因，我无法在此处发布其内容.这些列将转换为具有特定宽度(9,4,10,10,10)的varchar2.强制转换使我可以确保列宽恒定.

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.

在SQL Developer 1.5中，打印脚本输出(使用SQL工作表中的F5)将光标的内容很好地显示为:

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

但是，在SQL Developer 4.0中，输出以函数调用的宽度包装(类似于SQLPlus的linesize参数):

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

数据库版本为11g.我尝试使用"set linesize 1000"命令，但是输出仍然以相同的长度换行.但是，将行大小设置为5将导致它换行为5个字符.

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.

在SQL * Plus中，输出看起来很正常，而不使用任何设置"命令.

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

有没有一种方法可以防止将光标内容包装在4.0中?

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

推荐答案

不直接回答问题-我不知道为什么从查询调用函数时行为不同或如何更改行为，除了使用a column命令设置宽度，在此处使用别名稍微简化一下事情:

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;

但是您也可以通过不同地调用该函数来获得所需的输出.带有一个变量和一个匿名块(或execute为简写):

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 

您还可以从代码编辑器(而不是工作表)运行函数，该编辑器会为您生成一个匿名块.当您单击运行箭头(或按Control-F10)时，会出现如下对话框:

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.

运行select my_function(...) from dual时，您还可以看到网格视图.光标会进入普通的查询结果"窗口，但不会以非常友好的形式出现，如下所示:

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.

我个人更喜欢在脚本输出中使用print选项，但是无论如何我很少使用网格显示.

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

这篇关于SQL Developer脚本输出截断sys_refcursor宽度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！