Stored procedure provisioning rule

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Which IIQ version are you inquiring about?

Version 8.3

Share all details related to your problem, including any error messages you may have received.

We are getting below error while creating an account on Target application using Stored procedure. Could you please anyone help me to solve here.

Note : Stored procedure and provisioning rule are attached for your reference.

java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol “CREATE” when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

2

Could you please show us the rule? Seems it’s not attached

3

ASIA Sunsmart SP.xml (12.6 KB)

Please check now.

4

Are you sure line 106 is correct?
String createUserCall = "{call CREATE OR REPLACE PROCEDURE insertUSER_M(?,?,?,?,?,?)}";
as you see here

image
image519×552 8.88 KB

CREATE OR REPLACE PROCEDURE is used do define THE stored procedure. You shouldn’t us it to call the procedure.

5

In the Call statement you just need to call the Procedure, not Crete it or replace it.

best!

6

Correct. The JDBC syntax, which I constantly forget, is:

{ ? = call package_name.procedure_name(...) }

The initial ? = is optional and only if you are using a FUNCTION to return a value, rather than a PROCEDURE with a void return.

7

They told us to use complete below SP(Query) in
STORED PROCEDURES FOR SUNSMART SLGFI.docx (16.5 KB)
prv rule. Please advise.

PRE-REQUISITE:
CREATE OR REPLACE TYPE RoleList AS TABLE OF VARCHAR2(1024);

STORED PROCEDURE:

CREATE OR REPLACE PROCEDURE insertUSER_M(
p_userid IN NVARCHAR2,
p_username IN NVARCHAR2,
p_email IN NVARCHAR2,
p_accountstartdt IN TIMESTAMP,
p_accountenddt IN TIMESTAMP,
p_RoleList IN RoleList
)
IS
BEGIN
INSERT INTO USER_M (USERSEQ,USERID,USERNAME,EMAILID,USERSTATUSCD,USERLOCATIONID,ACCOUNTSTARTDT,ACCOUNTENDDT,LANGUAGECD,VERSIONID,GUID,USERTYPE,OFFLINEACCESS)
VALUES (SEQ_USER_M.NEXTVAL,p_userid,p_username,p_email,‘ACTIVE’,‘HO’,p_accountstartdt,p_accountenddt,‘EN’,1,SEQ_USER_M.CURRVAL,‘INTERNAL’,‘YES’);

FOR i IN 1..p_RoleList.COUNT LOOP
    INSERT INTO USERROLEMAP (USERROLEMAPSEQ,USERID,USERROLECD,VERSIONID,GUID,ROLESEQ)
    VALUES (SEQ_USERROLEMAP.NEXTVAL, p_userid, p_RoleList(i), 1, SEQ_USERROLEMAP.CURRVAL,(select roleseq from role_m where userrolecd=p_RoleList(i)));
END LOOP;

COMMIT;

END;
/

8

Hi Venugopala,
As we told you already - the query which you just shared is basicaly query to CREATE stored procedure in your database.

You have to execute it manualy ONCE. After it’s created it’s available for normal execution from you rule.

In this case you can see the procedure is called
insertUSER_M
and it accepts 6 attributes,
p_userid, p_username, p_email, p_accountstartdt, p_accountenddt and p_RoleList.

That means after you create it in the database you have to execute
call inserUSER_M ($userId,$userName,$email,$accountStartDt,$accountEndDt,$roleList);
(of course with correct values, types etc…

9

Yes We have told them(App team) the something. But they are telling to run full query from rule.

10

You can’t directly execute a CREATE OR REPLACE PROCEDURE statement from within a Java program using JDBC. The purpose of JDBC is to interact with an already existing database schema, not to create or modify the schema itself.

If you need to create or replace a stored procedure from your Java code, you typically have a few options:

  1. Pre-create the Procedure:
    Before running your Java program, ensure that the stored procedure is already created in your database. This is the most common approach for production systems where database schema changes are managed separately.

  2. Use SQL Script Execution:
    Include the SQL script for creating or replacing the procedure within your Java program, and execute this script using JDBC. This approach is suitable for development or testing scenarios but may not be recommended for production due to security and manageability concerns.

11

Sample code:

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ExecuteSqlScript {
public static void main(String args) {
// JDBC URL, username, and password
String url = “jdbc:mysql://server:port/your_database”;
String user = “username”;
String password = “password”;

    // SQL script file path
    String scriptFilePath = "path/to/your/sql_script.sql";

    try {
        // Establish a connection to the database
        Connection connection = DriverManager.getConnection(url, user, password);

        // Read the SQL script file
        BufferedReader reader = new BufferedReader(new FileReader(scriptFilePath));
        StringBuilder scriptContent = new StringBuilder();
        String line;
        while ((line = reader.readLine()) != null) {
            scriptContent.append(line).append("\n");
        }
        reader.close();

        // Execute the SQL script
        Statement statement = connection.createStatement();
        statement.execute(scriptContent.toString());

        // Close the statement and connection
        statement.close();
        connection.close();

        System.out.println("SQL script executed successfully.");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

12

This is pure java code so it might not work directly in IIQ rule. And also there’s a lot of unneccesary operations in the code for IIQ as it is handled in beanshell.

13

Hi Kamil,

can we get sample JDBC provisioning rule for stored procedure? Thanks in advance.

Regards,
venu

14

I just modified your rule - try it
ASIA Sunsmart SP (1).xml (12.6 KB)

15

Yeah. Its a sample java code to first validate if this use case works from your IDE and if yes, use this to build your beanshell code.