Oracle SP coding style
-
I'm not a big fan of Oracle and am quite happy to delegate the coding of the more complex stored procedures to others. Because of this lack of knowledge I need some opinions. When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATEto load the results into a temp table. The proc is complex and there are a number of steps so the temp table is valid but I have problems with the use of concatenated strings. I would think this would negate any benefit from compiling the proc as this would on validate the concatenation of the strings and not their content.Never underestimate the power of human stupidity RAH
-
I'm not a big fan of Oracle and am quite happy to delegate the coding of the more complex stored procedures to others. Because of this lack of knowledge I need some opinions. When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATEto load the results into a temp table. The proc is complex and there are a number of steps so the temp table is valid but I have problems with the use of concatenated strings. I would think this would negate any benefit from compiling the proc as this would on validate the concatenation of the strings and not their content.Never underestimate the power of human stupidity RAH
EXECUTE IMMEDIATEis a generally accepted method of coding dynamic sql in PLSql. I use this methodology and have not had any issues - yes it can take a bit to get the concatenation of quotes etc correct - however in the end this works fine. If you are concerned about the content of the queries you could put the queries into a meta table and pull the queries in and build your dynamic sql at runtime.Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
I'm not a big fan of Oracle and am quite happy to delegate the coding of the more complex stored procedures to others. Because of this lack of knowledge I need some opinions. When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATEto load the results into a temp table. The proc is complex and there are a number of steps so the temp table is valid but I have problems with the use of concatenated strings. I would think this would negate any benefit from compiling the proc as this would on validate the concatenation of the strings and not their content.Never underestimate the power of human stupidity RAH
Not that this helps but... I didn't know Oracle supported stored procedures, when I had to use it (1996-2001, Oracle 6?), we never used stored procedures, only PRO*C. And it was good. :cool:
-
Not that this helps but... I didn't know Oracle supported stored procedures, when I had to use it (1996-2001, Oracle 6?), we never used stored procedures, only PRO*C. And it was good. :cool:
You are correct, in terms of the naming convention they are called procedures - they are basically the same as Microsoft's stored procedures. Also procedures can be stored in packages(basically classes).
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
EXECUTE IMMEDIATEis a generally accepted method of coding dynamic sql in PLSql. I use this methodology and have not had any issues - yes it can take a bit to get the concatenation of quotes etc correct - however in the end this works fine. If you are concerned about the content of the queries you could put the queries into a meta table and pull the queries in and build your dynamic sql at runtime.Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
My point is that I don't think EVERY query should be dynamic sql. It has it's place but not for every single query.
Never underestimate the power of human stupidity RAH
-
Not that this helps but... I didn't know Oracle supported stored procedures, when I had to use it (1996-2001, Oracle 6?), we never used stored procedures, only PRO*C. And it was good. :cool:
Even way back when I was using it there were procedures (about the same time). I pointed out to the developer that I could more easily do the string concatenation in c#, as per your prefferred method, than have him do it in PL/SQL. I pulled in another dev who has very good Oracle skills, the coding style will be changing to proper PL/SQL rather than dynamic sql.
Never underestimate the power of human stupidity RAH
-
My point is that I don't think EVERY query should be dynamic sql. It has it's place but not for every single query.
Never underestimate the power of human stupidity RAH
I completely agree :thumbsup:
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
I'm not a big fan of Oracle and am quite happy to delegate the coding of the more complex stored procedures to others. Because of this lack of knowledge I need some opinions. When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATEto load the results into a temp table. The proc is complex and there are a number of steps so the temp table is valid but I have problems with the use of concatenated strings. I would think this would negate any benefit from compiling the proc as this would on validate the concatenation of the strings and not their content.Never underestimate the power of human stupidity RAH
-
Mycroft Holmes wrote:
When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATEto load the results into a temp table.Only if it can't be done using normal PL/SQL. Which would mean almost never.
That was my opinion, but without the authority of experience I am glad to have it confirmed. That dev now has a refactoring (rewrite) job to do. Luckilly this was the first couple of jobs I have given him.
Never underestimate the power of human stupidity RAH