but while stroring in it I am building string more than 32767(so exception) ..which is around 40000+
So I used LONG datatype - but still the same.
Will it be appropriate or is there any other one in Oracle ?
This is EXACTLY like my article[^]! You quote in your blog: "the difference with the method described below is that no oracle client installation or tsanames.ora is needed." That is the basis of the article. I suggest you go back and take another look!
If you took out the 'Oracle Bits' section, your article would not even work! The remaining bits of the article showing your code, while coded differently achieve the same results. Six in one hand, half a dozen in the other. The results are the same!
Give full credit where FULL credit is due. Your article is nothing more than a cheap knock off.
1) Using COMMIT inside SP is generally a very bad idea. Sooner or later U will need to call your SP from another SP an it will make your data corrupted. Cosider also application that (for example) loads multiple tab pages, each of tab representing a portion of data that need to be stored in single transaction, but the number of tab pages depends on "product" specyfication and is dynamically defined by user in runtime - how would U do that with Your design patern?
2) I don't see that Your way is any way cleaner than try/catch. U just trying to change try/catch to if. And if user forgots to implement "if" than your user won't notice that his operation failed.
3) Implementing your code pattern will not free developers from using try/catch. Ommiting try/catch is a BAD DESIGN. There are many things in your code that could go wrong so U shoud use try/catch anyway (wrong number of arguments after updating PL/SQL code for example,or exceptions from CLR ) to get rid of nasty unhandled exceptions.
4) Your code is faster only if an error occured and only for first error (since exceptios subsystem have to be loaded by CLR) and that difference, as far as exec time is concern (and user expirience too), is irrelevant.
My personal opinion is that if something may to be done from database it have to be done from database. But I will say again. I just show an alternative way which is not the best in all possible user's cases.
But I am very glad to see and other ideas and opinions as your.
Firstly I would like to send you my regards.
People who are ready to argument and have a different point a view are always welcome.
I feel only respect to them.
If you like to have ability to make multiple procedure calls you may make them in PL/SQL code inside this procedure. Or, other way is to make one big PL/SQL string which contain also and transaction support in your C#, VB or Delphi source code and send it as parameter to oracle store procedure and executed.
But I agree with you that in complicate business logic your approach is better. It is easier way.
But as always I say in this big world there is not only one absolute truth.
This article provide one different way which maybe will be useful in one business logic, your will be more powerful and flexible in other one logic and combination of 'my' and 'your' way in third logic.
I understand where you are coming from, but in my opinion it is much cleaner to support transactions within the context of C#, VB, etc. Early in my career I was placed in charge of a large process that was composed of several ksh/csh scripts and in many of those scripts we used the logic similiar to what you describe. The problem is that over 75 percent of the code ended up verifying that errors did not occur. I can only dream of the amount of code that would be dropped if this was done in a real language rather than scripts. Reading your article made me think of that system and cringe. As far as your suggested third way, I personnally would like to keep my code as consistent as possible, so I don't see a scenario where I would be switching back and forth between 2 different ways of handling errors from the stored procedures.
In order not to confuse any newbie's that happen to read your article, perhaps you should change the description of your article from "An article which describe the correct way of cooperation between Oracle PL/SQL and C#" to "An article which describes an alternative way of cooperation between Oracle PL/SQL and C#".
I would like to thank you about your next comments:
"In order not to confuse any newbie's that happen to read your article, perhaps you should change the description of your article from "An article which describe the correct way of cooperation between Oracle PL/SQL and C#" to "An article which describes an alternative way of cooperation between Oracle PL/SQL and C#"."
The your notice above are completely right. And as you may see I follow your advice.About other notice I would like to thank you that you are ready to share your experience with us and I am completely sure that it is very valued and will be useful for other developers to understand the way of controling and working with transactions which you describe. I do not want to say which way is better or worst. This depend from concrete applications and concrete client case on which you working. I just always like to have more than one way to do something.
I would like to say that to have an options, to have a choice is always better than to have not.
With your and my notice and help now the people who read this article will have this choice.
I agree with you that the decision to commit/rollback should be made by the caller, all the time.
The reason for that is simply that the caller is the only piece in the system that knows what the "business transaction" is. As you said, you may have multiple updates that need to be successful for the whole transaction to also be successful (thus triggering a commit everywhere). If any one of those updates comprising the "business transaction" fails, then all the updates should be rolled back. You could also have a case where the business transaction spans across multiple systems and not just a single Oracle instance. In this case, the need to have the caller fully in charge becomes obvious.
I wrote an article a while ago about working with PL/SQL in ASP.NET using C#. I clearly stated that the PL/SQL procedure was *not* committing the changes, because only the caller knew when to commit or rollback since the procedure could be part of a much bigger picture.
Dimitar, maybe you could update your article to emphasize this important point and that you made the assumption, when writing your code, that the PL/SQL procedure you call is the only step in the transaction and that more complex transaction processing must give full control to the caller to make the decision about issuing a commit or a rollback.
I agree with you.
But we have not to forget that we just describe two ways of controling transactions in Oracle.
The main goal of mine and yours article/I found it for very useful and helpful./ is just to give different points of view and option for any developer to have a CHOICE.
The question is not to have a choice or not, it is about not corrupting data. If you commit every piece in the "business transaction" step-by-step, by allowing the PL/SQL procedure to commit its portion without a controller on top of all of the different steps in your "business transaction", you will corrupt data, sooner or later.
If a subsequent step in the transaction doesn't complete correctly or the connection to the database gets dropped or something else happens that prevents the subsequent steps from being successful, you won't be able to roll back, and your data will be left in an inconsistent state "somewhere in between".
So, let's agree to disagree since there is no point in arguing forever
It is always question of choice.
And both ways have advantages and disadvantages.
And both of the ways have sufficiently reserves for improvement, modifications and combinations.
There is always more than one way to be done something.
I do not like to say which one is better or worst.
This depend from exact case and troubles which everyone developer have to solve.
You are absolutely right. To work with Oracle from Microsoft Dot.net any version you needs
from installed Oracle client version 8.1.7(8i) or highest.
If you like up-to now to have more power with playing with Oracle you may go on Oracle web site
and download an improvement version of oracle database drivers for Dot.net studio. They are free.
Last Visit: 31-Dec-99 18:00 Last Update: 30-May-16 12:30