Objetos LOB

Hola: sabes como conseguir insertar objetos de tipo imagen (LOB) mediante procedures en bases remotas? (dblink).
Tenemos un stores que lo hace, pero en cuanto se lo vinculamos a un dblink nos da el error ORA-22804.
Muchas gracias
1

1 respuesta

1
Respuesta de
Existen ciertas restricciones con algunos tipos de datos para realizar estas acciones, no obstante te remito una nota de Oracle para intentar conseguir solventar el problema, espero que te sirva
Problem Description ------------------- You are trying to select, insert, or update a remote table that contains an object type and it fails with one of the following errors: ORA-22804 remote operations not permitted on object tables or user defined type columns PLS-453 remote operations not permitted on object tables or user defined type columns For example (where ora816.prod2.mycorp.com is a dblink created on your local database and test1 has an object type column): SQL> connect test_user/oracle SQL> insert into test1@ora816.prod2.mycorp.com values (9,'nine',(9/3)) * ERROR at line 1: ORA-22804: remote operations not permitted on object tables or user-defined type columns Solution Description -------------------- To workaround this restriction, log into the remote database and create a procedure or function that accepts the pieces of the object table (breaking the object type down into it's components) and performs the DML operation for you. Then log into your local database and perform the desired DML operation using a remote procedure call to this new procedure or function. For example: On the remote database: SQL> create type complex as object (rpart real, ipart real, member function plus (x complex) return complex, member function less (x complex) return complex, member function times (x complex) return complex, member function divby (x complex) return complex ); SQL> create type body complex as member function plus (x complex) return complex is begin return complex(rpart + x.rpart, ipart + x.ipart); end plus; member function less (x complex) return complex is begin return complex(rpart - x.rpart, ipart - x.ipart); end less; member function times (x complex) return complex is begin return complex(rpart * x.rpart - ipart * x.ipart, rpart * x.ipart + ipart * x.rpart); end times; member function divby (x complex) return complex is z real := x.rpart**2 + x.ipart**2; begin return complex ((rpart * x.rpart + ipart * x.ipart) / z, (ipart * x.rpart - rpart * x.ipart) / z); end divby; end; SQL> create table test1 (id number, name varchar(20), complex_col complex ); SQL> create function store_test1 (id_val number, name_val varchar2, ipart_val real, rpart_val real )return varchar2 as sql_status varchar2(100); complex_col_val complex; begin sql_status := 'Success'; complex_col_val := complex(ipart_val, rpart_val); insert into test_user.test1 values (id_val, name_val, complex_col_val); return sql_status; exception when others then sql_status := SQLERRM; return sql_status; end; / SQL> -- Make sure it works locally at the remote database SQL> variable sql_msg varchar2(100); SQL> execute :sql_msg := store_test1(1,'one',5,10); SQL> print :sql_msg; SQL_MSG ----------- Success SQL> commit; Now use this from your local database instead of an insert statement: SQL> variable sql_msg varchar2(100); SQL> execute :sql_msg := store_test1@ora816.prod2.mycorp.com(9,'nine',9,3); SQL> print :sql_msg; SQL_MSG ----------- Success SQL> commit;
Un saludo
Añade un comentario a esta respuesta
Añade tu respuesta
Haz clic para o
Escribe tu mensaje
¿No es la respuesta que estabas buscando? Puedes explorar otras preguntas del tema Oracle o hacer tu propia pregunta: