FAQ: LONGS and LONG RAW Columns

Checked for relevance on 11-Jan-2008

Frequently Asked Questions on Longs and and Long Raw Columns 
============================================================ 

 1. How do you get the length of a LONG RAW column? 

    Check the utl_raw package.  It contains some useful functions to 
    manipulate raw columns.  This package is not created automatically. 
    It resides in the "ORACLE_HOME/rdbms/admin/utlraw.sql" file. 

    Note 98797.1 HOW TO DETERMINE THE SIZE OF A LONG RAW COLUMN

 2. How do you get the length of a LONG RAW column without selecting it? 

    There is currently no way to determine the length of a long or long  
    raw column without actually fetching it.  When you insert the length, 
    one workaround to determine the length is to manually store the length  
    in another database column. 

    There is an enhancement request in review (230827).  If the column 
    is < 64K, you can get the length by fetching using an indicator.  If  
    the column is > 64K, you are out of luck unless you store the length 
    explicitly in another column of the table (which is recommended). 

 3. How do you move a LONG to varchar2? 

    It is not possible to change a long to a varchar useing the ALTER TABLE 
    command.  You could create a new table with the varchar column, and 
    select the long into a PL/SQL variable, inserting that variable into 
    the varchar column in the new table. 

    Examples: 
    ========= 

    (I) This example assumes the LONG column <=2000 characters: 
        ------------------------------------------------------- 
         create or replace procedure 12vc is 
         cursor c0 is 
           select rowid, long_column 
           from table_name;
         c1 number;
         begin 
         for c1 in c0 loop 
           update table_name 
           set varchar_column = c1.long_column 
           where rowid = c1.rowid; 
         end loop; 
         end; 

    (II) Another example that also performs string manipulation on the long: 
         ------------------------------------------------------------------- 

         declare
         cursor my_cursor is
             select long_col 
             from   my_table; 
          my_var varchar2(32767); 
          begin 
            open my_cursor; 
            loop 
              fetch my_cursor into my_var; 
              exit when my_cursor%notfound; 
              my_var := substr(my_var,1,2000); 
              insert into new_table values (my_var); 
          end loop; 
          close my_cursor; 
          end; 

   (III) A method that uses export and import: 
         -------------------------------------  

         a) export the table with the long column 
         b) drop the table with the long column 
         c) import with the indexfile option (this writes the create table 
            statements in the specified file) 
         d) edit the indexfile and change the column datatype from long 
            to varchar 
         e) run the file as a script to create the table with the same name 
            but the datatype being changed from long to varchar2 
         f) import the data with ignore=y 

 4. Can you insert the old value of a long column before or after an 
    update into another table (such as a history table) using a trigger? 

    No, you cannot use :NEW and :OLD with LONG and LONG RAW columns. 

 5. How do you put data into LONG and LONG RAW columns? 

    This can be done in many ways, depending upon the size of the 
    data.  It can be done using OCI, PRO*C, IMPORT, SQL*Loader,  
    PL/SQL, SQL*Plus, and the COPY command. 

    Note 96961.1    SQL*Loader: Loading Binary Files Into Oracle Using 
                      SQL*Loader
    Note 1022033.6  HOW TO USE COPY COMMAND WITH LONGS AND LONG RAW 
    Note 123009.1   Example: How to Insert/Retrieve LONG RAW Data Using JDBC
    Note 119803.1   Example: How to insert and fetch LONG RAW data in OCI7 
    Note 118807.1   Example: How to insert/select character data into/from a 
                      raw column 

 6. How can you view LONG and LONG RAW data (bit images) using a 
    Forms application? 

    Note 1009546.6  HOW TO STORE AN IMAGE FROM A FILE INTO THE DATABASE USING 
                      ORACLE FORMS? 

 7. How can you view LONG and LONG RAW data? 

    You can view LONG and LONG RAW data using OCI,PRO*C, 
    FORM4.0 and 4.5 GUI, pl/sql. 
    LONG RAW cannot be viewed using sqlplus, sqldba.

    Note 1022033.6  HOW TO USE COPY COMMAND WITH LONGS AND LONG RAW 
    Note 1007848.6  HOW TO SELECT THE FULL 2147483647 BYTES IN A LONG OR LONG 
                      RAW COLUMN IN PLSQL?
    Note 123009.1   Example: How to Insert/Retrieve LONG RAW Data Using JDBC
    Note 119803.1   Example: How to insert and fetch LONG RAW data in OCI7 
    Note 118807.1   Example: How to insert/select character data into/from a 
                      raw column 

 8. How can one look at long raw columns, or copy a table containing one? 

    Long raw data types cannot currently be selected from sqlplus or sqldba 
    (as of V7.1.6) because there is no conversion between database and 
    session character sets.  One must access the field/table via 
    OCI (eg. PRO*C). 

    Longs can be viewed in sqlplus after issueing the set 
    command  "set long {80|n} where n can be up to 2G. 

 9. What are some of the limitations on longs? 

    *pl/sql --> 32Kb for size of long 
    *sql command --> 64Kb 
    *sqlloader ---> 64Kb 
         other Limitations on longs listed in the Application Developers Guide: 
    *Only one LONG column allowed per table 
    *LONG column cannot be used in a distributed statement
    *LONG column cannot be indexed 
    *LONG columns cannot appear in integrity constraints 
    *LONG columns cannot be used in WHERE,GROUP BY, 
    *ORDER BY, or CONNECT BY clauses, or with the DISTINCT 
         operator in SELECT statements. 
    *LONG columns cannot be used referenced by SQL functions 
         (such as SUBSTR or INSTR). 
    *LONG columns cannot be used in the SELECT list of a 
         subquery or queries combined by set operators (UNION, 
         UNION ALL, INTERSECT, or MINUS). NOTE:  This restriction 
                                                 removed with 9.2.x
    *LONG columns cannot be used in SQL expressions. 
    *LONG columns cannot be referenced when creating a table 
         with query (CREATE TABLE...AS SELECT...) or when inserting 
         into a table(or view) with a query (INSERT INTO ... SELECT...). 
    *LONG columns cannot be referenced in a sub-query 
         A variable or argument of a PL/SQL program unit cannot be 
         declared using the LONG datatype. 

    Some other restrictions of longs with triggers are: 

    *A LONG or LONG RAW column can be referenced in a SQL 
       statement within a trigger if the data from a LONG 
       or LONG RAW column can be converted to a 
       constrained datatype (such as CHAR and VARCHAR2). 
    *Variable cannot be declared using the LONG or LONG RAW 
       datatypes. 

