Search This Blog

Thursday, 4 September 2008

Displaying OC4J JVM MBean Statistics from a JMX Client

The JVM Mbean within an OC4J 10.1.3.x container has some useful statistic which are displayed in ASC MBean browser. Here is some code which shows how to obtain those from a JMX client.

So the MBean we want to access is as follows:

oc4j:j2eeType=JVM,name=single,J2EEServer=standalone

And the statistics we want to display which ASC shows for us are as follows:
  • FreeHeapSize
  • ActiveThreads
  • HeapSize
So here is the code we will use from a JMX Client. The method which obtains the statistics we need is called "showStats(ObjectName)"


package oracle.support.oc4j.standalone;

import java.text.DateFormat;

import java.util.Date;
import java.util.logging.Logger;

import javax.management.MBeanServerConnection;
import javax.management.ObjectName;
import javax.management.j2ee.statistics.JVMStats;
import javax.management.j2ee.statistics.Statistic;
import javax.management.j2ee.statistics.BoundedRangeStatistic;

public class JVMMonitor implements Runnable
{
private final Logger logger = Logger.getLogger(this.getClass().getName());
private static final DateFormat DATEFORMAT = DateFormat.getInstance();
private OC4JConnection con = null;
private MBeanServerConnection mbs = null;
private String targetMBean = "oc4j:j2eeType=JVM,name=single,J2EEServer=standalone";
private ObjectName mbean = null;

public JVMMonitor() throws Exception
{
// retrieve an OC4JConnection object this is a singleton class
con = OC4JConnection.getInstance();
mbs = con.getConnection();
mbean = new ObjectName(targetMBean);
System.out.println("** JVM Details **");
System.out.printf("Opmn Managed: %s\n",
mbs.getAttribute(mbean, "opmnEnabled"));
System.out.printf("Java Version: %s\n",
mbs.getAttribute(mbean, "javaVersion"));
System.out.printf("Java Vendor: %s\n",
mbs.getAttribute(mbean, "javaVendor"));
System.out.printf("Host Node: %s\n",
mbs.getAttribute(mbean, "node"));
System.out.printf("RMI Port: %s\n",
mbs.getAttribute(mbean, "rmiPort"));
System.out.printf("RMIS Port: %s\n",
mbs.getAttribute(mbean, "rmisPort"));
System.out.printf("RMI IP: %s\n",
mbs.getAttribute(mbean, "rmiServerAddress"));

}

public void run ()
{

System.out.println("\n");
try
{
System.out.printf("--> Current JVM Details [%s]\n\n",
DATEFORMAT.format(new Date()));


showStats(mbean);
System.out.printf("\n<-- End...\n");
}
catch (Exception e)
{
System.out.println("Error in run()");
e.printStackTrace();
}
finally
{
//con.close();
}
}

private void showStats(ObjectName cp) throws Exception
{

JVMStats stats =
(JVMStats) mbs.getAttribute(cp, "stats");

Object mem = null;
mem = mbs.getAttribute(cp, "totalMemory");
Long memLong = null;
memLong = (Long) mem;

System.out.printf("Total Memory: %s\n", memLong.intValue()/1024/1024 + "M");

mem = mbs.getAttribute(cp, "freeMemory");
memLong = (Long) mem;
System.out.printf("Free Memory: %s\n",
memLong.intValue()/1024/1024 + "M");

System.out.printf("HeapSize: %s\n",
stats.getHeapSize().getCurrent()/1024 + "M");
System.out.printf("HighWaterMark: %s\n",
stats.getHeapSize().getHighWaterMark()/1024 + "M");
System.out.printf("LowWaterMark: %s\n",
stats.getHeapSize().getLowWaterMark()/1024 + "M");

Statistic[] allStats = stats.getStatistics();

for (Statistic s: allStats)
{
if (!s.getName().equalsIgnoreCase("UpTime"))
{
System.out.println("\n--");
System.out.printf("JVM Statistic [%s]\n", s.getName());
System.out.printf("Description : %s\n", s.getDescription());
System.out.printf("Unit : %s\n", s.getUnit());
System.out.printf("Last Sample Time : %s\n", new Date(s.getLastSampleTime()));

BoundedRangeStatistic brs = (BoundedRangeStatistic) s;

if (s.getUnit().equals("THREADS"))
{
System.out.printf("High WaterMark : %s\n", brs.getHighWaterMark());
System.out.printf("Low WaterMark : %s\n", brs.getLowWaterMark());
System.out.printf("Current : %s\n", brs.getCurrent());
}
else
{
System.out.printf("High WaterMark : %s\n", brs.getHighWaterMark()/1024 + "M");
System.out.printf("Low WaterMark : %s\n", brs.getLowWaterMark()/1024 + "M");
System.out.printf("Current : %s\n", brs.getCurrent()/1024 + "M");
}

}
}


}

}



