long raw的copy

该类型的限制条件有(不能在where子句中使用， 不能create table as select ,不能insert into select , 不能在plsql中使用  etc)，新应该不应考虑使用，而应该代之以blob或clob.
对于已有的long raw类型，可以参考以下方式：
1.COPY命令
  表结构：

CREATE TABLE  APP_FILES
(
    FILE_NO                        VARCHAR2(4) NOT NULL,
    FILE_DESC                      VARCHAR2(40),
    FILE_NAME                      VARCHAR2(40),
    FILE_CON                       LONG RAW,
    FILE_TIME                      DATE,
    CONSTRAINT PK_APP_FILES PRIMARY KEY (FILE_NO) USING INDEX

）

想在另一个用户下，也建立一个一样的表，把数据拷贝过去，发现通过SQL不行；

 

只能使用sqlplus：

  COPY FROM  OKWMS/OKWMS@139 TO FSDQ/FSDQ@139 CREATE   APP_FILES  USING  SELECT * FROM APP_FILES;

  或者：

  COPY FROM  OKWMS/OKWMS@139 TO FSDQ/FSDQ@139 APPEND APP_FILES  USING  SELECT * FROM APP_FILES;

  或者：

  COPY FROM  OKWMS/OKWMS@139 TO FSDQ/FSDQ@139 INSERT APP_FILES  USING  SELECT * FROM APP_FILES;

2.java
for anything >32k that is LONG RAW, you cannot use plsql.  This approach might work (to get the
long raw length).  You can call this from sql or from plsql and would call it before reading out
the long raw (to see that you CAN in fact read it out).

But you can use the same technique to read and write a long raw in java as well if you *really* need to copy it.

SQL> create or replace and compile java source named "long_raw" as
    import java.io.*;
    import java.lang.*;
    import java.sql.*;
    import java.util.*;
    import oracle.jdbc.driver.*;
    import java.math.*;
 
    public class long_raw
   {
       public static java.math.BigDecimal getlength(java.lang.String p_query, oracle.sql.ARRAY p_binds) throws Exception
      {
                   int length;
                   int tot_length = 0;
                   Connection conn = DriverManager.getConnection("jdbc:default:connection:");
                   PreparedStatement pstmt = conn.prepareStatement(p_query);
                   String[] binds = (String[])p_binds.getArray();

                   for( int i = 0; i < p_binds.length(); i++ )
                   {
                           pstmt.setString( i+1, binds[i] );
                   }

                   ResultSet rset = pstmt.executeQuery();
 
                   if ( rset.next() )
                   {
                       InputStream is = rset.getBinaryStream(1);
                       byte buffer[] = new byte[1024];
                       while ((length = is.read (buffer,0,1024)) != -1)
                      {
                             tot_length += length;
                       }

                       is.close();
                  }

                   rset.close();
                   pstmt.close();
                   return new java.math.BigDecimal( tot_length );
           }
     }
   /
 
Java created.
 
SQL> show error
No errors.
SQL>
SQL >CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
/

SQL> create or replace function long_raw_length( p_query in varchar2, p_binds in strArray ) return number
    as language java
    name 'long_raw.getlength( java.lang.String, oracle.sql.ARRAY ) return java.math.BigDecimal';
    /
 
Function created.
 
SQL>
SQL> drop table t;
 
Table dropped.
 
SQL> create table t ( x int, y int, z long raw );
 
Table created.
 
SQL> insert into t values ( 1, 2, '0a0a0a0a' );
 
1 row created.
 
SQL> select x, y, long_raw_length('select z from t where x = ? and y = ?', strArray(x,y) ) lrl  from t;
 
         X          Y        LRL
---------- ---------- ----------
         1          2          4
 
SQL>
ops$tkyte@ORA9IR2> select max( long_raw_length('select piece from sys.idl_ub1$ where obj#=to_number(?) and part=to_number(?)   and  version=to_number(?) and piece# = to_number(?)', strArray(obj#,part,version,piece#) ) ) from sys.idl_ub1$;
 

MAX(LONG_RAW_LENGTH('SELECTPIECEFROMSYS.IDL_UB1$WHEREOBJ#=TO_NUMBER(?)ANDPART=T
-------------------------------------------------------------------------------
                                                                          64512

                                        表12.-2 LONG 类型与LOB 类型的比较
LONG/LONG RAW 类型                                                                    CLOB/BLOB 类型
每个表中只能有一个LONG 或LONG RAW 列                                    每个表可以有最多1000 个CLOB 或BLOB 类型的列
定义用户定义的类型时，不能有LONG/LONGRAW 类型的属性                     用户定义的类型完成可以使用CLOB 和BLOB 类型
不能在WHERE 子句中引用LONG 类型                                          WHERE 子句中可以引用LOB 类型，而且DBMS_LOB 包
                                                                         中提供了大量函数来处理LOB 类型
除了NOT NULL 之外，完整性约束中不能引用LONG 类型                         完整性约束中可以引用LOB 类型
LONG 类型不支持分布式事务                                                LOB 确实支持分布式事务
LONG 类型不能使用基本或高级复制技术来复制                                LOB 完全支持复制
LONG 列不能在GROUP BY、ORDER BY 或CONNECT BY 子句中引用，也不能在使用    只要对LOB 应用一个函数，将其转换为一个标量SQL 类型，
了DISTINCT、UNIQUE、INTERSECT、MINUS 这些子句中                          如VARCHAR2、NUMBER 或DATE，LOB 就可以出现在
或UNION 的查询中使用
PL/SQL 函数/过程不能接受LONG 类型的输入                                   PL/SQL 可以充分处理LOB 类型
SQL 内置函数不能应用于LONG 列（如SUBSTR）                                 SQL 函数可以应用于LOB 类型
CREATE TABLE AS SELECT 语句中不能使用LONG 类型                            LOB 支持CREATE TABLE AS SELECT
在包含LONG 类型的表上不能使用ALTER TABLE MOVE                             可以移动包含LOB 的表

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20648244/viewspace-667718/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20648244/viewspace-667718/