Search This Blog

Wednesday, 26 September 2007

Producing parameterized ADF JSF pages via a custom app module method

Steve has an article in the oracle Magazine which I ran through recently showing how to Produce Parameterized Pages in JDeveloper 10.1.3. I like how it's all done declaratively as shown in the link below.

http://www.oracle.com/technology/oramag/oracle/07-jul/o47frame.html

However at times you may won't to use the same ID being passed as a page parameter and setup various view objects using that ID prior to rendering the page. In that scenario I normally use a custom app module method which requires just the one action to be invoked, which then sets the view object parameters for multiple view objects for me. In the end my page binding only has to invoke my custom app module method and everything is done there for as many view objects which need to be setup prior to rendering the page.

Custom App Module Method Example

public void prepareEmpForView (String _empId)
{
// set as many view object where clauses as needed

GetEmpRecordImpl empVO = getGetEmpRecord();
empVO.setWhereClauseParam(0, _empId);
empVO.executeQuery();

}

Page Bindings

Note: Ensure the custom app module is exposed as a client method in the app module wizard before completing the bindings for the page

1. Right click on the page (JSP or JSPX) and select "Go To Page Definition"
2. Select View -> Structure
3. Right click on the bindings node and select "Insert Inside Bindings -> Method Action"
4. Select the app module data control
5. Select the method you wish to invoke in our example "prepareEmpForView"
6. Enter in the path to parameter values in my case it's usually the HTTP request parameter.

eg: #{param.id}

7. Press OK

Now all we need to do is invoke this method before the page is rendered.

8. Right click on the executables node and select "Insert inside Executables -> Invoke Action"
9. Set the id to what you like and ensure that the binds drop down is set to the "Method Action" in this case prepareEmpForView

Thats all you need now when the page is invoked it's vital that a page parameter ?id=xxxx is passed to the page as the method action requires that to invoke the custom app module method prior to rendering the page.

In this example not much point doing it this way if you only have to setup one view object , but if you have more view objects to setup and other code to invoke then I would do it as I have shown here.

Thursday, 20 September 2007

How to populate v$session.program from mid tier data source connections

I am constantly asked how to distinguish the different JDBC connections that come from the middle tier. His an example on how to do this OAS 10.1.3.x.

1. Create a data source as follows which is using the JDBC driver as the factory class (oracle.jdbc.driver.OracleDriver)

<managed-data-source
connection-pool-name="ScottConnectionPool"
jndi-name="jdbc/scottDS"
name="jdbc/scottDS"/>
<connection-pool
name="ScottConnectionPool"
initial-limit="5"
min-connections="5">
<connection-factory
factory-class="oracle.jdbc.driver.OracleDriver"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@//papicell-au2.au.oracle.com:1521/lnx102">
<property name="v$session.program"
value="OAS10132-apple-scottpool"/>
</connection-factory>
</connection-pool>


2. In order to populate the PROGRAM column you need to set the property as shown below.

<property name="v$session.program"
value="OAS10132-apple-scottpool">

3. When the data source is started it will create 5 connections so we can then run the following SQL to see if it worked once the connection pool has been started.

set head on feedback on

set pages 999
set linesize 120

prompt
prompt SCOTT sessions

col machine format a25
col username format a15
col username format a8
col program format a25

select
username,
program,
status,
last_call_et seconds_since_active,
to_char(logon_time, 'dd-MON-yyyy HH24:MI:SS') "Logon"
from v$session
where username = 'SCOTT'
/


The result is as follows which clearly shows that the PROGRAM field has been populated by our mid tier data source:


Tuesday, 18 September 2007

OracleDatabaseMetaData.getLobPrecision Returns Precision Of -1

When using the following code with the 10.2.0.3 JDBC driver it always results in -1 for the lob precision.

Code:

System.out.println( "LOB Precision: " +
((oracle.jdbc.OracleDatabaseMetaData)dbMeta).getLobPrecision());

Output:

LOB Precision: -1

The same code would give the lob precision correctly when using the 10.1.0.5 JDBC driver. This is an undocumented property which caused OracleDatabaseMetaData.getLobPrecision to return the string "-1". The JDBC driver now returns lob precision as -1 since, the precision value cannot fit into an int return value of the method.

To overcome this issue you can now use code as follows with the 10.2.0.3 JDBC Driver.

System.out.println( "LOB Precision: " +
((oracle.jdbc.OracleDatabaseMetaData)dbMeta).getLobMaxLength());

