glufke.net

Selecionar tudo

INSERT INTO tab1 (campo_long) (select campo_long from tabela2)

Selecionar tudo

DECLARE
CURSOR MyCursor IS SELECT Field1, Field2, RawField FROM Table1; 
vField1 integer;
vField2 varchar2(50);                                                            

  
vRawField long raw;
iCount integer;
BEGIN
iCount := 0;

OPEN MyCursor;

LOOP
FETCH MyCursor INTO vField1, vField2, vRawField; 
EXIT WHEN MyCursor%NOTFOUND;
iCount := iCount + 1;   
INSERT INTO Table2 (T2Field1, T2Field2, T2RawField) 
VALUES (vField1, vField2, vRawField); 

--Commit every 10 rows 
IF iCount = 10 THEN             
iCount := 0; 
COMMIT;
END IF;

END LOOP; 
COMMIT;   
CLOSE MyCursor;  
END;

Selecionar tudo

ops$tkyte@ORA9IR2> create or replace and compile java source named "long_raw" as
  2  import java.io.*;
  3  import java.lang.*;
  4  import java.sql.*;
  5  import java.util.*;
  6  import oracle.jdbc.driver.*;
  7  import java.math.*;
  8
  9  public class long_raw
 10  {
 11      public static java.math.BigDecimal getlength(java.lang.String p_query, 
oracle.sql.ARRAY p_binds) throws Exception
 12      {
 13                  int length;
 14                  int tot_length = 0;
 15                  Connection conn = 
DriverManager.getConnection("jdbc:default:connection:");
 16                  PreparedStatement pstmt = conn.prepareStatement(p_query);
 17                  String[] binds = (String[])p_binds.getArray();
 18                  for( int i = 0; i < p_binds.length(); i++ )
 19                  {
 20                          pstmt.setString( i+1, binds[i] );
 21                  }
 22          ResultSet rset = pstmt.executeQuery();
 23
 24                  if ( rset.next() )
 25          {
 26              InputStream is = rset.getBinaryStream(1);
 27                  byte buffer[] = new byte[1024];
 28              while ((length = is.read (buffer,0,1024)) != -1)
 29                                  tot_length += length;
 30              is.close();
 31          }
 32          rset.close();
 33                  pstmt.close();
 34                  return new java.math.BigDecimal( tot_length );
 35      }
 36  }
 37  /
 
Java created.
 
ops$tkyte@ORA9IR2> show error
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function long_raw_length( p_query in 
varchar2, p_binds in strArray ) return number
  2  as language java
  3  name 'long_raw.getlength( java.lang.String, oracle.sql.ARRAY ) return 
java.math.BigDecimal';
  4  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int, y int, z long raw );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 2, '0a0a0a0a' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, y, long_raw_length('select z from t where x = ? and 
y = ?', strArray(x,y) ) lrl
  2    from t;
 
         X          Y        LRL
---------- ---------- ----------
         1          2          4
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select max( long_raw_length('select piece from sys.idl_ub1$ 
where obj#=to_number(?) and part=to_number(?) and
  2  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
-------------------------------------------------------------------------------
                                                                          32767

Selecionar tudo

INSERT INTO tab1 (campo_long) (select to_lob(campo_long) from tabela2)