peckage.sql

create or replace
PACKAGE spu_exp_data_pkg AS
	-- declarando variáveis globais de trabalho
	logName					varchar2(50); -- variável para armazenar o nome do log.
	
	-- procedure responsável por realizar a impressão das saídas para depuração. 
	PROCEDURE SP_PUT_LINE (text IN VARCHAR2);
	PROCEDURE SP_GERA_ARQUIVO (p_sql IN VARCHAR2
                                     ,p_dir IN VARCHAR2
                                     ,p_header_file IN VARCHAR2
                                     ,p_data_file IN VARCHAR2 := NULL);
	PROCEDURE SP_EXPORTAR_ARQUIVO(p_funcionalidade IN VARCHAR2, p_ano IN VARCHAR2);
	FUNCTION FN_GET_SELECT (p_funcionalidade IN VARCHAR2, p_ano IN VARCHAR2) return VARCHAR2;
	FUNCTION FN_GET_ano_CLAUSE (alias IN VARCHAR2, p_ano IN VARCHAR2) return VARCHAR2;
	
	procedure sp_compacta_arquivo (p_diretorio in varchar2 --> Diretorio do arquivo 
								 ,p_arq_origem in varchar2 --> Arquivo Origem 
								 ,p_arq_destino in varchar2 --> Arquivo destino 
								 ,p_qualidade in binary_integer --> Qualidade da compactação 
								 ,p_remove_org in varchar2 --> Remove Origem 
								 ,p_retorno out varchar2); --> Retorno de erro 
	
 
end spu_exp_data_pkg;
/
 
 
 
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
 
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
 
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
 
 
create or replace
PACKAGE BODY spu_exp_data_pkg IS
	
	
	
	PROCEDURE SP_EXPORTAR_ARQUIVO(p_funcionalidade IN VARCHAR2, p_ano IN VARCHAR2)
	IS
		sqlParaArquivo varchar2(32767);
		retornoCompactacao varchar2(400);
		nomeArquivo varchar2(400);
		nomeArquivoCompactado varchar2(400);
	BEGIN
			logName := 'SP_EXPORTAR_ARQUIVO';
			nomeArquivo := p_funcionalidade||p_ano||'.txt';
			nomeArquivoCompactado := p_funcionalidade||p_ano||'.zip';
			
			sqlParaArquivo := FN_GET_SELECT(p_funcionalidade,p_ano);
			SP_GERA_ARQUIVO(sqlParaArquivo,'FILE_DATA',nomeArquivo);
			sp_compacta_arquivo('FILE_DATA',nomeArquivo,nomeArquivoCompactado,9,'S',retornoCompactacao);
			SP_PUT_LINE(retornoCompactacao);
		EXCEPTION
				-- caso haja uma exceção, mostra a exceção.
				WHEN others THEN
					SP_PUT_LINE(sqlcode || ' '||sqlerrm);
	END  SP_EXPORTAR_ARQUIVO;
	
	FUNCTION FN_GET_ano_CLAUSE (alias IN VARCHAR2, p_ano IN VARCHAR2) 
		return VARCHAR2
		IS
			anoClause varchar2(40);
		BEGIN
			IF p_ano IS NULL THEN	
				anoClause := '(1=1)';
			ELSE
				anoClause := alias||'.AA_EXERCICIO = '||p_ano;
			END IF;
		RETURN anoClause;
		EXCEPTION
				-- caso haja uma exceção, mostra a exceção.
				WHEN others THEN
					SP_PUT_LINE(sqlcode || ' '||sqlerrm);
	END  FN_GET_ano_CLAUSE;
			
	
	FUNCTION FN_GET_SELECT (p_funcionalidade IN VARCHAR2, p_ano IN VARCHAR2)
		RETURN VARCHAR2
		IS
			TYPE map_varchar IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(40);
			mapaSelect_funcionalidade map_varchar;
			selectReturn varchar2(32767);
		BEGIN
			mapaSelect_funcionalidade('dual') := 'select * from dual';					  
			selectReturn := mapaSelect_funcionalidade(p_funcionalidade);
			SP_PUT_LINE(selectReturn);
		-- retorna a query selecionada
		RETURN selectReturn;
		EXCEPTION
			-- caso haja uma execeção, mostra a exceção e faz o rollback da transação 
			WHEN others THEN
				SP_PUT_LINE('');
				SP_PUT_LINE(sqlcode || ' '||sqlerrm);
				SP_PUT_LINE('');
			ROLLBACK;
	END;
	
	PROCEDURE SP_GERA_ARQUIVO(p_sql IN VARCHAR2
                                     ,p_dir IN VARCHAR2
                                     ,p_header_file IN VARCHAR2
                                     ,p_data_file IN VARCHAR2 := NULL) IS
	  v_finaltxt  VARCHAR2(4000);
	  v_v_val     VARCHAR2(4000);
	  v_n_val     NUMBER;
	  v_d_val     DATE;
	  v_ret       NUMBER;
	  c           NUMBER;
	  d           NUMBER;
	  col_cnt     INTEGER;
	  f           BOOLEAN;
	  rec_tab     DBMS_SQL.DESC_TAB;
	  col_num     NUMBER;
	  v_fh        UTL_FILE.FILE_TYPE;
	  v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
	BEGIN
	  c := DBMS_SQL.OPEN_CURSOR;
	  DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
	  d := DBMS_SQL.EXECUTE(c);
	  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
	  FOR j in 1..col_cnt
	  LOOP
		CASE rec_tab(j).col_type
		  WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
		  WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
		  WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
		ELSE
		  DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
		END CASE;
	  END LOOP;
	  -- This part outputs the HEADER
	  v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
	  FOR j in 1..col_cnt
	  LOOP
		v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
	  END LOOP;
	  --  DBMS_OUTPUT.SP_PUT_LINE(v_finaltxt);
	  UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
	  IF NOT v_samefile THEN
		UTL_FILE.FCLOSE(v_fh);
	  END IF;
	  --
	  -- This part outputs the DATA
	  IF NOT v_samefile THEN
		v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
	  END IF;
	  LOOP
		v_ret := DBMS_SQL.FETCH_ROWS(c);
		EXIT WHEN v_ret = 0;
		v_finaltxt := NULL;
		FOR j in 1..col_cnt
		LOOP
		  CASE rec_tab(j).col_type
			WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
						v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
			WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
						v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
			WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
						v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
		  ELSE
			v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
		  END CASE;
		END LOOP;
	  --  DBMS_OUTPUT.SP_PUT_LINE(v_finaltxt);
		UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
	  END LOOP;
	  UTL_FILE.FCLOSE(v_fh);
	  DBMS_SQL.CLOSE_CURSOR(c);
	END
		SP_GERA_ARQUIVO;
	
	-- procedure resposável por gerar os logs da execução
	PROCEDURE SP_PUT_LINE (text IN VARCHAR2) 
	IS
		f  utl_file.file_type;
		file_name VARCHAR2(50);
	BEGIN
		file_name := logName||'_' || to_char(trunc(SYSDATE),'ddmmyyyy') ||'.log';
		f := utl_file.fopen ('LOG_DATA', file_name, 'A');
		utl_file.PUT_LINE ( f, text);
		utl_file.fclose (f);
		
		dbms_output.PUT_LINE(text);
		EXCEPTION
				-- caso haja uma exceção, mostra a exceção.
				WHEN others THEN
					dbms_output.PUT_LINE('');
					dbms_output.PUT_LINE(sqlerrm);
					dbms_output.PUT_LINE('');
	END
		SP_PUT_LINE;
		
	procedure sp_compacta_arquivo (p_diretorio in varchar2 --> Diretorio do arquivo 
								 ,p_arq_origem in varchar2 --> Arquivo Origem 
								 ,p_arq_destino in varchar2 --> Arquivo destino 
								 ,p_qualidade in binary_integer --> Qualidade da compactação 
								 ,p_remove_org in varchar2 --> Remove Origem 
								 ,p_retorno out varchar2) IS --> Retorno de erro 
 
		 BEGIN 
		--> Checando os parametros 
		IF p_diretorio is null THEN 
			p_retorno := 'Nome do diretorio deve ser informado'; 
		ELSIF p_arq_origem is null THEN 
			p_retorno := 'Nome do arquivo de origem deve ser informado'; 
		ELSIF p_arq_destino is null THEN 
			p_retorno := 'Nome do arquivo de destino deve ser informado'; 
		 ELSIF p_arq_origem = p_arq_destino THEN 
			p_retorno := 'Nome do arquivo de origem deve ser deferente do nome do arquivo de destino'; 
		 ELSIF UPPER(p_remove_org) not in ('S','N') THEN 
			p_retorno := 'Opção de Remoção do arquivo de origem deve ser S para sim e N para não'; 
		 ELSIF p_qualidade not in (1,2,3,4,5,6,7,8,9) THEN 
			p_retorno := 'Qualidade deve estar entre 1 (Menor compressao) e 9 (Maior compressao)'; 
		 ELSE 
		 DECLARE 
		 --> Variaveis Auxiliares 
			v_fp UTL_FILE.file_type; 
			v_bin_arq BFILE; 
			v_arq_compact BLOB; 
			v_buffer RAW(32767); 
			v_init_pos NUMBER := 1; 
			v_end_pos NUMBER; 
			v_tam_bloco BINARY_INTEGER := 32767; 
		BEGIN 
		 --> Atribuindo o nome do arquivo que queremos compactar. 
		 v_bin_arq := BFILENAME (p_diretorio, p_arq_origem); 
		 --> Atribuindo o arquivo. 
		 DBMS_LOB.fileopen (v_bin_arq); 
		 --> Criando um BLOB que vai ser usado para armazenar o arquivo compactado 
		 DBMS_LOB.createtemporary (v_arq_compact, TRUE, DBMS_LOB.session); 
		 --> Compactando o arquivo em uma variavel BLOB 
		 v_arq_compact := UTL_COMPRESS.lz_compress (v_bin_arq, p_qualidade); 
		 --> Posição final do arquivo. 
		 v_end_pos := DBMS_LOB.getlength (v_arq_compact); 
		 v_fp := UTL_FILE.fopen (p_diretorio, p_arq_destino, 'wb',32767); 
		 --> Gravando o arquivo compactado no arquivo de saída. 
		 WHILE v_init_pos < v_end_pos LOOP 
		 --> Lendo o arquivo 
			 DBMS_LOB.read (v_arq_compact, v_tam_bloco, v_init_pos, v_buffer); 
			 UTL_FILE.put_raw (v_fp, v_buffer); 
			 v_init_pos := v_init_pos + v_tam_bloco; 
			 v_buffer := NULL; 
		 END LOOP; 
		 --> Fechando os arquivos e liberando memória. 
		 UTL_FILE.fclose (v_fp); 
		 DBMS_LOB.filecloseall; 
		 DBMS_LOB.freetemporary (v_arq_compact); 
		 --> Removendo o arquivo 
		 IF UPPER(p_remove_org) = 'S' THEN 
			UTL_FILE.fremove (p_diretorio,p_arq_origem); 
		 END IF; 
		 EXCEPTION 
			WHEN others THEN 
				p_retorno := sqlerrm; 
			END; 
		 END IF; 
	 END sp_compacta_arquivo;
		
end spu_exp_data_pkg;
/