10. What are some tips/hints on using, storing long raws? 

    There are a number of generic long column recommendations: 
    1. Store long columns in their own table.  Don't store them with 
       rest of the row 
    2. Store rows with long columns smaller than the blocksize 
       together in their own table. 
    3. Store rows with long columns greater than the blocksize in 
       their own table. 
       This makes the tables more manageable.  It also will help when 
       dealing with space management issues. 
       Whenever a row contatining a long column is inserted and the long 
       column is larger than the blocksize then the long row is chained 
       and the long column is put in an empty block.  The block has to 
       be completely empty or it is not used.  We will look at 5 blocks 
       in the free list.  If one of them is not completely empty then 
       we push the highwater mark up.  In some cases this will cause a 
       new extent to be allocated. 

    4. It is a good idea to create a separate column in the table 
       that will contain the length of the long raw, and it can be 
       updated as the long raw is updated. 

11. How can import time on LONG and LONG RAW data be improved? 

    The only way to improve the import performance is to set COMMIT=N. 
    In this case, the transaction will only be committed after the whole  
    table has been imported. This will cause some growth of the rollback 
    segment and should be accounted for when specifying the rollback 
    segment size.

12. Can longs be replicated? 

    The answer is no if using Oracle's Symmetric Replication or snapshots. 

       Note 22513.1  Replication of LONGS

13. I have a ct who wants to copy a long raw from one Oracle instance to 
    another Oracle instance. We unsuccessfully tried: 
      1. insert into local_table 
         select long_raw from remote table; 
      2. create local_table as 
        select * from remote_table; (remote table had long raw column) 
      3. sqlplus copy command. 

    You must use export / import to move a table from one instance to
    another.

14. How can one move table to another where the first table has long fields? 
    Use the sqlplus 'COPY' command. The COPY command cannot be used for
    long raws.

    Note 1022033.6  HOW TO USE COPY COMMAND WITH LONGS AND LONG RAW 

15. Can a view with a long raw column be created? 

    No, because LONG RAW data cannot be selected from sqlplus 
    because Oracle tries to convert it. 
    May want to create a smaller view without the long raw 
    column and then joined it through ACCESS via an odbc driver. 

16. Why does the following query give ORA-997? 
    select distinct(col1), col2 from table1; (col2 is a long datatype) 
    See limitations above. Long columns cannot appear in 
    where, group by, order by, or connect by clauses 
    or with the DISTINCT operator in the select statement.

17. How can a long or long raw be converted to a blob or clob?

    Note 1071540.6  CAN A LONG RAW DATATYPE BE CONVERTED TO A CLOB DATATYPE IN 
                      ORACLE 8.1.X?
    Note 1012454.7  How to Convert from Long Raw to BLOB using PL/SQL

Gửi phản hồi

Please log in using one of these methods to post your comment:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s