So the output would then be as follows:

** JVM Details **
Opmn Managed: true
Java Version: 1.5.0_06
Java Vendor: Sun Microsystems Inc.
Host Node: beast.au.oracle.com
RMI Port: 12406
RMIS Port: 12706
RMI IP: 10.187.80.70

JVMMonitor
Period to report results in seconds is : 5

** Started [4/09/08 11:29] with 5(sec) interval between reports **


--> Current JVM Details [4/09/08 11:29]

Total Memory: 515M
Free Memory: 453M
HeapSize: 515M
HighWaterMark: 550M
LowWaterMark: 504M

--
JVM Statistic [FreeHeapSize]
Description : The amount of free heap memory
Unit : KILO BYTES
Last Sample Time : Thu Sep 04 11:06:44 EST 2008
High WaterMark : 515M
Low WaterMark : 433M
Current : 453M

--
JVM Statistic [ActiveThreads]
Description : The number of active thread(s)
Unit : THREADS
Last Sample Time : Thu Sep 04 11:06:44 EST 2008
High WaterMark : 64
Low WaterMark : 4
Current : 64

--
JVM Statistic [HeapSize]
Description : The size of the JVM's heap
Unit : KILO BYTES
Last Sample Time : Thu Sep 04 11:06:44 EST 2008
High WaterMark : 550M
Low WaterMark : 504M
Current : 515M

<-- End...

Few people asked for this class itself.

OC4JConnection.java


  
package oracle.support.oc4j.standalone;

import java.io.FileInputStream;

import java.util.Hashtable;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.management.MBeanServerConnection;
import javax.management.remote.JMXConnector;
import javax.management.remote.JMXConnectorFactory;
import javax.management.remote.JMXServiceURL;

import oracle.oc4j.admin.jmx.remote.api.JMXConnectorConstant;

public class OC4JConnection
{
private static Properties myProperties = new Properties();
private JMXConnector jmxCon = null;
private static OC4JConnection instance = null;
private final Logger logger = Logger.getLogger(this.getClass().getName());
private String username = null;
private String password = null;
private String url = null;

private OC4JConnection
(Properties inProps) throws Exception
{

try
{
processProps(inProps);

logger.log
(Level.INFO, "Username = " + username);
logger.log
(Level.INFO, "Service URL = " + url);

// Define the connection target
JMXServiceURL serviceUrl = new JMXServiceURL(url);

Hashtable env = new Hashtable();

env.put(JMXConnectorFactory.PROTOCOL_PROVIDER_PACKAGES,
"oracle.oc4j.admin.jmx.remote");

Hashtable credentials = new Hashtable();

credentials.put
(JMXConnectorConstant.CREDENTIALS_LOGIN_KEY, username);
credentials.put(JMXConnectorConstant.CREDENTIALS_PASSWORD_KEY,
password);

env.put(JMXConnector.CREDENTIALS, credentials);

jmxCon = JMXConnectorFactory.newJMXConnector(serviceUrl, env);
// Connect to the target OC4J instance defined in the JMXServiceURL
jmxCon.connect();
logger.log
(Level.INFO, "Connected to OC4J");
}
catch (Exception e)
{
logger.log
(Level.SEVERE,
"** [OC4JConnection()] : Unable to obtain Stand Alone Connection",
e);
e.printStackTrace();
}
}

public static OC4JConnection getInstance() throws Exception
{
if(instance == null)
{
myProperties.load(new FileInputStream("build.properties"));
instance = new OC4JConnection(myProperties);
}

return instance;
}

private void processProps(Properties inProps)
{
username = inProps.getProperty("username");
password = inProps.getProperty("password");
url = inProps.getProperty("serviceurl");

}

public MBeanServerConnection getConnection ()
{
MBeanServerConnection mbs = null;

if (jmxCon != null)
{
try
{
mbs = jmxCon.getMBeanServerConnection();
}
catch (Throwable t)
{
logger.log
(Level.SEVERE,
"**[getConnection] : Unable to retrieve MBeanServerConnection");
}
}

return mbs;
}

/**
* Determine if the client is currently connected
* @return true if connected
*/
public boolean isConnected ()
{
boolean ret = false;
if (jmxCon == null)
{
return false;
}
else
{
try
{
jmxCon.getConnectionId();
return true;
}
catch (Throwable t)
{
//
}
}

return ret;
}

/**
* Close the JMXConnector.
*/
public void close ()
{
if (jmxCon != null)
{
try
{
jmxCon.close();
jmxCon = null;
logger.log
(Level.INFO, "Closed Connection to OC4J");
}
catch (Throwable t)
{
}
}
}
}

