Menu de navegação

[lucianalopesParticipante]

Boa tarde galera,

Estou tentando inserir um arquivo XML maior que 32 KB na minha base de dados. O campo e CLOB.
Está dando esse erro:
PLS-00215: As restrições de tamanho de string devem estar na faixa (1 .. 32767).

Mas nao estou sabendo como dividir o clob?

 

Atenciosamente,

Luciana

[Sergio WilliansMestre]

Oi Luciana, tudo bem ?

De uma olhada nesse artigo do mestre @jlchiappa.

• Strings, LONGs, CLOBs e coisas enroladas (para quem não manja dos paranauês) (profissionaloracle.com.br)

Tenho certeza de que a ajudará.

Se puder, envie um exemplo do seu código para avaliarmos.

 

 

[José Laurindo ChiappaModerador]

Adicionalmente, Luciana : como vc diz “inserir um arquivo XML maior que 32 kb….”, eu ENTENDO que vc tem um Arquivo-texto, físico, com os dados E precisar ler esse arquivo na íntegra e o inserir numa coluna (CLOB, BLOB ou XML) de uma tabela, certo ?

Sendo isso mesmo, SE vc tiver como mover esse arquivo para um sub-diretório do servidor Oracle, sub-diretório esse ACESSÍVEL ao usuário Oracle, vc poderia fazer a operação TODINHA numa pancada só, via DBMS_LOB.LoadClobFromFile (se a coluna for CLOB) ou DBMS_LOB.LoadBlobFromFile (se a coluna for BLOB)….

[José Laurindo ChiappaModerador]

Claro, a limitação da linguagem PL/SQL é que ela só roda dentro do servidor Oracle, então (em princípio, ignorando extensões tipo APEX) só pode acessar arquivos e recursos NO SERVIDOR ORACLE – assim, se por qquer motivo esse arquivo .XML com os dados a carregar Não Puder ser movido  (via ftp, ssh, o que for) OU diretamente para um sub-diretório local do  servidor Oracle  OU para uma área remota acessível pelo servidor Oracle (via NFS, share de rede, o que for) aí sim, a sua app vai ter que ler o arquivo aos pedaços (sendo que nenhum pedaço tenha mais de 32767 bytes) e aí gravar o dado no banco Oracle, certamente chamando uma PROCEDURE de banco que aceita um argumento de string a gravar e de identificador do registro aonde apendar os dados recebidos…

SE FOR ESSA a sua necessidade, plz nos diga e a gente pode tentar criar uma procedure-exemplo para vc…

[lucianalopesParticipante]

Boa tarde galera,

 

O meu código está em casa, vou por ele aqui, assim que chegar lá.

 

Obrigada

[lucianalopesParticipante]

Boa noite,

 

Sergio vi o post que voce me mandou, mas sao tres funcoes nao tive tempo de testa-las.

Queria saber se tem um jeito de fazer, usando bloco anonimo.

Me desculpe pelos nomes que pus, nao sao adequados, mas e que estou meio sem tempo.

Vou postar aqui, so o arquivo xml nao vou postar por ser sigiloso.

Ja da erro na linha sete.

 

reate directory testetd as ‘C:\IMAGENS’;
grant read, write on directory testetd to hr;

drop directory testetd;

create table testeclobxml(id number not null, nome varchar(300), arquivo clob)

insert into testeclobxml(ID, NOME, ARQUIVO) VALUES(2,’teste’,EMPTY_CLOB());commit;

declare
l_clob clob;
tt clob;
v_src_offset number:=1;
v_dest_offset number:=1;
arquivo1 varchar2:=’testett.xml’;
diretorio varchar2:=’testetd’;
l_lang_context number := dbms_lob.default_lang_ctx;
v_inicio number:=1;
l_warning number;
vTamanho_Texto number;
V_BUFFER_SIZE BINARY_INTEGER := 32000;
pasta bfile;
begin
vTamanho_Texto := LENGTH(arquivo1);
select arquivo into l_clob from testeclobxml for update;
pasta:=bfilename(diretorio,arquivo1);
dbms_lob.open(pasta,dbms_lob.lob_readonly);
dbms_lob.createtemporary(l_clob,false);
dbms_lob.loadclobfromfile(l_clob, pasta,DBMS_LOB.LOBMAXSIZE,v_dest_offset,v_dest_offset,bfile_csid => dbms_lob.default_csid,
lang_context => l_lang_context,
warning => l_warning);
FOR I IN 1 .. CEIL(DBMS_LOB.GETLENGTH(l_clob) / V_BUFFER_SIZE) LOOP
–utl_http.read_text(resp, arquivo1, 32766);
dbms_lob.read(l_clob, vTamanho_Texto,v_inicio,arquivo1);
dbms_lob.writeappend (l_clob, vTamanho_Texto, arquivo1);
dbms_output.put_line(arquivo1);
v_inicio:=v_inicio+V_BUFFER_SIZE;
end loop;
dbms_lob.freetemporary(arquivo1);
dbms_lob.close(pasta);
end;

