Home » SQL & PL/SQL » SQL & PL/SQL » Pro*C to PL/SQL
Pro*C to PL/SQL [message #212636] Sat, 06 January 2007 21:06 Go to next message
Messages: 2
Registered: May 2005
Junior Member
Dear All

We are in loading lot of data(900MB) to shared memory using c/proc*C to avoid database hits and do comparison from memory directly using c/proc*C code.
This is loaded only once in a day, used by many process.

What would be best approach in PL/SQL to replace the code?

Much Thanks
Re: Pro*C to PL/SQL [message #212645 is a reply to message #212636] Sun, 07 January 2007 02:04 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
Rewrite from scratch. Let the database do what the database is designed for: take hits, compare data and execute queries.
It all depends on what exactly this c-program does.
Re: Pro*C to PL/SQL [message #212707 is a reply to message #212645] Sun, 07 January 2007 21:08 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. I agree and disagree with Frank.

If you have an intensive looping procedural process that requires frequent lookups on database tables, then - like Pro*C - PL/SQL is going to suffer from the frequent context-switches.

The usual work-around is to include all of the data you need into the main cursor - then you don't need to keep going back to the database (context switch to SQL) for every lookup table. Then you can get further improvements with BULK COLLECT, and - if the process writes back to Oracle - FORALL loops.This technique can also be done efficiently in Pro*C using array processing.

So that's where I agree with Frank. If you can, rewrite using this technique. Never code a SQL or nested cursor inside a heavy cursor loop.

However, this technique is not always possible. Sometimes there is much more complex logic involved. There may be dozens or hundreds of pathways through the procedural component, all of which have their own specific data requirements. It may not be feasible or even possible to code all possible pathways into a single cursor.

As a fallback, you could load some (or all) of the lookup tables into Associative Arrays (Index-By tables). However, these are session-specific, not like your shared memory segments. You may want to try to include the larger lookups in the main cursor, and just use the Associative Arrays for smaller lookups.

If this is still going to be a problem then PL/SQL is not the right language. I don't know of any way to implement a shared memory segment using PL/SQL.

Ross Leishman
Re: Pro*C to PL/SQL [message #212731 is a reply to message #212707] Mon, 08 January 2007 00:53 Go to previous message
Messages: 7880
Registered: March 2000
Senior Member
Looks like we totally agree on this then, Ross.
This is what I meant by my last sentence "it all depends".
I know there are perfectly valid reasons for using the 'read once into memory, use multiple times'.
IF the OP wants to rewrite it all in PL/SQL, I thought there would be a specific reason for it; normally if one has chosen to use pro*c in the way the OP described, one would not change back to PL/SQL unless either some major things changed, or the initial decision to use pro*c was wrong.
It is hard to say IF it all can be done in PL/SQL. Probably, using new possibilities like analytical functions, model clauses etc and with the use of MVs, it could turn out that the DB is well up for the task.
Big advantage might be (I don't know if it comes into play) that you would be dealing with actual data, instead of this mornings data..

Bottomline is, I do agree that not everything you do in pro*c can be done (as efficiently) in PL/SQL.
Previous Topic: ORA-00936: missing expression
Next Topic: group by
Goto Forum:

Current Time: Thu Aug 17 14:27:43 CDT 2017

Total time taken to generate the page: 0.15140 seconds