Invoking sqlplus in another machine - Net::Telnet

[dmazzini]

I am trying to connect to a remote unix machine using Net::Telnet, then execute this in the remote machine:

It works ok from command line in the remote machine

tpflss1:/m/home/dmazzini (16) dmazzini% echo "select name from objects where object_class = 811 and object_instance <> 0 and name is not null;" | sqlplus username/password

SQL*Plus: Release 9.2.0.8.0 - Production on Thu May 22 17:17:50 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL>
NAME
--------------------------------------------------------------------------------
FLRNC002
FLRNC001
ORRNC001
ATRNC002
ATRNC001
TPRNC001
ATRNC003
BHRNC001

8 rows selected.

When I trying to do it from perl script

sub connecting_remote_machine {	
	system("cls");
	my ($market)=shift;
	$market= $SERVERS{$market}->[0];
  $server=$SERVERS{$market}->[1];
  $ip=$SERVERS{$market}->[2];
  $username=$SERVERS{$market}->[3];
  $passwd=$SERVERS{$market}->[4];		
	print "User $SERVERS{$market}->[3] connecting to $SERVERS{$market}->[0] Server.....\n"; 
	 
  my $telnet = new Net::Telnet(Timeout => 10, Host=> $ip, Port=>'23');
  $telnet-> open(Host=>$ip);
  $telnet-> waitfor(-match => '/login: ?$/i');
  $telnet-> print($username);
  $telnet-> waitfor(-match => '/Password: ?$/i');
  $telnet-> print($passwd);
  $telnet-> waitfor(-match => '/$ ?$/i'); 
   my $cmd=qq(echo \"select name from objects where object_class = 811 and object_instance \<\> 0 and name is not null;\" | sqlplus username\/password);
  @line=$telnet-> cmd($cmd);  
  $telnet-> print("exit");
  foreach my $value(@line){
  	      print "$value";  	
  }
  	
 print @line;
 print "\n";  
  
}

Script connect properly to the remote machine, but @line just shows part of sqlplus output

xdagw02b:/home/dmazzini % perl 1.pl
User dmazzini connecting to the  Server.....
Please wait...checking for disk quotas
echo "select name from objects where object_class = 811 and object_instance <> 0 and name is not null;" |
(c)Copyright 1983-2000 Hewlett-Packard Co.,  All Rights Reserved.
(c)Copyright 1979, 1980, 1983, 1985-1993 The Regents of the Univ. of California
(c)Copyright 1980, 1984, 1986 Novell, Inc.
(c)Copyright 1986-1992 Sun Microsystems, Inc.
(c)Copyright 1985, 1986, 1988 Massachusetts Institute of Technology
(c)Copyright 1989-1993  The Open Software Foundation, Inc.
(c)Copyright 1986 Digital Equipment Corp.
(c)Copyright 1990 Motorola, Inc.
(c)Copyright 1990, 1991, 1992 Cornell University
(c)Copyright 1989-1991 The University of Maryland
(c)Copyright 1988 Carnegie Mellon University
(c)Copyright 1991-2000 Mentat Inc.
(c)Copyright 1996 Morning Star Technologies, Inc.
(c)Copyright 1996 Progressive Systems, Inc.
(c)Copyright 1991-2000 Isogon Corporation, All Rights Reserved.


                           RESTRICTED RIGHTS LEGEND
Use, duplication, or disclosure by the U.S. Government is subject to
restrictions as set forth in sub-paragraph (c)(1)(ii) of the Rights in
Technical Data and Computer Software clause in DFARS 252.227-7013.

                           Hewlett-Packard Company
                           3000 Hanover Street
                           Palo Alto, CA 94304 U.S.A.

Rights for non-DOD U.S. Government Departments and Agencies are as set
forth in FAR 52.227-19(c)(1,2).
s0Atpflss1:/m/home/dmazzini (1) dmazzini%
tpflss1:/m/home/dmazzini (1) dmazzini% echo "select name from objects where object_class = 811 and object
;" | sqlplus rdr/rdr

SQL*Plus: Release 9.2.0.8.0 - Production on Thu May 22 17:23:22 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL
xdagw02b:/home/dmazzini %

As you can see it "@line" does not populate the right info, I even tried writing results to a file:
echo "select name from objects where object_class = 811 and object ;" | sqlplus rdr/rdr > resultsl.dat

and it just show same output as above.

Any ideas how to handle it with Net::Telnet Module



dmazzini
GSM/UMTS System and Telecomm Consultant

 

[travs69]

any reason your not using the normal login method for net::telnet? I would use the dump_log and the input_log to see what is really going on .. but I wonder if you double quotes are making it there properly?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[max1x]

I can't be for sure but it could be the prompt "SQL>" that can be causing the issue, even though Net::Telnet understands these '/[\$%#>] $/'. You can try connecting to the DB first match the prompt and then try to send the query string.

- I use informix to access data as below and it works from me.

You might also want to try the last_line and last_prompt options

$t = Net::Telnet (Timeout => 15,
                  Dump_log => /etc/dump);
$t->open($HOST);
eval {
$t->login($uid,$passwd);
}; print $@ and die if ($@);
@lines = $t->cmd("echo \"select...\" | sqlplus uid/pwd");
if (@lines) {
print "Success \n @lines);
} else {
print "Check the logs\n" and $t->cmd("exit");
}
$t->break;

 

[travs69]

I would say it is catching the >, just take that out of the list of matches if you don't need it.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;