Issue getting Resource Object after calling a stored procedure configured in getObjectSQL

yaarlally · October 10, 2025, 2:41pm

Which IIQ version are you inquiring about?

8.4P1

Explanation Starts here:

I currently have the following configure in the getObjectSQL:

DECLARE
  P_CUR SYS_REFCURSOR;
BEGIN

  SYSADM.FD_PENDING_ITEMS(
    F_OPRID => '$(identity)',
    P_CUR => P_CUR
 );
  :P_CUR := P_CUR; --<-- Cursor
END;

I then try to run the following code to retrieve the resource object:

Application dbApp = context.getObjectByName(Application.class, "PeopleSoft Pending Items");
 
Connector connector = 	ConnectorFactory.getConnector(dbApp, null);
  
  if (connector == null){
    
    return "Connector is NOT Null!";
  } 
  
  ResourceObject ro = null;
  
 try {
      
   ro = connector.getObject("account", "JADAMS", null);
   return ro;
 } catch (Exception e){
  return "Exception: " + e; 
 }

I get a NPE. But when I configure the value directly in the query like the following:

DECLARE
  P_CUR SYS_REFCURSOR;
BEGIN

  SYSADM.FD_PENDING_ITEMS(
    F_OPRID => 'JADAMS',
    P_CUR => P_CUR
 );
  :P_CUR := P_CUR; --<-- Cursor
END;

I get the object that I am expecting.

What exactly do I need to configure in F_OPRID => '$(identity)', so that it takes the value when I pass it inconnector.getObject("account", "JADAMS", null);?

sukanta_biswas · October 10, 2025, 2:53pm

@yaarlally -

you’re already using the right token. In IdentityIQ’s JDBC connector, the literal $(identity) inside getObjectSQL is replaced at runtime with the nativeIdentity value you pass to connector.getObject("account", "<nativeId>", null). Example (valid):

DECLARE
  P_CUR SYS_REFCURSOR;
BEGIN
  SYSADM.FD_PENDING_ITEMS(
    F_OPRID => '$(identity)',
    P_CUR   => P_CUR
  );
  :P_CUR := P_CUR;
END;

That said, the NPE almost never means the token failed—it usually means the connector didn’t get a usable row set back (or the row set didn’t include the required Account ID column), so the ResourceObject couldn’t be built. Do these checks:

Confirm the nativeIdentity you pass matches what your app expects

Whatever you put in getObject("account", "JADAMS", null) must equal the application’s Account ID (Identity Attribute) value for that account. If your app’s Account ID is OPRID, make sure the cursor returns that column and that it matches “JADAMS”.
If the cursor returns a different column name, alias it to your Account ID attribute name, e.g. … SELECT oprid AS accountId, … (or whatever your Account Schema’s Identity Attribute is called).

Make sure the ref cursor returns at least the Account ID column every time

If your PL/SQL sometimes returns no rows (or a row without the Account ID), IIQ can throw an NPE when building the ResourceObject. Re-run the block in SQL*Plus/SQL Developer with the same input to confirm it returns a row for “JADAMS”.

Verify the Account Schema & mapping

In the application’s Account Schema, ensure the Identity Attribute (Account ID) is set and that your result set includes that column name exactly. If names differ, use SQL aliases in the ref cursor output to match.
Ensure any required attributes in your schema are present in the cursor output.

Sanity-check object class and call site

getObject("account", …) is correct for Account objects.
Your Connector connector = ConnectorFactory.getConnector(dbApp, null); is fine; you already guard against null.

If you want a quick A/B test: temporarily replace the PL/SQL block with a straight SELECT using $(identity) (e.g., SELECT … FROM … WHERE oprid='$(identity)'). If that works but the PL/SQL version doesn’t, the issue is inside the procedure or how the cursor columns are named/returned—not the token.

Reference showing $(identity) usage in getObjectSQL (JDBC): community thread examples use exactly … WHERE a.user_id='$(identity)'. (SailPoint Developer Community)

If you share (a) your Account Schema’s Identity Attribute name and (b) a sample of the cursor’s column list, I can tweak the block or add the right aliases so the ResourceObject builds cleanly.

Cheers!!!

yaarlally · October 10, 2025, 3:12pm

Hello @sukanta_biswas ,

First of all, I really appreciate your response. Here is the information you requested:

Schema:

Is this the cursor’s column list you requested?

OFSDEV7
SQL> set serveroutput on
SQL> variable P_CUR refcursor
SQL> DECLARE
 
  P_CUR SYS_REFCURSOR;
 
BEGIN
 
 
   
   SYSADM.FD_NFE_ARCS_PENDING_ITEMS(F_OPRID => 'JSCHMITT',   P_CUR => P_CUR);

   :P_CUR := P_CUR ;
END;
PL/SQL procedure successfully completed.
SQL> print P_CUR
 
APPROVAL_OPRID                 RESULT
------------------------------ ------
JSCHMITT                       True  
1 row selected.

