Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Handling BLOB data in ORACLE using perl script

by bhushanQA (Sexton)
on Sep 23, 2015 at 13:21 UTC ( #1142803=perlquestion: print w/replies, xml ) Need Help??

bhushanQA has asked for the wisdom of the Perl Monks concerning the following question:

I have written below script to retrieve BLOB data ( which has XML format) from oracle 11g, IF I run the Select statement in the query builder in some tool (i.e. ORACLE SQL Developer) it is giving me the BLOB values which is XML. Now I want to write a perl script which will invoke the BLOB as XML and print it on my screen.
#!/usr/bin/perl use strict; use warnings; use DBI; use DBD::Oracle; my $dbh = DBI->connect('dbi:Oracle:host=<IP ADRESS>;sid=11G;port=1521' +,'username', 'password', { RaiseError => 1, AutoCommit => 0 }) or die + "can not connect to the DB"; my $sth = $dbh->prepare(qq{select xmltype(SERVICE_SPECIFIC_DATA_V,871) + from CONNECTION_ACCOUNTS where account_id_n = 1462}); $sth->execute(); while ( my ($SERVICE_SPECIFIC_DATA_V) = $sth->fetchrow() ) { print STDOUT "BLOB is: $SERVICE_SPECIFIC_DATA_V\n"; } $sth->finish(); $dbh->disconnect;
Output of this program is :
BLOB is :ARRAY(0xdd705a0)
How Can I print the SERVICE_SPECIFIC_DATA_V xml values. or is there any alternative way where I can do it easily. Also, If I want to Insert or update this BLOB column which has XML, need Monks valuable suggestion on this. Thanks.

Replies are listed 'Best First'.
Re: Handling BLOB data in ORACLE using perl script
by choroba (Archbishop) on Sep 23, 2015 at 13:53 UTC
    What do you get if you dereference the array reference?
    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
Re: Handling BLOB data in ORACLE using perl script
by BravoTwoZero (Scribe) on Sep 23, 2015 at 13:56 UTC

    Long time since I tried answering a PM question, so apologies for the likely off-baseness of it. But I was drawn to it because I sense I'll be needing to do the same thing.

    Have you tried dereferencing the returned value?

    I've always been terrible at this, but the snippet from helped me in the past:

    #!/usr/bin/perl use strict; use warnings; my $array_ref = ['apple', 'banana', 'orange']; my @array = @$array_ref; print "Reference: $array_ref\n"; print "Dereferenced: @array\n";

    Listen. Strange women lying in ponds distributing swords is no basis for a system of government. Supreme executive power derives from a mandate from the masses, not from some farcical aquatic ceremony. You can't expect to wield supreme executive power just 'cause some watery tart threw a sword at you! I mean, if I went 'round saying I was an emperor just because some moistened bint had lobbed a scimitar at me, they'd put me away!
Re: Handling BLOB data in ORACLE using perl script
by CountZero (Bishop) on Sep 24, 2015 at 05:41 UTC
    What version of DBI are you using?

    The documentation of recent versions does not have a fetchrow method anymore; but rather fetchrow_arrayref (returns an array-ref), fetchrow_array (returns an array) or fetchrow_hashref (returns a hash-ref).

    If you replace your fetchrow call by fetchrow_array and assign the result to an array, you will find the data directly in the array and you do not need to dereference it.

    Probably the old fetchrow is now silently aliased to fetchrow_arrayref.


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
Re: Handling BLOB data in ORACLE using perl script
by fishy (Friar) on Sep 24, 2015 at 19:23 UTC
Re: Handling BLOB data in ORACLE using perl script
by KurtSchwind (Chaplain) on Sep 24, 2015 at 19:40 UTC

    fetchrow() returns a row (an array). You probably want $SERVICE_SPECIFIC_DATA_V[0] since you are only returning one field in your select.

    “For the Present is the point at which time touches eternity.” - CS Lewis
Re: Handling BLOB data in ORACLE using perl script
by ramachandrajr (Novice) on Sep 24, 2015 at 05:37 UTC
    well i am guessing here,that may be the fetchrow_array() tells you something that what you fetch is a row not a variable
Re: Handling BLOB data in ORACLE using perl script
by sundialsvc4 (Abbot) on Sep 23, 2015 at 14:08 UTC

    Start by using Data::Dumper to print what the $SERVICE_SPECIFIC_DATA_V variable actually contains.   You want to know, first of all, if this is, in fact, the XML data that you seek.   (A bit odd that it is an array, but let’s first find out what’s in it.   If it turns out to be XML, you’re in gravy.)

    When you say, “invoke the BLOB as XML,” I really can’t anticipate what you mean by the word, “invoke.”   Nor, when you say “print it on my screen,” exactly what you have in mind.   Please clarify ...

    XSLT is a standard technique for extracting data from XML and formatting it ... without programming.   XML::LibXML is a full-featured Perl package which uses an industry-standard library to do “everything XML.”   (There’s a very good chance that the library which it uses, libxml2, is the same one that was used to prepare the document you now have.)   Note that there are tools, commercial and otherwise, which can handle the entire task of getting XML from a database or other repository and formatting it to your taste.   (Online documentation, and all of the O’Reily books ... the ones with animals on the cover ... is prepared in just that way.)

    The same library provides an excellent way for retrieving data from an XML document:   XPath expressions.   (This is a key technology in XSLT, too.)   You do not have to write a Perl program whose logic follows that of the XML document structure, in order to efficiently retrieve anything-you-want from that document.

    (Now, if the XML document is so damned humongous that you can’t process it all at one time, there are other Perl packages that are specifically designed for that edge-case.   But I doubt that this is the issue here.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1142803]
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2021-06-22 01:07 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (100 votes). Check out past polls.