Calling A Stored Procedure From Oracle Fusion ECM (Stellent)

April 15, 2009

You want to call a stored procedure from within your Oracle Fusion ECM Content Server (Stellent). This post will detail two ways to accomplish that. This post will also be written under the assumption that the stored procedure you are attempting to call resides in your Content Server database.

The first step is to define a query resource. This is fairly simple through Component Wizard. When you create the actual query give it a name and put the word "exec", a space, and the name of your stored procedure in the Query text area.

A template is needed display our data. The template will be attached to a service we will craft in just a few moments. With your handy component wizard create a new template. Add to it some iDocScript that looks like this:

dDocName (# of Revisions)
<$loop MOST_REVISED$>
<$dDocName$> (<$REVCOUNT$>)
<$endloop$>

Now for a service with which to execute our query. Through component wizard go ahead and create the service and then add an action. The action is where we will execute our query resource which points to our stored procedure. Note the parameters textbox as we name the Result Set that will hold our data returned from the stored procedure. Do not forget to hook up your template you made earlier.

We have now used the triangle of power to produce results: Service, Query, Template. To call your query resource from Java you can use the createResultSet method of the Workspace object. You will need to copy the data from that ResultSet into a DataResultSet object and put that in the binder for your template to have access to it.

ResultSet rs = m_workspace.createResultSet("QmostRevisedContent", m_binder);
DataResultSet drs = new DataResultSet();
drs.copy(rs);
m_binder.addResultSet("MOST_REVISED", drs);

Here is a Sample Component to Call A Stored Procedure which demonstrates this functionality. It was developed against SQL Server, not Oracle, but the same concepts apply. The component includes a sample sql script you can run against your database to create a stored procedure for the component to execute.

© 2020 Jason Stortz (version 20200607-210013)