问题描述
我正在编写一个脚本,其中多个后续过程都需要一个过程的输出.因此,我需要绑定变量,而不是替换变量.但是,无论何时使用该变量,都会将其清除.这使得不可能多次使用变量的值.完全相同的脚本可在SQL * Plus中使用.我在下面做了一个简短的脚本来演示问题.
I am writing a script where the output from one procedure is needed for multiple later procedures. So, I need bind variables, not substitution variables. But, whenever the variable is used, it is cleared. That makes it impossible to use a variable's value more than once. The exact same script works in SQL*Plus. I have made a shortened script below that demonstrates the problem.
这是可以在SQL Developer中更改的设置吗?这是一个错误吗?就我而言,我使用的是SQL Developer 4.1.5.21.78.
Is this a setting that can be changed in SQL Developer? Is this a bug? In my case, I am using SQL Developer version 4.1.5.21.78.
var x varchar2(1)
var y varchar2(1)
print x
print y
exec :x := 'Z';
exec :y := 'Z';
print x
print y
exec :x := :y;
-- Why did that last line clear y?
print x
print y
输出
X
------
Y
------
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
X
-
Z
Y
-
Z
PL/SQL procedure successfully completed.
Y
-
X
-
Z
推荐答案
这似乎是4.1.5和其他版本中的错误,此错误已由4.2.0.17版本修复.
This seems to be a bug in 4.1.5, and possibly other versions, that has been fixed by version 4.2.0.17.
exec
只是一个匿名块的包装,但是使用显式块也会显示问题:
exec
is just a wrapper for an anonymous block, but using an explicit block instead also shows the problem:
begin
:x := :y;
end;
/
我很确定我以前看过此报告,但是我可以找到的唯一示例这个问题 ;如前所述,您可以通过将值重新分配给自身来解决此问题:
I'm pretty sure I've seen this reported before, but the only example I can find is this question; as that notes you can work around it by reassigning the value to itself:
begin
:x := :y;
:y := :y;
end;
/
或更不易读:
exec :x := :y; :y := :y;
它确实似乎是一个错误,但是在当前版本中已修复,升级似乎是解决问题的明智方法.否则,您需要向Oracle提出服务请求-尽管我怀疑他们还是建议升级.
It certainly appears to be a bug, but as it's fixed in the current release, upgrading seems like a sensible way to resolve it. Otherwise you'd need to raise a service request to Oracle - though I suspect they'd advise to upgrade anyway.
(我可能一直想着回到这个,但这似乎是一个不同的问题该示例在4.1.5中看起来还不错.在这两个问题中,我都看不到My Oracle Support中的任何错误报告;但这些错误报告并不总是发布的.)
(I may have been thinking all the way back to this, but that seems to be a different problem as that example looks OK in 4.1.5. I can't see any bug reports in My Oracle Support, for either issues; but they aren't always published.)
这篇关于SQL Developer清除脚本中的绑定变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!