Bob update

[Hill Station Murthy]

The first deliverable (stored procedures, DDL scripts et al) is behind. It turns out the developers off-shore are all junior.

One stored procedure which should have been using cursors was just doing blunt 1 row inserts. I quizzed the team about this. They do not understand cursors.

This project was sold on the fact that the off-shore team had the capability to deliver. Why do they do that, then put in really junior numpties?

So now I have a call with them to explain cursors and error handling in PL/SQL. It will be like the partially sighted leading the blind.

There is absolutley no need to be using cursors for any application in the present day.

All business logics should be achieved in the appropriate layer of your application. You should then have a data layer that performs simple reads/inserts/deletes/updates to your Db.

If you are in the situation of using this cursors I have to be quite honest with you and say that I am under no surprise that your team is querying your design.

HTH

Joshi.

[TestMangler]

There is absolutley no need to be using cursors for any application in the present day.

All business logics should be achieved in the appropriate layer of your application. You should then have a data layer that performs simple reads/inserts/deletes/updates to your Db.

If you are in the situation of using this cursors I have to be quite honest with you and say that I am under no surprise that your team is querying your design.

HTH

Joshi.

Suity's been bob-slapped

[fullyautomatix]

There is absolutley no need to be using cursors for any application in the present day.

All business logics should be achieved in the appropriate layer of your application. You should then have a data layer that performs simple reads/inserts/deletes/updates to your Db.

If you are in the situation of using this cursors I have to be quite honest with you and say that I am under no surprise that your team is querying your design.

HTH

Joshi.

For really simple CRUD type app, what you say could well be valid. But if you have ever worked in an enterprise level business application you will find that curors can sometimes be invaluable. I would rather wrap a rather lengthy procedure into one stored procedure wrapped around one transaction than make hundred calls via business layer.

[Mich the Tester]

There is absolutley no need to be using cursors for any application in the present day.

All business logics should be achieved in the appropriate layer of your application. You should then have a data layer that performs simple reads/inserts/deletes/updates to your Db.If you are in the situation of using this cursors I have to be quite honest with you and say that I am under no surprise that your team is querying your design.

HTH

Joshi.

No they shouldn't. The object of making IT applications is to provide invoicing opportunities for contractors, including contract testers. The applications are not supposed to do useful, efficient work. How often do we have to repeat this?

[MarillionFan]

Code review. What is a highly paid 600% BPM consultant checking code for????

[snigger]

[Zippy]

Yep, do that, including the inevitable discussion that involves 'we are feeling this is working according to specification please be introducing change request'. But still, bounce it back, retest, bounce it back, retest and above all; INVOICE DAY TODAY!

WHS ++

[DimPrawn]

There is absolutley no need to be using cursors for any application in the present day.

All business logics should be achieved in the appropriate layer of your application. You should then have a data layer that performs simple reads/inserts/deletes/updates to your Db.

If you are in the situation of using this cursors I have to be quite honest with you and say that I am under no surprise that your team is querying your design.

HTH

Joshi.

You've obviously never worked with an Oracle DBA team have you?

You:
I'm writing a middle-ware layer in C/C++/C#/Java...

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
I'm writing a utility to load, transform and write data to the database....

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
I'm using a webserver to convert data into XHTML...

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
I'm creating a front end user interface in .NET...

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
Right, I'm going for a tulip!

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

[Hill Station Murthy]

For really simple CRUD type app, what you say could well be valid. But if you have ever worked in an enterprise level business application you will find that curors can sometimes be invaluable. I would rather wrap a rather lengthy procedure into one stored procedure wrapped around one transaction than make hundred calls via business layer.

So what would happen if you write many of your procedures this complex and there is requirement to move to different database provider. This will be the result to rewrite all the stored procedures.

If this logic stays in this BL layer you are protected and future proofed.

[fullyautomatix]

So what would happen if you write many of your procedures this complex and there is requirement to move to different database provider. This will be the result to rewrite all the stored procedures.

If this logic stays in this BL layer you are protected and future proofed.

It is this kind of mindless useless thinking that has resulted in countless projects going over the budget and never getting finished. How many times do you get companies changing the back end database on their enterprise level apps ?

Are you SuitYou in disguise or something ?

[IR35 Avoider]

For really simple CRUD type app, what you say could well be valid. But if you have ever worked in an enterprise level business application you will find that curors can sometimes be invaluable. I would rather wrap a rather lengthy procedure into one stored procedure wrapped around one transaction than make hundred calls via business layer.

I am quite interested in this debate. I do not regard myself as a SQL expert, it's not how I make my living, however I have developed a couple of small web-sites with SQL Server back-ends. I did not use cursors and can't think of a situation where they would be useful.

The development standard I adopted was a simple two layer approach, a web page that in almost all cases did exactly one call to a SQL procedure. (There was one exception which I won't go into.) The web page mostly just handled user interface and simple validation, most logic was at the SQL level.

The idea behind one-page = one-proc is that once the user "presses enter", everything that is going to happen is predetermined, therefore there should be no need to call the database more than once. Many of my procs did return multiple results sets, to satisfy all the requirements of the particular transaction.

My take on cursors is that as soon as you use them, you are taking a procedural approach to computing your result sets, and this is not a good thing if you are using a tool that is designed and optimised for set processing. In other words, despite my lack of qualification, I'm claiming that using cursors is actually bad practice in SQL.

Can you give me an example (simple and made-up is fine) where using a cursor would be clearly superior to a non-cursor alternative? (Assumed constraint is that the mission is to take some parameters and compute one or more result sets from data contained in a single database. I have used cursors in contexts where I was interacting with something other than just a database, for example executing a sequence of operating system commands.)

Edit: an example involving inserts/updates/deletes would also be OK, as long as the only data involved is some combination of transaction parameters and database data.