79366
Question:
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.
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
output
X
------
Y
------
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
X
-
Z
Y
-
Z
PL/SQL procedure successfully completed.
Y
-
X
-
Z
Answer1: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 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 <a href="https://stackoverflow.com/q/43401127/266304" rel="nofollow">is this question</a>; as that notes you can work around it by reassigning the value to itself:
begin
:x := :y;
:y := :y;
end;
/
or slightly less readably:
exec :x := :y; :y := :y;
It certainly appears to be a bug, but as it's fixed in the current release, <a href="http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html" rel="nofollow">upgrading</a> 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.
(I may have been thinking all the way back to <a href="https://stackoverflow.com/a/15064702/266304" rel="nofollow">this</a>, 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.)
![Exception Using PayPal-PHP-SDK [Http response code 400]](/skin/wt/img/t.gif)