shell script access to oracle data

[John Flanagan]

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.

[H.Merijn Brand (procura · Accepted solution]

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

[Steve Steel]

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

[John Flanagan]

Procura

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

Thanks,

John.

[H.Merijn Brand (procura]

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

[Brian Crabtree]

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]

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]

hi john,

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

regards
Yogeeraj

[John Flanagan]

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]

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 ]

[John Flanagan]

I had downloaded and installed 5.8.2 earlier today. Is this version good enough?

Regards,

John.

[H.Merijn Brand (procura]

Yes. IMHO 5.8.3 is better, but that's only small changes.

Where did you get it? from my site?
Then make sure that /opt/perl/bin or /opt/perl64/bin (if you fetched the 64bit version) is in front in your $PATH. (in front of path elements where other perl's can be found that is)

If you want to build DBD::Oracle too, you need the matching GNU gcc too

Use DBD::Oracle-1.15

Enjoy, Have FUN! H.Merijn

[John Flanagan]

I got it from software.hp.com.
I am currently downloading gcc 3.3.2

Regards,

John.

[John Flanagan]

I have downloaded and extracted gcc from your page but how do I install?

[H.Merijn Brand (procura]

Installation instruction (litarally) in the section that has the "GNU gcc" header:

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


--8<---
The 3.3.2 version of gcc available in the download section includes binutils 2.14 and gdb-6.0. Install it like this:

# cd /usr/local
# bzip2 -d < /var/tmp/gcc-3.3.2-64-11.00-elf64.tbz | tar xf -

And add /usr/local/pa20_64/bin to your $PATH, preferably in /etc/PATH.
-->8---

Enjoy, have FUN! H.Merijn

[John Flanagan]

I have tried to compile with gcc and perl 5.6.1 and I get the following errors.

/usr/include/sys/socket.h:484: error: parse error before "sendfile"
/usr/include/sys/socket.h:484: error: parse error before "bsize_t"
/usr/include/sys/socket.h:486: error: parse error before "sendpath"
/usr/include/sys/socket.h:486: error: parse error before "bsize_t"
/usr/include/sys/socket.h:501: error: parse error before "__sendfile64"
/usr/include/sys/socket.h:501: error: parse error before "bsize_t"
/usr/include/sys/socket.h:502: error: parse error before "__sendpath64"
/usr/include/sys/socket.h:502: error: parse error before "bsize_t"
/usr/include/sys/socket.h:504: error: parse error before "sendfile"
/usr/include/sys/socket.h: In function `sendfile':
/usr/include/sys/socket.h:504: error: parse error before "bsize_t"
/usr/include/sys/socket.h: At top level:
/usr/include/sys/socket.h:505: error: parse error before "sendpath"
/usr/include/sys/socket.h: In function `sendpath':
/usr/include/sys/socket.h:505: error: parse error before "bsize_t"

Is it possible to find a compiled DBI and DBD for Oracle on HP-UX 11.11 32 bit?

Regards,

John.