Very Computer

Hallo!
I'm trying to connect an ORACLE-DB to the WWW throught CGI.
Because I want to learn more about it, I took Perl to write a
script - but I don't also want to use oraperl.
The database is running on a Solaris2.4 platform, and there is an
ORACLE-Web-Server 1.0 for the WWW-requests.

The CGI-Perl-script should do this:
1. Connect DB (here: SQL-PLUS)
2. SELECT * FROM ...
3. DISCONNECT DB

Therefore I wrote this:
[...]
print `sqlplus user/passw`;                    
print `select * from obj;`;                    
print `exit`;
[...]
As the result in the WebBrowser I could first see something
like "connected...", then there was an empty row and at last
somthing like "disconnected..." - but no result of the request.
I think the problem is about standardin /-out: when the DB is
connected, I don't know where <STDIN> and <STDOUT> are lying on.

Next I tried to get the result of the request into a file
with the line
print `spool /export/home/www/ergebn.data`;    
before the select-statement. This should send the answerstream
into the file ergebn.data, but I found no file in that path.

At last I put a pipe a) before and b) after the select-statement -
nothing new happened.

So what could I do? Is there anybody who knows how to handle
standardin/-out in this case?

Ingo Ruth.

The following subroutine calls sqlplus and loads the info obtained into
a hash.
Again this isn't exactly what you're looking for but I'm hoping that it
will point you (or
others) in the right direction

sub mysubroutine
{
    open(SQLPLUS, "/usr/oracle/bin/sqlplus username/password <<!
SET SQLPROMPT \" \"
SET HEADING OFF
select col1, col2
from   table1
order by col2;
quit;
! |")
        || die "cannot open sqlplus process: $!\n";

    while (<SQLPLUS>)
    {
        if ($_ !~ /\./ &&
            ($_ ne "\n"))
        {
            ($var1, $var2) = /^(\S+)\s+(.+)$/;
            $hash{$var2} = $var1;
        }
    }
    close (<SQLPLUS>);
    return %hash;

Quote:}

Quote:>Hallo!
>I'm trying to connect an ORACLE-DB to the WWW throught CGI.
>Because I want to learn more about it, I took Perl to write a
>script - but I don't also want to use oraperl.
>The database is running on a Solaris2.4 platform, and there is an
>ORACLE-Web-Server 1.0 for the WWW-requests.
>The CGI-Perl-script should do this:
>1. Connect DB (here: SQL-PLUS)
>2. SELECT * FROM ...
>3. DISCONNECT DB
>Therefore I wrote this:
>[...]
>print `sqlplus user/passw`;                
>print `select * from obj;`;                
>print `exit`;
>[...]
>As the result in the WebBrowser I could first see something
>like "connected...", then there was an empty row and at last
>somthing like "disconnected..." - but no result of the request.
>I think the problem is about standardin /-out: when the DB is
>connected, I don't know where <STDIN> and <STDOUT> are lying on.
>Next I tried to get the result of the request into a file
>with the line
>print `spool /export/home/www/ergebn.data`;
>before the select-statement. This should send the answerstream
>into the file ergebn.data, but I found no file in that path.
>At last I put a pipe a) before and b) after the select-statement -
>nothing new happened.
>So what could I do? Is there anybody who knows how to handle
>standardin/-out in this case?
>Ingo Ruth.

to a file, then open in perl.  Remember to put a "spool off" after
the select-statement.

Have fun,
Patrick

  http://src.doc.ic.ac.uk/packages/perl/db/perl4/oraperl

-T.H.

e.g.

$cmd = `/bin/ksh sql.sh arg1 arg2`;
print `$cmd`;

and the in sql.sh you can have

#!/bin/ksh
/sybase/bin/isql -Uuser -Ppasswd << END_SQL
your sql goes here
END_SQL
#end of script

hope that helps.

--
Charlie Wu
Web Applications Developer/Consultant
AT&T, Atlanta, Georgia

:  [ wants to connect to Oracle from Perl ]

: I suggest you check out OraPerl at

:   http://src.doc.ic.ac.uk/packages/perl/db/perl4/oraperl

I'd suggest you upgrade to Perl 5.0, and use the DB interface.  Its
the only sane way to use Perl and Oracle together if you actually
expect a heavy load on your web server. To be honest, when I expect a
heavy load on the web server, I'd forget about coupling Oracle and
Perl together. Although the DB interface is implemented pretty well,
there are several important relational database concepts that are hard
to map into Perl's world (like NULL columns, for example).  If you're
dealing with datasets where every column is text or just a number and
is always filled, then Perl works OK. oraperl is not very speedy,
however.

As a sidenote, does it amaze anyone else that Oracle has completely
failed to come up with a decent C interface to their server code ? OCI
is functional, but hardly convenient. Its not very difficult to write
a sscanf() like interface - I've done it twice, and I know at least
one other person who has done the same. Given that internal Oracle
developers do the same thing for themselves, how come Oracle has been
so lame with their exported interface ?

