FAQ
Folks,

I am running into a problem here:

My script is running on:

Solaris SunOS 5.8 Generic_108528-15 sun4u sparc SUNW,Ultra-Enterprise
Perl 5.6.1
DBI 1.18
DBD::Oracle 1.07


It gives the following error while preparing a select query:
DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory for
define buffer (DBD ERROR: OCIDefineByPos) at script.pl line 159.

Here is the code snippet:
print $stmt2;
eval{
$csr2 = $dbh_prod->prepare($stmt2);
};
if($@){
$dbh_prod->trace(6);
die "prepare failed : $DBI::errstr\n"
}

A 'select count(*)' of the same query $stmt2 runs without any issues.

Here is the Oracle trace 6 output:

DBI::db=HASH(0x208b10) trace level set to 6 in DBI 1.18-nothread
Note: perl is running without the recommended perl -w option
-> $DBI::errstr (&) FETCH from lasth=DBI::db=HASH(0x208b10)
prepare failed : ORA-01062: unable to allocate memory for define buffer
(DBD ERROR: OCIDefineByPos)
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x208b10)~INNER)
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect() during global destruction.
OCITransRollback(22d0e4,22d30c,0)=SUCCESS
OCISessionEnd(22d0e4,22d30c,239724,0)=SUCCESS
OCIServerDetach(22d150,22d30c,0)=SUCCESS
OCIHandleFree(239724,9)=SUCCESS
OCIHandleFree(22d150,8)=SUCCESS
OCIHandleFree(22d0e4,3)=SUCCESS
OCIHandleFree(22d30c,2)=SUCCESS
<- DESTROY= undef during global destruction.


Any help regarding this is highly appreciated.

Thanks,
-Jibo

Search Discussions

  • Steven Hajducko at Nov 13, 2002 at 11:43 pm
    truss ./script.pl?

    Might provide some more interesting information about what's
    happening. That or start up a vmstat/top in another window and watch
    what happens when you fire the script off.

    No real idea why that would happen though.

    - -----Original Message-----
    From: Jibo John
    Sent: Wednesday, November 13, 2002 3:31 PM
    To: dbi-users@perl.org
    Subject: DBI giving unable to allocate memory error


    Folks,

    I am running into a problem here:

    My script is running on:

    Solaris SunOS 5.8 Generic_108528-15 sun4u sparc
    SUNW,Ultra-Enterprise
    Perl 5.6.1
    DBI 1.18
    DBD::Oracle 1.07


    It gives the following error while preparing a select query:
    DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory
    for
    define buffer (DBD ERROR: OCIDefineByPos) at script.pl line 159.

    Here is the code snippet:
    print $stmt2;
    eval{
    $csr2 = $dbh_prod->prepare($stmt2);
    };
    if($@){
    $dbh_prod->trace(6);
    die "prepare failed : $DBI::errstr\n"
    }

    A 'select count(*)' of the same query $stmt2 runs without any issues.

    Here is the Oracle trace 6 output:

    DBI::db=HASH(0x208b10) trace level set to 6 in DBI 1.18-nothread
    Note: perl is running without the recommended perl -w option
    -> $DBI::errstr (&) FETCH from lasth=DBI::db=HASH(0x208b10)
    prepare failed : ORA-01062: unable to allocate memory for define
    buffer
    (DBD ERROR: OCIDefineByPos)
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x208b10)~INNER)
    Issuing rollback() for database handle being DESTROY'd without
    explicit
    disconnect() during global destruction.
    OCITransRollback(22d0e4,22d30c,0)=SUCCESS
    OCISessionEnd(22d0e4,22d30c,239724,0)=SUCCESS
    OCIServerDetach(22d150,22d30c,0)=SUCCESS
    OCIHandleFree(239724,9)=SUCCESS
    OCIHandleFree(22d150,8)=SUCCESS
    OCIHandleFree(22d0e4,3)=SUCCESS
    OCIHandleFree(22d30c,2)=SUCCESS
    <- DESTROY= undef during global destruction.


    Any help regarding this is highly appreciated.

    Thanks,
    - -Jibo
  • Steven Hajducko at Nov 14, 2002 at 12:05 am
    Also,

    Why use eval? I believe ( from my minor experience here.. ) that
    eval is meant to do 2 things.

    a) execute code at runtime -
    my $code_snip = "print 'hi\n'";
    eval $code_snip

    would print:

    hi

    b) trap errors that would otherwise kill the program.

    In your case, you're killing your script anyways if the eval fails.
    So why not just do...

    print $stmt2;
    $csr2 = $dbh_prod->prepare($stmt2) or die
    $dbh_prod->trace(6),"\n\nCould not prepare statement:
    \n",$dbh_prod->errstr();

    Like I said, my perl experience is small, so I'm not sure if maybe
    the whole eval testing thing is taking up too much memory or what.

    Hope it helps,

    - --
    sh


    - -----Original Message-----
    From: Jibo John
    Sent: Wednesday, November 13, 2002 3:31 PM
    To: dbi-users@perl.org
    Subject: DBI giving unable to allocate memory error


    Folks,

    I am running into a problem here:

    My script is running on:

    Solaris SunOS 5.8 Generic_108528-15 sun4u sparc
    SUNW,Ultra-Enterprise
    Perl 5.6.1
    DBI 1.18
    DBD::Oracle 1.07


    It gives the following error while preparing a select query:
    DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory
    for
    define buffer (DBD ERROR: OCIDefineByPos) at script.pl line 159.

    Here is the code snippet:
    print $stmt2;
    eval{
    $csr2 = $dbh_prod->prepare($stmt2);
    };
    if($@){
    $dbh_prod->trace(6);
    die "prepare failed : $DBI::errstr\n"
    }

    A 'select count(*)' of the same query $stmt2 runs without any issues.

    Here is the Oracle trace 6 output:

    DBI::db=HASH(0x208b10) trace level set to 6 in DBI 1.18-nothread
    Note: perl is running without the recommended perl -w option
    -> $DBI::errstr (&) FETCH from lasth=DBI::db=HASH(0x208b10)
    prepare failed : ORA-01062: unable to allocate memory for define
    buffer
    (DBD ERROR: OCIDefineByPos)
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x208b10)~INNER)
    Issuing rollback() for database handle being DESTROY'd without
    explicit
    disconnect() during global destruction.
    OCITransRollback(22d0e4,22d30c,0)=SUCCESS
    OCISessionEnd(22d0e4,22d30c,239724,0)=SUCCESS
    OCIServerDetach(22d150,22d30c,0)=SUCCESS
    OCIHandleFree(239724,9)=SUCCESS
    OCIHandleFree(22d150,8)=SUCCESS
    OCIHandleFree(22d0e4,3)=SUCCESS
    OCIHandleFree(22d30c,2)=SUCCESS
    <- DESTROY= undef during global destruction.


    Any help regarding this is highly appreciated.

    Thanks,
    - -Jibo
  • Jibo John at Nov 14, 2002 at 12:28 am
    Hi All,

    I did a trial and error by reducing the {LongReadLen} and the script is
    running without any problems.

    #$dbh_prod->{LongReadLen} = 100000000;
    $dbh_prod->{LongReadLen}=30000000;

    My 'select' reads the data from a long field, specifying {LongReadLen} to
    a higher value might have been causing the unable to allocate memory error.

    Thanks,
    -Jibo


    At 04:05 PM 11/13/2002 -0800, Steven Hajducko wrote:
    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Also,

    Why use eval? I believe ( from my minor experience here.. ) that
    eval is meant to do 2 things.

    a) execute code at runtime -
    my $code_snip = "print 'hi\n'";
    eval $code_snip

    would print:

    hi

    b) trap errors that would otherwise kill the program.

    In your case, you're killing your script anyways if the eval fails.
    So why not just do...

    print $stmt2;
    $csr2 = $dbh_prod->prepare($stmt2) or die
    $dbh_prod->trace(6),"\n\nCould not prepare statement:
    \n",$dbh_prod->errstr();

    Like I said, my perl experience is small, so I'm not sure if maybe
    the whole eval testing thing is taking up too much memory or what.

    Hope it helps,

    - --
    sh


    - -----Original Message-----
    From: Jibo John
    Sent: Wednesday, November 13, 2002 3:31 PM
    To: dbi-users@perl.org
    Subject: DBI giving unable to allocate memory error


    Folks,

    I am running into a problem here:

    My script is running on:

    Solaris SunOS 5.8 Generic_108528-15 sun4u sparc
    SUNW,Ultra-Enterprise
    Perl 5.6.1
    DBI 1.18
    DBD::Oracle 1.07


    It gives the following error while preparing a select query:
    DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory
    for
    define buffer (DBD ERROR: OCIDefineByPos) at script.pl line 159.

    Here is the code snippet:
    print $stmt2;
    eval{
    $csr2 = $dbh_prod->prepare($stmt2);
    };
    if($@){
    $dbh_prod->trace(6);
    die "prepare failed : $DBI::errstr\n"
    }

    A 'select count(*)' of the same query $stmt2 runs without any issues.

    Here is the Oracle trace 6 output:

    DBI::db=HASH(0x208b10) trace level set to 6 in DBI 1.18-nothread
    Note: perl is running without the recommended perl -w option
    -> $DBI::errstr (&) FETCH from lasth=DBI::db=HASH(0x208b10)
    prepare failed : ORA-01062: unable to allocate memory for define
    buffer
    (DBD ERROR: OCIDefineByPos)
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x208b10)~INNER)
    Issuing rollback() for database handle being DESTROY'd without
    explicit
    disconnect() during global destruction.
    OCITransRollback(22d0e4,22d30c,0)=SUCCESS
    OCISessionEnd(22d0e4,22d30c,239724,0)=SUCCESS
    OCIServerDetach(22d150,22d30c,0)=SUCCESS
    OCIHandleFree(239724,9)=SUCCESS
    OCIHandleFree(22d150,8)=SUCCESS
    OCIHandleFree(22d0e4,3)=SUCCESS
    OCIHandleFree(22d30c,2)=SUCCESS
    <- DESTROY= undef during global destruction.


    Any help regarding this is highly appreciated.

    Thanks,
    - -Jibo





    -----BEGIN PGP SIGNATURE-----
    Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

    iQA/AwUBPdLor5j4tZe4p/BuEQKrOQCeMqKOvBIm1ki7tm7gRv6wnRuPI+EAoOdB
    3YUwCyosfQ6As1dLKa1rDdDt
    =jH75
    -----END PGP SIGNATURE-----
  • Michael A Chase at Nov 14, 2002 at 2:46 pm

    On Wed, 13 Nov 2002 16:05:02 -0800 Steven Hajducko wrote:

    Why use eval? I believe ( from my minor experience here.. ) that
    eval is meant to do 2 things.

    a) execute code at runtime -
    Both parse and execute.
    my $code_snip = "print 'hi\n'";
    eval $code_snip

    would print:

    hi

    b) trap errors that would otherwise kill the program.

    In your case, you're killing your script anyways if the eval fails.
    So why not just do...

    print $stmt2;
    $csr2 = $dbh_prod->prepare($stmt2) or die
    $dbh_prod->trace(6),"\n\nCould not prepare statement:
    \n",$dbh_prod->errstr();

    Like I said, my perl experience is small, so I'm not sure if maybe
    the whole eval testing thing is taking up too much memory or what.
    Probably not; eval{} is usually fairly lightweight.

    I haven't looked up what value trace() returns, but it probably isn't
    anything useful in the argument list of die().

    More below.
    - -----Original Message-----
    From: Jibo John
    Sent: Wednesday, November 13, 2002 3:31 PM
    To: dbi-users@perl.org
    Subject: DBI giving unable to allocate memory error
    It gives the following error while preparing a select query:
    DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory for
    define buffer (DBD ERROR: OCIDefineByPos) at script.pl line 159.

    Here is the code snippet:
    print $stmt2;
    eval{
    $csr2 = $dbh_prod->prepare($stmt2);
    };
    if($@){
    $dbh_prod->trace(6);
    die "prepare failed : $DBI::errstr\n"
    }

    A 'select count(*)' of the same query $stmt2 runs without any issues.

    Here is the Oracle trace 6 output:
    You need to turn on trace() _before_ the error occurs. The trace output
    below just shows the DBI shutdown processing and tells us nothing about the
    original error.
    DBI::db=HASH(0x208b10) trace level set to 6 in DBI 1.18-nothread
    Note: perl is running without the recommended perl -w option
    -> $DBI::errstr (&) FETCH from lasth=DBI::db=HASH(0x208b10)
    Using -w in the #! line and 'use strict;' near the top of the program are
    very useful for finding errors before they cause program failure.
    prepare failed : ORA-01062: unable to allocate memory for define
    buffer
    (DBD ERROR: OCIDefineByPos)
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x208b10)~INNER)
    Issuing rollback() for database handle being DESTROY'd without
    explicit
    disconnect() during global destruction.
    OCITransRollback(22d0e4,22d30c,0)=SUCCESS
    OCISessionEnd(22d0e4,22d30c,239724,0)=SUCCESS
    OCIServerDetach(22d150,22d30c,0)=SUCCESS
    OCIHandleFree(239724,9)=SUCCESS
    OCIHandleFree(22d150,8)=SUCCESS
    OCIHandleFree(22d0e4,3)=SUCCESS
    OCIHandleFree(22d30c,2)=SUCCESS
    <- DESTROY= undef during global destruction.
    --
    Mac :})
    ** I normally forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.
  • Ellen Yu at Nov 14, 2002 at 12:06 am
    Here is what the Oracle utility oerr returns for 1062 code:

    01062, 00000, "unable to allocate memory for define buffer"
    // *Cause: Exceeded the maximum buffer size for current plaform
    // *Action: Use piecewise fetch with a smaller buffer size
    // *Action: Use a client application linked with V8 (or higher)
    libraries.60


    Regards,

    Ellen Yu

    On Wed, 13 Nov 2002, Jibo John wrote:

    Folks,

    I am running into a problem here:

    My script is running on:

    Solaris SunOS 5.8 Generic_108528-15 sun4u sparc SUNW,Ultra-Enterprise
    Perl 5.6.1
    DBI 1.18
    DBD::Oracle 1.07


    It gives the following error while preparing a select query:
    DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory for
    define buffer (DBD ERROR: OCIDefineByPos) at script.pl line 159.

    Here is the code snippet:
    print $stmt2;
    eval{
    $csr2 = $dbh_prod->prepare($stmt2);
    };
    if($@){
    $dbh_prod->trace(6);
    die "prepare failed : $DBI::errstr\n"
    }

    A 'select count(*)' of the same query $stmt2 runs without any issues.

    Here is the Oracle trace 6 output:

    DBI::db=HASH(0x208b10) trace level set to 6 in DBI 1.18-nothread
    Note: perl is running without the recommended perl -w option
    -> $DBI::errstr (&) FETCH from lasth=DBI::db=HASH(0x208b10)
    prepare failed : ORA-01062: unable to allocate memory for define buffer
    (DBD ERROR: OCIDefineByPos)
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x208b10)~INNER)
    Issuing rollback() for database handle being DESTROY'd without explicit
    disconnect() during global destruction.
    OCITransRollback(22d0e4,22d30c,0)=SUCCESS
    OCISessionEnd(22d0e4,22d30c,239724,0)=SUCCESS
    OCIServerDetach(22d150,22d30c,0)=SUCCESS
    OCIHandleFree(239724,9)=SUCCESS
    OCIHandleFree(22d150,8)=SUCCESS
    OCIHandleFree(22d0e4,3)=SUCCESS
    OCIHandleFree(22d30c,2)=SUCCESS
    <- DESTROY= undef during global destruction.


    Any help regarding this is highly appreciated.

    Thanks,
    -Jibo


Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedNov 13, '02 at 11:35p
activeNov 14, '02 at 2:46p
posts6
users5
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase