Operating System - HP-UX
1748185 Members
3913 Online
108759 Solutions
New Discussion юеВ

Re: shell script access to oracle data

 
SOLVED
Go to solution
John Flanagan
Regular Advisor

shell script access to oracle data

I want to write a unix shell script to collect serial numbers but as each serial number is entered I want to check it is available in my oracle database. Is it possible to make a single connection to oracle do I need to make a connection for every request?

ie
start script
connect to database
loop
enter serial number
run pl/sql script
check result
end loop
close database connection
run job on serial number list
end script

Regards,

John.
15 REPLIES 15
H.Merijn Brand (procura
Honored Contributor
Solution

Re: shell script access to oracle data

Hmmm, does it have to be a *shell* script? I see this as a perl DBI/DBD::Oracle job

#!/opt/perl/bin/perl

my %collect;
foreach my $db (qw( DBI_1 DBI_2 )) {
my $dbh = DBI->connect (.....);
while (1) {
print "Snr: ";
my $snr = scalar ;
$snr =~ m/regex-to-check-snr/ or last;
# some dbi stuff
# store results in %collect;
}
$dbh->commit;
$dbh->disconnect;
}

foreach my $key (keys %collect) {
# post processing
}

Enjoy, Have FUN! H.Merijn
Enjoy, Have FUN! H.Merijn
Steve Steel
Honored Contributor

Re: shell script access to oracle data

Hi

I think 3 choices

1) As is and open Oracle each time;

2)Read all numbers to a flat file and then input and check them

3)Script the whole thing to run in the oracle login . sql pl/sql


Steve Steel
If you want truly to understand something, try to change it. (Kurt Lewin)
John Flanagan
Regular Advisor

Re: shell script access to oracle data

Procura

Can you point me at a resource to start learning perl from.

Thanks,

John.
H.Merijn Brand (procura
Honored Contributor

Re: shell script access to oracle data

My site has a link to a nice article from Wendy:
http://perlmonks.org/index.pl?node_id=284175 (where and how to start learning Perl)

My HP ITRC site pages can be found at

Singapore https://www.beepz.com/personal/merijn/
Rotterdam http://www.cmve.net/~merijn/
Seattle http://ww.hpux.ws/merijn/

Enjoy, have FUN! H.Merijn
Enjoy, Have FUN! H.Merijn
Brian Crabtree
Honored Contributor

Re: shell script access to oracle data

John,

Why not load the list in the database? You could build the pl/sql script into a procedure, and pass it a variable from inside of a pl/sql block. You could even define your result as an out variable of the code, and check the result in the block:

declare
seriallist is select number from serial;
result varchar2(20);
begin
for seriallist_rec in seriallist loop
checkscript(seriallist_rec,result);
if result != 0 then
dbms_output.put_line(result||' failed!');
end if;
end loop;

end;
/
John Flanagan
Regular Advisor

Re: shell script access to oracle data

I considered using PL/SQL to do this but I also need a small user interface screen and I don't think PL/SQL is good at that.

I am currently looking at the PERL option. It looks promising but I am having problems with the DBI interface. DBI is looking for perl 5.00503 and my version is 5.00502. I am currently searching the hp web site.

Regards,

John.
Yogeeraj_1
Honored Contributor

Re: shell script access to oracle data

hi john,

for perl, also have a look at http://www.cmve.net/~merijn/#Perl

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
John Flanagan
Regular Advisor

Re: shell script access to oracle data

Hi,

I can't get DBI to build. I get the following errors.

(Bundled) cc: warning 480: The -A option is available only with the C/ANSI C product; ignored.
(Bundled) cc: warning 422: Unknown option "f" ignored.
(Bundled) cc: warning 480: The +Onolimit option is available only with the C/ANSI C product; ignored.
(Bundled) cc: warning 480: The +Opromote_indirect_calls option is available only with the C/ANSI C product; ignored.
(Bundled) cc: warning 480: The +Z option is available only with the C/ANSI C product; ignored.
cpp: "/opt/perl/lib/5.8.2/PA-RISC1.1-thread-multi/CORE/perlio.h", line 108: error 4065: Recursion in macro "PerlIO".

Regards,
John.
H.Merijn Brand (procura
Honored Contributor

Re: shell script access to oracle data

The binary available from my site is 5.8.3 and includes the most recent DBI already.

The perl you have (5.005.x) is OK for building DBI (with some nags), but I am sure you will not be able to build DBD::Oracle with it (for reasons you can read in README.hpux in more recent perl distributions)

Addresses already posted

Enjoy, Have FUN! H.Merijn [ Who wonders what info Yogeeraj had to add ]
Enjoy, Have FUN! H.Merijn