perl and sqlplus .

[donny750]

hello ,

I've problem
it's possible to use sqlplus in perl like with script shell :

for example
sqlplus login/password@database


Thanks

 

[donny750]

system ?

 

[stevexff]

Suggest you use the DBI module instead. It's quite easy to use, and allows you to get the data back into perl hash and array structures.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[donny750]

thanks
i've install the modul
and run my script
i've this error

DBI connect('','donny@basetest',...) failed: ORA-12154: TNS:could not resolve
the connect identifier specified (DBD ERROR: OCIServerAttach) at C:\Documents a
nd Settings\doc\connection.pl line 10

my script

#!/usr/bin/perl

use DBI;

$user = 'donny';
$password = 'ppp';
$dbconnectstring = 'basetest';


$dbh = DBI->connect('dbi:Oracle:',$user.'@'.$password,$dbconnectstring);

i don't understand

thanks

 

[donny750]

if i want to use just sqlplus and not the DBI modul
how can i do ?

 

[donny750]

i've use
this

sytem ("'sqlplus username/password@instance <<-!EOSQL
WHENEVER SQLERROR exit 5
select dummy from dual;
exit 0
!EOSQL
';");

and i've a problem because the code are not on the same line,so sytem use just the first line code.

 

[stevexff]

Donny

http://search.cpan.org/~pythian/DBD-Oracle-1.19/Oracle.pm#CONNECTING_TO_ORACLE

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[dmazzini]

Hi

Here an example:

$sql_return = `sqlplus -s user/password << SQL_END
			set linesize 80
			set pagesize 10
			set heading off
			set feedback off
			SELECT  ID,NAME,LASTNAME                  		                             
           		FROM    EMPLOYEES       
           		WHERE ( ID= '$id');					
           		quit
			SQL_END
			`;			
			chomp($sql_return);
			$sql_return = trim($sql_return);			
			my ($id,$name,$lastname)= split('\n',$sql_return);
			
			
sub trim {
    my @out = @_;
    for (@out) {
        s/^\s+//;          # trim left
        s/\s+$//;          # trim right
    }
    return @out == 1 
              ? $out[0]   # only one to return
              : @out;     # or many
}

I have tested it and it works.

I have not tried assigning the oracle database, you might use

$sql_return = `sqlplus -s user/password\@database << SQL_END

Try it!


dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

I have a typo, please delete semicolon from:

 WHERE ( ID= '$id');

it must be
WHERE ( ID= '$id')



dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

Hi nignore last comment, semi colon goes there...ooops. I think need to go to rest...jjeje!

other tip, using filehandle

open FH, "echo 'select name from objects where object_class=3 and name is not null and object_instance is not null;\n' \| sqlplus -s user/password|";

while(<FH>) {
   do whatever you want to do 
}

dmazzini
GSM System and Telecomm Consultant