"Error while accessing porting layer for ORACLE database via getSessionId()

[Former Member]

Hi,

My ejb3.0 Entity is created from Emp table in scott/tiger schema of an Oracle 10g database. I am guessing I made some mistake creating the datasource or uploading the driver, because when I run my application, I get a long exception stack trace. The bottom-most entry in the stack trace is:

Caused by: com.sap.sql.log.OpenSQLException: Error while accessing porting layer for ORACLE database via getSessionId().

at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:148)

at com.sap.sql.jdbc.direct.DirectConnectionFactory.createPooledConnection(DirectConnectionFactory.java:527)

at com.sap.sql.jdbc.direct.DirectConnectionFactory.createDirectPooledConnection(DirectConnectionFactory.java:158)

at com.sap.sql.jdbc.direct.DirectConnectionFactory.createDirectPooledConnection(DirectConnectionFactory.java:118)

at com.sap.sql.connect.factory.PooledConnectionFactory.createPooledConnection(PooledConnectionFactory.java:119)

at com.sap.sql.connect.factory.DriverPooledConnectionFactory.getPooledConnection(DriverPooledConnectionFactory.java:38)

at com.sap.sql.connect.datasource.DBDataSourceImpl.createPooledConnection(DBDataSourceImpl.java:685)

at com.sap.sql.connect.datasource.DBDataSourcePoolImpl.matchPool(DBDataSourcePoolImpl.java:1081)

at com.sap.sql.connect.datasource.DBDataSourcePoolImpl.matchPooledConnection(DBDataSourcePoolImpl.java:919)

at com.sap.sql.connect.datasource.DBDataSourcePoolImpl.getConnection(DBDataSourcePoolImpl.java:67)

at com.sap.engine.core.database.impl.DatabaseDataSourceImpl.getConnection(DatabaseDataSourceImpl.java:36)

at com.sap.engine.services.dbpool.spi.ManagedConnectionFactoryImpl.createManagedConnection(ManagedConnectionFactoryImpl.java:123)

... 90 more

[Former Member · Accepted solution]

Actually, now (after the GRANT described in my reply before) the Exception has changed to:

Caused by: com.sap.sql.log.OpenSQLException: Error while

accessing porting layer for ORACLE database via

<b>getDatabaseHost</b>().

at com.sap.sql.log.Syslog.createAndLogOpenSQLException

(Syslog.java:148)

at com.sap.sql.jdbc.direct.DirectConnectionFactory.

createPooledConnection(DirectConnectionFactory.java:527)

at com.sap.sql.jdbc.direct.DirectConnectionFactory.

createDirectPooledConnection(DirectConnectionFactory.java:158)

at com.sap.sql.jdbc.direct.DirectConnectionFactory.

createDirectPooledConnection(DirectConnectionFactory.java:118)

at com.sap.sql.connect.factory.PooledConnectionFactory.

createPooledConnection(PooledConnectionFactory.java:119)

at com.sap.sql.connect.factory.DriverPooledConnectionFactory.

getPooledConnection(DriverPooledConnectionFactory.java:38)

at com.sap.sql.connect.datasource.DBDataSourceImpl.

createPooledConnection(DBDataSourceImpl.java:685)

at com.sap.sql.connect.datasource.DBDataSourcePoolImpl.

matchPool(DBDataSourcePoolImpl.java:1081)

at com.sap.sql.connect.datasource.DBDataSourcePoolImpl.

matchPooledConnection(DBDataSourcePoolImpl.java:919)

at com.sap.sql.connect.datasource.DBDataSourcePoolImpl.

getConnection(DBDataSourcePoolImpl.java:67)

at com.sap.engine.core.database.impl.DatabaseDataSourceImpl.

getConnection(DatabaseDataSourceImpl.java:36)

at com.sap.engine.services.dbpool.spi.

ManagedConnectionFactoryImpl.createManagedConnection(ManagedConnectionFactoryImpl.java:123)

... 90 more

[Former Member · Accepted solution]

Hi,

it seems that your database user doesn't have the rights to access the objects in the Oracle dictionary. The user

doesn't have the SELECT_CATALOG_ROLE role or this role is inactive. Please use the following statement to check

whether the role exists (as user SYS or SYSDBA):

select granted_role, default_role

from dba_role_privs

where grantee = '<user>'

If the output doesn't include the SELECT_CATALOG_ROLE line

SELECT_CATALOG_ROLE YES

then use the following command to assign the role to the user:

grant SELECT_CATALOG_ROLE to <user>

Repeat the above statement to recheck the settings. If the role exists, but the value of the DEFAULT_ROLE column is set to NO, change this by executing:

alter user <user> default role <role1>, <role2>, ...

where <role1> is the SELECT_CATALOG_ROLE and the following roles are all other user roles that were already set to YES.

Kind regards

Ralf

[Former Member]

Thanks for your reply.

There was no nested exception, this was the last entry in the stack trace.

I ran your select statement in sqlplus and I got

"Table or View does not exist"

which meant that scott user did not have access privileges to V$MYSTAT.

I connected as SYS and granted the privileges ( I had to grant it on V_$MYSTAT ). Now the select statment runs fine but my problem persists.

[Former Member]

Hi,

the porting layer for Oracle tries to obtain the connection's session id by executing the following statement:

SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1

It seems that this statement failed. There should be a nested SQLException thrown by Oracle, that describes the reason for the OpenSQLException stated above.

Could you please check whether there is such a nested exception in your stack trace?

Many thanks in advance

Ralf Kürsch