copy long raw

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Tue, 31 May 2005 3:07 am

I have a table q has the long raw field, and within that field this stored pictures, but I have another similar table q também have a long raw field, what I need to do is pass the q figures are stored in table 1, table 2, but am having they're difficult to give you with the long raw fields, anyone know how can I fix this??
Location: Dongguan - Guangdong - China

Poston Tue, 31 May 2005 9:22 am

do an INSERT.
Code: Select all
INSERT INTO tab1 (campo_long) (select campo_long from tabela2)
here's a link viewtopic.php?t=154
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Mon, 06 Mar 2006 8:43 am

Dear Dr_Gori, yes indeed it works, but only for fields, not for long raw, Blob. I'm having the same difficulty of friend, backup long raw field, but couldn't find a solution yet. Thanks for the tip, but unfortunately it does not work.
Location: Maceió/AL

[Gerson Júnior]

Poston Mon, 06 Mar 2006 9:04 am

In fact, if you are using the Bank 8 or higher, you shouldn't be using LONG RAW. It still exists only for backward compatibility. You should be using a BLOB! (hence works the way that was nominated)

To copy a table to another a LONG RAW field only with pl/sql, is a little tricky: * make a table export.
* Rename the table to the desired name using the RENAME command * make the import you newly created ...

See what Tom Kyte talks about it: ... 8223421081 this cursor can be used if the long raw has up to 32 k
Code: Select all
CURSOR MyCursor IS SELECT Field1, Field2, RawField FROM Table1;
vField1 integer;
vField2 varchar2(50);                                                           

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

OPEN MyCursor;

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

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

CLOSE MyCursor; 
this routine is made in JAVA, but I think it's not 100% tested.
Code: Select all
ops$tkyte@ORA9IR2> create or replace and compile java source named "long_raw" as
  2  import*;
  3  import java.lang.*;
  4  import java.sql.*;
  5  import java.util.*;
  6  import oracle.jdbc.driver.*;
  7  import java.math.*;
  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 =
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();
24                  if ( )
25          {
26              InputStream is = rset.getBinaryStream(1);
27                  byte buffer[] = new byte[1024];
28              while ((length = (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> 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
  4  /

Function created.

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> 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> 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$;


Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

  • See also
    Last Post

    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 3 guests