Here is what the object look like after hardcoding it:

sukanta_biswas · October 10, 2025, 4:31pm

@yaarlally -

The setup looks correct.

1) Sanity checks (the quick wins)

A. Make sure you’re testing the same user

In your SQL print you used JSCHMITT but your Java test uses JADAMS. If the procedure returns no row for JADAMS, the connector will hand back a null ResourceObject, which then surfaces as an NPE when IIQ tries to use it.

Action: Change your test call to the one you’ve proven returns a row:

ro = connector.getObject("account", "JSCHMITT", null);

2) Your Java rule snippet (minor fix & safer return)

You had a tiny logic inversion and no null-guard on the return. Here’s a clean version:

Application dbApp = context.getObjectByName(Application.class, "PeopleSoft Pending Items");
Connector connector = ConnectorFactory.getConnector(dbApp, null);
if (connector == null) {
    return "Connector is NULL!";
}

try {
    ResourceObject ro = connector.getObject("account", "JSCHMITT", null); // test with a known-good value
    if (ro == null) {
        return "No object returned (null). Likely no row for the given nativeIdentity.";
    }
    // Convert to XML for easy viewing (avoids NPE when rendering)
    String xml = ro.toXml();
    return xml;
} catch (Exception e) {
    return "Exception: " + e;
}

3) If it still fails with `$(identity)`

Enable debug for sailpoint.connector.JDBCConnector and run the Preview again. You’ll see the final SQL sent to Oracle—confirm the token is replaced as expected.

Also, can you share the exact NPE stack (one frame above your rule), or the debug line showing the rendered SQL.

Cheers!!!

yaarlally · October 10, 2025, 5:12pm

2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering invokeLogger: Arguments => debug, [Ljava.lang.Object;@7e579ea9
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering logMe: Arguments => debug, User, JSCHMITT
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$34: Arguments => User, JSCHMITT
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$34: Arguments => User, JSCHMITT, Returns => User : JSCHMITT
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$34: Arguments => User, JSCHMITT
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$34: Arguments => User, JSCHMITT, Returns => User : JSCHMITT
2025-10-10T13:02:27,413 DEBUG https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:2364 - User : JSCHMITT
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting logMe: Arguments => debug, User, JSCHMITT, Returns => N/A
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering logMe: Arguments => debug, Object Type, account
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$34: Arguments => Object Type, account
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$34: Arguments => Object Type, account, Returns => Object Type : account
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$34: Arguments => Object Type, account
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$34: Arguments => Object Type, account, Returns => Object Type : account
2025-10-10T13:02:27,413 DEBUG https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:2364 - Object Type : account
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting logMe: Arguments => debug, Object Type, account, Returns => N/A
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting invokeLogger: Arguments => debug, [Ljava.lang.Object;@7e579ea9, Returns => N/A
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting printLogs: Arguments => debug, [Ljava.lang.Object;@7e579ea9, Returns => N/A
2025-10-10T13:02:27,413 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering getObject: Arguments => account, JSCHMITT, null
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering getConnection: Arguments => sailpoint.object.Schema@52e7c548[id=0a4883a69996144c819996f51c0d00d7,name=<null>], null
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:108 - Exiting getConnection: Arguments => sailpoint.object.Schema@52e7c548[id=0a4883a69996144c819996f51c0d00d7,name=<null>], null, Returns => 367186048, URL=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=***********)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=OFSDEV7))), UserName=STDSAILPOINTID, Oracle JDBC driver
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering getFetchSQL: Arguments => sailpoint.object.Schema@52e7c548[id=0a4883a69996144c819996f51c0d00d7,name=<null>], JSCHMITT
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:108 - Exiting getFetchSQL: Arguments => sailpoint.object.Schema@52e7c548[id=0a4883a69996144c819996f51c0d00d7,name=<null>], JSCHMITT, Returns => DECLARE
  P_CUR SYS_REFCURSOR;
BEGIN

  SYSADM.FD_NFE_ARCS_PENDING_ITEMS(
    F_OPRID => ?,
    P_CUR => P_CUR
 );
  :P_CUR := P_CUR; --<-- Cursor
END;

2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering execute: Arguments => null, null, 367186048, URL=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=***********)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=OFSDEV7))), UserName=STDSAILPOINTID, Oracle JDBC driver, DECLARE
  P_CUR SYS_REFCURSOR;
BEGIN

  SYSADM.FD_NFE_ARCS_PENDING_ITEMS(
    F_OPRID => ?,
    P_CUR => P_CUR
 );
  :P_CUR := P_CUR; --<-- Cursor
