stored procedures.. needs comments and suggestions

[adshocker]

hi,

i'm currently using Oracle Express 10g as my database. i usually create procedures by running sql scripts like...

Create or Replace Procedure procName (parameter datatype)
As
Begin
< Statements... >
End;

this procedure will be inside the database or something like that...

i saw some tutorials earlier where stored procedures are created using classes?
maybe i misunderstood the tutorial or something but is this possible? and if so, which method is better in using stored procedures?

any comments/suggestions will be appreciated.

thanks.

 

[jmcilhinney]

Perhaps you could provide a link to this tutorial because I have no idea what you're talking about. SQL Server allows you to write sprocs in .NET languages and load compiled DLLs into your instance, but I very much doubt that Oracle provides the same facility. At least not specifically geared towards .NET anyway.

 

[adshocker]

Perhaps you could provide a link to this tutorial because I have no idea what you're talking about. SQL Server allows you to write sprocs in .NET languages and load compiled DLLs into your instance, but I very much doubt that Oracle provides the same facility. At least not specifically geared towards .NET anyway.

hi,

here's one of the many samples i've read... though its more like a storedfunction instead of a storedprocedure but it might help you understand what i meant.

http://www.oracle.com/technology/obe/net11gobe/stfunc/vs2.htm

thanks.

 

[jmcilhinney]

OK, that's almost exactly the same as SQL Server provides. One advantage is that you can write your sprocs in a .NET language you're familiar with, rather than SQL which you may not be as comfortable with. It also allows you to keep your sprocs under source control and deploy without having to execute against the database itself. I guess it also provides some separation between your database and the sprocs themselves, which are actually business logic.

 

[cjard]

i saw some tutorials earlier where stored procedures are created using classes?

for a long time it has been possible to write stored procedure code in java. writing them in .NET is a more recent addition to oracle's functionality but I understand it's now possible. I've never bothered with either because my oracle is stuck at 9i, .net isnt available and the embedded java is a version too early for me to have any desire to use it for anything useful

 

[adshocker]

someone commented that doing this won't be so good for the dba... is this true?

 

[jmcilhinney]

A DBA is supposed to administer a database. If the DBA doesn't know VB.NET then how can they maintain stored procedures written in VB.NET? An Oracle DBA is going to be an expert in PL-SQL, so stored procedures written in PL-SQL is what they will be able to maintain.

If you don't have a DBA though, and it's up to a VB.NET developer, who may not be so well-versed in PL-SQL, to maintain the stored procedures then it's a big adavantage. It's horses for courses.