Re: creating stored procedure

In article <3A82573A.DA9413BB_at_exesolutions.com>,   "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:
> I strongly disagree with both Alex's comment and yours. I think a
 script is
> the wrong way to go for several reasons. One being security. If the
 code is
> in the database there is less chance of it being deleted or modified.

1. I'd accept your disagreement if it was for right reasons. About security. UNIX security is not worse than Oracle. I don't see any security problem with a script.

 You
> can definitely execute stored procedures and packages from cron jobs,
last

Yeah, right. It just about 3 times more code. With a script you just have one command: sqlplpus .... _at_script_name

> project I was on we used Perl. Truncates can be done with native
 dynamic
> SQL. And I would write the procedure using explicit cursors.

Using explicit cursors for inserts you decrease performance 3-9 times, depending on tables layout. If you don't trust me, try it yourself. You don't need PL/SQL, if you can do without it. You don't need explicit cursors if you can do without them. As in case with compilers, SQL engine works much better than average programmer!

>
> BTW: About the only difference between a script and a procedure is the
> following line:
>
> CREATE OR REPLACE PROCEDURE ....... IS
>
> so what's the big deal?
>
> And it definitely enhances your resume to have the experience.
>
> Daniel A. Morgan
>
> > Thanks for the help.
> > Writing SQL script is more easier. My boss wants me to write a
 stored
> > procedure. I want to know would the stored procedure run from cron
 or
> > not. Can I tell him it is better to run SQL script than stored
> > procedure.
> >
> > Thanks in anticipation.
> > In article <95s7hu$v5d$1_at_nnrp1.deja.com>,
> > Alex Filonov <afilonov_at_pro-ns.net> wrote:
> > > I don't think you really need a stored procedure. May be sqlplus
 script
> > > will work for you. In that script you can easily use truncate.
 It's
> > > also easier to run sqlplus script from cron.
> > > One more suggestion. Even if you decide to use stored procedure,
 use
> > > insert into <temp table>
> > > as select ....
> > > statements. It'll save you time.
> > >
> > > In article <95p2l8$4ik$1_at_nnrp1.deja.com>,
> > > kushan <vikasmamta_at_my-deja.com> wrote:
> > > > Hi
> > > >
> > > > I am creating a stored procedure to select the data from 5
 tables &
> > > > then inserting into one table. And I want to run this procedure
 hourly
> > > > by setting its cronjob. Can you check my syntax
> > > >
> > > > Create or replace procedure P1
> > > > Begin
> > > >
> > > > Select ......... from ..... where...=....;
> > > > Insert into temp table ........;
> > > > Select ......... from ..... where...=....;
> > > > Insert into temp table ........;
> > > > ....like this many selects & inserts......
> > > > end;
> > > >
> > > > Is that necessary to give parameters in or out or would it work.
> > > >
> > > > one more thing Everytime when the procedure will run it will get
 the
> > > > completedata from different table & insert it into temp table so
 that
> > > > temp table keep on getting duplicated rows. If I create a
 primary
 key
> > > > in the temp table would that help me. Or do I need to use
 truncate.
 If
> > > > So where i need to write this truncate in the procedure. Please
 help
 me
> > > > out with this problem> I am in real trouble.
> > > >
> > > > Thanks
> > > >
> > > > --
> > > > RICHARD
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> > >
> >
> > --
> > RICHARD
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 08 2001 - 22:37:55 CET