END;
, false, true
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:115 - Throwing execute - java.lang.NullPointerException
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering closeStatement: Arguments => null
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:108 - Exiting closeStatement: Arguments => null, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering closeResult: Arguments => null
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:108 - Exiting closeResult: Arguments => null, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering closeConnection: Arguments => 367186048, URL=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=***********)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=OFSDEV7))), UserName=STDSAILPOINTID, Oracle JDBC driver
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:108 - Exiting closeConnection: Arguments => org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper@15e2d080, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:97 - Entering closeStatement: Arguments => null
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:108 - Exiting closeStatement: Arguments => null, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:115 - Throwing getObject - java.lang.NullPointerException
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering printLogs: Arguments => info, [Ljava.lang.Object;@280f3fd9
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering invokeLogger: Arguments => info, [Ljava.lang.Object;@280f3fd9
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering logMe: Arguments => info, User, JSCHMITT
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$33: Arguments => User, JSCHMITT
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$33: Arguments => User, JSCHMITT, Returns => User : JSCHMITT
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$33: Arguments => User, JSCHMITT
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$33: Arguments => User, JSCHMITT, Returns => User : JSCHMITT
2025-10-10T13:02:27,418  INFO https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:2362 - User : JSCHMITT
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting logMe: Arguments => info, User, JSCHMITT, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering logMe: Arguments => info, Object Type, account
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$33: Arguments => Object Type, account
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$33: Arguments => Object Type, account, Returns => Object Type : account
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$logMe$33: Arguments => Object Type, account
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$logMe$33: Arguments => Object Type, account, Returns => Object Type : account
2025-10-10T13:02:27,418  INFO https-openssl-nio-443-exec-1 sailpoint.connector.JDBCConnector:2362 - Object Type : account
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting logMe: Arguments => info, Object Type, account, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting invokeLogger: Arguments => info, [Ljava.lang.Object;@280f3fd9, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting printLogs: Arguments => info, [Ljava.lang.Object;@280f3fd9, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering updateApplicationConfig: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering getLocalApplication: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting getLocalApplication: Arguments => N/A, Returns => sailpoint.object.Application@58e62bbf[id=0a4883a58b251cd1818b253eef0601d3,name=PeopleSoft Pending Items]
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting updateApplicationConfig: Arguments => N/A, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering clearConnectorEnv: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering removeLogContext: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering getApplication: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting getApplication: Arguments => N/A, Returns => sailpoint.object.Application@58e62bbf[id=0a4883a58b251cd1818b253eef0601d3,name=PeopleSoft Pending Items]
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting removeLogContext: Arguments => N/A, Returns => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering resetContextClassloader: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$resetContextClassloader$31: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$resetContextClassloader$31: Arguments => N/A, Returns => Resetting the context class loader for this thread to: ParallelWebappClassLoader
  context: arcs
  delegate: false
----------> Parent Classloader:
java.net.URLClassLoader@6b09bb57
 from: sailpoint.connector.ConnectorClassLoader@664d7d8a
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:97 - Entering lambda$resetContextClassloader$31: Arguments => N/A
2025-10-10T13:02:27,418 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting lambda$resetContextClassloader$31: Arguments => N/A, Returns => Resetting the context class loader for this thread to: ParallelWebappClassLoader
  context: arcs
  delegate: false
----------> Parent Classloader:
java.net.URLClassLoader@6b09bb57
 from: sailpoint.connector.ConnectorClassLoader@664d7d8a
2025-10-10T13:02:27,423 DEBUG https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:2192 - Resetting the context class loader for this thread to: ParallelWebappClassLoader
  context: arcs
  delegate: false
----------> Parent Classloader:
java.net.URLClassLoader@6b09bb57
 from: sailpoint.connector.ConnectorClassLoader@664d7d8a
2025-10-10T13:02:27,423 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting resetContextClassloader: Arguments => N/A, Returns => N/A
2025-10-10T13:02:27,423 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:108 - Exiting clearConnectorEnv: Arguments => N/A, Returns => N/A
2025-10-10T13:02:27,423 TRACE https-openssl-nio-443-exec-1 sailpoint.connector.ConnectorProxy:115 - Throwing getObject - java.lang.NullPointerExceptions

Here are the trace logs, and I not seeing the value being passed into the query. Not sure what I am doing wrong here. I appreciate your patience looking at this.

My SQL Statement and getObjectSQL looks like this:

Topic		Replies	Views
Executing getObjectsql from JDBC connector IIQ Discussion and Questions identityiq , connectors	1	65	November 14, 2024
Missing IN or OUT parameter at index 2 - Oracle Stored Procedure JDBC Connector Issue IIQ Discussion and Questions identityiq , jdbc-connector	4	66	April 28, 2026
Single Account Query cannot fetch '$(identity)' ISC Discussion and Questions rules , aggregation , identity-security-cloud , provisioning , jdbc-connector	3	196	October 29, 2024
Trigger email notification when AccountRequest operation failied when using JDBC Rule ISC Discussion and Questions identity-security-cloud , jdbc-connector	1	534	June 14, 2023
GetObject for JDBC connector ISC Discussion and Questions aggregation , identity-security-cloud	6	1114	October 13, 2023