Frequently Asked Questions (FAQs): LONG to LOB Migration

Moving From LOBs Back to LONGs

Question

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?

Answer

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 Needed?

Question

Is CREATE VIEW still needed when migrating from LONGs to LOBs?

Answer

No, you no longer need to use CREATE VIEW. Use the ALTER TABLE statement.

Are OCI LOB Routines Obsolete?

Question

How does 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, OCILobRead(),OCILobWrite(),...?

Answer

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.

PL/SQL Issues

Question

Does a fetch of a LOB column (with size > 32K) into a PL/SQL CHAR/RAW/LONG/LONG RAW buffer raise an exception?

Answer

In 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.

Retrieving an Entire Image if Less Than 32K

Question

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?

Answer

Yes.

Triggers in LONGs and LOBs

Question

In Triggers, some functionality is supported for LONGs that is not supported for LOBs. Will this cause a problem?

Answer

There are a couple of limitations on how LOBs work with triggers. See "LONG-to-LOB Migration Limitations" .