Perl script calling oracle procedures.

[StanleySWLee]

Hi all,
I had design a main menu using perl script to run procedures. However, each time I called the procedure, using the statement "system('./hardcodeScript.sh procedureName');" in perl, it will call another shell script(hardcodeScript.sh file) where it hardcoded the username/password using statement like "sqlplus -s username/password@ENV @$1".

Is there other way to call the procedure in perl script without hardcoded it or prompt login?

[Andrew Young_2]

Hi Stanley.

There are a number of ways of doing this.

Firstly some questions... is this a read only user or will the user need to do updates on this database? How trusted is this user?

Is the database on the same server that script is running?

Regards

Andrew Y

[StanleySWLee]

Hi Andrew,
User had full privilege to access d/b. And my concern is:

In the Perl script, i'm using statement like below to call procedures for option 1 and 2.
Option1:
system('./hardcodeScript.sh procedureName1');

Option2:
system('./hardcodeScript.sh procedureName2');

The hardcodeScript.sh will be:
sqlplus -s username/password@ENV @$1

If I'm do that way to call the procedure, I'll will be prompt user login to d/b everytime call procedure.
It'll be tedious task. Or is it got a faster way to execute the procedure.

Rgds,
Stanley

[Andrew Young_2]

Hi.

If the user was read only or if you wanted to only do specific functions with it you could create a restricted user on the database and use OS based authentication (OCI) within Oracle (bad but is it worse then hardcoded usernames and passwords?). Anything more than that gets very complex. You could use Perl to encrypt the user names and passwords in another file and read then decrypt them and pass them as variables inside your Perl program.

Oracle 8i and 9i do support SSL connections but you need to buy an additional licenses for Oracle Security (Any DBA's can correct me on this). Oracle 10G has some other mechanisms allowing key-based authentication using Oracle Wallet. However at that level you would probably need to use DBI/DBD::Oracle within your Perl instead of calling sqlplus.

HTH

Andrew Y

[StanleySWLee]

using array in the .sh script for manipulation.