And as a second sidenote, can I also comment on how unbelievable it is
to me that Oracle chose PL/SQL as the language their web server uses
for "extensions" ? This ADA/SQL monstrosity is admitted by the people
who actually implemented it to be incredibly slow compared to native
code, and SQL itself is so far from being even close to the kind of
language I would want to use for Web work ... how much more useful
their server would have been if they had added a reaonable API, and
let you do run-time dynamic linking of object code into their server,
like Netscape and Apache do. That allows you to write in the language
of your choice, rather than a lame attempt at proceduralizing SQL.

Oh well, thankfully its not hard to extend Apache to provide the
same kind of simple access to an Oracle database that OWS does.

--p

--
hybrid rather than pure; compromising rather than clean;    | Militant Agnostic
distorted rather than straightforward; ambiguous rather than| I Don't Know
articulated; both-and rather than either-or; the difficult  |   and You Don't
unity of inclusion rather than the easy unity of exclusion. |   Know Either

Ingo,
if you have Oracle's Webserver already installed then do not use Perl, use
stored procedures to create dynamic SQL and HTMLpages.
Regards,
Ferenc

If I'm not mistaking the Oracle Web Agent (a full featured cgi that
launches stored procedures for you) is available for free for all oracle
databases later than 7.0. only the websystem 2 requires new licenses...

check it out with your vendor...
the main goal of the owa was to recycle investment in training staff to
use pl/sql... with limited web knowledge you can link your oracle to the
web...

-marc=

--
Marc Portier                               e-COM Interactive Expertise
Software Engineer                         Innovatie & Incubatiecentrum

http://www.ecom.be/cvmpo.htm                         B-9052 Zwijnaarde
tel:+32-9-2415640                                    tel:+32-9-2415600
                                                     fax:+32-9-2451814

Quote:> >Hallo!
> >I'm trying to connect an ORACLE-DB to the WWW throught CGI.
> >Because I want to learn more about it, I took Perl to write a
> >script - but I don't also want to use oraperl.
> >The database is running on a Solaris2.4 platform, and there is an
> >ORACLE-Web-Server 1.0 for the WWW-requests.

> >The CGI-Perl-script should do this:
> >1. Connect DB (here: SQL-PLUS)
> >2. SELECT * FROM ...
> >3. DISCONNECT DB

1. Make a shell-script like:

sqlplus -s usr/passwd << EOF
select * from ...
EOF

This can off course be further complicated by introducing PL/SQL. If it
turns out to be too complex I use Pro*C to make a small program, which i
call from the CGI-shell script.

Regards

Ole Moller

Oraperl        Rmpf  Oraperl emulation interface for DBD::Oracle  DBIML !

which is an interface to the DBD::Oracle.pm perl5 package.

perl4 is NOT recommended.

Also, look at the perl5 CGI modules including the CGI::MiniServer
and Apache:: modules if you need to do commit/rollback over multiple
CGI transactions.

Quote:>-T.H.

p.s. I understand (but may be wrong) that Oracle WS2 licence
does not permit use of webserver2 connected to the internet.

p.p.s
if you do use perl remeber to set you enviroment up.

i.e.

$ENV{"ORACLE_HOME"} = "/home/oracle/home";
$ENV{"ORACLE_SID"} = "xxxxx";
$ENV{"TWO_TASK"} = "sssss.yyyy.co.uk";
...

and remeber to add $ORACLE_HOME/bin to your path if you intend
to run any oracle binaries from system `` or open("sqlplus |")...

If you are using Apache, you  can configure ORACLE_HOME etc
into the env using SetEnv and PassEnv - this does not work
with the mod_perlfast though...

All the best,
        Jacqui Caren
--

Commercial perl resources - support,training,products etc

AD: Need flexible, high quality reporting? - www.perl.co.uk/ig/dp.html

I think I understand your question correctly !
What is the problem with Pro*C /PL/SQL or OCI ?
I guess it is the best interface to RDBMS kernel compared to any other
products like SYBASE / INFORMIX .

Did you ever try using the Oracle Web Server 2x ? You dont want to do lot
of C code in Oracle any way. I bet there is always a cute way of writing
programs using PL/SQL oracle web agent. If you are going to use your own
PEARL scripts etc, you may have the headache of maintaining connections to
the DB etc.

Good Luck
Sree

It is very easy to get data from Oracle using Tim Bunce's Most Excellent
interface, for example:

my $drh = DBI->install_driver('Oracle');
my $dbh = $drh->connect('SID1', '/') || die($DBI::errstr);
my $csr = $dbh->prepare("select item, cost from inventory order by
item");
$csr->execute();
my ($item, $cost);
while (($item, $cost) = $csr->fetchrow())
   {
   print("$item costs $cost<BR>\n");
   }
$csr->finish();
$dbh->disconnect();

This in conjunction with the Perl CGI library makes it very easy to
write scripts
that interface to Oracle and to the rest of the world.  Try sending
email from
Oracle's WOW interface, for example...

--
Alan Burlison

My opinions may be incorrect, but they are my own.