Com o arquivo JSON maior ainda que 4KB, consegui usando o bulk collect e o forall, mas esse tentei e nao consegui.

 

 

 

Atenciosamente,

Luciana Maria Lopes

[José Laurindo ChiappaModerador]

Tudo bom, Luciana ? Então, PRIMEIRA COISA, tal como eu disse, o PL/SQL só funciona DENTRO DO SERVIDOR ORACLE, então se vc está criando um objeto do tipo DIRECTORY apontando para C:\IMAGENS , eu ** SUPONHO ** que o seu database Oracle está instalado NESSA MESMA MÁQUINA WINDOWS onde tem essa pasta C:\IMAGENS…. Para deixar Muito Claro : se na verdade teu database Oracle estiver instalado em Outra Máquina e essa máquina WIndows onde tem essa pasta C:\IMAGENS for só a sua máquina-cliente, onde vc tem as tools clientes que vc usa para acessar via rede o banco, SIMPLESMENTE NÃO VAI FUNCIONAR…
Isso claro, Segundo ponto : vc disse que estava “tentando inserir um arquivo XML maior que 32 KB na minha base de dados” : ora, isso ao que entendo significa ler o arquivo E gravar o conteúdo dele numa coluna LOB e morreu : isso quem faz é uma simples chamada ao DBMS_LOB.LOADxxxFROMFILE , onde XXX pode ser CLOB ou BLOB – porém, no seu código, DEPOIS de ter carregado os dados TODOS do arquivo para a coluna CLOB via LOADCLOBFROMFILE, vc Abre o CLOB, cria um LOOP para varrer ele inteirinho… ABSOLUTAMENTE NÃO ENTENDI!!!!
Se vc quer só ler o conteúdo de um arquivo para uma coluna CLOB, o código seria (supondo que COLUNA_CLOB é a tua coluna clob) simplesmente algo do tipo :

declare
v_lob clob;
l_bfile bfile;
v_qtd_bytes number;
begin
l_bfile := bfilename( ‘MEUDIRETORIO’, ‘nomedoarquivo.txt’ );

— crio uma linha na tabela com CLOB vazio, E guardo o ponteiro para esse CLOB…
insert into lob_tab (coluna_clob) values ( empty_clob() ) returning coluna_clob into v_lob;

v_qtd_bytes := dbms_lob.getlength( l_bfile );
dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
dbms_lob.loadfromfile( v_lob, l_bfile ,v_qtd_bytes);

dbms_output.put_line(‘Tamanho do arquivo é:’||dbms_lob.getlength( l_bfile ));
dbms_output.put_line(‘Bytes lidos e gravados=’||dbms_lob.getlength(v_lob));
dbms_lob.fileclose( l_bfile );
end;

 

Peguei o exemplo acima de um código antigo meu, que ainda usa o procedimento antigo/depreciado LOADFROMFILE, ao invés do mais moderno e recomendado LOADxxxFROMFILE, mas a lógica é a mesma… Plz nos EXPLIQUE todo esse código depois do LOAD que vc mostrou, e (mais importante) nos diga Exatamente o que vc quer : se for só e apenas carregar os dados do arquivo em uma coluna de uma tabela, TUDO que vc escreveu a mais Não Faz Sentido….

Abraços,

José Laurindo Chiappa

[lucianalopesParticipante]

Bom dia Jose Laurindo,

 

O que eu quero e exatamente isso, pegar um arquivo xml e gravar nas colunas do BD.

Esse codigo foi aonde criei o diretorio e gravei o arquivo xml nele(que quero gravar no bd)

E meu cliente e servidor esta na mesma maquina, windows 11

entao tentei carregar o arquivo com o loadclobfromfile e pedir para ler usando o read e usar o writeappend para dividir o arquivo em parte e gravar dentro do bd.

Mas assim que gero um arquivo para guardar o arquivo ele da erro falando que nao suporta mais de 32kb.

E o diretorio e criado corretamente

[lucianalopesParticipante]

E o mais engracado que com o arquivo JSON eu consegui, usando o forall e bulk collect, adaptando esse codigo no meu.

Agora com xml nao consigo

[lucianalopesParticipante]

Boa tarde Jose Laurindo,

 

Na verdade o read eu ia tirar, mas deu erro nessa linha arquivo1 varchar2:=’testett.xml’;

Ai nem teve como seguir em frente, queria uma  luz referente a isso

 

[José Laurindo ChiappaModerador]

“entao tentei carregar o arquivo com o loadclobfromfile e pedir para ler usando o read e usar o writeappend para dividir o arquivo em parte e gravar dentro do bd.”

REPITO, pela 3ª vez, que É a da sorte : vc ABSOLUTAMENTE NÂO PRECISA DIVIDIR um arquivo para que tenha TODOS OS DADOS DELE carregados para dentro de uma coluna LOB – o LOB permite SIM que vc grave (numa só coluna de uma só linha!!) até 4 GB de dados – vc só precisaria Dividir o XML em múltiplos pedaços SE não fosse isso, tipo : sua app tem, em memória, um XML grande, que não está em arquivo, aí TANTO na linguagem SQL quanto na linguagem PL/SQL existem LIMITES para tamanho de strings que vc pode colocar num INSERT, cfrme o meu artigo mostra – NÃO FAZ SENTIDO vc queuer dividir o LOB se os dados Já Estão num arquivo. o LOADFROMFILE CONSEGUE LER TUDO NUMA PANCADA Só…
No frigir dos ovos : SE os dados estão num arquivo-texto Acessível ao database OK, usa o LOADFROMFILE e com isso vc BYPASSOU os limites do comando INSERT que meu artigo mostra, falows ?

Vamos a um exemplo – o usuário SCOTT já recebeu GRANTs de READ e de WRITE no directory DATA_PUMP_DIR, que aponta para C:\app\oracle\product\21c\admin\XE\dpdump\32E2E310D29D4EC99CB01153313047B4 , e lá eu TENHO um arquivo de texto, com MAIS de 32 KB, que chamei de texto_longo.txt :

C:\app\oracle\product\21c\admin\XE\dpdump\32E2E310D29D4EC99CB01153313047B4>dir text*.txt
O volume na unidade C é Acer
O Número de Série do Volume é 2C56-9CDD

Pasta de C:\app\oracle\product\21c\admin\XE\dpdump\32E2E310D29D4EC99CB01153313047B4

03/10/2024 16:39 34.220 texto_longo.txt
1 arquivo(s) 34.220 bytes
0 pasta(s) 12.750.139.392 bytes disponíveis

C:\app\oracle\product\21c\admin\XE\dpdump\32E2E310D29D4EC99CB01153313047B4>

==> quero carregar o conteúdo desse arquivo NA ÍNTEGRA, para uma coluna CLOB : para poder rodar o teste, crio uma tabela com coluna CLOB :

SCOTT@XEPDB1::CONT=XEPDB1> create table TB_TESTE_CLOB(c1 number, c2 clob);
trigger disparada

Tabela criada.

=> e insiro uma LINHA na tabela, vai ser na coluna CLOB ** dessa linha ** que quero carregar o conteúdo INTEIRO do arquivo-texto :

==> pronto, tenho uma massa de dados, agora é só executar o bloco PL/SQL que te passei, só ALTERANDO os nomes, óbvio.. Adicionalmente, meu banco de testes é um XE 21c, então COMO EU TINHA DITO ANTES, em versões mais recentes a ORACLE RECOMENDA usar o LOADxxxFROMFILE, onde xxx é o tipo de LOB…
Eis o exemplo :

SCOTT@XEPDB1::CONT=XEPDB1> DECLARE
2 v_lob clob;
3 l_bfile bfile;
4 v_qtd_bytes number;
5 --
6 l_dest_offset integer := 1;
7 l_src_offset integer := 1;
8 l_bfile_csid number := 0;
9 l_lang_context integer := 0;
10 l_warning integer := 0;
11 BEGIN
12 l_bfile := bfilename( 'DATA_PUMP_DIR', 'texto_longo.txt');
13 v_qtd_bytes := dbms_lob.getlength( l_bfile );
14 dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
15 -- crio uma linha na tabela com CLOB vazio, E guardo o ponteiro para esse CLOB, usarei no LOAD …
16 insert into TB_TESTE_CLOB (C1, C2) values (1, empty_clob() ) returning C2 into v_lob;
17
18 dbms_lob.loadclobfromfile (
19 dest_lob => v_lob,
20 src_bfile => l_bfile,
21 amount => v_qtd_bytes,
22 dest_offset => l_dest_offset,
23 src_offset => l_src_offset,
24 bfile_csid => l_bfile_csid ,
25 lang_context => l_lang_context,
26 warning => l_warning
27 );
28 --
29 dbms_output.put_line('Tamanho do arquivo é:'||dbms_lob.getlength( l_bfile ));
30 dbms_output.put_line('Bytes lidos e gravados=' ||dbms_lob.getlength(v_lob));
31 dbms_lob.fileclose( l_bfile );
32 commit;
33 END;
34 /

