Serializing Storable.pm objects via DBI to Oracle 10g

--=-FOMINBQSf2Sqr2fROFOh
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Howdy,

I seem to have encountered an odd quirk somewhere when using DBI against
an Oracle 10g database. Here's the situation. I'm taking the following
object, serializing using Storable.pm, and inserting it into a CLOB
field in an Oracle 10g database running Solaris 9/sparc.

$VAR1 =3D 'obj';
$VAR2 =3D [
{
'security' =3D> undef,
'data' =3D> {
'tts_prop' =3D> '0',
'tts_enable' =3D> '1',
'tmpl_id' =3D> '',
'channel' =3D> '',
'_radio' =3D> 'tts_enable dis_amd redir',
'_select' =3D> 'mg_retries redir_retries redir_dela=
y',
'prog_id' =3D> '',
'slot_num' =3D> '1',
'slot_text' =3D> 'fklgjdflgkjdfl;kdjfglkjpwerwepmcv=
opwentweoinfoivnorinveribfdgynvlaiytlawygn cvgbtv iauefvgjdgfirugtv rilufn =
vughk vgru
gyfireuvgr'
},
'ses_id' =3D> '3e350107dfee1b015a66efce8bf835869372c316',
'cls_id' =3D> 'addprogram19'
}
];

Please make a note that slot_text is 128 bytes. This is very important.

Now, when I pull the "frozen" object from 10g it looks something like
this. Please note that this output was run through the strings(1)
before I cut-n-pasted it:

[
'addprogram19',
'2005/10/17 09:16:02',
'
tts_prop
tts_enable
tmpl_id
channel
tts_enable dis_amd redir
_radio
$mg_retries redir_retries redir_delay
_select
prog_id
slot_num
fklgjdflgkjdfl;kdjfglkjpwerwepmcvopwentweoinfoivnorinveribfd gynvlaiytlawygn=
cvgbtv iauefvgjdgfirugtv rilufn vughk vgrugyfireuvgr
slot_text',
'2005/10/17 09:16:02',
'3e350107dfee1b015a66efce8bf835869372c316',
undef,
'1'
],

When we attempt to unfreeze it using Storable, our perl program bombs
out spectacularly with an "Out of Memory" error.

Some interesting facts about this particular bug:

* This only happens when talking with Oracle 10g. This has worked
successfully with Oracle 8i running on both SPARC and intel
hardware.
* The application server that runs this code is using the Oracle
8i client software under Solaris 9 intel.
* Everything works fine under Oracle 10g when slot_text is LESS
THAN 128 bytes. Anything larger than that bombs out. I find
this suspect.
* Running our application code on a Solaris 8 sparc box using the
Oracle 8i client libraries produces the same error.
* This error is also reproducible on a Red Hat 9 Linux box running
on intel hardware.
* We have tried this with recent versions of DBI, DBD::Oracle
(1.15), and Storable.pm

I *suspect* the issue is in how Storable is putting the data into
Oracle, but I wanted to see if anyone has run into this error before.

Cheers!

--=20
Chris Fuhrman | Twenty First Century Communications
chris.fuhrman@tfcci.com | Senior Software Engineer
(W) 614-442-1215 x271 |
(F) 614-442-5662 | PGP/GPG Public Key Available on Request


--=-FOMINBQSf2Sqr2fROFOh
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQBDV8EqWfdjRwMfjNcRAs0NAKCd4Q8voNKqhDHyBbftxKe26ShE9gCe OV44
BZFOcwU6h6iXfRvBYgnqdqQ=
=IWn/
-----END PGP SIGNATURE-----

--=-FOMINBQSf2Sqr2fROFOh--

RE: Serializing Storable.pm objects via DBI to Oracle 10g

I haven't tried this on 10g, but what if you gzip your frozen object and
store it in a BLOB rather than a CLOB. Here's some code I use that works
fine in 9.2.0.6 ...

my $config_data = Compress::Zlib::memGzip(freeze($config));
my $table_data = Compress::Zlib::memGzip(freeze($Table));
my $sth_info_ins = $PLX->dbh->prepare(
"INSERT INTO $info_table (config_data, table_data)"
. " VALUES (:cdata, :tdata)"
);
$sth_info_ins->bind_param(
':cdata', $config_data,
{
ora_type => ORA_BLOB,
ora_field => 'config_data'
});
$sth_info_ins->bind_param(
':tdata', $table_data,
{
ora_type => ORA_BLOB,
ora_field => 'table_data'
});
$sth_info_ins->execute;

Steve

-----Original Message-----
From: Chris Fuhrman [mailto:chris.fuhrman@tfcci.com]
Sent: Friday, 21 October 2005 2:09 AM
To: dbi-users@perl.org
Cc: Jesse Throwe; Joseph Zarick; Carey Hall
Subject: Serializing Storable.pm objects via DBI to Oracle 10g

Howdy,

