E9.2 Calling PL SQL from Orchecestraions - passing in variables

[JohnDanter2]

Hi folks

Does anyone here use PL SQL and have they called the stored procedures from Orchestrations at all?

We are flirting with the idea of using Orchestrations as a rapid data copy tool from PD down to UT PY etc, but we have a lot of external data linked to orders etc outside of E1
So I would like to use the same orch to enter/copy Non PD data and also find a way to add all this external data at the same time too.

Calling the PL SQL is not enough, I would like to pass in things like where MCU = Variable or AN8 = Variable. This doc says it's doable, but has anyone done this?

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=114993346247346&parent=EXTERNAL_SEARCH&sourceId=HOWTO&id=2742423.1&_afrWindowMode=0&_adf.ctrl-state=1dql93e8ec_4

Thanks

John

 

[louisphicc]

Hello John,

This is something we are doing here. We are using the Custom Groovy connectors to do it and it's easy to use parameters in the query string. We are not calling procedures, only doing basic requests such as SELECT, DELETE, INSERT and UPDATE.

Here is a quick example :

// Imports
import com.oracle.e1.common.OrchestrationAttributes;
import groovy.sql.Sql; // needed for SQL transactions

// Function header
HashMap < String, Object > main(OrchestrationAttributes orchAttr, HashMap inputMap) {
    HashMap < String, Object > returnMap = new HashMap < String, Object > ();
    
    // Inputs
    def KCOO = (String)inputMap.get('KCOO');
    def DOCO = (String)inputMap.get('DOCO');
    def DCTO = (String)inputMap.get('DCTO');
    error_flag = 0;
    
    oracleDB = Sql.newInstance('jdbc:oracle:thin:@yourServerName.yourDomainName.com:youPortNumber/Environnement', 'user', 'password', 'oracle.jdbc.OracleDriver');

    query = "SELECT * FROM SCHEMA.F4201 WHERE SHKCOO = '" + KCOO + "' AND SHDOCO = " + DOCO + " AND SHDCTO = '" + DCTO + "'";   
    
    def result = oracleDB.firstRow(query);

    // test if we have a record
    if (result == null) {
        error_flag = 1;
    }

    // loop on each row   
    oracleDB .eachRow(query) { row-> 
        lineNumber = row.SDLNID;
        // do something ...
    }
        
    // Outputs 
    returnMap.put("error_flag", error_flag);
    returnMap.put("result", result);
    
    return returnMap;
}

So far we have a dozen Orchestrations that are doing regular I/O on various databases and it is fast and reliable.
Regards

 

[eydeak]

I recently did a prototype calling a DB2 stored procedure from an orchestration. You are on an older Tools release, so I'm not sure if there is much difference.
I defined the 2 inputs I needed on the Connecter and then used this Groovy Script:

import groovy.transform.CompileStatic;
import com.oracle.e1.common.OrchestrationAttributes;
import java.sql.*;

@CompileStatic
HashMap<String, Object> main(OrchestrationAttributes orchAttr, Connection sqlConnection, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();

//get inputs
def glDate = (String)inputMap.get("GL_Date");
def glLedger = (String)inputMap.get("GL_Ledger");

//define SQL Statement: call DB2SP.GETTRIALBALANCE('2022-04-30','AA')
def selectStmt = "call DB2SP.GETTRIALBALANCE('" + glDate + "','" + glLedger + "')";

//create a prepared statement with the SQL
PreparedStatement preparedStmt = sqlConnection.prepareStatement(selectStmt);

//execute the query returning the result set
ResultSet resultSet = preparedStmt.executeQuery();

//use the helper to set the records from the result set to the array variable
// value in quotes ("rows") needs to match Data Set Variable Name on Connector
returnMap = orchAttr.mapResultSetToDataSet(resultSet, "rows");

//close the result and statement
resultSet.close();
preparedStmt.close();

return returnMap;
}

 

[JohnDanter2]

Thank you both for your inputs

It's just exploratory for now but something I would like to do yes

 

[johncsqd]

I tried to do something similar to copy edi orders from prod to py. I couldn't find an easy solution for storing the pd data in py to then write it to py. I ended up writing a powershell script using Import-Module SQLServer, Invoke-Sqlcmd and Write-SqlTableData; then put it in Windows task scheduler on the enterprise server to run a few times a day. I'm not a developer but know some powershell and sql and it was fairly quick and easy.

We're going through a project with new business units. I created another set of sql's that copy data and update them with the new mcu's. It's working much better than manually creating py orders for the changes.

I don't like having it outside of jde and will revisit moving it into orchestrator when I get to the 9.2.6's.

 

[JohnDanter2]

Thank you John, sounds cool