Monday, 25 August 2008

How to do a "desc emp" from JDBC

If you ever try to do a "desc emp" from a JDBC program , that won't work as that's a SQL*Plus command only, but here is how to write an SQL statement to do that which can easily be converted into a JDBC program and used.

Firstly here is what we get when we describe the EMP table in SQL*Plus.

SCOTT@linux10g> desc emp;
Name Null? Type
----------------------------------- -------- ------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Now here is the SQL we can use to get the same output from an SQL query.


select
column_name as "Name",
decode(NULLABLE, 'Y', '', 'N', 'Not Null') as "Null",
data_type||decode(data_type,
'NUMBER', decode(DATA_PRECISION, NULL, '',
'('||DATA_PRECISION||','||data_scale||')'),
'VARCHAR2', '('||data_length||')',
'CHAR', '('||data_length||')') as "Type"
from user_tab_columns
where table_name = 'EMP'


The output of that is as follows from the query above , identical to a "desc emp", or close enough, couple more decodes will sort out a few minors issues , but seems to work for most tables I tested it against.

Name Null Type
------------------------------ -------- ------------------------------
EMPNO Not Null NUMBER(4,0)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4,0)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2,0)

So with a bind variable defined it would simply be as follows


select
column_name as "Name",
decode(NULLABLE, 'Y', '', 'N', 'Not Null') as "Null",
data_type||decode(data_type,
'NUMBER', decode(DATA_PRECISION, NULL, '',
'('||DATA_PRECISION||','||data_scale||')'),
'VARCHAR2', '('||data_length||')',
'CHAR', '('||data_length||')') as "Type"
from user_tab_columns
where table_name = ?


And then easily be integrated into a JDBC program, I did this over the web using a JSP page, which lists all user tables and adds a DESCRIBE button next to the table name to get the output above when clicked.

Monday, 11 August 2008

Determining if any active HTTP Sessions exist for deployed applications in OAS 10.1.3.x

Recently I was asked how to determine if any current HTTP sessions exist for an application. The reason for needing this information was so that the application could be redeployed only when no HTTP sessions existed and the application was currently not being used. It turns out there is an MBean which can give you that information.

The Mbean is as follows:

oc4j:j2eeType=ClassLoading,name=singleton,J2EEServer=standalone

His how to access it from the MBean browser in ASC and get the information on HTTP sessions for your deployed applications.

1. Log into ASC
2. Click on the container you wish to use.
3. Click on the link "Administration".
4. Click on the "Go To Task" icon as follows.

JMX -> System MBean Browser

5. Expand the ClassLoading node in the navigation pane, then select the singleton MBean instance.
6. Click the Operations tab in the right-hand pane, then click the executeQuery operation.

Three versions of the executeQuery operation are exposed. Click the version that takes one parameter.

7. For the value parameter enter in the following and press the "Invoke Operation" button

HttpSessions(details)

You will end up with output as follows which will give you the amount of HTTP sessions which currently exist for each deployed application.

** Summary at Aug 11, 2008 9:57:43 AM **

Total Sessions: 0
Total Attributes: 0
Total Session Size: 0 bytes

Largest Session: N/A
Youngest Session: N/A
Idlest Session: N/A
Oldest Session: N/A

** Details **

[ADFBC-HA:adfhaprofile]

No active sessions

[SimpleWS:WebServices]

No active sessions

[WebServiceDemo-SimpleWS-WS:WebServices]

No active sessions

...

Wednesday, 30 July 2008

Enable Remote Clients To Access OAS 10.1.3.x Server-Side JNDI Context From Deployed Applications

In order for remote clients to access a OAS 10.1.3.x JNDI Server-Side context they must authenticate with the server prior to doing a look up. Here is how to enable such access from a remote J2SE client and what setup steps are required to achieve this for deployed applications, thus avoiding using privileged users such as oc4jadmin.

This example is based on JAZN-XML security provider at the instance level.

1. Log into Application Server Console (ASC).
2. Click on your container you wish to use.
3. Click on administration link.
4. Click on the icon for the security task with a description as follows -> "Configure security providers, create/delete/view users and roles".

Here we will just use "Instance" level security which will enable this user we create to be used by all applications within this instance.

5. Click on the button "Instance Level Security".
6. Click on the link "Realms".
7. You will see a column called "Users" which will have an amount of users, simple click on the amount to go to the next screen.
8. Click on the "Create" button.
9. Enter the following.

Name - test
Password - test123
Confirm Password - test123
Shuttle Across "users" to the "Selected Roles" Area

Note: You can create your own role but you must make sure you set the check box "Grant RMI Login Permission". We are using the "users" role as that has been done already for us.

10. Press OK.

So the user we want to use will be "test" with a password as "test123", with this in place we now must grant access to the JNDI context to the correct group so that remote users which are part of this group can access the JNDI resource. This is done as shown below within a deployment descriptor for each deployed application which contains JNDI objects such as a Data Source or an EJB. The file is placed within a META-INF directory of an EAR file.

orion-application.xml


<?xml version = '1.0' encoding = 'windows-1252'?>
<orion-application xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/oracleas/schema/orion-application-10_0.xsd">
<namespace-access>
<read-access>
<namespace-resource root="">
<security-role-mapping>
<group name="users" />
</security-role-mapping>
</namespace-resource>
</read-access>
<write-access>
<namespace-resource root="">
<security-role-mapping>
<group name="users" />
</security-role-mapping>
</namespace-resource>
</write-access>
</namespace-access>
</orion-application>


As you can see the namespace-access has been setup to allow users which are part of the role "users" access to the JNDI Server-Side context for the deployed application.

So with a deployment descriptor added to my application named "pastest" and deployed, I can then perform a JNDI look up of resources within that application as the user "test" as shown below.

private Context getInitialContext() throws NamingException
{
Hashtable env = new Hashtable();
// Standalone OC4J connection details
env.put( Context.INITIAL_CONTEXT_FACTORY,
"oracle.j2ee.rmi.RMIInitialContextFactory" );
env.put( Context.SECURITY_PRINCIPAL, "test" );
env.put( Context.SECURITY_CREDENTIALS, "test123" );
env.put(Context.PROVIDER_URL, "ormi://localhost:23791/pastest");

return new InitialContext( env );
}


For more information see the documentation below.

Oracle® Containers for J2EE Security Guide
10g (10.1.3.1.0)
Part Number B28957-01
http://download.oracle.com/docs/cd/B31017_01/web.1013/b28957/loginmod.htm#BABIICGC

Friday, 18 July 2008

Having JDeveloper 10.1.3.x on your PC

If you have JDeveloper 10.1.3.x on your file system you can take advantage of 2 JAR files even without actually using the IDE. There useful utilities if your using OAS 10.1.3.x. Those are as follows.

$JDEV_HOME\j2ee\home\admin_client.jar - OC4J provides a command-line utility, admin_client.jar, for performing configuration, administration, and deployment tasks on active OC4J instances in an Oracle Application Server clustered environment as well as on a standalone OC4J server. In addition, you can use admin_client.jar to restart or stop an OC4J instance or group of instances. See this document for more details.

