ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663513] |
Tue, 06 June 2017 13:36 |
|
freis_bcf
Messages: 6 Registered: June 2017
|
Junior Member |
|
|
I'm trying to create a procedure that generates a text file using Spool, but an error is occurring when I try to execute the procedure.
Please, help me?!
CREATE OR REPLACE PROCEDURE EXPORT_FILE (VAR1 IN VARCHAR2, VAR2 IN VARCHAR2)AS
--VAR1 --> TIPO DO SEPARADOR
--VAR2 --> DATA DE PESQUISA
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FILE';
INSERT INTO FILE
SELECT VAR2||VAR1||
COL1||VAR1||
COL2||VAR1||
COL3||VAR1 AS STRING
FROM TABLE1
WHERE COL4 >= VAR2;
EXECUTE IMMEDIATE
'
set heading off
set trimspool on
set linesize 1520
set pagesize 50000
set echo off
set feedback off
col file_name new_value file1;
SELECT ''EXP_.txt'' as file_name from dual;
spool C:\TEST\&file1
SELECT * FROM FILE;
';
END EXPORT_FILE;
Another way I tried it was by creating a .sql file containing only the Spool code and then calling the procedure with @.
Spool file:
set heading off
set trimspool on
set linesize 1520
set pagesize 50000
set echo off
set feedback off
col file_name new_value file1;
SELECT 'EXP_.txt' as file_name from dual;
spool C:\TEST\&file1
SELECT * FROM FILE;
Procedure:
CREATE OR REPLACE PROCEDURE EXPORT_FILE (VAR1 IN VARCHAR2, VAR2 IN VARCHAR2)AS
--VAR1 --> TIPO DO SEPARADOR
--VAR2 --> DATA DE PESQUISA
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FILE';
INSERT INTO FILE
SELECT VAR2||VAR1||
COL1||VAR1||
COL2||VAR1||
COL3||VAR1 AS STRING
FROM TABLE1
WHERE COL4 >= VAR2;
EXECUTE IMMEDIATE '@C:\FOLDER\query.sql'
END EXPORT_FILE;
|
|
|
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663514 is a reply to message #663513] |
Tue, 06 June 2017 13:49 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SET, COL, SPOOL, @ are SQL*Plus commands not SQL or PL/SQL statements, you cannot use them in a procedure.
EXECUTE IMMEDIATE is a PL/SQL command which sends a SQL command to the SQL engine.
Also always post your Oracle version, with 4 decimals, as most often solution depends on it.
Also when you report an error, copy and paste your SQL*plus session, the WHOLE session.
[Updated on: Tue, 06 June 2017 13:51] Report message to a moderator
|
|
|
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663515 is a reply to message #663514] |
Tue, 06 June 2017 13:53 |
|
freis_bcf
Messages: 6 Registered: June 2017
|
Junior Member |
|
|
Michel Cadot wrote on Tue, 06 June 2017 13:49
SET, COL, SPOOL, @ are SQL*Plus commands not SQL or PL/SQL statements, you cannot use them in a procedure.
EXECUTE IMMEDIATE is a PL/SQL command which sends a SQL command to the SQL engine.
Also always post your Oracle version, with 4 decimals, as most often solution depends on it.
Also when you report an error, copy and paste your SQL*plus session, the WHOLE session.
Do you have any suggestions for extracting a text file from Oracle with procedure?
|
|
|
|
|
|
|
|
|
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663535 is a reply to message #663534] |
Wed, 07 June 2017 09:28 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
have you setup the directory object DIR_BCF in your database pointing to a folder ON THE DATABASE SERVER? Has the schema your running the code in been granted read/write access to the directory object? Does that actual folder on the database server have full read and write privileges for the user running the database. That is typically the oracle user.
|
|
|
|
|
|
|
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663637 is a reply to message #663539] |
Mon, 12 June 2017 03:02 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You should never write an exception handler like this:
WHEN Others THEN
Dbms_Output.Put_Line('Problemas na geração do arquivo.');
UTL_File.Fclose(exit_file);
How are you going to know what the actual error was?
If you need when others at all (understandable in this case to close the file) it should be this:
WHEN Others THEN
UTL_File.Fclose(exit_file);
raise;
|
|
|