Monday, 17 September 2007

Adding an estimated row count to a table in ADF

I used ADF JSP a lot back in JDeveloper 10.1.2 and today I needed to do something which I did a lot back then in ADF JSF Jdeveloper 10.1.3. Whenever I wrote ADF web based applications I would always like to show the amount of records a particular table contained at the top of the table. In ADF JSP JDeveloper 10.1.2 I would commonly use a TIP uix tag to achieve this and eventually I found what I could use in the 10.1.3 ADF JSF world. The tag is af:panelTip and his a small example.

<af:paneltip>
<af:outputformatted value="Total of #{bindings.DeptView1Iterator.estimatedRowCount} rows">
</af:panelTip>

His a simple screen shot showing how it looks.

Thursday, 13 September 2007

Fast way to determine the exact 11g JDBC driver version

With the new 11g JDBC driver you can now easily determine the JDBC driver full version as follows without having to use conn.getMetaData() which is alot easier.

> java -jar ojdbc5.jar

Output:

Oracle 11.1.0.6.0-Production JDBC 3.0 compiled with JDK5

Note: If you want to get the version of ojdbc6.jar then you will need to make sure you use JDK 1.6

> d:\jdev\jdk16\bin\java.exe -jar ojdbc6.jar

Output:

Oracle 11.1.0.6.0-Production JDBC 4.0 compiled with JDK6

ORA-00904 From getFunctionColumns Method Of The Interface DatabaseMetaData

When using the following code with the 11g JDBC driver against a 10.2.0.1 database the following error occurs.

Code:

DatabaseMetaData mdata = conn.getMetaData();
...
rset = mdata.getFunctionColumns(null, null, "ADDTWONUMBERS", "N1");
..

Runtime error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00904: "PROC"."OBJECT_ID":
invalid identifier
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)


It turns out then when using the latest 10g database patch set 10.2.0.3 this works fine so it seems this was a database bug / issue fixed in 10.2.0.3. I think it's always best to be on the latest database patch set if possible.

Tuesday, 11 September 2007

JBO-29000: Rollbackexception When Running a Long Running Query From ADF JSF Page

At times when running an ADF JSF page which has a long running query the following error can occur.

JBO-29000: Unexpected exception caught: javax.ejb.EJBException,
msg=An exception occurred during transaction completion: ; nested exception is:
javax.transaction.RollbackException: Timed out

The transaction timeout interval is controlled by the 'transaction-timeout' attribute in transaction-manager.xml, in $JDEV_HOME\j2ee\home\config or in the embedded config file $JDEV_HOME\jdev\system\oracle.j2ee.10.1.3.XX.XX\embedded-oc4j\config.

By default the timeout for a transaction is 30 seconds.

To avoid this you can alter this to be a larger value say 60 seconds and retry your page once the container is restarted.

<transaction-manager
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/oracleas/schema/transaction-manager-10_0.xsd"
transaction-timeout="60"
max-concurrent-transactions="-1">

Thursday, 6 September 2007

A createinstance issue to be aware of in OAS 10.1.3.x

When 10.1.3.0 came out and the only way to create a new oc4j instance was to use the createinstance BAT file on windows or script on unix. Once 10.1.3.1 came out you could create a new instance in ASC but I still use the createinstance script and doing so today ran into an issue as described below on OAS 10.1.3.1:

1. ran a command to create a new instance as follows

createinstance -instanceName ok

2. when promoted for the password I normally use "welcome1" but this time I thought I would use a different password and when I did that the instance still got created but an execption was thrown and it failed to start it.

