Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

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 web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2016-10-23 21:40 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (302 votes). Check out past polls.