cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieve external DB procedures results

clotilde_martinez
Participant
0 Kudos

Hello experts,

I'm running IDM 7.2 sp9 patch10.

We are going to connect IDM to an Oracle DB in order to manage some users and access rights in it. We'll use stored procedures from this same DB.

I'm currently able to execute a test procedure with a toDBpass in SQL updating mode, and able to retrieve any error message that can be writtent by the procedure.

I wanted to know if it would be possible to catch a return string from the procedure that wouldn't be an error? If so, what would be the syntax in the procedure and in IDM to catch it?

If not, i imagined writing the string in some table and then read it from IDM, but first i'd like to know if the other option is possible.

Thanks a lot,

Clotilde Martinez

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member2987
Active Contributor
0 Kudos

Hi Clotilde,

That's an interesting question.  I suppose a lot of the answer would depend on what the SP does in the first place.

What is the nature of the SP? Does it return a value, execute a select statement, populate a table / view?

My basic thinking would be tat it would be a two part solution, one part to execute the SP and the other part to evaluate the results.  This does not mean that there has to be two passes or tasks. It might be possible to do this via scripting.  I'd have to know more about the Stored Procedure in question first.

Matt

clotilde_martinez
Participant
0 Kudos

Hi Matt,

yes it will populate different tables/views. It will take as parameter the user, an application name and the access value in a string formatted as a XML.

What the functional team wanted to retrieve was a XML response when the assignment status was OK with some additional data on the user.

To test i used the following syntax in my toDB pass:

declare

    -- XML request

    k_xml_request constant varchar2(2000) := '<?xml version="1.0" encoding="latin1" ?>'

            || '<access>'

            || '    <person>'

            || '        <last_name>Malmedy</last_name>'

            || '        <first_name>Jean-Marc</first_name>'

            || '    </person>'

            || '    <level>1</level>'

            || '</access>';

    -- return status

    l_ret_status varchar2(2000);

begin

    -- Call the function.

    l_ret_status := access_manager.add_access(k_xml_request);

    -- Display the return status.

    dbms_output.put_line('Return status:');

    dbms_output.put_line(l_ret_status);

end;

the dbms_output unfortunately doesn't come back to IDM but since we're able to catch the error message, I'm sure we could catch something else, I just don't know how yet...

Regards,

Clotilde