Hi folks,

I'm currently working on an application which uses hibernate 3 with 2 distincts databases.
We are mapping one of the database with "native" Hibernate mappings. Some constraints on
the second DB force us to use only stored functions/procedures to communicate with it.
As we are very satisfied with the work hibernate does with the first DB, we wanna keep
the sameobject manipulation scheme for the second one and implement it with stored procedures.

In that second DB, we have the following:
A "Country" table has foreign keys coming from tables currency and language which are only use as reference tables
(no insert or update are done on those 2 tables)

here are the corresponding mappings:

<class name="Country" >
<id name="countryCode" type="java.lang.String">
<generator class="assigned"/>
</id>

<property name="wording" type="java.lang.String" />
<property name="wording1" type="java.lang.String" />
<property name="jcbCountryCode" type="java.lang.String" />

<many-to-one name="currencyTable" class="CurrencyTable" not-null="true" />

<many-to-one name="languageList" class="LanguageList" not-null="true" />
<sql-insert callable="true">{ call insertCountry(?,?,?,?,?,?)}</sql-insert>
<sql-update callable="true">{? = call updateCountry(?,?,?,?,?,?)}</sql-update>

</class>


<class name="CurrencyTable" >
<id name="currencyCode" type="java.lang.String">
<generator class="assigned"/>
</id>
<property name="currencyName" type="java.lang.String" />
<loader query-ref="currencyload"/>
</class>


<class name="LanguageList" >
<id name="languageCode" type="java.lang.String">
<generator class="assigned"/>
</id>

<property name="languageLabel" type="java.lang.String" />
<loader query-ref="languageload"/>
</class>



here are the Oracle PL/SQL procedures:

CREATE OR REPLACE FUNCTION currencyload(id IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
BEGIN
OPEN st_cursor FOR
SELECT CURRENCY_CODE, CURRENCY_NAME FROM CURRENCY_TABLE WHERE CURRENCY_CODE=id;
RETURN st_cursor;
END;
--------
CREATE OR REPLACE FUNCTION languageload(id IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
BEGIN
OPEN st_cursor FOR
SELECT LANGUAGE_CODE, LANGUAGE_LABEL FROM LANGUAGE_LIST WHERE LANGUAGE_CODE =id;
RETURN st_cursor;
END;
-------
CREATE OR REPLACE FUNCTION updateCountry(
country_code IN VARCHAR2,
wording IN VARCHAR2,
wording_1 IN VARCHAR2,
currency_code IN VARCHAR2,
language_code IN VARCHAR2,
jcb_country_code IN VARCHAR2)
RETURN NUMBER IS
BEGIN
UPDATE COUNTRY
SET
WORDING=wording,
WORDING_1=wording_1,
CURRENCY_CODE= currency_code,
LANGUAGE_CODE= language_code,
JCB_COUNTRY_CODE = jcb_country_code
WHERE
COUNTRY_CODE=country_code;
COMMIT;
RETURN SQL%ROWCOUNT ;
END;
-----

CREATE OR REPLACE PROCEDURE insertCountry(
country_code IN VARCHAR2,
wording IN VARCHAR2,
wording_1 IN VARCHAR2,
currency_code IN VARCHAR2,
language_code IN VARCHAR2,
jcb_country_code IN VARCHAR2)
AS
BEGIN
INSERT INTO COUNTRY VALUES(country_code, wording, wording_1, currency_code,language_code,jcb_country_code);
COMMIT;
END;

-------------

when trying to save a country bean to database,with previously built currencyTable and languageList beans; the following error occurs:


1547 [http-8080-3] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 942, SQLState: 42000
21547 [http-8080-3] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00942: table or view does not exist

Hibernate Exceptioncould not retrieve snapshot: [com.experian.liege.hibernate.beans.CurrencyTable#EUR]
21563 [http-8080-3] WARN org.apache.struts.action.RequestProcessor - Unhandled Exception thrown: class java.lang.RuntimeException
21594 [http-8080-3] ERROR org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/ligd].[action] - "Servlet.service()" pour la servlet action a généré une exception
java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not retrieve snapshot: [com.experian.liege.hibernate.beans.CurrencyTable#EUR]

when processing the session.save(country) command in an action.

It seems that hibernate wantsto check if the reference objects (currencyTable and languageList) were modified before actually commiting the insert of the Country Object.

We had no success implementing custom load for those objets.


Thanks in advance.