Re: java stored procedure - how do I connect?

I have not tried this, but you could try creating a utility class (e.g., com.mycompany.MyConnection) which implements the Singleton pattern (private constructor, public static getInstance() method which instantiates the class the first time it is called and stores the reference to the one and only object, then returns that single object every time it is called thereafter.)

In its private constructor the object would obtain a connection using the Oracle internal driver, and set a private Connection variable. It would expose the reference to that Connection variable through a public getConnection() method.

A class that needs the connection would use something like:

Connection conn = MyConnection.getInstance().getConnection();

getConnection() may have to be synchronized if a lot of asynchronous calling for connections happens?

As I said, I have not tried this. I am kind of surprised that you are running into this trouble, as I thought that the Oracle internal connection was supposed to act like this--similar to a connection pool, where there is one physical connection and anytime a connection is needed a logical connection object is created. I may be misunderstanding this, and would like to hear from someone who has this all figured out!

Craig

"Don Chambers" <dchamber_at_mindspring.com> wrote in message news:v2m2qts0q5n3rcn142ph1ufuueff5ubr4c_at_4ax.com...
> I am trying to implement Oracle stored procedures using java and I am
> having some trouble with the connection.
> These are complicated procedures that use many methods of different
> classes. I would also like these classes to be able to run outside of
> oracle if needed.
>
> I hava a connect class that gets a database connection and closes it.
> The version of this class that I deploy to Oracle gets the default
> connection and the close operation is disabled.
>
> Many of my methods must connect to the database, do their work, and
> close the connection. Everything works fine outside of Oracle but
> when I deploy to Oracle and execute the procedure I get an error that
> too many cursors are open. If I actually call the close method of the
> connection object I seem to loose all instances of my connection. The
> docuemntation says the close() emthod does nothing.
>
> I think this is because I create a lot of conenctions but I am using
> the default connection so it should only be 1 connection.
> Can anyone help me with this?
> Do you have any suggestions?
>
> Below is a sample of the tryp things I must do:
>
> Class LoadTable
> -----------------------
> connect
> get all items to load
> loop
> call InsertClass.load with item
> end loop
> close connection (if not in oracle)
> ************************************************
> Class InsertClass
> -------------------------
> load method
> connect
> call getID
> insert the record
> close conenction (if not in oracle)
>
> getID method
> connect
> get the id from sequence
> close conenction (if not in oracle)
> return id
>
>
>
> Thanks for any help,
> Don
Received on Thu Sep 20 2001 - 23:31:03 CDT