
CC&B - Building a web service with a custom SQL select
- Ivana Claudio
- Cc& b , Ouaf
- September 24, 2024
If you’re using Oracle Utilities Application Framewrok, you have access to extensive documentation, but it doesn’t have many step by step tutorials on how to build specific thing. Like an inbound web service with a custom SQL select and optional parameters in Customer Care and Billing. Here is my attempt to fill in that gap.
In this tutorial, we’ll go through setting up a zone with an SQL select with optional filters/parameters, using this zone to create a business service, calling this business service from a script to return the results we need in an IWS, and finally setting up an inbound web service that uses this script to get the data..
Create a Zone
There are many different ways to navigate around CC&B, so I’ll let you use your prefered way. Create a new zone. Or maybe you already have a zone with your SQL statement, and you can just use that. I find that a lot of useful stuff is already available in CC&B out of the box, so you may be lucky.
The important thing is to make your zone of a data explorer type, which includes F1–DE, F1–DE-QUERY, and F1–DE-SINGLE. Define your user filters. List the initial display columns. Include your SQL statement. Define your columns.
Tip
If you’re not getting stuff to show up like you wanted it to make sure that you have properly matched up filter and column numbers with filter and column definitions in the zone. Your filter will not work if you try to use POSTAL in place of ENTITY_NAME_UPR, like I did.
The neat thing about using a zone is that you can add it to a portal and test it until you get it to return exactly the data you need.
Create a Business Service
This little piece of documentation helped me realize how to set up my custom SQL with optional parameters.
Since our zone is of a data explorer type, we can use it to create a data explorer service (FWLZDEXP). Unless you have a specific reason not to, you can use F1-DFLTS application service for this.
The bulk of work on the business service is in the schema tab. You could start from scratch, or copy a schema from an existing service and modify it. The schema will do two things.
Specify the zone
First line in the schema specifies which zone is called by this business service.
<zone private="true" default="CM-ACCTSRCH" mapField="ZONE_CD" dataType="string"/>
Map filters
The following lines in the schema will map the passed in values to zone’s filters. This is done by the numbers, so pay close attention to the ordering of your filters. They must match the numbering of the filters defined by the zone.
<accountId mapField="F1_VALUE" dataType="string"/>
<address1 mapField="F2_VALUE" dataType="string"/>
<city mapField="F3_VALUE" dataType="string"/>
<entityName mapField="F4_VALUE" dataType="string"/>
<postal mapField="F5_VALUE" dataType="string"/>
Map the results
The last thing the schema does is map the resulting columns from the zone. This is again done in the order in which columns are defined for the zone. The schema will map them to field names, but it will still rely on the sequence number of each column returned in the zone. It doesn’t matter if the columns have been given labels in the zone definition.
<rowCount dataType="number" mapField="ROW_CNT"/>
<results type="list" mapList="DE">
<accountId mapField="COL_VALUE">
<row mapList="DE_VAL">
<SEQNO is="1"/>
</row>
</accountId>
<address1 mapField="COL_VALUE">
<row mapList="DE_VAL">
<SEQNO is="2"/>
</row>
</address1>
<city mapField="COL_VALUE">
<row mapList="DE_VAL">
<SEQNO is="3"/>
</row>
</city>
<postal mapField="COL_VALUE">
<row mapList="DE_VAL">
<SEQNO is="4"/>
</row>
</postal>
<entityName mapField="COL_VALUE">
<row mapList="DE_VAL">
<SEQNO is="5"/>
</row>
</entityName>
<cash_only mapField="COL_VALUE">
<row mapList="DE_VAL">
<SEQNO is="6"/>
</row>
</cash_only>
</results>
Create a service script (optional)
The next step in the process is to create a script of type “Service Script”, but this step is optional. CCB allows business services to be called directly from the inbound web service, however this approach will just pass in the values for filters and return the results. If you wish to do any processing of either filters or results, you will need to do that in a service script.
To write a service script, give it a name, description, select “Service Script” type, go with F1-DFLTS application service and select your script engine version. You could go with Groovy or 3.0 for this, depending on what kind of processing you need to do. 3.0 will allow you to add the above defined business service to the data area, and simply manipulate its fields using scripting, and it allows for using groovy for some tasks too. Pure groovy engine does not include data area usage, but you can still invoke the business script and get your data through groovy. Which one you choose, will likely depend on your personal preferences and the problem you are trying to solve.
Add business services to the data area
Once you have created the script, if it’s running on 3.0 engine, you can go to the data area tab and add the business service you created. In “schema type” select “business service”, in Object type the name of your business service, or look for it using the search function for the field. The rest should populate automatically. Remember to save your changes.
Define the schema for the script
The schema you define for the script will be what the web service accepts and returns. I think it’s possible to have different request and result schemas, but I haven’t tried doing that. My schema contained the filters and a list of results.
<schema>
<accountSearch mapField="ACCT_ID" dataType="string"/>
<nameSearch mapField="ENTITY_NAME" dataType="string"/>
<addressSearch mapField="ADDRESS1" dataType="string"/>
...
<accountSearchList type="list">
<accountId mapField="ACCT_ID" dataType="string"/>
<address1 mapField="ADDRESS1" dataType="string"/>
<city mapField="CITY" dataType="string"/>
<postal mapField="POSTAL" dataType="string"/>
<fullName mapField="ENTITY_NAME" dataType="string"/>
<cashOnly dataType="string"/>
<currentAmount dataType="money" mapField="CUR_BAL"/>
</accountSearchList>
</schema>
Write the script logic
Once the inputs and outputs are defined, it’s time to add the script steps. The inputs you get from the web service call will be passed through the “parm/varName” which you can then move to the input field of your business service. For my service, it looked something like this:
move null to "CM-ACCTSRCH";
if ("string(parm/accountSearch) != $BLANK")
move "parm/accountSearch" to "CM-ACCTSRCH/accountId";
end-if;
if ("string(parm/addressSearch) != $BLANK")
move "parm/addressSearch" to "CM-ACCTSRCH/address1";
end-if;
if ("string(parm/citySearch) != $BLANK")
move "parm/citySearch" to "CM-ACCTSRCH/city";
end-if;
This way, the filters that were passed into the web service got assigned to their corresponding fields in the business service, and this only happened if there was an actual value passed in, while the empty fields were ignored.
Once the filters were all set, it was time to invoke the service and populate the results:
invokeBS 'CM-ACCTSRCH' using "CM-ACCTSRCH";
move null to "parm/accountSearchList";
if("CM-ACCTSRCH/rowCount > 0")
for($account in "CM-ACCTSRCH/results")
move "$account/accountId" to "parm/+accountSearchList/accountId";
move "$account/address1" to "parm/accountSearchList[last()]/address1";
move "$account/city" to "parm/accountSearchList[last()]/city";
...
// getting the balance for each account since it seems faster this way
move "$account/accountId" to "C1-AccountBalanceRetriever/input/accountId";
invokeBS 'C1-AccountBalanceRetriever' using "C1-AccountBalanceRetriever";
move "C1-AccountBalanceRetriever/output/currentAmount" to "parm/accountSearchList[last()]/currentAmount";
end-for;
end-if;
Finally, create the inbound web service
Creating an inbound web service is pretty straight forward, and the help available in CC&B is adequate. In my case, I needed to make a REST service, so I followed the instructions on how to do it. What proved more challenging was debugging the service. I kept getting errors with service calls while trying to test the service using the integrated client. I didn’t know if my inputs were wrong or if there was something wrong with the script. What helped with that was to tick off the two “message options” for trace and active. This gave me the full error message and trace which immediately showed me that the problem was with a mismapped field that I recently modified in the script while trying to make it more pretty. The fix then took less than a minute, and my next test call went smooth like silk.
Wrong approaches
Coming in from Python and other scripting languages, my initial instinct was to use groovy to query the database for the data I needed to return. This approach seemed to work fine for scripts which only had required parameters, if any. I could just create a PreparedStatementQuery, then bind any parameters I needed and get data from the database. This was how the examples I found in our system were mostly written, and the approach I took initially.
However, I ran into problems with my script when I tried to build the query string before passing it into the createPreparedStatement() function. This always resulted in an error. Passing in an SQL statement as a literal works fine, but as soon as I moved the same String into a variable and tried to pass it into the function, I would get an error.
General error during canonicalization: Expression [MethodCallExpression] is not allowed: this.createPreparedStatement(sql, accountSearchQuery)
Running into this dead end is what made me reconsider my approach.
Final thoughts
And there you have it. It is not an intuitive process for someone who is not used to working with OUAF. I have worked previously with a zone, but it was for a portal, and it didn’t occur to me that a zone, which I assumed was a visual representation of the data, could be used as part of a business script. Another place where I encountered SQL statements being passed as a parameter was in algoritms used for batch controls, so that’s where I went to look first, but this also proved to be a road to nowhere.