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??
schnu
Location: Dongguan - Guangdong - China

Poston Tue, 31 May 2005 9:22 am

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

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

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.
gerson.vasconcelos
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: http://asktom.oracle.com/pls/ask/f?p=49 ... 8223421081 this cursor can be used if the long raw has up to 32 k
Code: Select all
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;
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 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





dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: Google [Bot] and 5 guests