It’s not yet announced on OTN, but you can finally download JDeveloper 10.1.3 production (build 3673).
Weblog
JDeveloper 10.1.3 available for download
Using Apache Derby with JDeveloper and OC4J
I need to do some development using JDeveloper on a machine which has only 512Mb of memory. And I also need to run a database on the same machine. Running JDeveloper and an Oracle database is not an option, so I’ve been trying to use Apache Derby as my development database. It works, but it’s not perfect.
Start by downloading Apache Derby. I downloaded the latest official release from the download page. Unzip the file, for example into e:\java\.
You can start Derby in client-server mode as follows:
..> cd e:\java\db-derby-10.1.2.1-bin\frameworks\NetworkServer\bin ..> setNetworkServerCP.bat ..> startNetworkServer.bat ..> E:\java\db-derby-10.1.2.1-bin\frameworks\NetworkServer\bin>startNetworkServer.bat Server is ready to accept connections on port 1527.
Next step is to create a database connection in JDeveloper. Go to the connections view, select the Database folder, and right click New Database connection….
Enter a connection name, and select Third Party JDBC Driver:
Skip the username, password screen. On the Connection screen you’ll first need to create a new Driver class. Select the New… button, press Browse… and create a new library for Derby:
Next enter a connection URL in the connection screen:
You should now be able to successfully test the connection. In the Connections view you’ll see the database users and object. You can also open a SQL Worksheetby right clicking the database name. In the SQL Worksheet you can create and query tables. I’ve created a table called customers:
create table customers (
id integer generated always as identity primary key,
name varchar(200)
);
insert into customers (name) values ('Customer A');
insert into customers (name) values ('Customer B');
Next step is to see if we can use the Derby database while developing web applications.
I’ll start with a little jsp:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>index</title>
</head>
<body>
<sql:setDataSource var="derbyDs"
driver="org.apache.derby.jdbc.ClientDriver"
url="jdbc:derby://localhost:1527/dev1;create=true;user=du;password=du"
user="du"
password="du" />
<sql:query var="customers" dataSource="${derbyDs}">select * from customers</sql:query>
<table>
<c:forEach var="row" items="${customers.rows}">
<tr>
<c:forEach var="column" items="${row}">
<td>
<c:out value="${column.value}"/>
</td>
</c:forEach>
</tr>
</c:forEach>
</table>
</body>
</html>
When you run the jsp in JDeveloper you’ll see a page in your browser displaying the two records inserted in the customer table.
Now, instead of specifying a driver and database url, i want to use a DataSource. To configure datasources go to the Tools|Embedded OC4J Server Preferences… menu. Add a native datasource to Current Workspace:
Now you should be able to rewrite the jsp as follows:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>index</title>
</head>
<body>
<sql:setDataSource dataSource="jdbc/derby-duCoreDS"/>
<sql:query var="customers">select * from customers</sql:query>
<table>
<c:forEach var="row" items="${customers.rows}">
<tr>
<c:forEach var="column" items="${row}">
<td>
<c:out value="${column.value}"/>
</td>
</c:forEach>
</tr>
</c:forEach>
</table></body>
</html>
Unfortunately, this doesn’t work. There are two problems. First, we need to provide some properties to the DataSource configuration. I’m not sure how to do this in JDeveloper, so instead i’ve opened the datasource configuration file in a text-editor to manually add the properties. In my case the configuration file is called oc4j-derby-test-data-sources.xml, and you can find it in your application directory. The datasource configuration file should look like this:
<?xml version = '1.0' encoding = 'windows-1252'?>
<data-sources
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd"
version="10.1" xmlns="http://xmlns.oracle.com/oracleas/schema">
<native-data-source name="jdev-connection-native-derby-du" jndi-name="jdbc/derby-duCoreDS"
url="jdbc:derby://localhost:1527/dev1;create=true;user=du;password=du" user="du" password="du"
data-source-class="org.apache.derby.jdbc.ClientConnectionPoolDataSource">
<property name="databaseName" value="dev1"/>
<property name="serverName" value="localhost"/>
<property name="portNumber" value="1527"/>
</native-data-source>
</data-sources>
As you can see, i’ve added properties for database name, server name and port number. However, the jsp still doesn’t work. When you run it, you’ll get the following exception:
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver" at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(QueryTagSupport.java:276) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(QueryTagSupport.java:159) at _index._jspService(_index.java:64) [/index.jsp]
I haven’t been able to fix this, but the datasource configuration seems to be correct as the following jsp (which basically does the same thing) works as expected:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"
import="javax.naming.InitialContext,javax.sql.DataSource,java.sql.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>index</title>
</head>
<body>
<table>
<%
InitialContext ic = new InitialContext();
DataSource nativeDS =
(DataSource) ic.lookup("jdbc/derby-duCoreDS");
Connection nativeDSConn = nativeDS.getConnection();
PreparedStatement stmt = nativeDSConn.prepareStatement("select * from customers");
ResultSet rset = stmt.executeQuery();
while(rset.next()){
%>
<tr>
<td>
<%=rset.getString(1) %>
</td>
<td>
<%=rset.getString(2) %>
</td>
</tr>
<%
}
nativeDSConn.close();
%>
</table>
</body>
</html>
Anybody have any clues how i can configure the datasource in JDeveloper, and how i can get the jstl sql tags to work with the derby datasource?
JSF and Oracle Portal?
After reading the Portal 10.1.4 feature overview, I wonder when Oracle is going to add JSF support in Oracle Portal. Oracle Portal has great facillities (JPDK and a special struts pdk library) to publish a Struts application as a portlet in your portal page, however in my opinion Struts is becoming old-fashioned and will be replaced by JSF in the near future as the new standard MVC framework for developing rich enterprise applications.
I hope Oracle is going to support JSF soon in the same way it supports Struts, so that I’m not forced to use Struts whenever I have to build an enterprise application which has to run as a portlet inside Oracle Portal..
Portlets and timeouts
When building portlets which have to wait for backend operations to complete, or you want to force your portlets to return within a certain interval you need to specify timeout settings. Often this is not done and defaults are used by Oracle Portal. When looking at application log files you often see something like the following at the startup of the application:
[app name]: [instance=instance-name, id=3508824395820,7] CONFIGURATION: Invalid or null value for property - executionWarningTimeout = null - setting to default value of 20 seconds
This warning (only displayed if loglevel is debug) says that the executionWarningTimout has not been specified and therefore set to a default value. This means that the Parallel Page Engine(PPE) stops waiting for the response from the portlet (provider actually) after this timeout. When you have a request which can take longer than this default value you need to specify this in your provider. When building a provider(xml) you also create a [applicationname].properties file to specify providers settings. Here you can specify the executionWarningTimeout. Example:
serviceClass=oracle.webdb.provider.v2.adapter.soapV1.ProviderAdapter loaderClass=oracle.portal.provider.v2.http.DefaultProviderLoader showTestPage=false definition=providers/provider.xml autoReload=true ExecutionWarningTimeout=60
When the PPE stops waiting for the request to complete and returns the appropriate error, the request however is still processed. To make sure the processing is stopped you can also add executionKillTimeout (default 80 seconds). This value has to be larger than the ExecutionWarningTimeout. To prevent system pollution and keep the performance up it is also good practice to set this property.
OWB (10.1.0.2.0) trying to access invalid object
I have seen strange behavior in the OWB client when creating a mapping. In the mapping I’m trying to join 3 tables through 3 INGROUPS. When linking the third table I get the error message: ‘Trying to access invalid object’ followed by ‘Lenght is not supported for datatype NUMBER’.
The problem is caused by a table that has a attribute with a datatype NUMBER and (something unbelievable) a lenght of 10, which is grayed-out.
This is only visible in the attribute properties when opened in within the mapping. When looking at table properties this is not visible.
Somehow this table and attribute has become corrupt and therefor causes the join action to crash.
Solution:
To solve this issue, you’ll need to go to the table properties, delete the attribute which is causing the error and create a new attribute instead of the deleted one. Reconcile inbound the table in the mapping and you’ll be able to create the join.
Starting with Ruby and Oracle
In this post i’ll explore some ways of using Ruby with an Oracle database. I’ve installed ruby on my windows laptop and i’m using Oracle Express Edition running on CentOS using vmware.
First, we’ll need to download ruby for windows. The easiest way to get ruby on windows is to use the Windows Ruby installer available on rubyforge. Install ruby by running the installer. Now we can test the installation. Create a test application e:\programs\ruby\tests\helloworld.rb:
puts "HelloWorld!"
When you run this ruby script you should get the following output:
E:\programs\ruby\tests>helloworld.rb HelloWorld!
Ok, ruby seems to be working. Time to add Oracle connectivity. There are a couple of libraries out there that will let you connect with Oracle, but it seems like oci8 is the most up to date option. Download ruby oci8 from oci8 on rubyforge, and run the installer as follows:
e:\temp>ruby ruby-oci8-0.1.13-mswin.rb Copy OCI8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 Copy oci8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8 Copy oci8lib.so to e:/programs/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt OK? Enter Yes/No: yes Copying OCI8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done Copying oci8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8 ... done Copying oci8lib.so to e:/programs/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done OK
You need the oracle sqlnet libries on your windows machine. If you do not have these, you can install oracle instant client. The 10Mb basic-lite is a 10M download contains everything you need to use sqlnet. Although not really needed, you may also want to download sqlplus for oracle instant client, which is another 720kb. Sqlplus is a command line sql interface for the oracle database.
Unzip both files, eg in e:\oracle. Test if you can connect to oracle using sqlplus. Start sqlplus.exe. I have an oracle express edition running on centos using vmware workstation. To connect to this database i can use the following connectstring: system/manager@//oraxe/xe, where oraxe is the machine name, and xe is the oracle instance name.
Add oracle instant client to your path:
E:\programs\ruby\tests>set PATH=%PATH%;e:\oracle\instantclient_10_2
We should now be able to connect to Oracle from ruby. The following ruby script uses the oci8 api to run a query. Create a ruby test application e:\programs\ruby\tests\oracle-test1.rb:
require 'oci8'
conn = OCI8.new('system', 'manager', '//oraxe/xe')
conn.exec('select * from all_users where username = :1','SYSTEM') do |r|
puts r
end
When you run this script you should see the following:
E:\programs\ruby\tests>oracle-test1.rb SYSTEM 5.0 2005/10/10 03:16:21
Another method for database access in ruby is Ruby DBI. Ruby DBI is a database independent interface for accessing databases, similar to perl’s DBI. Note that Ruby DBI seems pretty unsupported, the latest change on the changelog is dated september 7th 2003. Also, Ruby oci8’s website (you need it for dbi) says that support for dbi is experimental. So, this is not really a combination which you would want to use in a production environment. Anyway, lets see what a dbi script would like:
require 'dbi'
dbh = DBI.connect('DBI:OCI8://oraxe/xe','system','manager')
sth = dbh.prepare('select * from all_users where username = :1')
sth.execute('SYSTEM')
while row=sth.fetch do
p row
end
dbh.disconnect
The result when you run this script:
E:\programs\ruby\tests>oracle-test2.rb ["SYSTEM", 5.0, 2005/10/10 03:16:21]
Most people who want to start with Ruby usually do this because of Ruby on Rails. Ruby on rails contains another method for connecting to databases: ActiveRecord. This is an implementation of the Active Record design pattern.
I want to create a ruby script which uses ActiveRecord, but outside rails. I first need to install Ruby on rails, to get the required libraries:
E:\programs\ruby\tests>gem install rails --include-dependencies E:\programs\ruby\tests>"e:\programs\ruby\bin\ruby.exe" "e:\programs\ruby\bin\gem" install rails --include-dependencies Attempting local installation of 'rails' Local gem file not found: rails*.gem Attempting remote installation of 'rails' Updating Gem source index for: http://gems.rubyforge.org Successfully installed rails-1.0.0 Successfully installed rake-0.6.2 Successfully installed activesupport-1.2.5 Successfully installed activerecord-1.13.2 Successfully installed actionpack-1.11.2 Successfully installed actionmailer-1.1.5 Successfully installed actionwebservice-1.0.0 Installing RDoc documentation for rake-0.6.2... Installing RDoc documentation for activesupport-1.2.5... Installing RDoc documentation for activerecord-1.13.2... Installing RDoc documentation for actionpack-1.11.2... Installing RDoc documentation for actionmailer-1.1.5... Installing RDoc documentation for actionwebservice-1.0.0...
To test active record we first need some datbase objects. In this example i’m going to store todolists in a table. Create a user tl_owner, password tl_owner, a table to store todolists and a sequence for the table:
CREATE TABLE "TODO_LISTS"
( "ID" NUMBER NOT NULL ,
"NAME" VARCHAR2(30) UNIQUE,
"DESCRIPTION" VARCHAR2(2000),
CONSTRAINT "TODO_LISTS_PK" PRIMARY KEY ("ID")
)
create sequence todo_lists_seq;
The following ruby application will insert one record into this table and query it:
require 'active_record'
class TodoList < ActiveRecord::Base
end
ActiveRecord::Base.establish_connection(
:adapter => "oci",
:host => "oraxe/xe",
:username => "tl_owner",
:password => "tl_owner"
)
todoList = TodoList.new
todoList.name = "List 1"
todoList.description = "My first description"
todoList.save
todoList2 = TodoList.find(:first,:conditions =>["name = ?","List 1"])
puts "#{todoList2.id}, #{todoList2.name}, #{todoList2.description}"
Running this script results in the following output:
E:\programs\ruby\tests>oracle-test3.rb 104.0, List 1, My first description
I’ll finish this post by showing how you can do some simple reporting using ruby. The following script creates an html report listing Oracle users whose names contain ‘SYS’. For the report i’m going to use the ERB templating library, which is also used by Ruby on Rails.
require 'active_record'
require 'erb'
class AllUsers < ActiveRecord::Base
end
ActiveRecord::Base.establish_connection(
:adapter => "oci",
:host => "oraxe/xe",
:username => "tl_owner",
:password => "tl_owner"
)
users = AllUsers.find_all ['username like ?','%SYS%']
report = ERB.new <<-EOF
<html>
<head><title>Users</title></head>
<body>
<table>
<% users.each do | user | %>
<tr>
<td><%=user.user_id %></td>
<td><%=user.username %></td>
<td><%=user.created %></td>
</tr>
<% end %>
</table>
</body>
</html>
EOF
puts report.result(binding)
Running this gives the following result:
E:\programs\ruby\tests>oracle-test4.rb
<html>
<head><title>Users</title></head>
<body>
<table>
<tr>
<td>0.0</td>
<td>SYS</td>
<td>Mon Oct 10 03:16:21 West-Europa (standaardtijd) 2005</td>
</tr>
<tr>
<td>5.0</td>
<td>SYSTEM</td>
<td>Mon Oct 10 03:16:21 West-Europa (standaardtijd) 2005</td>
</tr>
<tr>
<td>20.0</td>
<td>TSMSYS</td>
<td>Mon Oct 10 03:24:37 West-Europa (standaardtijd) 2005</td>
</tr>
<tr>
<td>25.0</td>
<td>CTXSYS</td>
<td>Mon Oct 10 03:27:58 West-Europa (standaardtijd) 2005</td>
</tr>
</table>
</body>
</html>
Ok, enough for this post. Next time i’ll have a look at Ruby on Rails.
resources:
Want Oracle Java Days in the Benelux?
Oracle is organizing developer days to introduce some of the newer java technologies (EJB3, JSF, BPEL). Unfortunately, no events have been scheduled for the Benelux sofar. Would you like to attend one of these workshops in the Netherlands? Please, show your interest by leaving a comment on Wilfreds weblog OraTransplant: Free Oracle Java Days in the Benelux?. Hopefully these comments will convince Oracle to also plan these workshops in the Benelux.
Firefox Web Developer Extension version 1.0 released
The Firefox web developer extension is a Firefox plugin every webdeveloper should have. I just read on 456 Berea Street the good news that version 1.0 has been released. Many new features have been added, and the menus have also been improved (History).
Two positive Ruby on Rails postings
Today, I read these two postings about Ruby on Rails: Rails cafe and Tipping Rails. Both postings are written by David Geary, one of the lead-architects of Sun’s Javaserver pages (JSF) project. The first post is a nice metaphore comparing RoR with Java and in the second one David argues why Ruby On Rails can become a popular Web Development Framework (WAF).
Another remarkable thing is that the postings are written by one of the lead-architects of JSF, the new WAF of Sun. But, as David explains by himself, he’s a maven who wants to learn new things and teach them to others. In the end of the second posting he’s also outlining the different places of RoR and JSF in the world of web development. I think he’s having a point for now, but I can imagine that RoR can become a real competitor to JSF once it has become more mature and established.
SQL source formatter ant task
It’s a big pain having to manually format source code. Usually i write all my code without indenting. There are basically two ways to automatically format your code: using your IDE or using a build script. I prefer the latter. Source formatting in the IDE has two problems. First, you need to manually invoke the formatter. Second, developers can change the formatting preferences, which leads to formatting differences between developers.
Using a build script, in my case an Ant build script, guarantees that my files are formatted, even if i forget to invoke the format action in the IDE. In my opinion, source files need to be formatted almost after every change, to avoid having unformatted source files in CVS. Comparing different versions of a file is a lot easier when the files are formatted. The build script also solves the second problem. All developers use the same build script, which means that formatting is consistent.
For java source code it’s easy to define an Ant task which will format the source code. There are quite a number of formatting tools you can run from Ant: jalopy, jindent, and some others. But for sql source code i haven’t found any useable tools. That is, until Oracle released Project Raptor.
Project Raptor doesn’t contain an Ant task for sql formatting, but it does contain some java code which formats sql source. So it’s not difficult to create an Ant task to format sql source code.
Here’s an example of how i would like to use this formatter:
<taskdef name="format-sql" classname="..." classpathref="lib.path" />
<format-sql>
<fileset dir="sql">
<include name="**/*.sql"/>
</fileset>
</format-sql>
The first step is to create a class which extends Task. In the execute method we’ll loop through all the sql source files as specified using the nested fileset task.
public void execute() throws BuildException {
Iterator fileSetListIterator = fileSetList.iterator();
while (fileSetListIterator.hasNext()) {
FileSet fileSet = (FileSet)fileSetListIterator.next();
DirectoryScanner ds = fileSet.getDirectoryScanner(getProject());
String[] files = ds.getIncludedFiles();
for (int i = 0; i < files.length; i++) {
try {
format(new File(ds.getBasedir(), files[i]));
} catch (Exception e) {
throw new BuildException(e.getMessage());
}
}
}
}
For every file the format method is called. This is where we need to call Raptors sql formatter. The jar file which contains the sql formatter is oracle.dbtools.sqlformatter.10.1.3.jar. You can find this file in %RAPTOR_HOME%\jdev\extensions. You need to place this jar in your classpath. Doing the formatting is actually pretty straight forward. You create a file reader and writer for the source and destination files, and instantiate PrettyPLSQL to do the formatting:
private void format(File file) throws FileNotFoundException, IOException,
TokenStreamException {
System.out.println("Formattings: " + file);
boolean status = false;
File backupFile = new File(file.getAbsolutePath() + ".bck");
File outputFile = new File(file.getAbsolutePath());
// delete existing backup file
status = backupFile.delete();
// rename file to backup file
status = file.renameTo(backupFile);
if (!status) {
throw new BuildException("Failed to create backup file " +
backupFile.getAbsolutePath());
}
// create reader and writer
FileReader reader = new FileReader(backupFile);
FileWriter writer = new FileWriter(outputFile);
// format sql file
System.setProperty("ANTLR_USE_DIRECT_CLASS_LOADING", "true");
PrettyPLSQL prettyPlsql = new PrettyPLSQL();
PLSQLLexer lexer = new PLSQLLexer(reader);
writer.write(prettyPlsql.format(lexer, getFormatOptions()));
// close reader and writer
writer.close();
reader.close();
}
The getFormatOptions method returns a FormatOptions object. You can use this object, for example, to specify if identifiers need to be uppercase or lowercase.
During testing i got some ClassNotFound exceptions. Turns out that you can specify how certain classes need to be loaded using the ANTLR_USE_DIRECT_CLASS_LOADING property. Jad is a big help to discover these things.
More info:
