Run.sql file in procedure

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
Post Reply
freis_bcf
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Mon, 10 Apr 2017 5:07 pm

I need a help ..

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;

Select all

 
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:

Select all

 
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; 
Note: If I run procedure

Select all

EXEC EXPORT_DADOS_TELEMETRIA (';','20170101')
, with the line commented here it works:

Select all

EXECUTE IMMEDIATE '@C:\TESTE\....
In the same way that if I run only @c: \ test \ .... it works too, but I need to leave the two in the same procedure to deliver to a person.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good afternoon freis_bcf,

"I think" that you can not run the Immediate execute with the call of a script.

No way in your procedure you mount the line and run?
without generating spool
freis_bcf
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Mon, 10 Apr 2017 5:07 pm

spernega wrote: Advanced search
Unfortunately no .. I need spool to generate a txt . I wanted to leave everything in a procedure only .. but from what I researched on the internet, I could not leave the spool command in the procedure. So I tried to leave the command in file.sql and then try to call in procedure ..
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

This is complicated.

If you generate spool just for reference and run direct commands by procedure?

may be better to use UTL_File to generate the file unless you want the file the user machine, so complicates.
freis_bcf
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Mon, 10 Apr 2017 5:07 pm

Yes .. I need the file to stay on the user machine = / ..

I tried this way:

Select all

 
create or replace PROCEDURE EXPORT_DADOS (VAR1 IN VARCHAR2, VAR2 IN VARCHAR2)AS 
 
--VAR1 --> TIPO DO SEPARADOR 
--VAR2 --> DATA DE PESQUISA 
 
BEGIN 
 
EXECUTE IMMEDIATE 'TRUNCATE TABLE DADOS'; 
 
INSERT INTO DADOS  
SELECT VAR2||VAR1|| 
            COL1||VAR1|| 
            COL2||VAR1|| 
            COL3||VAR1|| STRING 
FROM TAB1 
WHERE TO_CHAR(COL4,'YYYYMMDD')>=VAR2; 
 
EXECUTE IMMEDIATE  
' 
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_.txt'' as nome_arq from dual; 
 
spool C:\TESTE\&arquivo 
 
SELECT * FROM DADOS; 
'; 
 
END EXPORT_DADOS; 
] I can cow to procedure, but when I do the error appears:

Select all

ORA-00922: opção não encontrada ou inválida 
ORA-06512: em "BD.EXPORT_DADOS", line 30             (Linha do comando EXECUTE IMMEDIATE para o Spool) 
ORA-06512: em line 8 
Processo encerrado. 
Desconectando do banco de dados
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

I understand, unfortunately will not generate the spool from within the procedure.

The procedure I had thought more or less thus

Select all

create or replace  
PROCEDURE EXPORT_DADOS (VAR1 IN VARCHAR2, --VAR1 --> TIPO DO SEPARADOR 
                        VAR2 IN date) is  --VAR2 --> DATA DE PESQUISA 
 -- 
 cursor c is 
  SELECT VAR2||VAR1|| 
         COL1||VAR1|| 
         COL2||VAR1|| 
         COL3||VAR1|| STRING 
  FROM   TAB1 
  WHERE  COL4 >= VAR2; 
 c_r c%rowtype; 
 -- 
BEGIN 
  -- 
  EXECUTE IMMEDIATE 'TRUNCATE TABLE DADOS'; 
  -- 
  open c; 
   loop 
    fetch c into c_r; 
     exit when c%notfound; 
     -- 
     insert into dados values(c_r.string); 
     -- 
     EXECUTE IMMEDIATE c_r.string; 
     -- 
   end loop; 
  close c; 
  -- 
END EXPORT_DADOS; 
/
But the spool would have to be left out.

Unless you use the UTL_File, but then the user will have to have access to the directory and "fetch" the file.
If you can do so, it gets easy.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 6 guests