Call sqlplus with exec command FROM PERL

[GerGon]

Hi gurus,

I want to call sqlplus from Perl, but passing parameters, I do not want use DBI, it's a simple call.. What shoud I correct?

Notes:
1. Remember, Oracle end each command with ';' like perl.
2. I want to mix variables, inside oracle command put perl variables, see below.
3. The output from sql command is redirected to a file out_file, see below.

For each line en the file, call sqlplus and execute one command but passing variables from perl.

If you think... it's better with unix shell rather than perl, I accepted too.

#!/usr/bin/perl
open(INFILE,"salida");
while () {
$user=~substr($_,1,20);
$vday=~substr($_,22,8);
$vtime=~substr($_,32,8);
exec 'sqlplus',
' << OEF
connect system/manager;
select username,status
from v$session
where username = $user
and logon_time = $vday$time;
EOF
' > out_file.txt;
}
close(INFILE);

Thanks for your time...

[Marvin Strong]

one problem I see, unless I'm losing it is your "here" doc is looking for OEF and you end it with EOF.

[Marvin Strong]

wish there was an edit.

here is how I am doing it in a ksh.

sqlplus /nolog << eof
connect ${usr}
column file_name format a55
column member format a55
set pagesize 0
select file_name from dba_data_files
order by file_name
/
select member from v\$logfile
order by member
/
exit
eof

[Hein van den Heuvel · Accepted solution]

For such simple sqplus 'one liner' I would use something like:

$username = "SAPLNX";
$command = "select status from v\\\$session where username = '${username}';";
foreach (`echo "$command" | sqlplus -s "/ as sysdba"`) {
print "-- $_";
}

For somewhat more elaborate work I would create an sqlplus script that takes params.

For even more advanced work I'd 'OPEN' a sqlplus command: open (SQL ,"sqlplus..|")
Redirect output to a file and feed commands.
Later parse the file.

Or... generate a sqplus script and open a sqplus session for output (backticks again?)


For full control of input and output you'll need a fork and wait and stuff.


Cheers,
Hein.

[Hein van den Heuvel]

On re-visit I noticed that you intend to invoke sqlplus once for each line in 'salida'. That's rather clumsy.

Why not have SQLplus/Oracle do it all?
Define an EXTERNAL TABLE over 'salida' and just JOIN with SQL statements.

Here is a simplified example:
First...

CREATE OR REPLACE DIRECTORY salida_dir AS '/tmp';
CREATE TABLE salida_exernal (username VARCHAR (20))
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY salida_dir
ACCESS PARAMETERS ( records delimited by newline)
LOCATION ('salida.dat')
);

Next just execute the appropriate procedure over the tables:

cat > /tmp/test.sql
select status, s.username
from v$session s, salida_external e
where s.username = e.username;
:
order..
;


back to perl (or shell)


$foreach (`sqlplus -s "/ as sysdba" @/tmp/test.sql`){
... your result processing ...
}


This way you can create and maintain the more or less complex join in a seperate .sql file. You could of course also stick it in a string as per earlier reply.

You'll have to create the external table definition to match the data of course. I did not test this, but I think the actuall definition you need is something like:

CREATE TABLE salida_exernal (
username VARCHAR (20),
fill_1 CHAR (1),
vday VARCHAR (8),
fill_2 CHAR (1),
vtime VARCHAR (8))
:

Enjoy!
Hein.

[Mic V.]

A couple of thoughts...

I like to "hide" the login information ("system/manager") -- preferably by prompting the user. Next choice is creating an Oracle account with just enough permissions to do what's needed. Last choice, and I've had to do this, is create a file that's only readable by the user that runs the script and embed the information there.

As to shell vs Perl -- I guess part of that depends on other sysadmins at your site. If they know Perl, more power to you. If they don't, shell might be better so that you're not the only one who knows how to do it.

HTH,
Mic

[Hein van den Heuvel]

Also... always consider turning things around :-)
Given that the query action desired is trivial,
you might just want to do it all in perl. Untested:

#!/usr/bin/perl
$command = "select username, logon_time, status from v\\\$session;"
foreach (`echo "$command" | sqlplus -s "/ as sysdba"`) {
($user,$logon,$status} = split;
$session{$logon.$user} = $status;
}
open (INFILE,"open (OUTFILE,">out_file.txt) or die "xxx";
while () {
$user=~substr($_,1,20);
$vday=~substr($_,22,8);
$vtime=~substr($_,32,8);
$status = $session{$vday.$vtime.$user};
print OUT_FILE "$user $vday $vtime $status\n" if $status;
}

grins,
Hein.

[GerGon]

Thanks a lot everybody that has participated here..