view release on metacpan or  search on metacpan

README.mkdn  view on Meta::CPAN

        SELECT t1.lobcol,
                 a2.lobcol
          FROM t1,
               t2.lobcol@dbs2 a2 W
         WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);

    as does:

           SELECT t1.lobcol
             FROM t1@dbs1
        UNION ALL
           SELECT t2.lobcol
             FROM t2@dbs2;

- DDL commands are not supported;

    so the following returns an error:

        CREATE VIEW v AS SELECT lob_col FROM tab@dbs;

- Only binds and defines for data going into remote persistent LOBs are supported.

    so that parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed .

    These statements all produce errors:

        SELECT foo() FROM table1@dbs2;

        SELECT foo()@dbs INTO char_val FROM DUAL;

        SELECT XMLType().getclobval FROM table1@dbs2;

- If the remote object is a view such as

        CREATE VIEW v AS SELECT foo() FROM ...

    the following would not work:

        SELECT * FROM v@dbs2;

- Limited PL/SQL parameter passing

    PL/SQL parameter passing is not allowed where the actual argument is a LOB type
    and the remote argument is one of VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.

- RETURNING INTO does not support implicit conversions between CHAR and CLOB.

    so the following returns an error:

        SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test

## Locator Data Interface

### Simple Usage

When fetching LOBs with this interface a 'LOB Locator' is created then used to get the lob with the LongReadLen and LongTruncOk attributes.
The value for 'LongReadLen' is dependent on the version and settings of the Oracle DB you are using. In theory it ranges from 8GBs
in 9iR1 up to 128 terabytes with 11g but you will also be limited by the physical memory of your PERL instance.

When inserting or updating LOBs some _major_ magic has to be performed
behind the scenes to make it transparent.  Basically the driver has to
insert a 'LOB Locator' and then refetch the newly inserted LOB
Locator before being able to write the data into it.  However, it works
well most of the time, 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.

To insert or update a large LOB using a placeholder, 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 use the corresponding integer values (112 and 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' });

There are some limitations inherent in the way DBD::Oracle makes typical
LOB operations simple by hiding the LOB Locator processing:

    - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
    - To INSERT a LOB, you need UPDATE privilege.

The alternative is to disable the automatic LOB Locator processing.
If ["ora\_auto\_lob"](#ora_auto_lob) is 0 in prepare(), you can fetch the LOB Locators and
do all the work yourself using the ora\_lob\_\*() methods.
See the ["Data Interface for LOB Locators"](#data-interface-for-lob-locators) section below.

### LOB support in PL/SQL

LOB Locators can be passed to PL/SQL calls by binding them to placeholders
with the proper `ora_type`.  If ["ora\_auto\_lob"](#ora_auto_lob) is true, output LOB
parameters will be automatically returned as strings.

If the Oracle driver has support for temporary LOBs (Oracle 9i and higher),
strings can be bound to input LOB placeholders and will be automatically
converted to LOBs.

Example:
     # Build a large XML document, bind it as a CLOB,
     # extract elements through PL/SQL and return as a CLOB

     # $dbh is a connected database handle
     # output will be large

     local $dbh->{LongReadLen} = 1_000_000;

     my $in_clob = "<document>\n";
     $in_clob .= "  <value>$_</value>\n" for 1 .. 10_000;
     $in_clob .= "</document>\n";

     my $out_clob;