Oracle® Containers for J2EE Configuration and Administration Guide
10g (10.1.3.1.0)
Part Number B28950-01
http://download-uk.oracle.com/docs/cd/B31017_01/web.1013/b28950/adminclient.htm#BABCCJEC

$JDEV_HOME\webservices\lib\wsa.jar - The WebServicesAssembler tool assists in assembling Oracle Application Server Web Services. It enables you to generate the artifacts required to develop and deploy Web services, regardless of whether you are creating the service top down or bottom up. The WebServicesAssembler can also be invoked to create Web service client objects based on a WSDL. See this document for more details.

Oracle® Application Server Web Services Developer's Guide
10g (10.1.3.1.0)
Part Number B28974-01
http://download-uk.oracle.com/docs/cd/B31017_01/web.1013/b28974/wsassemble.htm#CHDDBCCA

For both of these utilties use the -help option for more details of what command line options can be used.

Eg:

> java -jar D:\jdev\jdevprod\10133\webservices\lib\wsa.jar -help

Note: Stand Alone OC4J has the same JAR files as well.

Monday, 30 June 2008

ORA-00932 Using the Collect Function with Oracle JDBC driver

Recently I tried to use the COLLECT function from 10g as follows from SQL*Plus which worked fine.

SQL> select COLLECT(ename) as emps from emp;

EMPS
--------------------------------------------------------------------------------
SYSTPUJ7P3Tw/Wq/gQLsKRlBJ8Q==('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAK
E', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

I then decided to use the same query in JDBC and got this error when trying to execute a query from a Statement object.

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got -

It's obvious the return type needs to be something which the JDBC client can actually work with , so to get this to work I simply did this.

1. Create a TYPE to hold my array of Employee names.

CREATE OR REPLACE TYPE varchar2_enames AS TABLE OF VARCHAR2(50);

I then quickly ran a new query to ensure my TYPE was working correctly as shown below.

SQL> select CAST(COLLECT(ename) AS varchar2_enames) AS emps from emp;

EMPS
--------------------------------------------------------------------------------
VARCHAR2_ENAMES('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', '
SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

Note: You will se this time the return output is a table of varchar's which we can easily map to a JDBC array which we can then obtain without any errors.

2. JDBC code as follows, using the 10.2.0.4 JDBC driver


package pas.au;

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class Test
{
public Test()
{
}

public void run ()
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;

String queryStr = "select CAST(COLLECT(ename) AS varchar2_enames) AS emps from emp";

try
{
conn = getConnection();
driverDeatils(conn);
stmt = conn.createStatement();
rset = stmt.executeQuery(queryStr);
// only expecting one row
rset.next();

Array arr = rset.getArray(1);
String[] values = (String[])arr.getArray();
for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" + values[i] +"'" );

}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if (conn != null)
conn.close();

if (stmt != null)
stmt.close();

if (rset != null)
rset.close();
}
catch (Exception e)
{
// bad luck
}
}

}

public void driverDeatils (Connection conn) throws SQLException
{
DatabaseMetaData dmd = conn.getMetaData();
System.out.println("DriverVersion: ["+
dmd.getDriverVersion()+"]");
System.out.println("DriverMajorVersion: ["+
dmd.getDriverMajorVersion()+"]");
System.out.println("DriverMinorVersion: ["+
dmd.getDriverMinorVersion()+"]");
System.out.println("DriverName: ["+
dmd.getDriverName()+"]");
}

public static Connection getConnection() throws SQLException
{
String username = "scott";
String password = "tiger";
String thinConn = "jdbc:oracle:thin:@beast:1522:linux10g";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn,username,password);
conn.setAutoCommit(false);
return conn;
}

public static void main(String[] args)
{
Test test = new Test();
test.run();
}
}


3. Output as follows.

