Geek asks and answers

Oracle stored procedure storage when called via jdbc

I have a web application that executes oracle stored procedures and shows the results in a browser. The technology stack is as follows: Browser ↔ spring mvc ↔ [(tomcat) jboss] <-jdbc-> oracle. Stored procedures have an out pointer for results, and java code uses this cursor to retrieve a set of results.

Everything worked fine until a new stored procedure was added, which, although it ends fairly quickly in SQL Developer, hangs when it is called from the application. Debugging java showed code to hang in OracleCallableStatement.execute. Initially, I thought that something was wrong with the procedure, but it really works successfully in the sql developer, so I now point out the jdbc problem more ...

I assume this is due to some kind of deadlock when reading the cursor, or it could be a bug in the jdbc driver (the version I'm using: ojdbc6 is 11.1.0.7.0)? Any ideas?

thank

+3
source share
2 answers

, . SQL * Plus script , . Oracle Enterprise Manager (- Oracle) " ".

, - - , , ...

REM  Purpose
REM  -------
REM  Display locks currently held and requested. Displays which session a
REM  blocked lock is waiting for.
REM
REM  Ver  Who  When       What
REM  ---  ---  ----       ----
REM  1.0  DrB  12-Dec-97  Initial version
col uname     head "Username"  form a12
col sid       head "SID"       form 999
col ltype     head "Type"      form a4
col lmode     head "Mode"      form a10
col blocked   head "Wait"      form a4
col details   head "Details"   form a40
set verify off
set pause on
accept user prompt  "Username [%]: "
select SubStr('alter system kill session ''' || s.sid || ',' || s.serial# || ''';', 1, 40) as kill, s.username uname, 'DML' ltype,
  decode (l.lmode,1,'Null',
                  2,'Row-S',
                  3,'Row-X',
                  4,'Share',
                  5,'S/Row-X',
                  6,'Exclusive') lmode,
  decode (l.request,0,'No','Yes') blocked,
  u.username||'.'||o.name details,
  Nvl(s.Program, s.Module) What
from v$session s, v$lock l, sys.obj$ o, all_users u
where s.username like nvl(upper('&user'||'%'),'%')
and s.sid = l.sid
and l.id1 = o.obj#
--and l.type = 'TM'
and o.owner# = u.user_id(+)
union all
select SubStr('alter system kill session ''' || s.sid || ',' || s.serial# || ''';', 1, 40) as kill, s.username uname,
  decode (l.type,'TX','TX',
                 'UL','USR',
                      'SYS') ltype,
  decode (l.lmode,1,'Null',
                  2,'Row-S',
                  3,'Row-X',
                  4,'Share',
                  5,'S/Row-X',
                  6,'Exclusive') lmode,
  decode (l.request,0,'No','Yes') blocked,
  decode (l.request,0,null,'Waiting on session '||to_char(b.sid)) details,
  Nvl(s.Program, s.Module) What
from v$session s, v$lock l, v$lock b
where s.username like nvl(upper('&user'||'%'),'%')
and s.sid = l.sid
and l.type != 'TM'
and l.id1 = b.id1(+)
and b.request(+) = 0
order by 5 desc,3 desc,2,1;
set verify on
REM  End of file
+2

. ( ), .

, XA Java, Java SQLDeveloper. (?), JMXConsole jboss.system: type = ServerInfo MBean? , .

==== ====

- , DDL , ?

==== ====

, , . DDL, , WITH, , . , CREATE TEMPORARY TABLE?

0

More articles:


All Articles