Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

use oracle home in script

by bluethundr (Pilgrim)
on May 24, 2012 at 20:14 UTC ( #972308=perlquestion: print w/replies, xml ) Need Help??
bluethundr has asked for the wisdom of the Perl Monks concerning the following question:

hello monks,
I am attempting to assign the output of a sqlplus command to an array. Assuming I can get that far I'll need to try to parse it with regexes but I'm not there yet. :)

Here's what I've been able to come up with so far:
#!/bin/perl # credentials / environment variables $ORACLE_HOME="/u01/app/oracle/product/"; $ORACLE_SID=qaecom1; $sqlplus="/u01/app/oracle/product/"; $USERNAME=dbuser; $PASS=pass; $SID=${ORACLE_SID}; @TSPACES=`$sqlplus -s -l $USERNAME/$PASS@$SID \@/opt/bin/ops/mlb_tabl +espace.sql | awk '{print $1}' $sqlplus -s -l $USERNAME/$PASS@$SID +\@/opt/bin/ops/mlb_tablespace.sql | awk '{print $3}' $sqlplus -s -l +$USERNAME/$PASS@$SID \@/opt/bin/ops/mlb_tablespace.sql | awk '{print + $2}' $sqlplus -s -l $USERNAME/$PASS@$SID \@/opt/bin/ops/mlb_table +space.sql | awk '{print $4}'`;

The biggest problem I am having, currently, is getting the script to recognize the environment variables needed to access the oracle db. Namely ORACLE_HOME.
Here is what happens when I attempt to run the script:
[db07-dc2:~] root% Error 6 initializing SQL*Plus Message file sp1<lang>.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software dire +ctory
Then it just hangs.. I was wondering if anyone might have some advice on how to get this script to work with Oracle DB. Thanks

Replies are listed 'Best First'.
Re: use oracle home in script
by Perlbotics (Canon) on May 24, 2012 at 20:30 UTC

    You are setting Perl-variables, not environment variables. So as a quick remedy, you could set

    $ENV{ORACLE_HOME}="/u01/app/oracle/product/"; ...
    and so on. See perlvar (%ENV).

    Long term approach would be to start using DBI and DBD::Oracle. There are also some PM tutorials: Database Programming available.

Re: use oracle home in script
by roboticus (Chancellor) on May 24, 2012 at 20:43 UTC


    Perlbotics already told you about the perl variable vs. environment variable, and has also suggested using DBI to talk to your database, so I won't elaborate on those.

    However, if you just want to get something working, I have a suggestion: You're running the same script four times and pulling out a different column each time. I suggest you run the script once, and let perl pull out the columns for you. Something like:

    #!/bin/perl # credentials / environment variables $ORACLE_HOME="/u01/app/oracle/product/"; $ORACLE_SID=qaecom1; $sqlplus="/u01/app/oracle/product/"; $USERNAME=dbuser; $PASS=pass; $SID=${ORACLE_SID}; # Get all the data at once @TSPACES=`$sqlplus -s -l $USERNAME/$PASS@$SID \@/opt/bin/ops/mlb_table +space.sql`; # Now split the 4 columns you want into separate arrays, # and then join those arrays in the order you want: { my (@col1, @col2, @col3, @col4); for (@TSPACES) { my @cols = split; push @col1, $cols[0]; push @col2, $cols[1]; push @col3, $cols[2]; push @col4, $cols[3]; } @TSPACES = (@col1, @col2, @col3, @col4); } # ... the rest of your script


    When your only tool is a hammer, all problems look like your thumb.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://972308]
Approved by Perlbotics
and the monks are chillaxin'...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2017-05-29 22:50 GMT
Find Nodes?
    Voting Booth?