DriverVersion: [10.2.0.4.0]
DriverMajorVersion: [10]
DriverMinorVersion: [2]
DriverName: [Oracle JDBC driver]
row 0 = 'SMITH'
row 1 = 'ALLEN'
row 2 = 'WARD'
row 3 = 'JONES'
row 4 = 'MARTIN'
row 5 = 'BLAKE'
row 6 = 'CLARK'
row 7 = 'SCOTT'
row 8 = 'KING'
row 9 = 'TURNER'
row 10 = 'ADAMS'
row 11 = 'JAMES'
row 12 = 'FORD'
row 13 = 'MILLER'

Wednesday, 25 June 2008

Fast Connection Failover (FCF) Within OAS 10.1.3.x

If you read the 10.2 JDBC developer guide you will find that you will find you can subscribe remotely to ONS within the driver itself without the need for an oracle client.

http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/fstconfo.htm#CHDCHAGH
Remote ONS Subscription

OAS 10.1.3.x ships with the 10.1.0.5 JDBC driver, so is it right to assume it doesn't support FCF out of the box?

After a recent customer visit they swapped out to the 10.2.0.4 JDBC driver as they assumed 10.1.0.5 was not supported and would not work.

That is not true in actual fact 10.1.0.5 does work and the ONS subscription occurs within opmn.xml. However if you try to use the 10.1.0.5 driver outside of OAS in a stand alone client then it won't work as you can't subscribe to ONS with that driver outside of OAS 10.1.3.x.

It is all explained here.

http://www.oracle.com/technology/products/ias/hi_av/OracleApplicationServer10gFCF.pdf

Tuesday, 27 May 2008

Partial Page Renderring (PPR) fails for commandLink within ADF Table

There are 2 ways to get this to work.

Option #1

Follow the technique shown in the blog entry below.

Dynamically-Updating JSP Graph in JSF Page
http://radio.weblogs.com/0118231/stories/2004/09/23/notYetDocumentedAdfSampleApplications.html#88

In that example the backing bean for the page illustrates how to allow an updateable UIComponent in a table to trigger partial page rendering for other elements in the page by programmatically calling the ADF Faces addPartialTarget API.

Option #2

1. Make sure the af:table containing the af:commandLink has its id set.
2. Set the PartialSubmit property for the af:commandLink to true.
3. Set the PartialTriggers property for the af:outputText to the following.

tableId:commandLinkId

For example, if the id of the af:table is myTableId and the id for the af:commandLink is cmdTradeName, then the PartialTrigger property for the af:outputText should be set to.

myTableId:cmdTradeName

Example as follows.

<h:form>
<af:table value="#{bindings.findAllPipelineData1.collectionModel}"
var="row" rows="#{bindings.findAllPipelineData1.rangeSize}"
first="#{bindings.findAllPipelineData1.rangeStart}"
emptyText="#{bindings.findAllPipelineData1.viewable ? 'No rows yet.' : 'Access Denied.'}"
id="myTableId">

......

<af:column sortProperty="tradeName" sortable="false"
headerText="#{bindings.findAllPipelineData1.labels.tradeName}">
<af:commandLink text="#{row.tradeName}" id="cmdTradeName"
partialSubmit="true" immediate="true">
<af:setActionListener from="#{row.tradeName}"
to="#{sessionUser.data}"/>
</af:commandLink>
</af:column>
</af:table>
<af:outputText value="#{sessionUser.data}"
partialTriggers="myTableId:cmdTradeName"/>
</h:form>

Thursday, 8 May 2008

REF CURSOR parameters in PLSQL Web Services

If you have ever tried to publish a PLSQL package as a Web Service that has a method signature as follows JDeveloper 10.1.3.x doesn't allow you.

procedure test1 (pRefCursor1 out sys_refcursor, prefCursor2 out sys_refcursor);
However the following does work for a function returning a refcursor.

function retrieve_emps return sys_refcursor;
If you press the "Why Not" button in the PLSQL Web Service dialog you get this message.

"Ref cursor type arguments are unsupported due to a JDBC limitation. Ref cursor types are only supported for use as the return type".

Due to this limitation if you have the luxury of developing the PLSQL from scratch you can simple use PLSQL tables to overcome this. So lets assume we have a collection defined as follows.


DROP TYPE employee_list;
DROP TYPE employee_obj;

