A shot from the hip (with shotgun :-))

Hi!

[Quoted] Your actual business problem is not quite clear to me :-) but I can try to point at some pitfalls/questions:

First note; if your tables contains LONG columns the INSERT INTO ... SELECT * FROM
will notoriously make problem (read: is not allowed). If you have a few tables having LONG columns it might be a good idea to work a bit on those early.

1. in some of the info you have entered it looks like you are going to copy info from several tables into one single one by just copying one and then the next asf. If all your 150 tables have the same columns (they are the same table in a way), then inserting in this way would make sense; a statement along the lines of

INSERT INTO temptable
SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3
.....asf

could help you. If some of your tables have different types or different number of columns this may become a lot of work, because in UNION's
each query set must contain the same number of columns, and they must be of the same type.
Snapshots may also be worth a look, instead of INSERT.

2) the more common type of problem is that all the tables are going to be joined together in one query, or more likely groups of tables are going to be join together to form several queries, and their result is to be inserted into temp tables for later use in reports.

INSERT INTO ...

SELECT        a.col1, a.col2,
                      b.col1, b.col2, ...

is still possible, also here snapshots are worth a look. Your main challenge here is to join correct :-|.

The source code for this development object will probably become large.

Another thing that comes to my mind when we are talking about 100-150 tables is the performance of this. Keeping an eye one the time consumption issue (you probably want the logic to complete within 1 hour ?) Trying to split the job in several parts that can be performed separately might be
a good idea.

Also if you choose to use TRUNCATE on the tables bear in mind that this statement performs a implicit commit, which may leave some of your reports blank
in other oracle sessions until all the information is regenerated.

If you choose insert-solution also keep an eye on the ROLLBACK segment resourse.

These kinds of issues are relevant both if you choose stored procedures or sqlscripts.

I m not sure that I have answered any of your question, but ...:-) Frank

<vikasgupta_at_excite.com> wrote in message news:968ug1$44o$1_at_nnrp1.deja.com...
> 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> [Quoted]
> > > > 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...=....; [Quoted]
> > > > > 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/
Received on Mon Feb 12 2001 - 20:07:03 CET