[SEVERE: CoreRemoteMBeanServer.fetchMBeanServerEjbRemote Error reading application-client descriptor: Error communicating with server: Lookup error: javax.naming.AuthenticationException: Not authorized; nested exception is:
javax.naming.AuthenticationException: Not authorized; nested exception is:


3. So I started it manually as follows which worked fine in the end.

opmnctl startproc process-type=ok

4. Then when I logged into ASC and tried to administer this instance the following error occurred

Unable to make a connection to OC4J instance ok on Application Server 1013linux_purple.papicell-au2.au.oracle.com. A common cause for this failure is an authentication error. The administrator password for each OC4J instance in the Cluster must be the same as the administrator password for the OC4J instance on which Application Server Control is running.

It turns out that in order for ASC to administer the OC4J instance the password must be the same password used by ASC application which in my case was the home container. This being the first time I decided to use a different password I had never seen this issue before. In the end I changed the password for my instance "ok" to be welcome1 like the "home" container so I could administer it from ASC.

The following link shows how to change the password:

http://download.oracle.com/docs/cd/B31017_01/core.1013/b28940/em_app.htm#BABFAHBH

I decided to change it manually using the instructions under this heading:

A.2.5 Using the Command Line to Change the oc4jadmin Password for a Remote OC4J Instance

Monday, 3 September 2007

My First BPEL demo

I followed this how to on OTN using JDeveloper 10.1.3.1. All though the how to is for JDeveloper 10.1.2 it was easy enough to do it in JDeveloper 10.1.3 as the steps are similar.

http://www.oracle.com/technology/obe/obe_as_1012/integration/bpel/jdev_sect/first_bpel_proj/1st_bpel_prj.htm

All though just a simple hello world BPEL demo what I found interesting is that once you deploy your BPEL project the actual BPEL deployed process (HelloWorld in my case) is automatically published as a web service making it easily accessible from any client. In my case I accessed my BPEL process using a J2SE java client and then an ADF JSF Web Client.

Impressed from what I have seen on my first run with BPEL and the SOA suite, hope to play around with this a little bit more when time permits.

Friday, 24 August 2007

OC4J 10.1.3 j2ee-logging.xml console handler example

A note for myself , as I was trying to find an example on this:

j2ee-loging.xml

<log_handler
name='console-handler'
class='java.util.logging.ConsoleHandler'
formatter='oracle.core.ojdl.logging.SimpleFormatter'
level='FINEST' />

....

<logger name="simplelogging" level="FINEST" useparenthandlers="false">
<handler name="console-handler">
</logger>


Java Code

....
public class LoggingTest extends HttpServlet
{
private static Logger logger =
Logger.getLogger("simplelogging");
....

Date d = new Date();
String s = d.toString();
logger.fine("pas simple logging test the current time is " + s);
// log levels
logger.log(Level.SEVERE,"pas simple logging test SEVERE!");
logger.log(Level.WARNING,"pas simple logging test WARNING!");
logger.log(Level.INFO,"pas simple logging test INFO!");
logger.log(Level.CONFIG,"pas simple logging test CONFIG!");
logger.log(Level.FINE,"pas simple logging test FINE!");
logger.log(Level.FINER,"pas simple logging test FINER!");
logger.log(Level.FINEST,"pas simple logging test FINEST!");

....

Output Location

The output using a console handler will go to the opmn log file for the container being used.

Eg:

$ORACLE_HOME/opmn/logs/<container-log-file>

Tuesday, 21 August 2007

11G JDBC driver ojdbc5.jar and ojdbc6.jar

After installing 11g on linux you will find that the JDBC 11g driver is shipped as two files and ojdbc14.jar is no longer in there.
  • $ORACLE_HOME/jdbc/lib/ojdbc5.jar - JDK 1.5
  • $ORACLE_HOME/jdbc/lib/ojdbc6.jar - JDK 1.6
So if your using JDK 1.5 use ojdbc5.jar and if using JDK 1.6 use ojdbc6.jar. JDK 1.4 is no longer supported for the 11g drivers so thats the reason no ojdbc14.jar exists now for 11g. The documentation below clearly specifies support for JDK 1.4 is no longer available in the 11g JDBC drivers.
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/getsta.htm#i1008205

SYS.ANYDATA Oracle Type support in 11g JDBC Driver

This release of Oracle JDBC driver provides a Java interface to access SYS.ANYTYPE and SYS.ANYDATA Oracle types. His a small piece of code showing how to access a SYS.ANYDATA column from JDBC 11g.

Table definition:

create table anydata_table
(col1 number,
col2 sys.anydata)
/
Java Code snippet:

stmt = conn.createStatement();
rset = stmt.executeQuery("select * from anydata_table");
int i = 0;
while (rset.next())
{
i++;
System.out.println("** Row " + i);
ANYDATA anydata = (ANYDATA)rset.getObject(2);
Datum embeddedDatum = anydata.accessDatum();
TypeDescriptor typeDescriptor = anydata.getTypeDescriptor();
int typeCode = typeDescriptor.getTypeCode();
if (typeCode == TypeDescriptor.TYPECODE_TIMESTAMP)
{
// the embedded object is a DATE
TIMESTAMP datedatum = (TIMESTAMP)embeddedDatum;
System.out.println("Timestamp Type");
System.out.println(datedatum.stringValue());
}
else if (typeCode == TypeDescriptor.TYPECODE_NUMBER)
{
// the embedded object is a NUMBER
NUMBER numberdatum = (NUMBER)embeddedDatum;
System.out.println("Number Type");
System.out.println(numberdatum.stringValue());
}
else if (typeCode == TypeDescriptor.TYPECODE_VARCHAR2)
{
// the embedded object is a VARCHAR2
String varchardatum = embeddedDatum.stringValue();
System.out.println("VARCHAR2 Type");
System.out.println(varchardatum);
}
}

More information on this can be found in the documentation

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oraint.htm#CHDEBICE

Monday, 20 August 2007

Database Change Notification in 11g JDBC driver

With the release of the 11g JDBC driver I decided to give Database Change Notification a test drive which is now part of the 11G JDBC driver. I tested this against a 10.2.0.3 database and it worked well, and I can see this being a very popular new feature.

An example and documentation can be found here:

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/dbmgmnt.htm#CHDEJECF

The 11g JDBC driver can be downloaded from here:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

His some output of the database change event as a result of an INSERT into a table I had previously registered a change event for.

DCNListener: got an event (pas.jdbc.DCNListener@1a001ff)
Connection information : local=papicell-au.au.oracle.com/10.187.80.135:47632, remote=papicell-au2.au.oracle.com/10.187.80.136:15087
Registration ID : 6
Notification version : 0
Event type : OBJCHANGE
Database name : lnx102
Table Change Description (length=1)
operation=[INSERT], tableName=SCOTT.DEPT, objectNumber=101051
Row Change Description (length=1):
ROW: operation=INSERT, ROWID=AAAYq7AAEAAAGv+AAA

Tuesday, 14 August 2007

Obtaining the trace file name from JDBC when sql tracing is enabled

When turning on SQL trace in a JDBC thin program as follows I needed to get the actual trace file name at the time.

// turn on sql_trace
stmt = conn.createStatement();
stmt.execute("alter session set sql_trace=true");
System.out.println("\nSQL_TRACE=TRUE has been started \n");

All though not difficult I found this piece of SQL that I could run to determine the trace file being used by my program, which I executed from my JDBC program prior to turning on SQL tracing, so that at the end of the program I knew exactly what trace file I needed to analyze.

final String traceSQL =
"select c.value || '/' || d.instance_name || '_ora_' || " +
"to_char(a.spid, 'fm99999') || '.trc' "
+
"from v$process a, v$session b, v$parameter c, v$instance d " +
"where a.addr = b.paddr " +
"and b.audsid = userenv('sessionid') " +
"and c.name = 'user_dump_dest'";
......


public void displayTraceFileName (Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(traceSQL);
rset.next();

String fileName = rset.getString(1);
System.out.println("TRACE FILE NAME : " + fileName);
}

Example output:

TRACE FILE NAME : /home/oracle/oracle/product/10.2.0/db_1/admin/lnx102/udump/
lnx102_ora_8462.trc


Note: If your on a windows the SQL differs slightly, as shown below.

rem
rem User must have access to v$session, v$process,
rem v$paramater and v$instance views

rem

select c.value || '\ORA' || to_char(a.spid, 'fm00000') || '.trc'
from v$process a, v$session b, v$parameter c
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest';

Friday, 10 August 2007

Removing the data-sources.xml from deployment archives in JDeveloper

I am constantly asked how to remove the data-sources.xml file from a deployment EAR file. The issue here is JDeveloper is automatically configured to bundle a data-sources.xml file during deployment which is basically all your JDeveloper connections created as a entry for each connection. The easy way to avoid the data-sources.xml being bundled during deployment is as follows BUT there is a catch to doing this.

Steps
  1. Choose Tools > Preferences to display the Preferences dialog.

  2. Select Deployment on the left side.

  3. Deselect Bundle Default data-sources.xml During Deployment.

  4. Click OK.

So that will ensure you don't end up bundling the data-sources.xml during deployment , as most people have their data sources created at the container level so there is no need to deploy data sources in each application.

The problem is once you deselect the option as shown above if your running an application in the embedded OC4J server and it requires the data-sources.xml then you will get runtime issues. The trick is to switch it on while testing in the embedded OC4J server and then switch it off at deployment time.

This issue is documented here:

http://download-west.oracle.com/docs/html/B25947_01/deployment_topics013.htm