General
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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
John Flanagan
Regular Advisor

Re: shell script access to oracle data

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

Regards,

John.
H.Merijn Brand (procura
Honored Contributor

Re: shell script access to oracle data

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
Enjoy, Have FUN! H.Merijn
John Flanagan
Regular Advisor

Re: shell script access to oracle data

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

Regards,

John.
John Flanagan
Regular Advisor

Re: shell script access to oracle data

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

H.Merijn Brand (procura
Honored Contributor

Re: shell script access to oracle data

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
Enjoy, Have FUN! H.Merijn
John Flanagan
Regular Advisor

Re: shell script access to oracle data

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.