CREATE OR REPLACE TYPE employee_obj AS OBJECT
( empno NUMBER(4)
, ename VARCHAR2(10)
, job VARCHAR2(9)
, sal NUMBER(7,2)
, deptno NUMBER(2)
);
/
create or replace TYPE employee_list AS TABLE OF employee_obj;

So now we can use a method signature as follows and this will work fine.

procedure copy_emps (pInEmps in employee_list, pOutEmps out employee_list);

Tuesday, 29 April 2008

Determining the RAC instance my ADF JSF application is using

I was testing ADF BC deployed applications using a RAC enabled data source setup for FCF. While I was testing I needed to know what instance in the database the ADF application module was using. To do this I simply created a dummy VO called "DetermineInstanceVO" with a simple query as follows which does not require access to v$session for example.

select
sys_context('userenv', 'instance_name') "Instance",
sys_context('userenv', 'server_host') "Host",
sys_context('userenv', 'service_name') "ServiceName"
from dual

With that View Object I then would execute a query on my pages and display the current RAC instance the Application Module was using.

Of course I couldn't see this being that useful in a production application but handy during the testing phase while taking RAC instances down to ensure you are routed to another RAC instance to service the request without any errors.

Friday, 18 April 2008

JDBC Thin connection using tnsnames.ora entry

As of the 10.2.x JDBC driver we now allow you to connect to your database using a tnsnames.ora entry from JDBC THIN. Previously this was not possible without fully qualifying the description alias. To do this in 10.2.x JDBC driver you can do it one of 2 ways.

Option 1:

Pass a system property as follows at the command line

-Doracle.net.tns_admin=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

Option 2:


System.setProperty("oracle.net.tns_admin",
"D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");



His a quick code example showing how it works:

tnsnames.ora entry

LNX102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = papicell-au2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lnx102)
)
)

Java Code


/*
* Option 1 : Systsem property as command line option
*
* -Doracle.net.tns_admin=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
*
* Option 2 : System propety within code
*
* System.setProperty("oracle.net.tns_admin",
* "D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");
*/

package pas.test;

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class JdbcThinTnsNamesTest
{
public JdbcThinTnsNamesTest()
{
System.setProperty("oracle.net.tns_admin",
"D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");
}

public static Connection getConnection() throws SQLException
{
String username = "scott";
String password = "tiger";
String thinConn = "jdbc:oracle:thin:@lnx102";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn,username,password);
conn.setAutoCommit(false);
return conn;
}

public void run () throws SQLException
{
Connection conn = getConnection();
System.out.println("Auto Commit = " + conn.getAutoCommit());
conn.close();
}

public static void main(String[] args)
{
JdbcThinTnsNamesTest test = new JdbcThinTnsNamesTest();
try
{
test.run();
System.out.println("all done..");
}
catch (SQLException e)
{
e.printStackTrace();
}
}

}


Runtime Output

Auto Commit = false
all done..

Tuesday, 15 April 2008

SQLException: Data size bigger than max size for this type using 9i JDBC Driver

If your using the 9i JDBC Driver in this case the (9.2.0.8) driver and you try to update/insert a very large string , say 100,000 bytes into a LONG column you will get a runtime error as follows

java.sql.SQLException: Data size bigger than max size for this type: 1000000

There are 2 ways to get around this.

OPTION 1
-----------

Use CLOB to handle very large column data instead of using a LONG column. CLOB columns is the recommended way to deal with large column values, so avoid using LONG if possible.

OPTION 2
-----------

Until JDBC THIN Driver 10.1.x there is a restriction on the amount of data that can be used be with methods setBytes(), setString() for example.

When using a JDBC THIN Driver 9.x or 10.1.x you should use setBinaryStream() or setCharacterStream() instead. The 10.2.x driver will automatically switch to using streams if the data exceeds a certain value. So switching to the 10.2.0.4 JDBC driver for example will make it even easier to get around this.


Here is some code you can use to verify this with 9.2.0.8 JDBC driver then use the 10.2.0.4 driver and verify it will work in 10.2.0.4 JDBC Driver fine.

SQL Needed


drop table long_table;

create table long_table
(id number,
long_col long)
/

insert into long_table values (1, 'Sample Data');

commit;


Java Code


import java.sql.*;

