Perl quoting Problem doing select on Oracle.

[Kalin Evtimov]

Hallo!

I have a stupid problem hier. I have following piece of code:

$s=qx{/opt/oracle/ora92s/bin/sqlplus -SL /nolog <set echo off termout off heading off feedback off trimspool on trimout on verify
off linesize 300 pagesize 0 pause off
connect / as sysdba
whenever sqlerror exit failure
select value from v$parameter where name = 'background_dump_dest';
exit
END};

I don't know how to quote v$parameter, so that it is read as v$parameter, not as v or something like that.

Thank you!

[Oviwan · Accepted solution]

hi

try to escape the '$' --> v\$parameter

Regards

[Peter Godron]

Kalin,
have you looked at perldoc -q quote ?

[Kalin Evtimov]

Tried all possible slashes and bachslashes but didn't work. Anyway, I changed it in:

open ORA, "|/opt/oracle/ora92s/bin/sqlplus -SL /nolog\n";
print ORA "set echo off termout off heading off feedback off trimspool on trimout on verify off linesize 300 pagesize 0 pause off\n";
print ORA "connect / as sysdba\n";
print ORA "whenever sqlerror exit failure\n";
print ORA "select value from v\$parameter where name = 'background_dump_dest';\n";
print ORA "exit\n";
close ORA;

It worked.
But now I don't know how to put the output of this in a variable, instead to STDOUT.

[Muthukumar_5]

Try to use array variable instead of scalar.

@arr=qx [ command1; command2; command3; etc.. ]

print @arr;

gives that.

-Muthu

[Kalin Evtimov]

But the escaping problem stays, using qx{}..

[Muthukumar_5]

try as,

$param1="v$parameter";
@arr=qx [ command1;select value from $param1 where name= ... ]

You can also try with ` ` instead of qx [ ] to accomplish this.

-Muthu

[Kalin Evtimov]

Thanks.
Unfortunately nothing works. I dont't know anymore and I am leaving it.

[Oviwan]

you can create a synonyme on the oracle database for v$parameter without a '$' in the synonyme name, then it should work.

Regards

[Kalin Evtimov]

Nice, but I am not allowed to do anything on the DB.
Perhaps I will use the "print HANDLE" approach and a file as a buffer, where the output is written, because two way communication in Perl is a bit complex for me.

[Kalin Evtimov]

IT Works!

I don't know why, but it looks like this:

select value from v\\\$\parameter where name = 'background_dump_dest';

after half a day testing I found it.

[Oviwan]

nice :)

other solution. if you have installed the dbi module you could use this code:

#!/usr/bin/perl -w

use DBI;
$connection = DBI->connect("DBI:Oracle:DBNAME","USERNAME", "PASSWORD");
$query = $connection->prepare("select value from v\$parameter where name = 'background_dump_dest'");
$query->execute();
while (@result=$query->fetchrow_array) {
print "$result[0]\n";
}
$connection->disconnect();

[Kalin Evtimov]

Yeah, but I don't know whether DBI-Module is on all systems. Except this I am logging in without having to type password etc..