0

I was trying to create a procedure within one of the Oracle DBs. Procedure is intended for my colleagues, so that everybody could export the result of the query in a csv-file.

I was using "spool" commands. When running the code between BEGIN and END separately in Oracle SQL Developer (running as script, F5) - it worked, and created the file locally.

Then I tried to put it in the procedure, and that is where issue began. Some compilation errors come out. Most of them have code "PLS-00103". I tried searching for it, but not successful so far. I even tried EXECUTE IMMEDIATE statement, putting some script blocks inside it - not working so far.

I will appreciate any hints/help on approaching my situation.

P.s. one of my first attempts is below:

CREATE OR REPLACE PROCEDURE export_to_csv (SOURCE IN VARCHAR2, EXPORT_PATH IN VARCHAR2) is
BEGIN
   spool on;
   set feedback off;
   set heading off; ​ ​ ​
   set sqlformat csv;
   spool EXPORT_PATH;

   select * from SOURCE; 
   spool off;
END export_to_csv;
Anton FromButovo
  • 29
  • 3
  • PL/SQL runs in the database and has no output stream. That’s why it has no `spool` command in its [syntax](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/toc.htm) and why `select` requires an `into` clause. – William Robertson Dec 03 '19 at 22:55

1 Answers1

2

Nope, that won't work. SPOOL, as well as all SET commands you use, are SQL*Plus.

Stored procedure is PL/SQL, so - if you want to do it from there, you'll have to use UTL_FILE package. The result will be on the database server, not your local PC.

Alternatively:

  • put those SPOOL, SET, SELECT, ... commands into a .SQL file on the database server
  • create a database job using DBMS_SCHEDULER package which is capable of running operating system files. On MS Windows, you'd call CMD which establishes connection to the database using SQLPLUS executable and calls your .SQL script (using @)
  • just like in the previous option, the result will be on the database server, not on a local PC

See which option you prefer.

Maybe it would be simpler to create that .SQL file and give it to all your colleagues who'd then run it on their PCs and get the result locally.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57