# JDBC Store Procedure provisoning issue

**URL:** <https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545>\
**Category:** SHF Discussion and Questions\
**Tags:** rules, provisioning, sailpoint-identity-security\
**Created:** [October 1, 2024, 8:02pm UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545 "2024-10-01T20:02:35Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![gayare](https://avatars.discourse-cdn.com/v4/letter/g/51bf81/32.png) [@gayare](https://developer.sailpoint.com/discuss/u/gayare)\
**Post date:** [October 1, 2024, 8:02pm UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/1 "2024-10-01T20:02:35Z")

</div>

Hi,

I have created JDBC connector and using below JDBC provisioning rule (Store procedures). when I tried to provision getting below error.

java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘PR\_INSERT\_USER’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored [ORA-06550 - Database Error Messages](https://docs.oracle.com/error-help/db/ora-06550/)

From DB we are able to create user successfully, but SailPoint it is getting failed.

 ![image](https://global.discourse-cdn.com/sailpoint/original/3X/4/3/43ffe4a57a6fd22c23623ebf9fb6cbb810f5151a.jpeg)

```auto
import java.sql.CallableStatement;
	import java.sql.SQLException;
	import java.util.List;

	import sailpoint.object.ProvisioningPlan;
	import sailpoint.object.ProvisioningPlan.AccountRequest;
	import sailpoint.object.ProvisioningPlan.AttributeRequest;
	import sailpoint.object.ProvisioningResult;

	public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
	if (acctReq != null) {
		AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
		if (attrReq != null) {
			return (String) attrReq.getValue();
		}
	}
	return null;
	}
	ProvisioningResult result = new ProvisioningResult();
	CallableStatement statement;

	if (plan != null) {
		List accounts = plan.getAccountRequests();
		if ((accounts != null) && (accounts.size() > 0)) {
			for (AccountRequest account : accounts) {
				try {
					if (AccountRequest.Operation.Create.equals(account.getOperation())) {
						statement = connection.prepareCall("{call CSGSYSREP.CSGS_USER_ROLE_API_PKG.PR_INSERT_USER(?,?,?,?,?)}");
						statement.setString(1, (String) account.getNativeIdentity());
						// statement.setString ( 2, getAttributeRequestValue(account,"USER_ID") );
						statement.setString(2, getAttributeRequestValue(account, "FIRST_NAME"));
						statement.setString(3, getAttributeRequestValue(account, "LAST_NAME"));
						statement.setString(4, getAttributeRequestValue(account, "EMAIL_ID"));
						statement.setString(5, getAttributeRequestValue(account, "ROLE_NM"));
						statement.executeUpdate();

						result.setStatus(ProvisioningResult.STATUS_COMMITTED);
					}
				} catch (SQLException e) {
					result.setStatus(ProvisioningResult.STATUS_FAILED);
					result.addError(e);
				}
			}
		}
		return result;
	}

```

---

<div class="post-metadata">

**Author:** ![arjun\_sengupta](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/arjun_sengupta/32/3594_2.png) [@arjun\_sengupta](https://developer.sailpoint.com/discuss/u/arjun_sengupta)\
**Post date:** [October 1, 2024, 8:27pm UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/2 "2024-10-01T20:27:28Z")

</div>

Hi Gayathri,

Is the number and data type of arguments passed in the function same as the stored procedure?

Regards  
Arjun

---

<div class="post-metadata">

**Author:** ![gayare](https://avatars.discourse-cdn.com/v4/letter/g/51bf81/32.png) [@gayare](https://developer.sailpoint.com/discuss/u/gayare)\
**Post date:** [October 2, 2024, 1:48am UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/3 "2024-10-02T01:48:15Z")

</div>

> [@arjun\_sengupta](#):
>
> Is the number and data type of arguments passed in the function same as the stored procedure?

How to find that, What type of data arguments procedure accepting ??

---

<div class="post-metadata">

**Author:** ![arjun\_sengupta](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/arjun_sengupta/32/3594_2.png) [@arjun\_sengupta](https://developer.sailpoint.com/discuss/u/arjun_sengupta)\
**Post date:** [October 2, 2024, 1:51am UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/4 "2024-10-02T01:51:00Z")

</div>

Hi Gayathri,

You can check the stored procedure definition. First 2 are input arguments

Ex:

```auto
CREATE PROCEDURE uspUpdateEmpSalary
    @empId INT,
    @salary MONEY
AS
BEGIN
    UPDATE dbo.Employee
    SET Salary = @salary
    WHERE EmployeeID = @empId;
END

```

There are some return arguments in your screenshot which also needs to be passed and will be defined as Out arguments.

Regards  
Arjun

---

<div class="post-metadata">

**Author:** ![pallavi](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/pallavi/32/17939_2.png) [@pallavi](https://developer.sailpoint.com/discuss/u/pallavi)\
**Post date:** [October 2, 2024, 2:50am UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/5 "2024-10-02T02:50:09Z")

</div>

Hi Gayathri,

Is this issue resolved? If its still reproducible after fixing the number of input/output argument, then please check if its related to the type of argument. For e.g., we were receiving ORA-06550 error when the date column field value was provided in incorrect format.

Thanks,  
Pallavi

---

<div class="post-metadata">

**Author:** ![gayare](https://avatars.discourse-cdn.com/v4/letter/g/51bf81/32.png) [@gayare](https://developer.sailpoint.com/discuss/u/gayare)\
**Post date:** [October 10, 2024, 9:27pm UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/6 "2024-10-10T21:27:25Z")

</div>

I updated the rule and tested the account is creating, but it is not replicating into DB the created account and I can see USER\_ID is not populated in SailPoint. Any idea?

 ![image](https://global.discourse-cdn.com/sailpoint/original/3X/c/b/cbddba16f0e8156e0be042524ed16c32e1b819aa.png)  
 ![image](https://global.discourse-cdn.com/sailpoint/original/3X/5/4/54d1f4fb5a31704c173a89a63de622ac03c05ba3.png)

```auto
import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.List;
  import sailpoint.api.SailPointContext;
  import sailpoint.connector.JDBCConnector;
  import sailpoint.object.Application;
  import sailpoint.object.ProvisioningPlan;
  import sailpoint.object.ProvisioningPlan.AccountRequest;
  import sailpoint.object.ProvisioningPlan.AttributeRequest;
  import sailpoint.object.ProvisioningPlan.PermissionRequest;
  import sailpoint.object.ProvisioningResult;
  import sailpoint.object.Schema;

  public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {

    if ( acctReq != null ) {
      AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
      if ( attrReq != null ) {
        return attrReq.getValue();
      }
    }
    return null;
  }

  ProvisioningResult result = new ProvisioningResult();
  PreparedStatement statement;

  if ( plan != null ) {

    List accounts = plan.getAccountRequests();
    if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
      for ( AccountRequest account : accounts ) {
        try {
          if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) 
		  {
				result.setStatus( ProvisioningResult.STATUS_FAILED );
				result.addError("Create Account Block is not available in the JDBC provisining rule.");
					

          }
		  else if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {

            // Modify account request -- change role

            PreparedStatement statement = connection.prepareStatement( "UPDATE ORIONUSERSMT SET ADMIN= ? WHERE NAME = ?" );
            statement.setString ( 2, (String) account.getNativeIdentity() );
			try
			{
				if ( account != null ) {
				  AttributeRequest attrReq = account.getAttributeRequest("groups");
					if(attrReq != null && attrReq.getValue() instanceof String) 
					{
					    if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
							statement.setString ( 1, "0" );
							statement.executeUpdate();
							//result.setStatus( ProvisioningResult.STATUS_COMMITTED );
						} 
						else {
							statement.setString(1, attrReq.getValue());
							statement.executeUpdate();
							//result.setStatus( ProvisioningResult.STATUS_COMMITTED );
						}	
					}
					else{
						//result.setStatus( ProvisioningResult.STATUS_COMMITTED );
					}
				}
			}
			catch (SQLException e) 
			{
				result.setStatus(ProvisioningResult.STATUS_FAILED);
				result.addError(e);
			}
            result.setStatus( ProvisioningResult.STATUS_COMMITTED );

          } else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {

           
			PreparedStatement statement = connection.prepareStatement( "UPDATE ORIONUSERSMT SET ADMIN= ? WHERE NAME = ?" );
			try
			{
				statement.setString ( 2, (String) account.getNativeIdentity() );
				statement.setString ( 1, "0" );
				statement.executeUpdate();
				result.setStatus( ProvisioningResult.STATUS_COMMITTED );
			}
			catch (SQLException e) 
			{
				result.setStatus(ProvisioningResult.STATUS_FAILED);
				result.addError(e);
			}
          }
        }
        catch( SQLException e ) {
          result.setStatus( ProvisioningResult.STATUS_FAILED );
          result.addError( e );
        }
        finally {
          if(statement != null) {
            statement.close();
          }
        }
      }
    }
  }

  return result;

```

---

<div class="post-metadata">

**Author:** ![rkhade](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/rkhade/32/6043_2.png) [@rkhade](https://developer.sailpoint.com/discuss/u/rkhade)\
**Post date:** [October 11, 2024, 5:39am UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/7 "2024-10-11T05:39:19Z")

</div>

Hi @gayare ,

I have used Below rule in JDBC connector to create accounts using stored procedure, can you try this once

Please modify the rule as per the requirement,

```auto
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

public Object getAttributeRequestValue(AccountRequest acctReq, String attribute) {
	
    if ( acctReq != null ) {
        AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
        if ( attrReq != null ) {
            return attrReq.getValue();
		}
	}
    return null;
}
ProvisioningResult result = new ProvisioningResult();

if ( plan != null ) {
	
    List accounts = plan.getAccountRequests();
	
    if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
        for ( AccountRequest account : accounts ) {
			
			if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) {
					String employeeID = account.getNativeIdentity();
					String displayName = getAttributeRequestValue(account,"Full Name");
					String deptName = getAttributeRequestValue(account,"Department");
					int role = getAttributeRequestValue(account,"Role");

					CallableStatement st = connection.prepareCall("? = call dbo.up_addUserAccess(?,?,?,?,?)");
					st.registerOutParameter(1, java.sql.Types.INTEGER);
					st.setString(2, employeeID);
					st.setString(3, displayName);
					st.setInt(4, role);
					st.setString(5, "8190");
					st.setString(6, deptName);
					st.execute();
					result.setStatus(ProvisioningResult.STATUS_COMMITTED);
				}
		}
	}
}
return result;

```

Let me know how it goes

Thank you

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/sailpoint/original/2X/f/f2136700ed5e3703e0b85e02f6be799dacca7735.png) [@system](https://developer.sailpoint.com/discuss/u/system)\
**Post date:** [December 10, 2024, 5:40am UTC](https://developer.sailpoint.com/discuss/t/jdbc-store-procedure-provisoning-issue/83545/8 "2024-12-10T05:40:08Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.