Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

getInnerMostDelegate not working

509387Apr 27 2006 — edited Mar 3 2007

Hi ,
I need to pass an array to a stored procedure but Iam stuck up at getting the underlying connection.

My env is --

Server : Tomcat
1. commons-dbcp-1.2.1.jar, classes12.jar in common/lib folder.
2. My data source setting --


<Resource
name="jdbc/ddmoracle"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
accessToUnderlyingConnectionAllowed="true"
url="jdbc:oracle:thin:@192.168.192.27:2200:LBSL"
username="preit03"
password="preit"
maxActive="20"
maxIdle="10"
maxWait="-1"/>

My code --

private int setDistributionSetVersionPriority(Connection conn, int[] lRelVersions , long lUserId)
throws Exception
{

int iReleaseFlag = 0;
try
{
CallableStatement objCallStmt = null;
objCallStmt = conn.prepareCall("CALL PKDSTVERSION.PRSETPRIORITY(?,?,?)");

Connection oracleConnection = conn;

//exception is thrown at the following line
oracleConnection = ((DelegatingConnection) conn).getInnermostDelegate();

ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "NUM_ARRAY", oracleConnection );
ARRAY array_to_pass =
new ARRAY( descriptor, oracleConnection, lRelVersions );

objCallStmt.setArray(1, array_to_pass);
objCallStmt.setLong(2,lUserId);
objCallStmt.registerOutParameter(3,Types.INTEGER);
objCallStmt.executeUpdate();
iReleaseFlag = objCallStmt.getInt(3);
CommonAPI.log(_lUserId, "DistributionDAOImpl", "setDistributionSetVersionPriority",
"Executed PKDSTVERSION.PRSETPRIORITY, iReleaseFlag = "+ iReleaseFlag, DDMConstants.INFO);
}
catch(Exception ex)
{
System.out.println("in set Priority :"+ex.getMessage());
}
return iReleaseFlag;
}

The message displayed in the catch is -- org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper

Any directions from you would be of great help.

Thanks
-Aj

ben23

Nope, no dirty reads in Oracle.

No blocking in Oracle either.

But, ahem, your snapshot might get a bit too old.

sgalaxy

The Oracle Db allows :
1) readers of the data not to be blocked by writers of the data
2) writers of the data not to be blocked by readers of the data

You can find info reading the following...
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm#insertedID3

Greetings...
Sim

Aman....

That's one of my favourite topics.
In oracle select doesn't block anything except when we want it to. And Oracle the isolation level read committed is preserved by default that means as mentioned by Sim, in oracle, Readers don't wait for writes and writers don't wait for readers.
Please read the link that is mentioned by Sim.And just a suggestion, if you are in Oracle world, think from oracle point of view. I meet lot of people every week who wants x feature implemented in database abc in Oracle.Well it may be there , may not be.
Have fun with Oracle.
Aman....

sgalaxy

Yes... exactly these characteristics permit Oracle DB to be the more scalable DB in the world....

Greetings...
Sim

Aman....

100% and also no lock escalation as like other db's.
Aman....

Basil

yes, we all know that Oracle has unlockable read from rollback segments using SCN, but as DBA I WANT to see uncommited data of some of my hanged applications to know what they are doing before they commit.

And how can I do it?

289595

You may want to read:

http://laurentschneider.com/wordpress/2008/08/read-uncommitted.html

Basil

Not helpful...
If I had a chance to interfere with first session and suspend transaction in it, I also could see it's data in it. But as I stated above, session is hanged in application server and I need to see its data. Looks like Oracle dont bother such administrative monitoring things.

289595

Actually, the link was to support that you won't be able to do so.....

The last line in the link: 'But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

'

Hans Forbrich

Basil wrote:
Not helpful...
If I had a chance to interfere with first session and suspend transaction in it, I also could see it's data in it. But as I stated above, session is hanged in application server and I need to see its data. Looks like Oracle dont bother such administrative monitoring things.

You are right. Oracle does not bother with such things, as they are rarely needed in Oracle in a well designed application.

Unfortunately many developers are not aware of things like DBMS_APPLICATION_INFO which would help weird monitor situations.

sb92075

And how can I do it?

You can see SQL involved by starting with code example below

  1  SELECT USERNAME, ss.sql_text
  2  FROM   v$session vv, v$sql ss
  3  WHERE  vv.TADDR IS NOT NULL
  4*  and   vv.prev_sql_id = ss.sql_id
SQL> /

USERNAME
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
DBADMIN
insert into ora4063 values (1,2)

I issued the INSERT in a different terminal window & did not COMMIT;

mbobak

Hi Claire,

In addition to everyone else's comments, I would add this:

It's important to understand that when you come from a different database system, such as SQL Server, for example, that concepts may not map in a 1 to 1 manner. It's better to ask about the problem you're trying to solve, rather than ask "How do I do this particular task in Oracle?" Problems in Oracle may map to a completely different set of tasks than in SQL Server.

If you had started out by asking "How can I monitor in-process, uncommitted data, in order to understand what's happening with my application when it gets hung?", you may have gotten a pointer to DBMS_APPLICATION_INFO, or DBMS_PIPE or UTL_FILE much more quickly.

Hope that helps,

-Mark

1 - 12

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 31 2007

Added on Apr 27 2006

#java-database-connectivity-jdbc

5 comments

5,506 views

Java Database Connectivity (JDBC)

getInnerMostDelegate not working

Comments

Post Details