Enabling sql trace in OC4J

Create a new dummy user in DB say test. Create a Connection in the
data-source.xml file with the min-connections="1" and max-connections="1".

On the DB side create a login trigger ,
CREATE OR REPLACE TRIGGER test_user_trg_after_logon
AFTER LOGON ON DATABASE
BEGIN
IF (user = 'TEST') THEN
DBMS_SESSION.SET_SQL_TRACE(true);
END IF;
END;

The sql trace files will be generated in the USER_DUMP_DESTINATION for the
database.

To check the sql's run the command
tkprof sys=no

The above procedure will not give you the bind variables. To get the bind variable , please
perform the following steps.

cd $OH/rdbms/admin/
connect / AS SYSDBA
dbmssupp.sql
SQL> GRANT execute ON dbms_support TO ;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

Create the stored procedure ,

CREATE OR REPLACE TRIGGER test_user_trg_after_logon
AFTER LOGON ON DATABASE
BEGIN
IF (user = 'SCOTT') THEN
dbms_support.start_trace (binds=>true);
END IF;
END;

The above steps will have the bind variables in the trace files.

To disable the tracing , perform the step as ,
drop trigger test_user_trg_after_logon;

Set the event 942 to capture ORA-942 errors

alter system set events '942 trace name errorstack level 12';
Try to find the udump directory

show parameters user_dump
user_dump_dest string /home/ias/infra101202/admin/ias1012/udump
cd /home/ias/infra101202/admin/ias1012/udump and find out the latest file.

Use SYS.DBMS_SYSTEM.SET_EV() procedure. Here is the specification for this procedure:

PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
SID: V$SESSION.SID
SE: V$SESSION.SERIAL#
EV: Event number. For example:
10046: SQL traces.
10053: Optimizer traces.
NNN : ORA-NNN errors.
65535: IMMEDIATE traces.
LE: Event level. For Event 10046 events:
0: Disable event.
1: PARSE, FETCH, EXEC, EXECUTION PLAN
4: Level 1 + BINDS
8: Level 1 + WAITS
12: Level 4 + Level 8
NM: Event name. For example:
ERRORSTACK.......: For error stack traces.
PROCESSSTATE...: For process states
SYSTEMSTATE.......: For System states.
''..................................: For CONTEXT FOREVER.
Sample:
Dumps PROCESSSTATE trace IMMEDIATELY in LEVEL 10:

SQL> exec dbms_system.set_ev(8,1056,65535,10,'PROCESSSTATE');
Dumps ERRORSTACK trace in LEVEL 3 on ORA-942 error:

SQL> exec dbms_system.set_ev(8,1060,942,3,'ERRORSTACK');
Dumps Event 10046 trace in LEVEL 8 for CONTEXT FOREVER:

SQL> exec dbms_system.set_ev(8,1060,10046,8,'');

How to Convert JDeveloper CMT Application to Sun Application Server

Create the EJB using the Jdeveloper , say based on the Emp table
from Scott schema.
Change the following methods , so that they throw the CreateException
For example ,
public Long ejbCreate() throws CreateException {
return null;
}
public Long ejbCreate(Long empno) throws CreateException {
setEmpno(empno);
return empno;
}
Create the JAR file and open the Sun Deploy tool ,
File -> Open and select the jar file created by the previous method.
here select Emp Entity object , here click on the General TAB
And Click on Sun Specific Settings , select the View as CMP DataBase
Create DataBase Mappings , and select the DataSource Vendor as Oracle and select OK.
Now Click on the Table Generation Settings and unselect Create Table and Drop Table check boxes.
Change the JNDI name to jdbc/OracleDS.
Now Click on Tools -> Deploy menu option , and select the
option return Client Jar in some directory. After this deploy the application.
This creates the file empClient.jar in the directory you have specified in the above step.
Create the file EmpClient.java file ,
package project2;
import java.util.Collection;
import java.util.Iterator;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.rmi.PortableRemoteObject;
public class EmpClient {
public static void main(String [] args) {
try {
final Context context = getInitialContext();
final EmpHome empHome =
(EmpHome) PortableRemoteObject.narrow( context.lookup( "Emp" ), EmpHome.class );
Emp emp;
// Use one of the create() methods below to create a new instance
// emp = empHome.create( );
// emp = empHome.create( empno );
// Retrieve all instances using the findAll() method (CMP Entity beans only)
final Collection coll = empHome.findAll();
final Iterator iter = coll.iterator();
while ( iter.hasNext() ) {
emp = ( Emp ) iter.next();
System.out.println( "empno = " + emp.getEmpno() );
System.out.println( "ename = " + emp.getEname() );
System.out.println( "job = " + emp.getJob() );
System.out.println( "mgr = " + emp.getMgr() );
System.out.println( "hiredate = " + emp.getHiredate() );
System.out.println( "sal = " + emp.getSal() );
System.out.println( "comm = " + emp.getComm() );
System.out.println( "deptno = " + emp.getDeptno() );
System.out.println();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
private static Context getInitialContext() throws NamingException {
// Get InitialContext for Embedded OC4J
// The embedded server must be running for lookups to succeed.
return new InitialContext();
}
}

2. Create the file META-INF\application-client.xml



ConverterClient

ejb/Emp
Session
project2.EmpHome
project2.Emp



3. Create the file , META-INF\sun-application-client.xml






ejb/Emp
Emp



Compile the above file using the command ,
set classpath=c:\empClient.jar;.;c:\sun\AppServer14\lib\j2ee.jar;c:\sun\AppServer14\lib\appserv-rt.jar
javac -d *.java
Now run the file using the command ,
java project2.EmpClient

Creating the Connection using the Sun Application Server

Create a data source in Sun J2ee container

Copy the files ojdbc14dms.jar and dms.jar file in to
C:\Sun\AppServer14\lib directory.

Create a connection pool with the following ,

oracle.jdbc.pool.OracleDataSource

resource type as javax.sql.DataSource

Enter the following fields

Password tiger
User scott
ServiceName ravi.idc.oracle.com
PortNumber 1522
ServerName incq128ad
URL jdbc:oracle:thin:@incq128ad:1522:ravi1

After this create JDBC Resources and name it as jdbc/OracleDS
and select the above connection pool.

Create a War file based on the test.jsp file with the following code ,


<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="java.sql.*,oracle.jdbc.driver.*,javax.naming.*,oracle.sql.*,javax.sql.*" %>

Employee ID:

<% if (request.getParameter("empID") != null) {
String empID = request.getParameter("empID");
InitialContext testContext = null;
DataSource testOC4JDataSource = null;
Connection testDatabaseConnection = null;
Statement testDatabaseStatement = null;
ResultSet testDatabaseResultSet = null;
try {
testContext = new InitialContext();
testOC4JDataSource = (DataSource) testContext.lookup("jdbc/OracleDS");
testDatabaseConnection = testOC4JDataSource.getConnection();
testDatabaseStatement = testDatabaseConnection.createStatement();
String testQuery = "SELECT * FROM SCOTT.EMP where EMPNO =" + empID;
testDatabaseResultSet = testDatabaseStatement.executeQuery(testQuery);
while (testDatabaseResultSet.next()) {
out.println("
" + testDatabaseResultSet.getString("ENAME"));
}
} catch (Throwable e) {
String err = e.toString();
System.out.println(err);
} finally {
try {
testDatabaseStatement.close();
testDatabaseConnection.close();
} catch ( Throwable e) {
String err = e.toString();
System.out.println(err);
}
}
}
%>



Create the war file in Jdeveloper and deploy the file using the Sun Admin Console