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 188.8.131.52.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 - ZAnswer1:
This seems to be a bug in 4.1.5, and possibly other versions, that has been fixed by version 184.108.40.206.
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.)