Re: creating stored procedure

[Quoted] hi Frank,

I agreed with your views, creating stored procedure is better idea than SQL script. I am creating this for inserting data into temporary table pulling data from more than 100 tables. If I am writng stored procedure I need to use more than 150 'Insert statement as select from' I have to open 150 cursor may be more than this. I am creating all this for a Crystal Report. wouldn't the procedure be very long. Or all this will be ok.

Any suggestions.
 Thanks

In article <iwbh6.2640$xT3.73490_at_news1.oke.nextra.no>,   "Frank" <franjoe_at_frisurf.no> wrote:
> Hi!
>
> We have also choosen to prefer stored procedures instead of scripts.
>
> The reason is you get fewer development environments (only Oracle
 database
> not UNIX). This means that you
> simply have fewer things to keep in order. Keep the Database running
 an the
> code is there (and working ?-).
> It is also easier to copy the database to test/development
 environment,
> because everything is copied in the same
> way and everything is in the Database. With SQLPlus you sometimes must
> create temporary files/scripts
> to achieve things - another thing to remember when moving/copying a
> database.
>
> Business logic have a tendency to grow in complexity, PLSQL is easier
 to
> allow business logic to "grow" in
> (become more complex). SQLPlus scripts are easy when the problem is
 easy,
> but very hard when the problem grows.
>
> Actually we would not have choosen the cron solution either, we would
 have
> used the DBMS_JOB. I believe the
> application becomes easier to document/hand over to other people, when
> everyting(?) is in the Database.
>
> Frank
>
> <vikasmamta_at_my-deja.com> wrote in message
> news:95sfc6$72g$1_at_nnrp1.deja.com...
> > Hi Alex
> >
> > 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/
>
>

--
RICHARD


Sent via Deja.com
http://www.deja.com/