I seem to have encountered an odd quirk somewhere when using DBI against
an Oracle 10g database. Here's the situation. I'm taking the following
object, serializing using Storable.pm, and inserting it into a CLOB
field in an Oracle 10g database running Solaris 9/sparc.

$VAR1 = 'obj';
$VAR2 = [
{
'security' => undef,
'data' => {
'tts_prop' => '0',
'tts_enable' => '1',
'tmpl_id' => '',
'channel' => '',
'_radio' => 'tts_enable dis_amd redir',
'_select' => 'mg_retries redir_retries redir_delay',
'prog_id' => '',
'slot_num' => '1',
'slot_text' =>
'fklgjdflgkjdfl;kdjfglkjpwerwepmcvopwentweoinfoivnorinveribf dgynvlaiytlawygn
cvgbtv iauefvgjdgfirugtv rilufn vughk vgru
gyfireuvgr'
},
'ses_id' => '3e350107dfee1b015a66efce8bf835869372c316',
'cls_id' => 'addprogram19'
}
];

Please make a note that slot_text is 128 bytes. This is very important.

Now, when I pull the "frozen" object from 10g it looks something like
this. Please note that this output was run through the strings(1)
before I cut-n-pasted it:

[
'addprogram19',
'2005/10/17 09:16:02',
'
tts_prop
tts_enable
tmpl_id
channel
tts_enable dis_amd redir
_radio
$mg_retries redir_retries redir_delay
_select
prog_id
slot_num
fklgjdflgkjdfl;kdjfglkjpwerwepmcvopwentweoinfoivnorinveribfd gynvlaiytlawygn
cvgbtv iauefvgjdgfirugtv rilufn vughk vgrugyfireuvgr
slot_text',
'2005/10/17 09:16:02',
'3e350107dfee1b015a66efce8bf835869372c316',
undef,
'1'
],

When we attempt to unfreeze it using Storable, our perl program bombs
out spectacularly with an "Out of Memory" error.

Some interesting facts about this particular bug:

* This only happens when talking with Oracle 10g. This has worked
successfully with Oracle 8i running on both SPARC and intel
hardware.
* The application server that runs this code is using the Oracle
8i client software under Solaris 9 intel.
* Everything works fine under Oracle 10g when slot_text is LESS
THAN 128 bytes. Anything larger than that bombs out. I find
this suspect.
* Running our application code on a Solaris 8 sparc box using the
Oracle 8i client libraries produces the same error.
* This error is also reproducible on a Red Hat 9 Linux box running
on intel hardware.
* We have tried this with recent versions of DBI, DBD::Oracle
(1.15), and Storable.pm

I *suspect* the issue is in how Storable is putting the data into
Oracle, but I wanted to see if anyone has run into this error before.

Cheers!

--
Chris Fuhrman | Twenty First Century Communications
chris.fuhrman@tfcci.com | Senior Software Engineer
(W) 614-442-1215 x271 |
(F) 614-442-5662 | PGP/GPG Public Key Available on Request

RE: Serializing Storable.pm objects via DBI to Oracle 10g

--=-auxI6TSiq2OnAKJRSHUe
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Not sure this would work but I can give it a shot.

Reason I say this is that I tried to take the "frozen" storable object
and then MIME::Base64 encode it before putting it in the data-base.
Still got the same error so I'm now starting to suspect it's something
going on between Storable.pm and Oracle 10g.

On Fri, 2005-10-21 at 06:38 +1000, Steve Baldwin wrote:
> I haven't tried this on 10g, but what if you gzip your frozen object and
> store it in a BLOB rather than a CLOB. Here's some code I use that works
> fine in 9.2.0.6 ...
>=20
> my $config_data =3D Compress::Zlib::memGzip(freeze($config));
> my $table_data =3D Compress::Zlib::memGzip(freeze($Table));
> my $sth_info_ins =3D $PLX->dbh->prepare(
> "INSERT INTO $info_table (config_data, table_data)"
> . " VALUES (:cdata, :tdata)"
> );
> $sth_info_ins->bind_param(
> ':cdata', $config_data,
> {
> ora_type =3D> ORA_BLOB,
> ora_field =3D> 'config_data'
> });
> $sth_info_ins->bind_param(
> ':tdata', $table_data,
> {
> ora_type =3D> ORA_BLOB,
> ora_field =3D> 'table_data'
> });
> $sth_info_ins->execute;
>=20
> Steve
>=20

--=20
Chris Fuhrman | Twenty First Century Communications
chris.fuhrman@tfcci.com | Senior Software Engineer
(W) 614-442-1215 x271 |
(F) 614-442-5662 | PGP/GPG Public Key Available on Request


--=-auxI6TSiq2OnAKJRSHUe
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQBDWOlSWfdjRwMfjNcRAiPkAJ0U+XQTgNYb73aJSokGnNYtqwpsRwCg 0dVH
Vgx2Tf+GfCHRhVSqQ+TwQDE=
=upXz
-----END PGP SIGNATURE-----

--=-auxI6TSiq2OnAKJRSHUe--