Menu de navegação

[Afonso RodriguesParticipante]

Bom dia,

Eu estou fazendo uma carga de campos BLOB’s, onde a origem é acessada via DB_LINK:


/* Use of a PL-SQL bloc to perform the Insert (management of LONGS and LOBS etc.) */
declare cursor myCursor is
select

C1_IMG_ID IMG_ID,
(select C2_ICON from dual) ICON,

'I' IND_UPDATE

from DESTINO.C$_0IMAGES
where (1=1)

;
begin
/* Loop over the Cursor and execute the insert statement */
for aRecord in myCursor loop
insert into DESTINO.I$_IMAGES
(
IMG_ID,
ICON,
IND_UPDATE
)
values (
aRecord.IMG_ID,
aRecord.ICON,
aRecord.IND_UPDATE
)
;
end loop;
end;


Porem, quando eu rodo o pl ele me retorna o seguinte erro:

Oracle Error: ORA-22275
Error Description:
Invalid LOB locator specified

Error Cause:
There are several causes: (1) the LOB locator was never initialized; (2) the locator is for a BFILE and the routine expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a BLOB/CLOB/NCLOB and the routine expects a BFILE locator; (4) trying to update the LOB in a trigger body -- LOBs in trigger bodies are read only; (5) the locator is for a BFILE/BLOB and the routine expects a CLOB/NCLOB locator; (6) the locator is for a CLOB/NCLOB and the routine expects a BFILE/BLOB locator;.

Action:
For (1), initialize the LOB locator by selecting into the locator variable or by setting the LOB locator to empty. For (2),(3), (5) and (6)pass the correct type of locator into the routine. For (4), remove the trigger body code that updates the LOB value.


Eu fiz o teste usando dois schemas no ORACLE XE mesmo e criando um DB_LINK para uni-los, e deu certo.

Porém , quando faço isso em produção é que ocorre o erro.
Será que isso é um BUG do Banco?


Versão da origem em produção:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Versão do destino em produção:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Versão do banco de teste:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


[rmanParticipante]

@afonso.rodrigues

Verifique se esse artigo é o seu caso:

http://jiri.wordpress.com/2010/06/04/qu … mple-view/

Obs: Procure trabalhar com a mesma versão do Oracle em todos os ambientes (Produção, homologação e teste), isso evita surpresas.

[Afonso RodriguesParticipante]

Olá @rman.

Infelizmente, este não é o meu erro.

Fiz alguns testes e quando eu crio as tabelas diretamente, sem opcionais, apenas o
create table na origem e no destino dá certo a carga.

Creio que o erro que estou tendo, é relacionado à forma com que a tabela foi criada.

Tabela na origem:

CREATE TABLE "USER"."TABELA_TESTE"
(
"IDC_REGISTRO" NUMBER(10,0) NOT NULL ENABLE,
"IDC_PECA" NUMBER(12,0) NOT NULL ENABLE,
"IDC_ANALOGICA" NUMBER(2,0) NOT NULL ENABLE,
"COD_ORDEM_PRODUCAO" VARCHAR2(12 BYTE) NOT NULL ENABLE,
"NUM_SECAO_EIXO_X" BLOB,
"NUM_SECAO_EIXO_Y" BLOB,
"DTH_CRIACAO_REG" DATE,
"TESTE" VARCHAR2(20 BYTE),
CONSTRAINT "TABELA_TESTE_PK" PRIMARY KEY ("IDC_PECA", "IDC_ANALOGICA") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "USERS" LOB
(
"NUM_SECAO_EIXO_X"
)
STORE AS BASICFILE
(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
)
LOB
(
"NUM_SECAO_EIXO_Y"
)
STORE AS BASICFILE
(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
) ;
CREATE UNIQUE INDEX "USER"."SYS_IL0000205504C00006$$" ON "USER"."TABELA_TESTE"
(
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "USER"."SYS_IL0000205504C00005$$" ON "USER"."TABELA_TESTE" ( PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "USER"."TABELA_TESTE_PK" ON "USER"."TABELA_TESTE" ("IDC_PECA", "IDC_ANALOGICA") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;

Tabela no destino

CREATE TABLE "USER"."TABELA_TESTE"
(
"IDC_REGISTRO" NUMBER(10,0) NOT NULL ENABLE,
"IDC_PECA" NUMBER(12,0) NOT NULL ENABLE,
"IDC_ANALOGICA" NUMBER(2,0) NOT NULL ENABLE,
"COD_ORDEM_PRODUCAO" VARCHAR2(36 BYTE) NOT NULL ENABLE,
"NUM_SECAO_EIXO_X" BLOB,
"NUM_SECAO_EIXO_Y" BLOB,
"DTH_CRIACAO_REG" DATE,
"TESTE" VARCHAR2(20 BYTE),
CONSTRAINT "TABELA_TESTE_PK" PRIMARY KEY ("IDC_PECA", "IDC_ANALOGICA") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USER_TBS" ENABLE
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "USER_TBS" LOB
(
"NUM_SECAO_EIXO_X"
)
STORE AS BASICFILE
(
TABLESPACE "USER_TBS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
)
LOB
(
"NUM_SECAO_EIXO_Y"
)
STORE AS BASICFILE
(
TABLESPACE "USER_TBS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
) ;
CREATE UNIQUE INDEX "USER"."TABELA_TESTE_PK" ON "USER"."TABELA_TESTE"
(
"IDC_PECA", "IDC_ANALOGICA"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "USER_TBS" ;
CREATE UNIQUE INDEX "USER"."SYS_IL0014893528C00005$$" ON "USER"."TABELA_TESTE"
(
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USER_TBS" PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "USER"."SYS_IL0014893528C00006$$" ON "USER"."TABELA_TESTE" ( PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USER_TBS" PARALLEL (DEGREE 0 INSTANCES 0) ;

Obrigado

[Autor]

Posts