Skip to Main Content
  • Questions
  • Alternate to pro*c batch programming

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jagan.

Asked: November 06, 2019 - 11:17 am UTC

Last updated: November 08, 2019 - 5:03 am UTC

Version: oracle 12c

Viewed 1000+ times

You Asked

Dear Team,

We would like to have suggestion on the topic related to alternate programming for the existing batch system in pro*c with oracle 12c database.

Currently we have many number of batch jobs which are in pro*c (by interacting with oracle database) and uses master-slave methodology.

Now we are in the search of decomissioning the pro*c by keeping the oracle database and replace with new programming language which should match the speed given by pro*c.

And we have explored few alternatives like spring batch and ....

We would like to have expert opnion on this to shed some light.

Thank You.

and Connor said...

I suppose my first question would be - what is the issue with pro*c ?

Anyway, it very much depends on your requirements. I worked at a place where we migrated a stack of pro*C and C#/odp.net batch processes entirely in PLSQL. Its close to the database, has fantastic SQL integration and is easy to build and deploy.

So for me, PLSQL is your first port of call. After that, my next driving requirement would be - what skills do your people have? It doesn't matter if (say) Python is "really cool" if no-one on your workforce has python skills.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Job security!

Stew Ashton, November 07, 2019 - 8:15 am UTC

This is a wonderful project that will keep your team occupied for a long time, perhaps until retirement! Congratulations!

Seriously, I foresee big problems whose causes are not the technology but the ideas behind the technology. Without looking at the code, I suspect the pro*c solutions were written by data-centric programmers who had learned how to deal with databases. Spring batch is part of the Spring framework, written in Java by programmers who think processing should be done outside the database.

Data-intensive processing should be done as close to the data as possible, especially if the database is an expensive, high-quality product. That means leveraging SQL itself as much as possible and driving SQL through PL/SQL in jobs scheduled in the database.

Personally, I know of a spring batch job that took 8 VMs (7 Java and 1 Oracle) and several hours to insert 8 million rows. The Java experts got the time down to one hour.

I got the time down to 25 minutes using 1 VM (Oracle). This was without parallelism so 1 CPU. SQL and PL/SQL.

By the way, generating test data for the same project took up to a week. I got it down to a few hours. SQL and PL/SQL.

So here are your alternatives:
1) Dogmatic managers / architects: either go with the flow and watch the project fail or be the voice crying in the wilderness.
2) Reactive management: let the project start to fail and come in as the savior at the right moment.
3) Proactive management: propose a test phase comparing two approaches based on objective criteria agreed in advance.

Enjoy!

Best regards,
Stew Ashton
Connor McDonald
November 08, 2019 - 5:03 am UTC

:-)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database