79366

SQL Developer Clears Bind Variable in Script

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.)

Recommend

  • NSURL Error Handling
  • confusion about using types instead of gtts in oracle
  • Create a dynamic name table with Prepared Statement in java
  • Is there a way to create a table and some initial partitions dynamically?
  • Create a pl/sql function & find leap years [closed]
  • Dapper Oracle Number(10,0) is returned as Decimal Parser error
  • DES3 encryption: ruby openssl::cipher vs. oracle dbms_obfuscation_toolkit
  • Memory Leak with OracleCommand
  • Accessing types defined inside package from java
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • SQL Procedures - Comparing values
  • Spring Web Security locks Neo4j embedded database
  • Android Error: Binary XML file line #16: Error inflating class fragment when adding map fragment
  • “Yesod devel” fails with fromJust in devel.hs
  • DML and Exception Handling - Oracle
  • iPhone - UIWebView multiple locks
  • How to get to older Xcode beta version?
  • Safari PHP form submission -file upload hangs
  • zope_i18n_compile_mo_files doesn't work on a Zeo configuration
  • Asynchronous UI Testing in Xcode With Swift
  • If I include Java 8 in my Android app does that affect which devices it will work on?
  • Why is an OPTIONS request sent to the server?
  • Spring Data JPA custom method causing PropertyReferenceException
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • RectangularRangeIndicator format like triangular using dojo
  • Excel - Autoshape get it's name from cell (value)
  • Deserializing XML into class C#
  • How to apply VCL Styles to DLL-based forms in Inno Setup?
  • How to make Safari send if-modified-since header?
  • How to pass list parameters for each object using Spring MVC?
  • How to delete a row from a dynamic generate table using jquery?
  • How to include full .NET prerequisite for Wix Burn installer
  • using HTMLImports.whenReady not working in chrome
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • Authorize attributes not working in MVC 4
  • EntityFramework adding new object to nested object collection
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • JaxB to read class hierarchy
  • costura.fody for a dll that references another dll
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize