Package utl_file não carrega arquivo txt

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
fehein
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Qua, 23 Jan 2008 1:32 pm
Localização: Taboão da Serra
Fehein

Bom dia.
Foi criada a package conforme abaixo porem não esta carregando as informações do arquivo para a tabela, porem tambem não retorna erro algum.
Existe algo errado com a proc ou pode ser problema de diretório?
Verifiquei na pasta Directories e o diretorio esta correto, porem o diretorio não esta na pasta do oracle e sim na pasta C:\.
O Banco de teste esta instalado localmente na minha maquina.
Obrigado

Selecionar tudo

WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR CONTINUE

CREATE OR REPLACE PACKAGE BODY mrsc_gl_interface001_pk IS
   -- $Header: %M% %I% %E% %U% appldev ship $
   -- +=================================================================+
      -- |       Copyright (c) 2006 Oracle Brasil, São Paulo, Brasil       |
      -- |                       All rights reserved.                      |
      -- +=================================================================+
      -- | FILENAME    							   |
      -- |   MRSC_GL_INTERFACE001_PK_B.PLS				   |
      -- |                                                                 |
      -- | PURPOSE                                                         |
      -- |   Script de criacao de package.                                 |
      -- |                                                                 |
      -- | DESCRIPTION                                                     |
      -- |   Package de carga de Saldos Contábeis MRS		           |
      -- |                                                                 |
      -- | CREATED BY                                                      |
      -- |   Fernando Hein Magalhães  (09/02/2009)                         |
      -- |                                                                 |
      -- | UPDATED BY                                                      |
      -- |                             					   |
   -- +=================================================================+

   -- Interface GL
PROCEDURE ins_carga_saldo(       errbuf                OUT VARCHAR2
                                ,retcode           OUT NUMBER
                              --  ,p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE
                                ,p_period_name_ini IN gl_periods.period_name%TYPE
                                ,p_period_name_fim IN gl_periods.period_name%TYPE
                                ,p_dt_contabil IN date) IS

l_set_of_books_id  	NUMBER(15,0);
l_accounting_date  	DATE;
l_creation_date    	DATE;
l_created_by	   	NUMBER (15,0);
l_user_je_source_name 	VARCHAR2(25);
l_user_je_category_name VARCHAR2(25);
l_period_name		VARCHAR2(15);
l_reference10		VARCHAR2(100);
l_segment01		VARCHAR2(25);
l_segment02		VARCHAR2(25);
l_segment03  	   	VARCHAR2(25);
l_segment04  	   	VARCHAR2(25);
l_segment05  	   	VARCHAR2(25);
l_segment06  	   	VARCHAR2(25);
l_segment07  	   	VARCHAR2(25);
l_segment08  	   	VARCHAR2(25);
l_segment09  	   	VARCHAR2(25);
l_segment10  	   	VARCHAR2(25);
l_segment11  	   	VARCHAR2(25);
l_segment12  	   	VARCHAR2(25);
l_debit  	   	NUMBER(10,2);
l_credit 	   	NUMBER(10,2);
l_tam_linha         	varchar2(5000)    ;
v_nome_arquivo      	utl_file.file_type;
FND_FILE 	        utl_file.file_type;	


--BEGIN
--      --
--      ---------------------------------------gravacao de log  ---------------------------------- 
--      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, chr(10) ||
--                         '                             INÍCIO Processo de Carga de Saldos Contábeis MRS');
--      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '                       --------------------------------------------------------------------');
--      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '                                         ' ||
--                         to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS'));
--   
--      ------------------------------------Carga do Arquivo texto de Saldos Contábeis----------------------------- 
   
   BEGIN
       -- abre o arquivo para escrita
	  v_nome_arquivo := UTL_FILE.FOPEN('PROJETO_MRS','TESTE_INSERT.TXT','r');
	  
      BEGIN
      		
         LOOP
         
          utl_file.get_line(v_nome_arquivo, l_tam_linha);
          
            BEGIN
		
		l_segment03 :=  trim(substr( l_tam_linha,1 ,instr(l_tam_linha ,'|',1,1)-1));
		l_segment04 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,1)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment05 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,2)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment06 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,3)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment07 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,4)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment08 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,5)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment09 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,6)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment10 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,7)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment11 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,8)+1, instr(l_tam_linha,'|',1,1) -1));
		l_segment12 :=  trim(substr( l_tam_linha, instr(l_tam_linha, '|',1,9)+1, instr(l_tam_linha,'|',1,1) -1));
		l_debit :=  trim(to_number(substr( l_tam_linha, instr(l_tam_linha, '|',1,10)+1, instr(l_tam_linha, '|',1,1)-1)));
		l_credit := trim(to_number(substr( l_tam_linha, instr(l_tam_linha, '|',1,11)+1)));	
            
            
           		BEGIN
           		
            			SELECT DISTINCT SET_OF_BOOKS_ID
            			INTO L_SET_OF_BOOKS_ID
            			FROM GL_SETS_OF_BOOKS
            			WHERE NAME = 'MRS_FISCAL';
            			
            		EXCEPTION WHEN NO_DATA_FOUND THEN
            		DBMS_OUTPUT.PUT_LINE('NÃO FOI ENCONTRADO ID PARA O LIVRO FISCAL');
            		
            		END;
            
            
            
            		   BEGIN
            			select period_name 
            			INTO l_period_name
            			from gl_periods
	    			where start_date >= p_period_name_ini
            			and end_date <= p_period_name_fim;
            		   
            		   EXCEPTION WHEN NO_DATA_FOUND THEN
            		   DBMS_OUTPUT.PUT_LINE ('NÃO FOI ENCONTRADO O PERIODO NA TABELA ORIGEM');
            		   
            		   END;
             
             
                BEGIN
                
                     INSERT INTO GL_INTERFACE_TESTE (
			         STATUS,  		     		--1                
                     		 SET_OF_BOOKS_ID,			--2
                     		 ACCOUNTING_DATE,			--3
                     		 CURRENCY_CODE	,			--4
                     		 DATE_CREATED,				--5
                     		-- CREATED_BY,				--6
                     		 ACTUAL_FLAG	,			--7
                     		 USER_JE_CATEGORY_NAME	,		--8
                     		 USER_JE_SOURCE_NAME	,		--9
                     		 SEGMENT1		,		--10
                     		 SEGMENT2		,		--11
                     		 SEGMENT3,				--12
                     		 SEGMENT4,				--13
                     		 SEGMENT5,				--14
                     		 SEGMENT6,				--15
                     		 SEGMENT7,				--16
                     		 SEGMENT8,				--17
                     		 SEGMENT9,				--18
                     		 SEGMENT10,				--19
                     		 SEGMENT11,				--20
                     		 SEGMENT12,				--21
                     		 ENTERED_DR,				--22
                     		 ENTERED_CR,				--23
                     		 ACCOUNTED_DR,				--24
                     		 ACCOUNTED_CR,				--25
                     		 REFERENCE10,	      			--26	
                     		 PERIOD_NAME)				--27
                     	VALUES (
                     		'NEW',					--01
                     		L_SET_OF_BOOKS_ID,			--02
                     		p_dt_contabil,				--03
                  		'BRL',					--04
                  		SYSDATE,				--05
                  		--FND_PROFILE.VALUE('USER_ID'),		--06
                  		'A',					--07
                  		'CARGA INICIAL',			--08
                  		'CARGA',				--09
                  		'001',					--10
                  		'0000',					--11
                  		l_segment03,				--12
                  		l_segment04,				--13
                  		l_segment05,				--14
                  		l_segment06,				--15
                  		l_segment07,				--16
                  		l_segment08,				--17
                  		l_segment09,				--18
                  		l_segment10,				--19
                  		l_segment11,				--20
                  		l_segment12,				--21
                  		l_debit,				--22
                  		l_debit,				--23
                  		l_credit,				--24
                  		l_credit,				--25
                  		'CARGA INICIAL',			--26
                  		l_period_name	);			--27
                 
                EXCEPTION
		WHEN OTHERS THEN
		     errbuf  := SQLERRM;
                    retcode := SQLCODE;
                
                END;
            
            EXCEPTION WHEN NO_DATA_FOUND THEN
            EXIT;
            
            END;
         
          END LOOP;
      
      EXCEPTION
      WHEN OTHERS THEN
      		     errbuf  := SQLERRM;
                     retcode := SQLCODE;
      
      END;
      
   COMMIT;
  
  -- END;      
   utl_file.fclose(v_nome_arquivo);       
      
      
      ---------------------------------------gravacao de log  ----------------------------------
   
--      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, chr(10) ||
--                         '                             FIM  Processo de Carga de Saldos Contábeis MRS      ');
--      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '                       --------------------------------------------------------------------');
--      IF to_char(SQLCODE) <> '0' THEN
--         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'ERRO: ' || to_char(SQLCODE) || '-' ||
--                            errbuf);
--      END IF;
--   
--      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '                                                                                                      ' ||
--                         to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS'));
 
exception
      when utl_file.invalid_operation then
           utl_file.fclose(v_nome_arquivo);
           raise_application_error(-20002,'Operação invalida !!!');
      when utl_file.read_error then
           utl_file.fclose(v_nome_arquivo);
           raise_application_error(-20003,'Erro de leitura !!!');
      when utl_file.write_error then
           utl_file.fclose(v_nome_arquivo);
           raise_application_error(-20004,'Erro de gravação !!!');
      when TOO_MANY_ROWS then
           utl_file.fclose(v_nome_arquivo);
           raise_application_error(-20006,'Arquivo já Processado !!!');
           
end INS_CARGA_SALDO;           
           
END;

-- indicativo de final de arquivo, não deve ser removido
/

--EXIT;

Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

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

Veja se o parâmetro UTL_FILE_DIR está setado.

Leitura:
http://glufke.net/oracle/viewtopic.php?t=1783
fehein
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Qua, 23 Jan 2008 1:32 pm
Localização: Taboão da Serra
Fehein

Thomas,

Como eu faço para setar o utl_file_dir no arquivo init.ora?
Abaixo como esta o meu init.

Selecionar tudo


##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
 
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=202375168
 
###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.2.0/oradata/bdferna/\control01.ctl", "C:\oracle\product\10.2.0/oradata/bdferna/\control02.ctl", "C:\oracle\product\10.2.0/oradata/bdferna/\control03.ctl")
db_recovery_file_dest=C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size=2147483648
 
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\product\10.2.0/admin/bdferna/bdump
core_dump_dest=C:\oracle\product\10.2.0/admin/bdferna/cdump
user_dump_dest=C:\oracle\product\10.2.0/admin/bdferna/udump
 
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
 
###########################################
# Job Queues
###########################################
job_queue_processes=10
 
###########################################
# Database Identification
###########################################
db_domain=""
db_name=bdferna
 
###########################################
# SGA Memory
###########################################
sga_target=609222656
 
###########################################
# Processes and Sessions
###########################################
processes=150
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1
 
###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\oracle\product\10.2.0/admin/bdferna/adump
remote_login_passwordfile=EXCLUSIVE
 
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=bdfernaXDB)"
fehein
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Qua, 23 Jan 2008 1:32 pm
Localização: Taboão da Serra
Fehein

Thomas,

Pelo que eu verifiquei o utl_file_dir esta configurado corretamente. Criei uma proc de teste para escrever um arquivo texto utilizando o mesmo diretorio e funcionou corretamente, o arquivo de texto foi escrito, porem ainda não esta carregando as informações dentro da proc.
Não esta lendo as informações.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

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

Como que está o DIRECTORY 'PROJETO_MRS' ?

Selecionar tudo

SELECT * FROM DBA_DIRECTORIES
WHERE DIRECTORY_NAME='PROJETO_MRS'
fehein
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Qua, 23 Jan 2008 1:32 pm
Localização: Taboão da Serra
Fehein

Selecionar tudo

OWNER	DIRECTORY_NAME	DIRECTORY_PATH
SYS	PROJETO_MRS	C:\PROJETO_MRS
Eu já executei o comando como sys conforme abaixo.

Selecionar tudo

GRANT READ, WRITE ON DIRECTORY PROJETO_MRS TO USER; 
fehein
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Qua, 23 Jan 2008 1:32 pm
Localização: Taboão da Serra
Fehein

Boa tarde.

O problema da pack eu já consegui solucionar.
Agora estou com problema no fnd_file. Se verificar no codigo verá que eu coloquei mensagens utilizando o fnd_file, porem quando eu compilo a pack me retorna que eu tenho que declarar o componente output.

O fnd_file foi declarado como fnd_file utl_file.file_type.
O que eu estou fazendo de errado?

[]'s

Fernando Hein :-o
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 20 visitantes