It seems to be doing fine until it asks the database to actually execute the
block, then the database never sends back a response. Except for your
statement that the block ran as expected from SQL*Plus, I'd lay the whole
problem on the server.
I think I've gone as far with this as I'll be able to. To help the next
person help you, please list the following for both the server running
Oracle and the client running DBI (not everything will apply to both):
Host type
OS and OS Version
Oracle Version
DBI Version
DBD::Oracle Version
If you ran SQL*Plus on a different host than DBI, list the applicable
information for it too.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 15, 2001 09:11
Subject: RV: Problem with stored procedures
This is the contained by the log:
DBI::db=HASH(0x81a7f70) trace level set to 4 in DBI 1.13-nothread
Note: perl is running without the recommended perl -w option
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x81a7f10)~0x81a7f70 '
BEGIN
:whoami := plsql_example.func_np;
END;
')
dbih_setup_handle(DBI::st=HASH(0x80f7bc4)=>DBI::st=HASH(0x80f7c0c),
DBD::Ora
cle::st, 80f7bd0, Null!)
dbih_make_com(DBI::db=HASH(0x81a7f70), DBD::Oracle::st, 204)
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql BEGIN
<- prepare= DBI::st=HASH(0x80f7bc4) at prueba.plx line 61.
-> bind_param_inout for DBD::Oracle::st
(DBI::st=HASH(0x80f7bc4)~0x80f7c0c '
:whoami' SCALAR(0x817ca08) 30)
bind :whoami <== 'nada' (type 0, inout 0x817ca08, maxlen 30)
bind :whoami <== 'nada' (size 4/5/30, ptype 4, otype 1, inout)
bind :whoami <== 'nada' (size 4/30, otype 1, indp 0)
bind :whoami done
<- bind_param_inout= 1 at prueba.plx line 67.
-> execute for DBD::Oracle::st (DBI::st=HASH(0x80f7bc4)~0x80f7c0c)
dbd_st_execute BEGIN (out1, lob0)...
with :whoami = 'nada' (len 4/30, indp 0, otype 1, ptype 6)
dbd_phs_in ':whoami' (0,0): len 4, ind 0
Hern�n Moreno Murillo.
Ingeniero de Desarrollo 3T. PORTA.
Oficina: 2517 000 ext. 4146 Celular: 09 9613 572
www.porta.net
> -----Mensaje original-----
> De: Michael A. Chase [SMTP:[EMAIL PROTECTED]]
> Enviado el: S�bado 13 de Octubre de 2001 7:20 PM
> Para: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Asunto: Re: Problem with stored procedures
>
> Add '$dbh -> trace( 4, "trace.log" );' before the prepare to see if the
> trace.log can shed some light.
> --
> Mac :})
> ** I normally forward private questions to the appropriate mail list. **
> Give a hobbit a fish and he eats fish for a day.
> Give a hobbit a ring and he eats fish for an age.
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, October 12, 2001 03:08 PM
> Subject: RE: Problem with stored procedures
>
>
> Yes i added print statements ,before and after the execute() the first is
> printed the second doesn�t. I think it hang.
>
> Hern�n Moreno Murillo.
> Ingeniero de Desarrollo 3T. PORTA.
> Oficina: 2517 000 ext. 4146 Celular: 09 9613 572
> www.porta.net
>
> > -----Mensaje original-----
> > De: Michael A Chase [SMTP:[EMAIL PROTECTED]]
> > Enviado el: Viernes 12 de Octubre de 2001 11:46 AM
> > Para: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> > Asunto: Re: Problem with stored procedures
> >
> > Please reply to the list. I don't always check my mail every day and
> > others
> > often have ideas to share.
> >
> > When you execute the script from the command line, you don't even get
> the
> > message from before execute()? You might want to add print statements
> > before connect() and prepare() to make sure the script actually started.
> >
> > You might also add '$| = 1;' to the script to make sure the "hasta
> aqui\n"
> > message isn't trapped in a buffer. That shouldn't be necessary when
> > running
> > from the command line unless you are redirecting the output, but it
> won't
> > hurt for small amounts of output.
> > --
> > Mac :})
> > ** I normally forward private questions to the appropriate mail list. **
> > Give a hobbit a fish and he eats fish for a day.
> > Give a hobbit a ring and he eats fish for an age.
> > ----- Original Message -----
> > From: <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Friday, October 12, 2001 11:10
> > Subject: RE: Problem with stored procedures
> >
> >
> > That looks good under SQL*Plus but with DBI isn�t the same that look
> like
> > this:
> >
> > [mmacias@intranet detalles]$
> > [mmacias@intranet detalles]$
> > [mmacias@intranet detalles]$
> > [mmacias@intranet detalles]$ perl prueba.plx
> > The cursor stays there, under the next line ..... there isn�t error
> > notifications..... prueba.plx is the CGI on another server called
> > intranet
> >
> >
> > Hern�n Moreno Murillo.
> > Ingeniero de Desarrollo 3T. PORTA.
> > Oficina: 2517 000 ext. 4146 Celular: 09 9613 572
> > www.porta.net
> >
> > > -----Mensaje original-----
> > > De: Michael A Chase [SMTP:[EMAIL PROTECTED]]
> > > Enviado el: Viernes 12 de Octubre de 2001 10:14 AM
> > > Para: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> > > Asunto: Re: Problem with stored procedures
> > >
> > > I haven't used packages or stored functions from DBI very often, but I
> > > haven't seen any problems when I have.
> > >
> > > What happens if you run the following under SQL*Plus with the same
> > > userid/password@instance?
> > >
> > > VARIABLE whoami VARCHAR2(30);
> > > BEGIN
> > > :whoami := plsql_example.func_np;
> > > END;
> > > /
> > > PRINT whoami
> > >
> > > --
> > > Mac :})
> > > ** I normally forward private questions to the appropriate mail list.
> **
> > > Give a hobbit a fish and he eats fish for a day.
> > > Give a hobbit a ring and he eats fish for an age.
> > > ----- Original Message -----
> > > From: <[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Friday, October 12, 2001 07:45
> > > Subject: Problem with stored procedures
> > >
> > >
> > >
> > > > I have a problem when i was trying to access data base with the
> > > > DBD::Oracle, my the problem is:
> > > > Using any stored procedure with parameters the script remains
> > paralyzed
> > > > (zombie) I even used of such examples of the documentation but the
> > > result
> > > > was negative.
> > > >
> > > > Of course this stored procedure exists in the data base
> > > > (plsql_example.func_np)
> > > >
> > > >
> > >
> >
> **************************************************************************
> > > > ******************************
> > > > #!/usr/bin/perl
> > > >
> > > > use DBI;
> > > > use DBD::Oracle qw(ORA_VARCHAR2);
> > > > use strict;
> > > > my($db, $sth, $ret_val);
> > > >
> > > > $ENV{ORACLE_HOME} = "/u1/oracle/product/8.0.5";
> > > > $db = DBI->connect("dbi:Oracle:host=my_ip.com;sid=gyte", "VMA",
> > > > "veritus")
> > > > or die "Unable to connect: $DBI::errstr";
> > > > $db->{RaiseError} = 1;
> > > >
> > > > my $whoami="";
> > > > my $sql = qq(
> > > > BEGIN
> > > > :whoami := plsql_example.func_np;
> > > > END;
> > > > );
> > > > $sth = $db->prepare($sql);
> > > > $sth->bind_param_inout( ":whoami", \$whoami, 30,{ora_type =>
> > > ORA_VARCHAR2}
> > > > );
> > > > print "hasta aqui\n";
> > > > $sth->execute;
> > > > print "esto no funciona\n";
> > > > $db->disconnect;
> > > >
> > >
> >
> **************************************************************************
> > > > ******************************
> > > > Only the first message is printed (hasta aqui)
> > > > the second isn�t.
> > > >
> > >
> >
> **************************************************************************
> > > > ******************************
> > > > OS = Linux Mandrake (with perl 5)
> > > > DB = Oracle 8i
> > > >
> > >
> >
> **************************************************************************
> > > > ******************************
> > > > Also this description is important,I can do all kind of selects,
> > > > insert,updates but if i use parameters like used in stored
> procedures
> > > then
> > > > it doesn�t work.
> > > >
> > > >
> > > >
> > > > Hern�n Moreno Murillo.
> > > > Ingeniero de Desarrollo 3T. PORTA.
> > > > Oficina: 2517 000 ext. 4146 Celular: 09 9613 572
> > > > www.porta.net
> > > >
> > >
> >
> >
