Fernando,
It's very naive to view this is just a task for "split /;/".
Sqlplus has its own little language, with a grammar and
sytntax, variable substitution, etc. Consider this sqlplus
script:
@defines
whenever sqlerror exit failure
create table &&prefix._snafu(col varchar2(10))
/
whenever sqlerror continue
insert into &&prefix._snafu values (:prefix)
/
set scan off
insert into some_table values ('this & that');
and it's depdendent script @defines.sql
define prefix='fubar'
var prefix varchar2(30)
exec :prefix := '&prefix'
This is a contrived example, but not at all unrealistic for
real-world sqlplus. Perl's 'split' won't be quite enough :-).
You also need to add support for:
- amper substitution variables
- bind variables, define statements
- sqlplus exec function
- sqlplus 'set' commands and their effect on interpreting sql code
- included "@" files (and finding them, following $ENV{SQLPATH}, etc
- etc, etc, etc......
I suggest you step way back and ask yourself various key questions,
such as:
- who controls the source code that is the input to your module?
- what control do you have over them?
- what do you _really_ need to accomplish here?
(especially in light of the fact that you have a working
shell-script solution, no?)
The gist is that unless you can exert complete control over the
sql source code, you will be taking on a potentially endless hassle
because if the programmers generating the source are writing for
sqlplus, you will have to effectively implement a sqlplus emulator.
What's the point? You will spend a _lot_ of time, and how will the
end result be different (better) from your current solution? I'm not
saying it can't be better, but rather saying that you should have
a very clearly identified and defined objective of what exactly
that added value will be, and ask yourself if it will be worth the
price, and/or if there isn't some simpler way to add that value.
Cheers,
Mark
--- Fernando Luna <[EMAIL PROTECTED]> wrote:
> The only problem I see with the approach of stripping out semi-colons
> before passing DDL statements to Oracle is that CREATE PACKAGE
> statements have semi-colons all over the place... in fact, you can't
> have a PACKAGE Specification and body without the semi-colons which
> terminate each statement in PL/SQL.
>
> Will $dbh->do() cough and gag on that?
>
> > One problem you may be facing in your current approach is
> > the semicolons -- remember they may have to be stripped off
> > before being
> > passed to a $dbh->do(), at least some DBDs don't want to see
> > the semicolons.
> >
> > --
> > Jeff
> >
> >
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
