Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00922: missing or invalid option - PROCEDURE with SPOOL
ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663513] Tue, 06 June 2017 13:36 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
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 #663516 is a reply to message #663515] Tue, 06 June 2017 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean by "extracting a text file", it is not clear what you want to do in the end, not what you are trying to do to implement it which is clearly a dead-end.

Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663517 is a reply to message #663516] Tue, 06 June 2017 14:08 Go to previous messageGo to next message
freis_bcf
Messages: 6
Registered: June 2017
Junior Member
Michel Cadot wrote on Tue, 06 June 2017 13:57

What do you mean by "extracting a text file", it is not clear what you want to do in the end, not what you are trying to do to implement it which is clearly a dead-end.

Sorry if I'm writing something wrong, I'm using a translator to type here.

I need to extract a query from Oracle and send it to a text file.

For example.

The result of "select column1||';'||column2 from table;" Go to a .txt file
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663518 is a reply to message #663517] Tue, 06 June 2017 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So why not just call your script from SQL*Plus?

Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663520 is a reply to message #663518] Tue, 06 June 2017 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@vbgeneric ~]$ cat grants.sql 
set  echo on term on
SPOOL CAPTURE.SQL
SELECT 'GRANT SELECT ON ' || OBJECT_NAME || ' TO NEW_GUY;' FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
SPOOL OFF
@CAPTURE.SQL
[oracle@vbgeneric ~]$ sqlplus scott/tiger @grants.sql

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 6 15:52:10 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Jun 06 2017 15:50:55 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SPOOL CAPTURE.SQL
SQL> SELECT 'GRANT SELECT ON ' || OBJECT_NAME || ' TO NEW_GUY;' FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';

'GRANTSELECTON'||OBJECT_NAME||'TONEW_GUY;'
--------------------------------------------------------------------------------
GRANT SELECT ON SAMPLE_DATASET_EVOLVE TO NEW_GUY;
GRANT SELECT ON SAMPLE_DATASET_FULLTEXT TO NEW_GUY;
GRANT SELECT ON SAMPLE_DATASET_INTRO TO NEW_GUY;
GRANT SELECT ON SAMPLE_DATASET_PARTN TO NEW_GUY;
GRANT SELECT ON SAMPLE_DATASET_XQUERY TO NEW_GUY;
GRANT SELECT ON SAMPLE_DATASET_XMLDB_HOL TO NEW_GUY;
GRANT SELECT ON A TO NEW_GUY;
GRANT SELECT ON B TO NEW_GUY;
GRANT SELECT ON C TO NEW_GUY;
GRANT SELECT ON EVENT TO NEW_GUY;

10 rows selected.

SQL> SPOOL OFF
SQL> @CAPTURE.SQL
SQL> SQL> SELECT 'GRANT SELECT ON ' || OBJECT_NAME || ' TO NEW_GUY;' FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SQL> 
SQL> 'GRANTSELECTON'||OBJECT_NAME||'TONEW_GUY;'
SP2-0734: unknown command beginning "'GRANTSELE..." - rest of line ignored.
SQL> --------------------------------------------------------------------------------
SQL> GRANT SELECT ON SAMPLE_DATASET_EVOLVE TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON SAMPLE_DATASET_FULLTEXT TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON SAMPLE_DATASET_INTRO TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON SAMPLE_DATASET_PARTN TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON SAMPLE_DATASET_XQUERY TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON SAMPLE_DATASET_XMLDB_HOL TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON A TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON B TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON C TO NEW_GUY;

Grant succeeded.

SQL> GRANT SELECT ON EVENT TO NEW_GUY;

Grant succeeded.

SQL> 
SQL> 10 rows selected.
SQL> 
SQL> SQL> SPOOL OFF
SP2-0734: unknown command beginning "SQL> SPOOL..." - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@vbgeneric ~]$ cat grants.sql 
set  echo on term on
SPOOL CAPTURE.SQL
SELECT 'GRANT SELECT ON ' || OBJECT_NAME || ' TO NEW_GUY;' FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
SPOOL OFF
@CAPTURE.SQL
[oracle@vbgeneric ~]$ 

Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663533 is a reply to message #663520] Wed, 07 June 2017 09:07 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Read up on the UTL_FILE procedure it will do exactly what you want to do with one restriction. The flat file produced can ONLY be on the database server.
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663534 is a reply to message #663533] Wed, 07 June 2017 09:24 Go to previous messageGo to next message
freis_bcf
Messages: 6
Registered: June 2017
Junior Member
Bill B wrote on Wed, 07 June 2017 09:07
Read up on the UTL_FILE procedure it will do exactly what you want to do with one restriction. The flat file produced can ONLY be on the database server.
Thanks, I'm browsing the internet about examples of UTL_File .. I'm just having trouble letting it inside the procedure..
Apparently this is correct procedure, however I execute and do not save anything in the folder..

CREATE OR REPLACE PROCEDURE EXPORT_NEW(VAR1 IN VARCHAR2, VAR2 IN VARCHAR2) AS

    exit_file                    UTL_File.File_Type;
    Cursor Cur_Linha is SELECT COL1 FROM DWATANA WHERE ROWNUM <= 10;
 
BEGIN
    exit_file := UTL_File.Fopen('DIR_BCF','test.txt', 'W');
    For Reg_Line in Cur_linha Loop
        UTL_File.Put_Line(exit_file, Reg_line.COL1);
    End Loop;
    UTL_File.Fclose(exit_file);
    Dbms_Output.Put_Line('Arquivo gerado com sucesso.');
    
EXCEPTION
      WHEN UTL_FILE.INVALID_OPERATION THEN
               Dbms_Output.Put_Line('Operação inválida no arquivo.');
               UTL_File.Fclose(exit_file);
      WHEN UTL_FILE.WRITE_ERROR THEN
               Dbms_Output.Put_Line('Erro de gravação no arquivo.');
               UTL_File.Fclose(exit_file);
      WHEN UTL_FILE.INVALID_PATH THEN
               Dbms_Output.Put_Line('Diretório inválido.');
               UTL_File.Fclose(exit_file);
      WHEN UTL_FILE.INVALID_MODE THEN
               Dbms_Output.Put_Line('Modo de acesso inválido.');
               UTL_File.Fclose(exit_file);
      WHEN Others THEN
               Dbms_Output.Put_Line('Problemas na geração do arquivo.');
               UTL_File.Fclose(exit_file);
END EXPORT_NEW;
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663535 is a reply to message #663534] Wed, 07 June 2017 09:28 Go to previous messageGo to next message
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 #663536 is a reply to message #663534] Wed, 07 June 2017 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does posted code run to successful completion?

Just for debugging purposes, I suggest that you eliminate the whole EXCEPTION handler code
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663537 is a reply to message #663535] Wed, 07 June 2017 09:36 Go to previous messageGo to next message
freis_bcf
Messages: 6
Registered: June 2017
Junior Member
Bill B wrote on Wed, 07 June 2017 09:28
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.
It worked!!! Had forgotten to give permission to the folder .. Thank you .. You saved my life!!! Smile Smile Smile
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663538 is a reply to message #663537] Wed, 07 June 2017 09:37 Go to previous messageGo to next message
freis_bcf
Messages: 6
Registered: June 2017
Junior Member
Thanks everyone for the attention.. =D
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663539 is a reply to message #663538] Wed, 07 June 2017 09:38 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
fantastic. Congratulations
Re: ORA-00922: missing or invalid option - PROCEDURE with SPOOL [message #663637 is a reply to message #663539] Mon, 12 June 2017 03:02 Go to previous message
cookiemonster
Messages: 13917
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;
Previous Topic: Trigger to fill foreign key from another table
Next Topic: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view
Goto Forum:
  


Current Time: Fri Mar 29 08:44:24 CDT 2024