Wednesday, 26 September 2007

Creating a data source from JDeveloper to a RAC 10.2.0.3 configuration

Creating a data source in JDeveloper that is using a RAC configuration URL can easily be achieved in JDeveloper by simply created a database connection and using a custom URL as shown below.

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)

(HOST=aulnx11-vip.au.oracle.com)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)

(HOST=aulnx12-vip.au.oracle.com)(PORT=1521))
(CONNECT_DATA=(service_name=TGDG1_S1)))

This works I have done it many times but when you run your J2EE applications in the embedded server it will simply use the first host in the list, in this case aulnx11-vip.au.oracle.com. That may be all you need in which case setting up a database connection this way is fine, but typically when deployed to OAS you would set up your data source to use some connection pool properties in which case when testing your application you may invoke separate browser windows expecting to see connections to the various RAC nodes. This can also be done from JDeveloper when testing your application in the Emdedded OC4J Server using Tools -> Embdedded OC4J Server Preferences dialog. His how it's done

Note: Assuming a connection name in the IDE as "RAC10203".

1. Select Tools ->
Embdedded OC4J Server Preferences
2. Press the + symbol to expand the current workspace node
3. Press the + symbol to expand the Data Sources node

You will find 3 different type of entries for each connection in JDeveloper which basically allows for the 3 types supported in OC4J 10.1.3.x. That's a native data source, managed data source and a connection pool entry used by a managed data source. For more information about data sources refer to the following

Oracle® Containers for J2EE Services Guide
10g (10.1.3.1.0)

Part Number B28958-01
Data Sources

4. Click on the connection pool entry know as "jdev-connection-pool-RAC10203"
5. Set the initial limit to 4, max connections to 6 and
min limit to 4.
6. Click on the the managed data source "jdev-connection-managed-RAC10203" you will see that it's automatically setup to use the connection pool "jdev-connection-pool-RAC10203"

This is the default setup for each connection in jdeveloper and is the recommended way to use data sources in OC4J 10.1.3.x
.

7. Press OK to dismiss the dialog

Now when we run your J2EE application in the embedded OC4J server it will create 4 connections when the data source is started which will load balance between our RAC nodes in our case the 2 RAC nodes are
aulnx11 and aulnx12.

That will then ensure when you run your J2EE application in the embedded server that your database connection setup to access a RAC configuration can use either node depending on which connection is handed out to the page as the data source was setup with 4 connections and most likely 2 will belong to
aulnx11 and the other 2 to aulnx12.

This becomes more important at deployment time but gives you the chance to setup JDeveloper as if your running in an OAS environment with a data source using a balance of connections between your RAC instances.

His what the data source entry looks like which exists on the folder where your workspace files exist. The file with have a name $JDEV_WORK_DIR\{workpsace-folder}\{workspace-folder}-data-sources.xml:


<connection-pool
name="jdev-connection-pool-RAC10203"
initial-limit="4"
max-connections="6"
min-connections="4">
<connection-factory
factory-class="oracle.jdbc.pool.OracleDataSource"
user="scott"
password="->DataBase_User_i9JHwrMdRZMa2v5bKtQ3d8muOP6LzyvA"
url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=aulnx11-vip.au.oracle.com)
(PORT=1521))(ADDRESS=(PROTOCOL=TCP)
(HOST=aulnx12-vip.au.oracle.com)
(PORT=1521))(CONNECT_DATA=(service_name=TGDG1_S1)))"/>
</connection-pool>
<managed-data-source
name="jdev-connection-managed-RAC10203"
jndi-name="jdbc/RAC10203DS"
connection-pool-name="jdev-connection-pool-RAC10203"/>

Now you can simply use this same data source entry on OAS when your ready
to deploy to OAS.

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.