Special Environment Variable

[drkestrel]

I have a Perl script that would amongst other things Invoke Oracle 8i's SQL Plus with a perl generated SQL script for execution.

The problem is that now the SQL script needs to be invoked against different Oracle database (denoted by the oracle_sid environment variable).

Is it possible to set this variable in Perl? The way I do it in UNIX would be something like

ORACLE_SID=DB1
export ORACLE_SID

I just wonder if it would be 'better' to have an ordinary shell script that sets the environment and kicks of the perl script or whether it could be as easily done in Perl or not?

Also, when I pipe output from, say, invoking SQL script, is there a way to do something like

set -x

in UNIX?

 

[MikeLacey]

Hi there dr,

When you do a system() call or run a process on the end of a pipe (with open()) in Perl it builds an environment by looking at the %ENV array.

You can set $ORACLE_SID for an child processes like this:

$ENV{'ORACLE_SID'}='DB1';

and then run your sqlplus script like this:

system("sqlplus system/manager \@sqlscript.sql" Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[mbaranski]

Excelent answer, Mike, but I want to add 1 more thing that is worth mentioning that I've banged my head about before.

When you use the ENV hash to set a variable in your script, your script is a child process of the shell or whatever invoked it, so when you exit your script the environment change goes away with the script.

I don't know if this is an issue for you or not, but it's something that wasn't obvious to me right away...
Disclaimer:
Beware: Studies have shown that research causes cancer in lab rats.

 

[MikeLacey]

Quite true Mike, and you're not the only one this has bitten - there was a long thread about this a while ago wasn't there? Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.