JBO-28006 exceptions when running ADF BC application

Written By: Pascal Alma on January 2, 2007 No Comment

As you may know it is a good practice to have a distinction between the owner and the user of your database objects. With this distinction you make sure that the user that is connecting to the database by the connection pool in iAS (or any other application server) is not able to drop or alter objects (no DDL is allowed by that user).

The way DBA's like it most is that you have one database user that is used to create the objects and one user that is granted a custom made role. This role gets the necessary DML rights to the created objects. The connection pool uses this last user to connect to the database.

Now why am I telling you this? Well, we had this setup on my last project. Except not in the development and test environment. In those environments we used the object owner to connect to the database with our ADF BC application.

So after we deployed the application to our production server and the users started their tests we got issues about not being able to delete records in the application. That was weird because we could do this in our development an test environment. After searching the log files on the production environment we found the following line: 'oracle.jbo.PCollException: JBO-28006:Could not create persistence table PS_TXN_seq'.

Although there were occurring errors they didn't show up in our application. The application just said 'no changes to save'! After reading this error message we quickly realized what was happening: the ADF BC is using the table PS_TXN and the sequence PS_TXN_SEQ for deserializing purposes. If these objects don't exist, the framework tries to create them. But since we were now connected with the user that only had DML rights to existing objects, it couldn't create these objects.

The solution was to have the objects created by the object owner and grant access to it via the custom made role.

Here is the code to have the necessary objects created:

PLAIN TEXT

SQL:

CREATE TABLE "OBJECT_OWNER"."PS_TXN"
( "ID" NUMBER(20,0),
"PARENTID" NUMBER(20,0),
"COLLID" NUMBER(10,0),
"CONTENT" BLOB,
"CREATION_DATE" DATE DEFAULT sysdate,
CONSTRAINT "PS_TXN_PK" PRIMARY KEY ("COLLID", "ID") ENABLE
) ;
CREATE SEQUENCE "OBJECT_OWNER"."PS_TXN_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ;

And here is the code to grant access to it via the role:

PLAIN TEXT

SQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON PS_TXN TO user_role
/
GRANT SELECT ON PS_TXN_SEQ TO user_role
/

and to create synonyms for them:

PLAIN TEXT

SQL:

CREATE public synonym PS_TXN FOR PS_TXN
/
CREATE public synonym PS_TXN_SEQ FOR PS_TXN_SEQ
/

After performing these scripts the application runs fine and deleting records is possible in all environments :-). Hope this helps if you're running into the same issue.