Tamanho do arquivo é:34220
Bytes lidos e gravados=34218

Procedimento PL/SQL concluído com sucesso.

=> para COMPROVAR que os dados do arquivo INTEIRO ** FORAM LIDOS E CARREGADOS ** (absolutamente SEM PRECISAR dessa lógica de ‘separação’ !!!), vou configurar o sqlplus para exibir os dados Todos :

SCOTT@XEPDB1::CONT=XEPDB1> set LONG 500000

=> claro, para não ocupar espaço aqui no Fórum, vou CORTAR a string, mas acredite, exibiu os dados do arquivo INTEIRINHO :
SCOTT@XEPDB1::CONT=XEPDB1> select * from TB_TESTE_CLOB;

C1 C2
———- ——————————————————————————–
1 Texto com MAIS de 32767 caracteres por¿com MENOS de 4 GB, terminado com ! : Lore
m ipsum dolor sit amet, consectetuer adipiscing elit
…. blablabla, montes de dados cortados…. Phasellus
gravida semper nisi. Nullam vel sem. Pellentesque libero tortor, tincidunt et,
tincidunt eget, semper nec, quam.!

SCOTT@XEPDB1::CONT=XEPDB1>

CERTINHO ????

[lucianalopesParticipante]

Boa noite Jose,

Agora entendi, o loadfromclobefile faz a mesma função do read. Vou usar o seu exemplo e posto aqui o resultado

[lucianalopesParticipante]

Boa noite jose,

Fiz o codigo conforme falou, vou posta-lo aqui, mas fala arquivo inexistente, sendo que ele esta criado no mesmo lugar.

Consegui criar um arquivo json no mesmo diretorio e salva normal

a criacao do diretorio

create directory testetd as ‘C:\IMAGENS’;
grant read, write on directory testetd to hr;

criacao da tabela

create table testeclobxml1(id number not null, nome varchar(300), arquivo clob, primary key(id))

o codigo:

declare
l_clob clob;
v_src_offset number:=1;
v_dest_offset number:=1;
l_lang_context number := dbms_lob.default_lang_ctx;
v_inicio number:=1;
l_warning number;
vTamanho_Texto number;
pasta bfile;
begin
pasta:=bfilename(‘testetd’,’teste.xml’);
dbms_lob.fileopen(pasta,dbms_lob.lob_readonly);
vTamanho_Texto:=dbms_lob.getlength(pasta);
insert into testeclobxml1(ID, NOME, ARQUIVO) VALUES(2,’teste’,EMPTY_CLOB()) returning arquivo into l_clob;commit;
dbms_lob.loadclobfromfile(l_clob, pasta,DBMS_LOB.LOBMAXSIZE,v_dest_offset,v_dest_offset,bfile_csid => dbms_lob.default_csid,
lang_context => l_lang_context,
warning => l_warning);

dbms_output.put_line(‘Tamanho do arquivo é:’||dbms_lob.getlength( pasta ));

dbms_output.put_line(‘Bytes lidos e gravados=’ ||dbms_lob.getlength(l_clob));
dbms_lob.filecloseclose(pasta);

end;

 

da a mensagem: elatório de erros –
ORA-22285: diretório ou arquivo inexistente para operação FILEOPEN
ORA-06512: em “SYS.DBMS_LOB”, line 822
ORA-06512: em line 12
22285. 00000 – “non-existent directory or file for %s operation”
*Cause: Attempted to access a directory that does not exist, or attempted
to access a file in a directory that does not exist.
*Action: Ensure that a system object corresponding to the specified
directory exists in the database dictionary, or
make sure the name is correct.

Se passar seu email passo o arquivo xml que estou tentando usar.

Tentei por na pasta que voce citou acima dentro do app do 21 xe, mas deu o mesmo erro.

Uso sql developer e oracle 21.0

Obrigado,

Luciana Maria Lopes

 

 

 

o codigo que fiz, de acordo com o seu:

 

[lucianalopesParticipante]

Outra coisa o hr esta com todas as permissoes necessarias,

[lucianalopesParticipante]

Jose bom dia,

 

Analisando o meu codigo com o seu percebi uma diferenca que no meu nao tem

13 v_qtd_bytes := dbms_lob.getlength( l_bfile );
14 dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);

 

Sera que isso influenciou, para dar aquele tipo de erro?

Atenciosamente,

Luciana Maria Lopes

[Autor]

Posts