topic Re: Perl quoting Problem doing select on Oracle. in Operating System - Linux

Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 02:49:43 GMT

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!

Re: Perl quoting Problem doing select on Oracle.

Oviwan — Tue, 13 Dec 2005 03:39:23 GMT

hi

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

Regards

Re: Perl quoting Problem doing select on Oracle.

Peter Godron — Tue, 13 Dec 2005 03:45:30 GMT

Kalin,
have you looked at perldoc -q quote ?

Re: Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 03:46:00 GMT

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.

Re: Perl quoting Problem doing select on Oracle.

Muthukumar_5 — Tue, 13 Dec 2005 03:51:15 GMT

Try to use array variable instead of scalar.

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

print @arr;

gives that.

-Muthu

Re: Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 03:59:09 GMT

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

Re: Perl quoting Problem doing select on Oracle.

Muthukumar_5 — Tue, 13 Dec 2005 04:04:04 GMT

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

Re: Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 04:18:40 GMT

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

Re: Perl quoting Problem doing select on Oracle.

Oviwan — Tue, 13 Dec 2005 04:45:55 GMT

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

Regards

Re: Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 05:05:12 GMT

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.

Re: Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 05:36:16 GMT

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.

Re: Perl quoting Problem doing select on Oracle.

Oviwan — Tue, 13 Dec 2005 05:46:57 GMT

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();

Re: Perl quoting Problem doing select on Oracle.

Kalin Evtimov — Tue, 13 Dec 2005 08:56:35 GMT

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..