Oracle DB contains an embedded Java 1.5 JVM which can be used to run java programs. It should be noted right away that SQL and PL/SQL is the optimal method for database interaction. Java should only be used for specific interactions, in this case, WCC integration.
This post will only include a simple integration test, the RIDC equivalent of "Hello World", which is the "PING_SERVER" service request :)
Further parts of this article will show how to send and receive data via check ins and check outs, and working with more complex data types.
Let's get started...
1. let's create a user which we will be our execution user:
CREATE USER ryan identified by password;
2. Let's grant the necessary privileges
GRANT connect TO ryan;
GRANT resource TO ryan;
exec dbms_java.grant_permission('RYAN','java.net.SocketPermission','*','connect, resolve');
3. We need to make the ridc library available. We'll use the loadjava binary to load the jar. Note that this is not available on the express versions of the db!
Note that I copied the ridc jar from the standard location on the WCC instance to my db VM's home directory. I loaded it from that location.
loadjava -verbose -f -resolve -user ryan/password ~/oracle.ucm.ridc-11.1.1.jar
4. Next, let's prepare the sample classes. Starting with the UcmContent class which is a simple RIDC connection handling class:
package com.ryansullivan.wcc.ridc;
import oracle.stellent.ridc.IdcClient;
import oracle.stellent.ridc.IdcClientConfig;
import oracle.stellent.ridc.IdcClientException;
import oracle.stellent.ridc.IdcClientManager;
import oracle.stellent.ridc.IdcContext;
import oracle.stellent.ridc.model.DataBinder;
import oracle.stellent.ridc.protocol.Connection;
import oracle.stellent.ridc.protocol.Protocol;
import oracle.stellent.ridc.protocol.ServiceResponse;
public class UcmContent
{
/**
*
*/
private static final long serialVersionUID = 1L;
private static IdcClientManager manager = null;
private static IdcClient<IdcClientConfig>, Protocol, Connection> consumerClient = null;
private static final String ucmServerUrl = "idc://**yourserver**.com";
private static final int ucmConnPoolSize = 20;
/**
*
* @return
*/
@SuppressWarnings("unchecked")
// for unchecked conversion on createClient call
synchronized private static boolean open()
{
if (manager == null)
{
try
{
manager = new IdcClientManager();
consumerClient = manager.createClient(ucmServerUrl);
consumerClient.getConfig().setConnectionSize(ucmConnPoolSize);
consumerClient.initialize();
} catch (IdcClientException e)
{
System.out.println("IdcClientException");
close();
}
}
return (manager != null && consumerClient != null);
}
synchronized private static void close()
{
consumerClient = null;
manager = null;
}
public static DataBinder createBinder()
{
DataBinder binder = null;
if (open())
{
binder = consumerClient.createBinder();
}
return binder;
}
public static ServiceResponse sendRequest(IdcContext context,
DataBinder binder)
{
ServiceResponse response = null;
if (open())
{
try
{
response = consumerClient.sendRequest(context, binder);
} catch (IdcClientException e)
{
System.out.println("IdcClientException");
close();
}
}
return response;
}
public static IdcClient getClient()
{
return consumerClient;
}
}
This class handles the overhead of creating and managing the ridc connection.
5. Now, let's make the RIDC class which will contain the specifics of our request.
package com.ryansullivan.wcc.ridc;
import oracle.stellent.ridc.IdcClientException;
import oracle.stellent.ridc.IdcContext;
import oracle.stellent.ridc.model.DataBinder;
import oracle.stellent.ridc.protocol.ServiceResponse;
public class Ridc
{
public static void main(String args[])
{
// prep context
IdcContext userContext = new IdcContext("weblogic");
// create binder for this context
DataBinder binder = UcmContent.createBinder();
// build param table
binder.putLocal("IdcService", "PING_SERVER");
//binder.putLocal("pName","SystemServerSocket");
binder.putLocal("tableNames", "CoreFeatures");
// execute
ServiceResponse response = UcmContent.sendRequest(userContext, binder);
DataBinder resultData;
try
{
// retrieve result as databinder
resultData = response.getResponseAsBinder();
System.out.println("Results::::::::::::::::::::::");
System.out.println(resultData.toString());
System.out.println(resultData.getLocal("dUser"));
System.out.println(resultData.getLocal("StatusMessage"));
} catch (IdcClientException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
You see that I'm using some simple sysouts in order to output the values. In later versions I'll show how to pass these values back as PL/SQL variables which can be further processed by PL/SQL or other RIDC requests!
6. Make the classes available to the DB:
Copy the class files from your IDE to the db server. Once they're available from the db server, you can again use the loadjava utility to load the classes:
loadjava -verbose -f -user ryan/password UcmContent.class Ridc.class
We're loading the files directly from the directory we copied them into on the db server, but we would properly package these into a single jar file in a real-world case.
7. Some additional notes:
- In order for the sysouts to be displayed to the console while we're testing, use the following:
set serveroutput on; CALL dbms_java.set_output(2000);
- Be sure to compile the UcmContent and Ridc classes against a 1.5 jdk
- Functions return values while procedures do not
- Make sure your WCC server is up and running before attempting to execute the RIDC code
- Make sure your WCC server allows connections from the DB server via its security ip filter.
8. Create the Java Stored Procedure
8a. Log into sqlplus as your context user:
sqlplus ryan/password
8b. Once in sqlplus, create the procedure:
create or replace procedure ridc_ping (s1 varchar2) as language java name 'com.ryansullivan.wcc.ridc.Ridc.main(java.lang.String[])'; /
Note that, although we're not supplying any parameters, we have used a main method in our RIDC class in order to easily test it outside the Java Stored Procedure. Since the main method has a String[] param in its signature, we must include it in the procedure signature as well.
8c. Now, execute the procedure:
set serveroutput on;
CALL dbms_java.set_output(2000);
call ridc_ping('');
Since the signature defines a parameter for the method, the stored procedure must include an argument to match or else we'll get an ORA-06553 error ("wrong number or types of arguments in call").
(I went ahead and added the statements for the server output to make sure you don't forget!)
9. Reap the rewards of your simple test! If everything worked as planned, you should get some output similar to this:
SQL> call ridc_ping('');
Results::::::::::::::::::::::
IdcService: PING_SERVER
ClientEncoding: UTF-8
IsJava: 1
NoHttpHeaders: 0
StatusMessage: You are logged in as 'weblogic'.
UserDateFormat: iso8601
UserTimeZone: UTC
blDateFormat: yyyy-MM-dd HH:mm:ssZ!tUTC!mAM,PM
blFieldTypes: xGlobalLanguage bigtext,xWebsiteObjectType text,xWCPageId
bigtext,xDontShowInListsForWebsites memo,xTemplateType text,xcontenttype
bigtext,xWebFlag text,xRegionDefinition text,xCategory bigtext,xStorageRule
text,dOutDate date,xExternalDataSet bigtext,xWebsites memo,xForceFolderSecurity
text,xWebsiteSection bigtext,xPartitionId text,dInDate date,xReadOnly
text,xPublicationDate date,xWCTags text,xregion text,dMessage
message,xInhibitUpdate text,xWCWorkflowAssignment text,dCreateDate date,xHidden
text,xKeywords text,xproducttype bigtext,dReleaseDate date,StatusMessage
message,xCollectionID int,xWCWorkflowApproverUserList memo,xIdcProfile
text,xComments memo
changedMonikers:
changedSubjects:
dUser: weblogic
idcToken:
localizedForResponse: 1
refreshMonikers:
refreshSubMonikers:
refreshSubjects:
tableNames: CoreFeatures
embedded ResultSets: 'UserAttribInfo'
weblogic
You are logged in as 'weblogic'.
Call completed.
I'll write up some more examples in the future about passing values into the RIDC call, returning values from the RIDC call, as well as handling binary data!
If you want to get a jump on the next session, you can check out the 'Oscar Wilde' sample that Oracle has already put together. You can find that example here: http://docs.oracle.com/cd/B28359_01/java.111/b31225/chfive.htm
That shows how to capture a value back from the java class back into a PL/SQL variable.
Enjoy!
-ryan
