Thursday, November 26, 2015

Oracle Dynamic SQL scripts

There are scenarios where you used scheduled jobs to run some script procedure. In such a situation if you need to create/drop temp tables, with scheduled jobs it might cause errors if the temp table creation/deletion is done outside the procedure. Therefore in order to add all that logic into a single procedure you need to use dynamic SQL.

Below is a sample oracle  procedure written with dynamic sql. In dynamic sql in order to execute a query you need to add your query login within a statement.So the first step would be to define these statements inside the procedure.

  stmt1  VARCHAR2(2048);
  stmt2 VARCHAR2(2048);
  stmt3 VARCHAR2(2048);
  stmt4 VARCHAR2(2048);

Next we can initialize the defined statement variables and execute them accordingly.



  stmt4 := 'DROP TABLE TEMP_DATA';



What happens in this script is ,

1. create a temp table.
2.Insert data into temp table from an existing table.
3. Add temp table data into another table.
4.Drop temp table.

Note that the statements need to be within ' ' .

Next if you execute this procedure you might receive an error stating user doesn't have sufficient privileges. This is due to oracle security model, and in order to execute dynamic queries you need to specify the permissions given to execute this procedure. Here I'm providing the permissions available of the currently logged in user. This can be done like below by placing an 'AUTHID'.

You could look into 

which explains other types of users that can be defined with AUTHID property.

Friday, November 20, 2015

Revoke OAuth Access Token from Soap Endpoint

In Wso2 Identity Provider when you need to revoke an OAuth token endpoint, there are two options that you could follow.

1. Rest endpoint
2. Soap endpoint

Rest endpoint is detailed and explained in  However if you need to revoke the token based on the resource owner, then you could go for Soap endpoint revoke operation. A good explanation on these differences can be found in

These are the steps to follow in order to try out this soap endpoint. For this example I have used IS 5.0. + Service Pack 1.

The operation we are going to invoke is admin service, OAuthAdminService's  operation revokeAuthzForAppsByResourceOwner. Therefore make sure that you set <HideAdminWsdl> to false in <IS_HOME>/repository/conf/carbon.xml.

1. Create a new Service Provider (RWM) through management console and enable OAuth.
2. Note down the Client key and client secret values of the new SP. (under OAuth section)
2. Make the grant type password.
3. Create a new user (user1) and provide login permission for the user.( through internal role)
4. Use below curl command and invoke an access token for the newly created user 'user1'.

Here the format would be,

curl -v -k -X POST --user clientKey:clientSecret -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" -d 'grant_type=password&username=user1&password=test123' https://localhost:9443/oauth2/token

For this example I have used,
curl -v -k -X POST --user mlwL69uKnERmOwDnygn2kwAgVJca:vhW8WZ1qSHsAX6RZH7YQ7QvxVwwa -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" -d 'grant_type=password&username=user1&password=test123' https://localhost:9443/oauth2/token

5. Once you invoke this, you should get a json response with the access token and refresh token values.

6. You could check if this user1 gained authorization for the SP RWM, by accessing
 https://localhost:9443/dashboard/  as 'user1' and go to autorized apps - > view details. If previous invoke was successful, you should see that RWM app listed under this page.

7. Now in order to revoke create a soapui project with https://localhost:9443/services/OAuthAdminService?wsdl and send a request to
revokeAuthzForAppsByResourceOwner operation like below.

Make sure to enable 'authenticate pre-emptively option ,else you would get an illegal login attempt error, and the token will not be revoked. Also the basic:auth credentials should be the user credentials that you used to invoke the the token in the first place. 

8. Now if you go to admin dashboard and view authorize apps, you should not be able to see the previous RWM listed. You can further clarify that the token got revoked, by invoking another token for the same user, and see if it is a new value given.


Wednesday, November 18, 2015

Workaround for ConnectionPool closed error at API Manager Gateway

Hi in a clustered setup if there is a continuous high load , following issue can occur at the gateway node.

An error occurred while submitting resources for indexing {org.wso2.carbon.registry.indexing.ResourceSubmitter}
org.wso2.carbon.registry.core.exceptions.RegistryException: Failed to commit transaction.
    at org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.commitTransaction(
    at org.wso2.carbon.registry.core.jdbc.dao.JDBCLogsDAO.commitTransaction(
    at org.wso2.carbon.registry.core.jdbc.dao.JDBCLogsDAO.getLogList(
    at org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.getLogs(
    at org.wso2.carbon.registry.core.caching.CacheBackedRegistry.getLogs(
    at org.wso2.carbon.registry.core.session.UserRegistry.getLogsInternal(
    at org.wso2.carbon.registry.core.session.UserRegistry.access$3600(
    at org.wso2.carbon.registry.core.session.UserRegistry$
    at org.wso2.carbon.registry.core.session.UserRegistry$
    at Method)
    at org.wso2.carbon.registry.core.session.UserRegistry.getLogs(
    at org.wso2.carbon.registry.indexing.ResourceSubmitter.submitResource(
    at java.util.concurrent.Executors$
    at java.util.concurrent.FutureTask.runAndReset(
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(
    at java.util.concurrent.ScheduledThreadPoolExecutor$
    at java.util.concurrent.ThreadPoolExecutor.runWorker(
    at java.util.concurrent.ThreadPoolExecutor$
Caused by: java.sql.SQLException: PooledConnection has already been closed.

Here we do not need registry indexing to happen at the gateway node. Therefore we could disable the indexing at the gateway node to overcome this issue. However, in APIM 1.8.0 these index disabling configs are not available. So what you could do is to delay indexing at the gateway node. For this go to /conf/registry.xml file(of the gateway node) and under <indexConfiguration> , increase below interval value to few hours.


Tuesday, November 10, 2015

BPEL process and fault handeling

When it comes to bpel processes, you can handle the faults been thrown by using fault handlers. Suppose the process you invoke will throw custom fault responses. Then in your bpel process you could catch them and perform another sequence.

This is the definition of a fault handler.

        <bpel:catch faultName="ns2:fault1" faultVariable="fault1"
                <!-- your custom sequence -->

        <bpel:catch faultName="ns2:fault2" faultVariable="fault2"
                <!-- your custom sequence -->

                <!-- your custom sequence -->


As above you could define any number of different catch sequences. 'catchAll' could be used to catch any other that didn't match other clauses. However the use of the faultVariable is, you could access the faults parameters from the fault variable defined. For example, if we are passing a parameter named 'errorCode' from the fault response, we could access it from the fault variable like below.



Sunday, November 8, 2015

Adding dispatch logic to SOAPUI mock services

There might be instances, where you have few variations of a response that needs to be sent based on a logic. You could do this by adding a dispatch script including your logic like below.

1.Go to required service's process interface and change DISPATCH option to 'script'.

In this example I will be dispatching a specific response based on an element of the incoming request.

Following is the sample script .

// create XmlHolder for request content

def holder = new mockRequest.requestContent )
def arg1 = holder["//category"]

//if above element is empty
if( ! arg1 ))
 return "Invalid Input Response"

if(arg1 == "Books" )
return "SendBooksReponse"

else if(arg1 == "Articles")
return "SendArticlesResponse"

So here what we are returning would be the relevant mock response's name.