Writing a script to configure connector behavior
You must write a script file to define SQL commands used in the interaction between the agtoraebiz connector and the Oracle E-Business Suite. A sample script, agtorascript-oraebiz.cfg is contained in the samples directory. Note that any sample script may need customizations in order to work with your system.
Learn more about writing script files for SQL application connectors.
The agent script, agtoraebiz.cfg is contained in the agent directory.
Stored Procedure calls
Calls to stored procedures must be called in an anonymous PL/SQL block. The SQL key-value for the operation must include the anonymous PL/SQL block. The anonymous PL/SQL block begins with "Begin and ends with End;".
Calls to stored procedures must be called in an anonymous PL/SQL block. The SQL key-value for the operation must include the anonymous PL/SQL block. The anonymous PL/SQL block begins with "Begin and ends with End;".
For example, write:
"sql"= "begin app.adduser(:acctID, :newPW); end;"
When an ora-20410 error code is returned by a stored procedure, Bravura Security Fabric displays the message text to the end user. These messages should be meaningful to the end user and describe something they can act on alone. If another error code is returned, Bravura Security Fabric displays the code only; because these errors are not likely to be meaningful to end users, describe internal errors, or contain security vulnerabilities. It is expected the user will pass on the code to an administrator.
Stored procedure calls can be made for operations that list users, groups, or group members. The stored procedure needs to return a SYS_REFCURSOR and that parameter needs to be bound to a built-in variable ’cursorret’:
"sql"= "begin app.listusers(:cursorret); end;"
The columns defined in the returns KVGroup need to match the sequence of the cursor returned by the procedure.
"sql"= "begin app.adduser(:acctID, :newPW); end;"
When an ora-20410 error code is returned by a stored procedure, Bravura Security Fabric displays the message text to the end user. These messages should be meaningful to the end user and describe something they can act on alone. If another error code is returned, Bravura Security Fabric displays the code only; because these errors are not likely to be meaningful to end users, describe internal errors, or contain security vulnerabilities. It is expected the user will pass on the code to an administrator.
Stored procedure calls can be made for operations that list users, groups, or group members. The stored procedure needs to return a SYS_REFCURSOR and that parameter needs to be bound to a built-in variable ’cursorret’:
"sql"= "begin app.listusers(:cursorret); end;"
The columns defined in the returns KVGroup need to match the sequence of the cursor returned by the procedure.
Binding versus substitution
There are two options available for binding values for Oracle. The script connector agtoraebiz can either bind using names or text substitution.
Binding by name is the preferred method of linking the variable data with the SQL statement. Substitution should be used when binding isn’t available.
For example, a sql key-value in the script that includes binding:
"sql" = "insert into people (id, name, passwd) values (:acctID, :fullname, :newPW)"
For calls involving DDL (for example: alter user or create table), bind variables cannot be used. In these cases, substitution variables must be used instead; for example:
"sql" = "alter user %k:_acctID% identified by %k:_newPW%"