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/10.2.0.4";
$ORACLE_SID=qaecom1;
$sqlplus="/u01/app/oracle/product/10.2.0.4/bin/sqlplus";
$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}'`;

[download]


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

[download]
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 (Archbishop) 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/10.2.0.4"; ...

    [download]
    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.

[reply]
[d/l]
Re: use oracle home in script
by roboticus (Chancellor) on May 24, 2012 at 20:43 UTC

    bluethundr:

    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/10.2.0.4";
$ORACLE_SID=qaecom1;
$sqlplus="/u01/app/oracle/product/10.2.0.4/bin/sqlplus";
$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

    [download]

    ...roboticus

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

[reply]
[d/l]

Back to Seekers of Perl Wisdom