conditional logic to trigger a script

[Thargy]

Folks,

NEWBIE POST - you've just been warned...

I know nothing about PERL and have never used it. However, I'm considering learning it to solve a particular problem, and would like to know if it can do what I need, before investing significant effort.

I routinely use slqplus scripts to upgrade Oracle databases. Unfortunately, sqlplus does not provide conditional logic, i.e. there's no such thing as an IF ... THEN statement. By means of cumbersome and involved scripting, I have achieved a pseudo IF statement, but the work involved is becoming more and more onerous.

I want to be able to inspect a variable (the db version_number) and if it's less than the current version, sequentially trigger sqlplus upgrade scripts, to bring the db up to date.

Can PERL programatically get a value from an Oracle database, inspect it, and based on the value either do nothing, or invoke one or more operating system files?

Regards

Tharg

Grinding away at things Oracular

 

[dmazzini]

Perl has a great Module DBI

Using DBI you could do a lot of stuff like insert, delete, update, etc. as well it incorporates a lot of DBMS functions. (tableinfo, catalog etc)

As weel, using Perl you can access you oracle Database using sqlplus, just invoking it from your perl script...and then you can use fantastic perl code.

Cheers

dmazzini
GSM System and Telecomm Consultant

 

[Thargy]

dmazzini,

Thanks for the swift response.

Can you recommend a good starting point for PERL, ideally something I could download and use rapidly. Are there any licensing issues associated with using PERL and/or DBI - (I presume that stands for DataBase Interface)?

Regards

Tharg

Grinding away at things Oracular

 

[dmazzini]

http://www.activestate.com

(download perl)

Perl and DBI modules are free. Don't need to pay for Perl.
ODBC id an option as well

http://www.perl.com

(perl)

http://learn.perl.org/library/beginning_perl/

(perl tutorial)

http://www.saturn5.com/~jwb/dbi-examples.html

(DBI)

http://www.codeproject.com/books/1578700671.asp

(ODBC)
Cheers




dmazzini
GSM System and Telecomm Consultant

 

[stevexff]

If you install the ActiveState perl, you get the full perl doc with it. Perl is a great language for doing the kind of quick utilities you want. Although the learning curve can seem a little steep at first, especially some of the terser language idioms and regular expressions, it is worth the effort. There is also a huge selection of modules available from Activestate, and an even bigger set on

http://www.cpan.org.

It's always a good idea to check there first, as you will often find that someone else has had your problem before, and written a module to solve it (like DBI, for example).

Because of the flexibility of the language, there is usually no right or wrong way to do things. In fact, one of the central perl mottoes is TIMTOWTDI (there is more than one way to do it). So just get stuck in.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[1DMF]

WIN32::ODBC comes as standard with PERL

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[PaulTEG]

pl/SQL is another tool from the oracle stable, and allows for conditional logic, it might be your quickest route to market here, but Perl is a lovely language and well worth the effort ;-)

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[1DMF]

we all PERL here

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Thargy]

Everyone,

thanks for the helpful responses.
I've got loads to chew on now!

Paul, I know of pl/sql (I'm an oracle DBA), but unfortunately it can't be used client side unless you create anonymous blocks in a sqlplus script. Sadly, even though one could do just about anything in such a block, it operates within the sqlplus environment, and so it can't conditionally trigger a file - sigh.

I have to admit that lack of conditional logic is a major bugbear of sqlplus. I was desperately looking for alternatives when I stumbled across PERL.

Once again, my thanks to you all.

Tharg
P.S. see you in the oracle 8, 9 or 10 forums, if you need help any time.

Grinding away at things Oracular