Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
Rick

Avatar / Picture

Senior Veteran
Registered:
Posts: 255
Reply with quote  #1 

I have recently taken over a rather tedious monthly data admin task from a colleague. Most of it is mucking about with data in spread sheets and could easily be accomplished in SQL Server so I had a mind to write a BPM process with a timed action which could do this job for me. (We are running BPM on SQL Server.)

The only problem I have come across is that the initial data is pulled from an Oracle database. I'm comfortable with running SQL scripts against Oracle from BPM however this is not a simple 'select' statement. This is a PL/SQL procedure which is run from the SQL*Plus prompt with the command @DataExtract.sql. The command populates an Oracle table from which I then extract the data.

Can anyone advise me on how I can issue this command from within BPM please?

All the stuff done in the PL/SQL could, I suppose, be done within BPM but re-writing it all would take much longer than the potential time saving from the conversion!

Thanks in advance,

Rick.


__________________

Another full day of doing nothing but rearranging zeros and ones. :)
You know it will be a good day when there is no human interaction on the schedule.

0
BMellert

Guru
Registered:
Posts: 670
Reply with quote  #2 
Off the top of my head, and without knowing what the scripts are doing more,
I accomplish something similar by creating a variable with the code in DataExtract.sql and run it using SelectSQL(conn,variable)
0
Rick

Avatar / Picture

Senior Veteran
Registered:
Posts: 255
Reply with quote  #3 
Thanks for the reply, Brian.

I've done similar things myself with single SELECT, INSERT, UPDATE and DELETE scripts. This piece of code, however, is a bit too much of a beast for that approach. It is a collection of about a dozen such scripts and also a PL/SQL block containing a cursor, several variables and a three or four level deep set of nested 'if' statements.

This is why I just want to run it 'as is' in Oracle SQL rather than re-writing it in MS SQL or using BPM code!

Rick.

__________________

Another full day of doing nothing but rearranging zeros and ones. :)
You know it will be a good day when there is no human interaction on the schedule.

0
BMellert

Guru
Registered:
Posts: 670
Reply with quote  #4 
Since its populating a data table and not returning results directly to the caller, I'd think you'd still be able convert your .sql file into a new stored procedure and call that.

Otherwise, I'm not aware of a way to get MBPM to call external commands (it also opens you up to possible hacking).

However, could you create a batch file which runs your SQL then calls a flag to <whatever> from the resulting data table?  It could be scheduled or run on demand.
0
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #5 
use a timer to kick off the process where time(cur) > var(time).  Then at the end of that just add 24 hours or w/e time you need to var(time) so it'll fire off again tomorrow at the same time.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!