How do I make a call to a stored procedure in Oracle?

Question

I have a stored procedure on an Oracle database that I need to call from inside of a mapping function.  This stored procedure accepts as a parameter a CURSOR and that CURSOR is updated with the result-set, I then need to cycle the result set and get the data.  How do I make that call?

Answer 1

There are two ways to make this call.  Both ways require you to get a raw database connection by using the command:

qie.getDbConnection('{connection name'});

Once you have the connection you can create your query.  This can be done in two ways.

The first example is:

// this is the raw database connection
var connection = qie.getDbConnection('Db Connection');

try {
    // Setup the query, the Stored Procedure may have input paramaters,
    // so you would pass that in sometimes there are no parameters,
    // so you would just need to include `:cursor`
    var query = "{call STORED_PROCEDURE('Input1', 'Input2', :cursor)}";

    // We have to prepare the query to be executed, and get a handle
    // on that query
    var cstmt = connection.prepareCall(query);

    // We now register our `:cursor` with this query
    // In this statement we will use the Oracle JDBC jar to Identify
    // the output object as a CURSOR
    cstmt.registerOutParameter('cursor', Packages.oracle.jdbc.OracleTypes.CURSOR);

    // Now we can execute the statement
    // If there is a problem with the statement, this is where we
    // will get the error
    cstmt.execute();

    // Put the cursor into a local variable
    var cursorObject = cstmt.getObject('cursor');

    // Last we can now cycle the CURSOR and get the output
    // If there was nothing in the result set, then cursorObject.next()
    // will be 'false' and you won't get anything

    // In this example, the output will put the first column of the output
    // in our `output` variable
    var output = '';
    while (cursorObject.next()) {
       output += cursorObject.getString(1) + '\n';
    }
} finally {
    // close the connection to avoid connection leaks
    connection.close();
}

The second example is very similar to the first example, but we can do it using a pl sql script:

// this is the raw database connection
var connection = qie.getDbConnection('Db Connection');

try {
    // Setup the query, the Stored Procedure may have input paramaters,
    // so you would pass that in sometimes there are no parameters,
    // so you would just need to include `:cursor`
    // The `BEGIN` and `END;` statements allow you to script a query or call.
    var query =  "BEGIN STORED_PROCEDURE(:cursor); END;";

    // We have to prepare the query to be executed, and get a handle on that query
    var cstmt = connection.prepareCall(query);

    // We now register our `:cursor` with this query
    // In this statement we will use the Oracle JDBC jar to Identify the output
    // object as a CURSOR
    cstmt.registerOurParameter('cursor', Packages.oracle.jdbc.OracleTypes.CURSOR);

    // Now we can execute the statement
    // If there is a problem with the statement, this is where we will get the error
    cstmt.execute();

    // Put the cursor into a local variable
    var cursorObject = cstmt.getObject('cursor');

    // Last we can now cycle the CURSOR and get the output
    // If there was nothing in the result set, then cursorObject.next() will be
    // 'false' and you won't get anything
    // In this example, the output will put the first column of the output in
    // our `output` variable
    var output = '';
    while (cursorObject.next()) {
       output += cursorObject.getString(1) + '\n';
    }
} finally {
    // close the connection to avoid connection leaks
    connection.close();
}