CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

Home: Perl Programming Help: Intermediate:
longvarchar with DBI



Feb 13, 2001, 7:25 PM

Post #1 of 3 (136 views)
longvarchar with DBI Can't Post

I am using SQL to select a longvarchar (an Oracle datatype) which I then print; nothing is ever returned. I have had no trouble retrieving and printing other datatypes, such as varchars. Is perl's Oracle DBI incapable of handling longvarchar?

Here's my code:

my $sth = $dbh->prepare("SELECT article_pages.body_lg
FROM articles, article_pages, sections
WHERE articles.section_fk = sections.section_pk
AND sections.name_vc = 'stories'
AND articles.article_pk = article_pages.article_fk
AND articles.date_archived_dt IS NULL
AND articles.date_online_dt <= '$currentdate'
AND articles.article_pk = 61
ORDER BY articles.date_online_dt DESC,
article_pages.page_number_nb ASC")

or die "Couldn't prepare statement: " . $dbh->errstr;

or die "Couldn't execute statement: " . $sth->errstr;

while (@data = $sth->fetchrow_array()){
print @data;


Feb 14, 2001, 1:25 AM

Post #2 of 3 (134 views)
Re: longvarchar with DBI [In reply to] Can't Post

From the DBD::Oracle documentation:

Handling LOBs
When fetching LOBs, they are treated just like LONGs and are subject to $sth->{LongReadLen} and $sth->{LongTruncOk}. Note that with OCI 7 DBD::Oracle pre-allocates the whole buffer (LongReadLen) before constructing the returned column. With OCI 8 it grows the buffer to the amount needed for the largest LOB to be fetched so far.

When inserting or updating LOBs some major magic has to be performed behind the scenes to make it transparent. Basically the driver has to refetch the newly inserted 'LOB Locators' before being able to write to them. However, it works, and I've made it as fast as possible, just one extra server-round-trip per insert or update after the first. For the time being, only single-row LOB updates are supported. Also passing LOBS to PL/SQL blocks doesn't work.

To insert or update a large LOB, DBD::Oracle has to know in advance that it is a LOB type. So you need to say:

  $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });

The ORA_CLOB and ORA_BLOB constants can be imported using

  use DBD::Oracle qw(:ora_types);

or just use the corresponding integer values (112 and 113).

To make scripts work with both Oracle7 and Oracle8, the Oracle7 DBD::Oracle will treat the LOB ora_types as LONGs without error. So in any code you may have now that looks like

  $sth->bind_param($idx, $value, { ora_type => 8 });

you could change the 8 (LONG type) to ORA_CLOB or ORA_BLOB (112 or 113).

One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has to be able to tell which parameters relate to which table fields. In all cases where it can possibly work it out for itself, it does, however, if there are multiple LOB fields of the same type in the table then you need to tell it which field each LOB param relates to:

  $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });

Hope this helps!


Feb 14, 2001, 9:47 AM

Post #3 of 3 (128 views)
Re: longvarchar with DBI [In reply to] Can't Post

thank you, Jasmine; I was mistaken, though, in calling longvarchar an Oracle datatype-- the Oracle database actually uses a type Long (this means that I can bypass the discussion about LOB's, which is a newer datatype, I believe). What documentation I've been able to get my
hands on tells me that "When LONG database fields hold values larger than a (perl) variable allows, Oracle provides functions for retrieving the LONG database value in smaller pieces." However, it neither tells me what these functions are nor where I can find out more about them.

Now since this is a Perl forum and not an Oracle forum I don't expect to find out about them here; I was hoping, however, to know if there is a perl work-around of some kind that would let me handle type Long without invoking the above-mentioned Oracle functions. Any ideas?



Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives