title: Stored Proc hitting ORA-06502: PL/SQL: numeric or value error
body: <p>I'm having a problem calling a stored procedure from OSB 11g thru JCA Adapter.
  The error I'm getting is:</p>
  
  <pre><code>ORA-06502: PL/SQL: numeric or value error
  ORA-06512: at line 1
  </code></pre>
  
  <p>The error is as short as it is without any appends behind the error description. It only happens in production environment and there are no problems when I point the database to the UAT one.
  More surprisingly, it will work in production envrionement if one of the variables I pass in is less than 128 characters. (or else I will hit the error above).
  The stored procedure type and length are defined correctly. having the table column able to cope for 4000 characters.</p>
  
  <p>Both Oracle 9i DB sharing the same major minor build revision. The stored proc is located in a package. I extracted out and compared the stored proc from both DB environments using winmerge and it shows they mirror each other. </p>
  
  <p>Executing the stored procedure manually in production environment works, provides that I declare a big enough varchar size. I manage to simulate the <code>ORA-06502</code> error by declaring a variable size lesser than the data length I'm passing in.</p>
  
  <p>I even went as far as capturing the network dump from both executions and found there are slightly differences. (Note: I'm reusing the same datasource and only change the ip and username in WLS and did a restart before each executions)</p>
  
  <p>When I point to production environment(the 1 having trouble), the tcpdump looks something like below:</p>
  
  <pre><code>BEGIN packagename.stored_proc_name(V_value1=&gt;:1 , v_value2=&gt;:2, v_value3=&gt;:3); END .... {variable1}... {variable2}... {variable3}) 
  </code></pre>
  
  <p>When I point to UAT environment(the successful 1), the dump looks shorter and without the <code>BEGIN; END</code> tag</p>
  
  <pre><code>.... {variable1}... {variable2}... {variable3}) 
  </code></pre>
  
  <p>What else could have gone wrong? Appreciate for any helps!</p>