|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Migrating From LONGs to LOBs, 14 of 14
Once we ALTER a table to change LONG columns to LOB and consequently move all LONG data to LOBs, we cannot ALTER the column back to LONG. Is there a work around?
There is a workaround for this. You can add a LONG column and use an OCI application to read the data from the LOB column and insert it into the LONG column. Then you can drop the LOB column.
Is CREATE VIEW still needed when migrating from LONGs to LOBs?
No, you no longer need to use CREATE VIEW. Use the ALTER TABLE statement.
OCIStmtFetch() work for LOB columns? Does it return OCI_NEED_DATA as it previously did for LONG column and must data be completely fetched before the data for other columns is available? Are all OCI routines for LOBs obsolete, such as,
OCIStmtFetch() for LOBs works the same way as it did for LONGs previously if the datatype is specified as SQLT_LNG/SQLT_CHR,... in the define. If the datatype is specified as SQLT_CLOB or SQLT_BLOB, then the
OCIStmtFetch() call fetches the LOB locator and you can call
OCILobRead() to read LOB data. OCI LOB calls will not be obsoleted.
If the datatype is SQLT_LNG/SQLT_CHR,... for a LOB column, then the LOB data needs to be completely fetched before the data for other columns are available. The way SQL*PLUS can get around this problem is to continue using the existing OCI LOB interface.
Does a fetch of a LOB column (with size > 32K) into a PL/SQL CHAR/RAW/LONG/LONG RAW buffer raise an exception?
OCIDefineByPos() and PL/SQL "SELECT INTO" commands, there is no way of specifying the "amount" wanted. You only specify the buffer length. The correct amount is fetched without overflowing the buffer, no matter what the LOB size is. If the whole column is not fetched, then in OCI a truncation error is returned, and in PL/SQL, an exception is raised.
This behavior is consistent with the existing behavior for LONGs and VARCHAR2s.
I can now SELECT LOB data without first retrieving the locator. Can I now retrieve an entire image with a single SELECT in PL/SQL if the image is less than 32K?
In Triggers, some functionality is supported for LONGs that is not supported for LOBs. Will this cause a problem?
There are a couple of limitations on how LOBs work with triggers. See "LONG-to-LOB Migration Limitations" .