Re: calling sql script from pl/sql

Sorry to interrupt your conversation ;-) I didn't really mean for this to turn into a discussion of WHY I want to create DDL on the fly, but HOW to generate the output files (more SQL*Plus/SQL/PLSQL syntax than anything) But I should probably provide a little background on the WHY part...

I tried to simplify the problem to make it easier to understand and address by other readers. I am actually working on a project that uses a SQL Server database as an intermediate data store for data originating from some Oracle views. This is part of a data warehouse/mart solution, and may eventually be incorporated into a packaged software product. As part of the install of this solution, the DDL must be either created manually (which would take hours) or done automatically (which would take minutes). We're talking hundreds of views here. The automated process, for a packaged solution and this number of views (not to mention the data type conversion between SQL Server and Oracle), is a no-brainer to me.

The script I wrote (yes, I got it working) generates a DDL script to make tables in SQL Server that mirror the Oracle views, taking into account the data type conversion. I will use MS DTS to copy the data once the tables are created in SQL Server. The reason for materializing the views into tables is that there are often cases where many of these views must be joined together to get the data into a form suitable to load into the data warehouse or data mart. I have run into severe performance problems because each of these views is very complex, and joining them together compounds the problem. Loading them as tables in SQL Server and indexing the columns I need solves the performance issue.

The way I got the script to work was simply to write the entire piece in a single PL/SQL block (thus eliminating the need to call the external file), put a spool command outside the block and use DBMS_OUTPUT.PUT_LINE to output the needed statements to the file. From SQL*Plus, this works just fine using @generateddl.sql as the syntax.

Oh yeah, please don't go down the road of why I'm using SQL Server for this... it's a whole different issue - more of a constraint I'm working under for this solution.

Anyway, thanks to everyone for the valuable input.

