SQL Logging

Sooner or later you'll run in to a problem with Granite and the only hint you'll have as to the problem is a cryptic SQLException error message being shown in the client. Unfortunately, the error often doesn't give enough information to find out exactly what went wrong. But fear not! With a few configuration changes on the server, you can instruct Granite to log SQL statements as they're being run. Then a quick scan of the logs should quickly lead you in the right direction.

On the Granite server, modify the file $DOMAIN_HOME/Xng_config/granite-config.xml. Find the attributes listed below and set them accordingly.

Next, modify $DOMAIN_HOME/Xng_config/log4j.xml. Find the appenders named SQL and FAILED-SQL. The Threshold value for these should be set to ALL.

Restart the application server, and your sql.log and failed-sql.log files should begin to fill with data as t he application is used. Once you've hit the error again, you should easily find it in one of those two logs:

[18260556000000]|[]|[03 Dec 2011 08:01:05,181]|[com.granite.sql.failures]|[[ACTIVE]
ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)']|[WARN]|[Operation
insert into SITE_INST (SITE_INST_ID, SITE_HUM_ID, CLLI, NUM, BASE_NUM, LATITUDE,
LONGITUDE, RESTRICTIONS, CONTACTS, ADDRESS, POST_CODE_1, POST_CODE_2, CITY,
STATE_PROV, COUNTY, COUNTRY, ROOM, FLOOR, NPA_NXX, COMMENTS, LAST_MOD_TS,
LAST_MOD_BY, INST_VER, LOCK_TS, LOCK_BY, PARENT_SITE_INST_ID, STATUS,
FPLAN_ DEPTH, FPLAN_WIDTH, FPLAN_HEIGHT, FPLAN_NAME, FPLAN_ORIGIN_X,
FPLAN_ORIGIN_Y) values (SITE_INST_ID.nextval,'ASDF',null,'BTS',null,
null,null,null,null,null,null, null,null,null,null,null,null,null,
null,null,2011-12-03 08:01:05.092,'sysadm',null,null,null,null,
'Ordered',null,null,null,null,null,null)
failed.]
java.sql.SQLSyntaxErrorException: ORA-01950: no privileges on tablespace 'USERS'