> > > Fernando Luna wrote: > > [...] > > I am running perl version 5.005_03 built for AIX Unix. > > Get ready to upgrade. >
So, is this version not stable enough for me to do what I want? Why should I worry about upgrading right now? > > I have Oracle version 8.1.7 installed and running. > > I have AIX version 4.3.3.0 > > [...] I am able to > > execute queries and PL/SQL blocks using these modules with no > > problems. What I'd like to do is read a file that contains DDL > > statements and execute them through these modules. > > > > These files contain specifically > > > > CREATE TABLE statements > > CREATE INDEX statements > > CREATE PACKAGE/PACKAGE BODY statements > > CREATE trigger statements > > CREATE VIEW statements > > > > In some cases, these files have more than one statement in them. > > (example, creating several indexes on a table) [...] > > > > I've searched the DBI and DBD documentation and I've not found > > anything that specifically addresses this. > > > The DBI::Shell might handle this. One issue is how are the > statements > separated in the source code. If you don't have an explicit > separator > (typically semi-colon, but I don't know whether that's what Oracle > uses), then you need something (SQL::Statement?) to parse the file so > that you get each separate statement into a string, which you then > submit to $dbh->prepare, $sth->execute, and maybe some > $sth->fetch* code. My currently approach is a ksh script that calls sqlplus. The statements are separated by either a semi-colon (which I guess most databases use) or a forward slash (which Oracle supports through the sqlplus tool, though in all other cases only the semi-colon is appropriate). > > If DBI::Shell does not handle this, then I still have a rudimentary > script which I abandoned when DBI::Shell came along (circa 1997), > which almost does what you need. It has a couple of fixable Informix > barbarisms in it, and the statement end scanner simply looks for a > semi-colon (anywhere, including inside strings and comments) so it can > be fooled quite easily, but it does actually do what you need. I'll > cheerfully make that code available to you if it is needed -- I never > like throwing code away, and this would be an example (nearly six > years later) of why you shouldn't do it. I'd be very thankful for any help you can toss my way. > > > I tried running one CREATE TABLE statement and it appears > to choke on > > some keywords that Oracle has no trouble with, but I suspect has to > > do with the fact that they are not necessarily ANSI-SQL compliant. > > Shouldn't be an issue - but it depends on why you think it might be It chokes on the keyword NOCACHE in the last line of my create table statement. The syntax is valid, because it's always worked in the past, and also because I just now tried it out to be sure. > > > Is this something that might be covered in this book or can you > > tell me if this just cannot be done with these modules? > > It can be done OK - it just may not be fully pre-packaged if > DBI::Shell does not handle this for you. > It would be nice to have something pre-packaged, but I wasn't really expecting that. SQL::Statement will parse it out... that's nice, but, I mean, what's to stop me from writing something that detects the ';' and then try to run everything right up to it? The current approach I'm using is written in ksh and calls sqlplus which runs one sql file that in turn runs a series of sql scripts against a production machine and spools the output from the sqlplus tool to a file, showing any errors as appropriate (if, for example, a package doesn't compile or some object doesn't exist which in turn prevents another object from being created)... I'm just looking for the best way to do that in perl. Seems to me that perl has so much power and that there is so much expertise in this group... I'm surprised this has never been addressed up to now in the context of change management in Oracle. I notice that there's a book out that introduces Oracle DBAs to perl but from what I could tell, nothing is mentioned about sql files. Typically DDL and DML will be stored in .sql files; DBAs use the very frequently (not to mention developers, for a variety of reasons) and I'm surprised that no one has developed something in perl to process them specifically. Maybe I'm wrong?