-John

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CC42606.E64D2477_at_exesolutions.com>...
> I've already weighed in, above, with my opinion of creating tables on the fly
> ... which is don't. But I would like to also weigh in on allowing yourself to be
> put into the position of performing DBA functions when, as you state, you
> clearly don't have the expertise. My advice is the same ... don't.
>
> I can't think of any better way to ruin one's professional reputation or equally
> bad, end up defending against legal action, than to poke around where one
> clearly doesn't know where the mines have been laid.
>
> I would suggest that you develop a relationship with a solid Oracle DBA that you
> can work with on an ad hoc basis. Just because a client asks you to do something
> doesn't mean you should.
>
> Daniel Morgan
>
>
>
> Radu Lascae wrote:
>
> > ROTFLMAO:-))
> >
> > Ok, let's settle down a bit here, shall we?:-) I didn't intend to turn this
> > into a flame war nor show any disrespect to the DBA profession. Apparently I
> > was misunderstood, so let me clarify.
> >
> > I am a system integrator and a consultant. I have no DBA in my company to
> > work with, only at my clients sites - which would answer mr. Kennedy's
> > question on why am I not talking to my DBA. The DBA's are in our development
> > department and are basically off-limits to all consultants - apparently
> > management decided they were simply too busy, which may indeed be the case.
> >
> > The problems don't occur when a DBA is present - frankly the Oracle DBA's
> > I've worked with wouldn't dream of allowing me to make schema changes
> > myself, least if it were in code. And I've always co-operated very well with
> > them, so all this discution about being professional and showing respect
> > seems a bit redundant to me - I thought it goes without saying.
> >
> > My problem begins when a particular customer has no DBA at all and expects
> > me to fill in the role on a temporary basis. This is not a very inviting
> > role, believe me, but one has to play with the cards one is dealt. So my
> > question was inviting a technical answer, really, which mr. Kennedy
> > provided. On non-Oracle databases, particularly on SQL Server 7, which does
> > not support UDF's (sic!), I was left with no other choice but to create
> > temporary tables in code on some occasions. It makes for shitty code, it
> > makes for almost impossible to manage schema's and on the whole I whish I
> > had other choices - but I didn't.
> >
> > I'll agree using the word "dogma" may have inflamed the message a bit. It
> > was not my intention and I'm ready to appologise for it, should anyone have
> > taken offence.
> >
> > Greetings,
> > Radu
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:uc4p562innn750_at_corp.supernews.com...
> > > OK, Mr. Lascae is just begging for this kind of comment, reading his
> > > previous replies:
> > >
> > > Evidently, Mr. Lascae thinks he knows better than the DBA's in his firm,
> > > and/or he wants enough freedom (maybe he already has it) to bring the
> > > database on it's knees, so the DBAs have put a prize on his head.
> > > He must have been consistently following his own plan, not working
> together
> > > (which is, granted, *exactly what almost all developers do*, most
> > > developers think DBAs are stupid, not as smart as they are, and dogmatic.
> > > DBAs can become dogmatic if they have consistently to clean out developers
> > > messes. Currently, this constitutes 80 percent of my work.), so he has put
> a
> > > strain on his working relationship with his DBAs and probably with the DBA
> > > species in general. The way he uses the word 'dogma' quite convincingly
> > > demonstrates this.
> > > If he wants to be stubborn and follow his own plan, let him do so, soon he
> > > will be exposed.
> > >
> > > Regards
> > >
> > >
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
> > >
> > > to reply remove '-verwijderdit' from my e-mail address
> > >
> > >
> > >
> > > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> > > news:1Sgw8.91424$CH1.80732_at_sccrnsc02...
> > > >
> > > > It doesn't have anything to do with dogma. In Oracle I have never
> needed
> > > > temp tables, sure I could have used them but found views more
> efficient -
> > > > less IO, less latch contention, less CPU usage. That doesn't strike me
> as
> > > > dogma. More an issue of physics. Ignoring physics would make it dogma.
> > > > That said, in other rdbmss I have seen how they work and there temp
> tables
> > > > made sense (only way to get around certain locking problems, and views
> were
> > > > not efficient), but we are talking about Oracle here not other rdbmss in
> > > > this case. Contrary to some points of view a database is not a
> database -
> > > > there are different vendors and they implemented things differently.
> > > > Therefore, It is best to meet the requirements of the problem - reports
> > > > gotta run fast - by taking advantage of that particular database's
> strengths
> > > > and not forcing another database's practices -which are effective
> there -
> > > > on the one you are currently running on.
> > > >
> > > > Executing adhoc DDL is very CPU intensive and limits system concurrency.
> If
> > > > you really feel you just gotta have them then look up global temporary
> > > > tables in the manual. It will make things run faster for you than
> > > > dynamically creating tables ad-hoc.
> > > >
> > > > I am kind of confused, if you treat the DBA as a professional why are
> you
> > > > asking us and not talking to him or her? I don't know your DBA, but my
> > > > suggestion was a suggestion not an accusation. Is there no DBA or are
> they
> > > > on vacation? In our group we do code reviews and in this life I am the
> > > > acting DBA - most of the past lives I have done development - but our
> team
> > > > is pretty open - and small - and so we try to use each other's
> expertise.
> > > > The code reviews and design reviews can really help tighten up code
> > > > (performance is part of the job not something we bolt on afterwards) and
> > > > keep everyone up to date as to how things work etc.
> > > >
> > > > Jim
> > > >
> > > >
> > > >
> > > > "Radu Lascae" <r.nospam.lascae_at_wanadoo.nl> wrote in message
> > > > news:Pggw8.217$r7.13016_at_castor.casema.net...
> > > > > Temporary tables are tables - funny I should need to mention it.
> > > > >
> > > > > This is not an issue of profesionalism, I've never treated DBA's other
> then
> > > > > as professionals, it's a dificult profession, I'm aware of it. They've
> never
> > > > > treated me otherwise either.
> > > > >
> > > > > The issue is of dogmatics. I have my own. I wonder if this is a
> particular
> > > > > DBA dogma. The question stays unanswered: why?
> > > > >
> > > > > Regards,
> > > > > Radu
> > > > >
> > > > > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> > > > > news:5yfw8.90947$CH1.80375_at_sccrnsc02...
> > > > > > I think the best approach is to work with them and state what you
> are
> trying
> > > > > > to accomplish - need to run this report, but the performance is not
> up
> to
> > > > > > snuff. Then ask them how we could improve it. Ask them for their
> expertise
> > > > > > and input. You are then treating them as a professional, an expert
> in
> their
> > > > > > area and they will probably have a whole different attitude. Yes,
> Sybrand
> > > > > > is correct. I can think of two ways - a view or a global temporary
> table.
> > > > > > I suspect the problem lies in how the data is getting retrieved and
> some
> > > > > > analysis would come up with a better solution.
> > > > > > Jim
> > > > > > "Radu Lascae" <r.nospam.lascae_at_wanadoo.nl> wrote in message
> > > > > > news:fOew8.176$r7.11119_at_castor.casema.net...
> > > > > > > With due respect: why?
> > > > > > >
> > > > > > > I'm not a DBA. I can think of several scenario's in BIS
> applications
> where
> > > > > > > I'd improve performance of certain repports by creating tables on
> the
> fly.
> > > > > > > Some of these reports can't even be created in straight SQL.
> > > > > > >
> > > > > > > Am I bound to outrage the DBA's with whom I'm working?
> > > > > > >
> > > > > > > Regards,
> > > > > > > Radu
> > > > > > > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > > > > > > news:uc21ba11ld1076_at_corp.supernews.com...
> > > > > > > >
> [...]
> > > > > > > > Creating tables on the fly however must be considered very bad
> practice.
> > > > > > > >
> > > > > > > > Regards
> > > > > > > >
> > > > > > > > --
> > > > > > > > Sybrand Bakker
> > > > > > > > Senior Oracle DBA
> > > > > > > >
> > > > > > > > to reply remove '-verwijderdit' from my e-mail address
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
Received on Mon Apr 22 2002 - 18:23:26 CDT