Oracle 9i vs 10g RAC

[TheExcession]

This is coppied and pasted from an email s oliekly the formatting won't work. Where you see HHME that is my java prrogram.

So here we go......

When I run my copy of the HHME against the Oracle9i database that is running locally here everything works fine.

When I try to run the HHME against the new Oracle RAC DB I am getting an "Invalid Column Name'" Exception.

The Exception is thrown when trying to ascertain the APN ID using the following stored procedure:

This Stored Procedure is the same on both Hosts.

CREATE OR REPLACE PACKAGE BGANGetCallTypePAC AS
TYPE RecCursor IS REF CURSOR;

FUNCTION BGAN_getCallType ( V_APN BGAN_APN.APN_NAME%type )
RETURN RecCursor;
END BGANGetCallTypePAC;
/
show errors;
CREATE OR REPLACE PACKAGE BODY BGANGetCallTypePAC AS
FUNCTION BGAN_getCallType ( V_APN BGAN_APN.APN_NAME%type )
RETURN RecCursor IS RecRefCursor RecCursor;
BEGIN
OPEN RecRefCursor FOR
SELECT
ID,
APN_TYPE,
ACTIVATE_FIREWALL
FROM BGAN_APN WHERE
APN_NAME = V_APN;
RETURN RecRefCursor;
EXCEPTION
WHEN OTHERS THEN
Rollback;
Raise;

END;
END BGANGetCallTypePAC ;
/
show errors;



Bear with me whilst I try to work through what we are dealing with:

On Oracle9i: - Table def:
SQL> desc BGAN_APN
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3Cool
APN_NAME VARCHAR2(255)
APN_DESCRIPTION NOT NULL VARCHAR2(255)
APN_TYPE VARCHAR2(16)
ACTIVATE_FIREWALL NUMBER(3Cool

On Oracle9i: - Table data

SQL> SELECT ID, APN_TYPE, ACTIVATE_FIREWALL FROM BGAN_APN WHERE APN_NAME ='xantic.bgan.inmarsat.com';

ID APN_TYPE ACTIVATE_FIREWALL
---------- ---------------- -----------------
0 BGAN 1



On Oracle 10g - Table def:
SQL> desc BGAN_APN
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3Cool
APN_NAME VARCHAR2(255)
APN_DESCRIPTION NOT NULL VARCHAR2(255)
APN_TYPE NOT NULL VARCHAR2(16)
ACTIVATE_FIREWALL NOT NULL NUMBER(3Cool


On Oracle 10g - Table data

SQL> SELECT ID, APN_TYPE, ACTIVATE_FIREWALL FROM BGAN_APN WHERE APN_NAME ='xantic.bgan.inmarsat.com';

ID APN_TYPE ACTIVATE_FIREWALL
---------- ---------------- -----------------
0 BGAN 1



So, as we can see both table defs are the same and both Stored Procedures are the same, and both tables contain the same row data:


Now, when it comes to the Java inside the HHME:

The call into the Stored Procedure works fine, until we come to enumerate the Result Set: - everything goes accordingly until I try to read the 'ID' cloumn

(Incidently - at this stage I have already made a call into the database to validatethe NAS Identifier and NAS IP Address - this works fine - so I am 99.999% certain that the DB inteface is working correctly)

So the HHME Java goes as follows:

public Vector getBGANCallType(String _apn) throws HHMEException
{
Vector v = new Vector();
v.addElement(new Integer(CHAR));
v.addElement(_apn);
StoreProcedure sp = new StoreProcedure(conn);
Vector result = null;
ResultSet rSet;

try
{
rSet = sp.execProcedure("BGANGetCallTypePAC.BGAN_getCallType",v);
}
catch (SQLException e)
{
throw new HHMEException(e.getMessage());
}

try
{
if(rSet.next())
{
result = new Vector();
result.add(new Integer(rSet.getInt("ACTIVATE_FIREWALL")));
result.add(rSet.getString("APN_TYPE"));
result.add(rSet.getString("ID")); // this fails
}
}catch(SQLException e)
{
debugLog("getBGANCallType(): SQL exception in executing a Procedure - " + e.getMessage());
HHMEException he = new HHMEException("getBGANCallType(): SQL Exception - " + e.getMessage());
throw he;
}

try
{
if(rSet != null)
{
rSet.close();
}
sp.closeCallable();
}catch(SQLException e)
{
debugLog("getBGANCallType(): SQL exception in closing the ResultSet - " + e.getMessage());
HHMEException he = new HHMEException("getBGANCallType: SQL Exception in closing the ResultSet.");
throw he;
}

return result;
}


Now:

All of this works fine up until result.add(rSet.getString("ID"));

When debugging in Eclipse - I can see the correct 'ACTIVATE_FIREWALL' values and the correct 'APN_TYPE' come back.

However an invalid column name exception is thrown when I try go get the 'ID' value:

I was thinking that maybe because the column is defined as an integer this might be a problem with 10g but even when I change this line of Java to

result.add(new Integer(rSet.getInt("ID")));

I get the same error: RadiusObj: getBGANCallType(): Exception, Message = HHMEException: getBGANCallType(): SQL Exception - Invalid column name


Utterly lost as what to do about this - can anyone help?

[jimothy]

Get your hands on PL/SQL developer. This allows you to debug the PL/SQL and see whats wrong. I think its available on a 30 day trial.

[3200ac]

the issue *might* be use of ref cursors - remember that a ref cursor is a pointer to a result set, and passing a ref cursor to another database instance (for example using a database link) isn't appropriate (the Oracle 9i docs state this explicitly). Possible that RAC environment is running into a variant of this feature...

[TheExcession]

Te only thing I've got to go on at the moment is the fact that I'm pulling stuff fro mthe result set in a different order to that specified in the SQL:

SQL: SELECT
ID,
APN_TYPE,
ACTIVATE_FIREWALL
FROM BGAN_APN

Java:
result.add(new Integer(rSet.getInt("ACTIVATE_FIREWALL"));
result.add(rSet.getString("APN_TYPE");
result.add(rSet.getString("ID"); // this fails

However I can't see this being the problem as both the ACTIVATE_FIREWALL and APN_TYPE calls worked - and they are in a different order too

[3200ac]

you're using rSet.getString method, is there an equivalent getValue or getInt method that you can use as an alternative?

[TheExcession]

Thanks for the input guys - seem to have fixed it now though.

I just recompiled the stored procedure again in Oracle.

I guess the sql got modified but the procedure was never recompiled.

Too many late nights....

cheers
Ex

[3200ac]

most of the PL/SQL editors out there are pretty good at highlighting out of date/invalid modules, my own favourite is SQL Navigator by Quest, might help in future