JDBC Store Procedure provisoning issue

gayare · October 1, 2024, 8:02pm

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

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

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;
	}

arjun_sengupta · October 1, 2024, 8:27pm

Hi Gayathri,

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

Regards
Arjun

gayare · October 2, 2024, 1:48am

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

arjun_sengupta · October 2, 2024, 1:51am

Hi Gayathri,

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

Ex:

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

pallavi · October 2, 2024, 2:50am

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

gayare · October 10, 2024, 9:27pm

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?

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;

rkhade · October 11, 2024, 5:39am

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,

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

Topic		Replies	Views
Calling a sql server stored procedure for rule provisioning IIQ Discussion and Questions	13	3439	July 19, 2023
How are we calling stored procedures for provisioning operations in JDBC connector ISC Discussion and Questions identity-security-cloud , jdbc-connector	8	79	March 16, 2026
JDBC provisioning Error in Global Provisioning Rule IIQ Discussion and Questions identityiq , provisioning	7	256	December 2, 2024
Stored procedure provisioning rule IIQ Discussion and Questions identityiq	15	430	June 23, 2024
JDBC connector provisioning queries ISC Discussion and Questions identity-security-cloud , provisioning , connectors	6	170	November 24, 2025

JDBC Store Procedure provisoning issue

Related topics