1

I need to export data to a csv file in a scheduler job. Here are the commands:

SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE;
SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;
SPOOL OFF;

When I make the "type of job" a "PL/SQL Block", it fails and I get the following error:

"ORA-06550: line 1, column 764:
PLS-00103: Encountered the symbol "\\AServerName\Output\RMS_DataFileSizes.csv" when expecting one of the following:

   := . ( @ % ;
ORA-06550: line 17, column 7:
PLS-00103: Encountered the symbol "OFF" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "OFF" to continue.

What am I doing wrong?

  • Great idea, but I'd like to keep this as generic as possible as someone else may view or edit (eek!) what I have done. What I did end up doing is taking the entire code and putting it into a sql script file. Then I run it as an external (sqlplus) script. It actually dumps the output into an external file (export.csv). However, no matter what I do, whether using SET SQLFORMAT CSV or adding the /*csv*/ shortcut, the output is never CSV. An other ideas? – Darrell Oct 31 at 19:48
0

That is not how you do that. A PLSQL_BLOCK type job does not accept such commands. Use a SQL_SCRIPT type job. Example:

begin
  dbms_credential.create_credential(
    credential_name => 'oracle_user',
    username        => 'oracle',
    password        => 'Oracle123'
  );
end;
/

begin
  dbms_scheduler.create_job(
    job_name        => 'my_script_job',
    job_type        => 'sql_script',
    job_action      => 'conn bp/bp
set markup csv on
spool "/home/oracle/output.txt"
select tablespace_name, file_id, bytes/1024/1024 as size_mb from dba_data_files;
spool off',
    credential_name => 'oracle_user',
    enabled         => true,
    auto_drop       => false
  );
end;
/

$ cat /home/oracle/output.txt
SQL> select tablespace_name, file_id, bytes/1024/1024 as size_mb from dba_data_files;

"TABLESPACE_NAME","FILE_ID","SIZE_MB"
"SYSTEM",1,700
"SYSAUX",2,870
"UNDOTBS1",3,295
"USERS",4,112.5
"TEST",5,100

Keep in mind, SET MARKUP CSV ON is a 12.2 feature. It will not work with 12.1. /*csv*/ is a SQL Developer feature, that will also not work.

  • Thanks. The MARKUP hint fixed it. See my further comments below. – Darrell Nov 1 at 14:28
0

How about a slightly different approach?

Put that code into a .SQL file, let's call it p.sql:

spool p.txt

SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;

spool off
exit

I'm on MS Windows, so - create a .BAT file; let's call it p.bat:

sqlplus sys/pwd as sysdba @p.sql

Finally, use your operating system scheduling program (Task Manager on my Windows) and schedule p.bat to run as often as you want, e.g.

  • let it run daily at 22:00
  • action = Start a program (navigate to p.bat)
  • optionally (I'd suggest you to use it), set "Start in" directory

Should work; at least, works for me.

  • This is exactly what I ended up (sorta) doing. But, instead of running it in Windows Task Scheduler, I ran it out of the job as an external script. This was a big help, but the MARKUP hint that @Balazs-Papp handed out was exactly what I was missing. If I could have marked both as helpful answers I would have. – Darrell Nov 1 at 14:27
  • No problem, I'm glad you made it work. – Littlefoot Nov 1 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.