Call Stored Procedure before accessing data from view

[kabalevsky]

Hi all,

I have to execute the following:
begin
procedureA.storeid(1234567890123456);
end;

before I can access data from view as the view calls:
tableparam.id = procedureA.retrieveid;

My DAO java object:
public List loadAll() throws DataAccessException
{
List retval = null;
HibernateTemplate template = getHibernateTemplate();
List objects = template.loadAll( package.ViewA.class );
retval = objects;
return retval;
}

I am able to run the storeprocedure as follows:
DriverManagerDataSource ds = new DriverManagerDataSource();
.....
MyStoredProcedure sproc = new MyStoredProcedure(ds);
sproc.execute(id);

But I guess this opens a new session and I get error ORA-14552: cannot perform DDL....(this is due to the procedure not called beforehand)

I guess I'm creating a different session and that my data access object is accessing a different session. How do I go about calling the stored procedure before getting the data from views?

Please advise,
Thanks.
M

[Rod Johnson]

You should not use DriverManagerDataSource in any real-world application.

Basically what you need to do is to change the session for the duration of your transaction to see the parameterized data in the view. You can do this even with normal, recommended use of connection pooling.

We have built a sophisticated application for a client based on a similar approach. (Actually we used a global temporary table with ON COMMIT DELETE, but it's a similar idea, parameterizing views by session state).

I recommend:
- Using a connection pool. Much better performance. Configure it as a bean as in the Spring sample apps.
- Invoking the stored procedure using AOP interception. E.g. a Spring AOP interceptor around all service layer methods
- You need to check that DBMS session is cleared before the connection is returned to the pool. You can also do this with an interceptor. Alternative, the temporary table approach I mentioned does this automatically in the database.

Rgds
Rod

[kabalevsky]

Thanks for the reply, Rod. I'm very new to Spring framework and would you mind directing me to any code samples that I can better understand the process?

Thx again,
M

[Rod Johnson]

I'd recommend managing transactions declaratively with TransactionProxyFactoryBean. Look at the PetClinic and PetStore sample applications for examples. You can specify interceptors that run within the transaction--for example, to call your stored proc and clear the session--as follows:

<property name="postInterceptors">
   <list>
      <ref local="storedProcInterceptor"/>
   </list>
</property>

Where "storedProcInterceptor" is the bean name of an AOP Advisor or Advice (probably MethodInterceptor) that invokes your stored procedure. You can parameterize that interceptor bean with DataSource etc. to allow it to get to the database.[/quote][/code]