I'm creating a procedure in which she writes some data in a table, from that table, I generate a .txt file by spool.
As I want everything in the same procedure, I left the spool in a file.sql for the procedure to read and run, but it is giving error at the time that generates the procedure. Can someone help me? Below is the codes I am using.
procedure;
create or replace PROCEDURE EXPORT_DADOS_TELEMETRIA (VAR1 IN VARCHAR2, VAR2 IN VARCHAR2)AS
--VAR1 --> TIPO DO SEPARADOR
--VAR2 --> DATA DE PESQUISA
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE DADOS_TELEMETRIA';
INSERT INTO DADOS_TELEMETRIA
SELECT VAR2||VAR1||
COL1||VAR1||
COL2||VAR1||
COL3||VAR1||
COL4||VAR1
AS STRING
FROM TABELA
WHERE TO_CHAR(COL5,'YYYYMMDD')>=VAR2;
EXECUTE IMMEDIATE '@C:\TESTE\TXT\export_txt.sql'; ----> Onde acontece o erro
END EXPORT_DADOS_TELEMETRIA;
Spool code:
set heading off
set trimspool on
set linesize 1520
set pagesize 50000
set echo off
set feedback off
col nome_arq new_value arquivo;
SELECT 'EXP_'||TO_CHAR(SYSDATE, 'YYMMDDHH24MI')||'.txt' as nome_arq from dual;
spool C:\TESTE\TXT\&arquivo
SELECT * FROM DADOS_TELEMETRIA;
EXEC EXPORT_DADOS_TELEMETRIA (';','20170101')
EXECUTE IMMEDIATE '@C:\TESTE\....