import oracle.jdbc.OracleDriver;

public class LongDemo
{
public LongDemo()
{
}

public void run () throws SQLException
{
Connection conn = getConnection();

// print driver details
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData ();

// gets driver info:

System.out.println("\n=============\nDatabase Product Name is ... " +
meta.getDatabaseProductName());
System.out.println("Database Product Version is " +
meta.getDatabaseProductVersion());
System.out.println("\n=============\nJDBC Driver Name is ........ " +
meta.getDriverName());
System.out.println("JDBC Driver Version is ..... " +
meta.getDriverVersion());
System.out.println("JDBC Driver Major Version is " +
meta.getDriverMajorVersion());
System.out.println("JDBC Driver Minor Version is " +
meta.getDriverMinorVersion());
System.out.println("=============");

String updateQuery = "update long_table set long_col = ? where id = 1";
PreparedStatement updateStatement = conn.prepareStatement(updateQuery);
String outValue = createLargeString(1000000);
System.out.println("Update String size: " + outValue.length());
updateStatement.setString(1, outValue);
updateStatement.executeUpdate();
updateStatement.close();

conn.commit();
conn.close();
}

/*
* Creates a large string the size we require
*/

private String createLargeString(int size)
{
StringBuffer b = new StringBuffer(size);
for (int i=0; i<size; i++)
{
b.append("X");
}
return b.toString();
}

public static Connection getConnection() throws SQLException
{
String username = "tars";
String password = "tars";
String thinConn = "jdbc:oracle:thin:@papicell-au2:1521:lnx102";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn,username,password);
conn.setAutoCommit(false);
return conn;
}

public static void main(String[] args)
{
LongDemo longDemo = new LongDemo();
try
{
longDemo.run();
System.out.println("update worked fine");
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}

Monday, 14 April 2008

Obtaining statistics on a Message Driven Bean from a JMX Client connecting to OC4J 10.1.3.x

A while ago steve showed me a few code demos on how to access OC4J Mbeans within OC4J 10.1.3.x. Since then I have created a few command line clients to perform tasks which admin_client.jar doesn't expose. A few for example include these.
  • View application statuses
  • Stop/Start a Message Driven Bean
While doing this I noticed that if you ever need to access a MDB statistics you would do it with code as follows.


private void showStats(ObjectName cp) throws Exception
{

EJBStats stats =
(EJBStats) mbs.getAttribute(cp, "stats");



When you run this with say with a 10.1.3.3 OAS client side distribution home you will get a runtime exception as follows.

[java] 14/04/2008 12:05:27 oracle.j2ee.rmi.RMIMessages
EXCEPTION_ORIGINATES_FROM_THE_REMOTE_SERVER
[java] WARNING: Exception returned by remote server: {0}
[java] java.lang.NoClassDefFoundError:
Loracle/oc4j/admin/management/callbackinterfaces/MessageDrivenBeanCallBackIf;


In order to get around this add the following JAR to your classpath, which can be obtained from the OAS server home or a stand alone OC4J home.

$ORACLE_HOME\j2ee\home\lib\oc4j-internal.jar

Tuesday, 1 April 2008

Changes made in bc4j.xcfg from JDeveloper Application Module Configuration editor

The file bc4j.xcfg has various configuration parameter's which if not displayed will take on their defaults. After a recent customer visit I was asked if a default configuration parameter was added to the file and then reverted back to it's original default value is the parameter removed from the bc4j.xcfg file to ensure it can be supplied at the container level if required?

I would suspect it would but to prove that here is what I did.

1. Created a simple ADF BC project based on 1 entity and 1 view object, in this case EMP
2. Right click on the application module and select -> Configurations
3. Click the "Edit" button on your configuration
4. Click on the tab "Pooling and scalability"
5. Set the initial pool size of the "Application Pool" to 5 and press OK

The following is added to the bc4j.xcfg file.

<jbo.ampool.initpoolsize>5</jbo.ampool.initpoolsize>

Now if we redit the config and set the same value to 0 (which is it's default) does it remove the parameter?

The answer is yes.

Of course you can click on "Reset" button BUT that will remove all configuration parameters you set which may not be what you require.