0

i have this code into my sql file :

IF ((date1-date2)*60*24) > nb_minutes
THEN
    DBMS_OUTPUT.PUT_LINE('You have passed the threshold');
END IF;

I want to write the message of DBMS_OUTPUT.PUT_LINE into a log file, for exemple /tmp/test.log. I call my sql file from a file.sh

josef
  • 89
  • 2
  • 9
  • " I call my sql file from a file.sh" - presumably via SQLPLUS. Why not just redirect the stdio from SQLPLUS to your log file? (`sqlplus user:pass@db yourlog.txt`) – symcbean Feb 23 '17 at 14:36
  • I have a crontab where i made my `file.sh` and into `file.sh` i call `file.sql`, i want to put `You have passed the threshold` in `file.log` how i can do it ? or can you explain me more ? – josef Feb 23 '17 at 14:39
  • The standard approach with SQL*Plus is to spool. Take a look at this question, http://stackoverflow.com/questions/8590826/how-to-create-a-oracle-sql-script-spool-file. Ollie's answer does a good job of displaying how to do this. – Patrick Bacon Feb 23 '17 at 14:55
  • @PatrickBacon it does not work with SQL Developper ? because i tried it but it does not work – josef Feb 23 '17 at 15:16
  • SQL*Plus is specifically designed for robust spooling (for that matter, also SQLCl). Coding is often about using the right tool for the task. If you really want to try spooling with SQL Developer, this post looks interesting, http://stackoverflow.com/questions/19033943/spool-command-do-not-output-sql-statement-to-file – Patrick Bacon Feb 23 '17 at 15:30
  • @PatrickBacon i tried this in my sql developper `set echo off SPOOL Bibliothèques\Documents\toto.txt SELECT SYSDATE FROM DUAL; SPOOL OFF` but the `toto.txt` does not created – josef Feb 23 '17 at 15:48
  • When I follow Alex's instructions of placing `set echo off spool c:\test.csv select /*csv*/ username, user_id, created from all_users; spool off;` in a file, test.sql and call that file from SQL Developer, the test.csv file is created. – Patrick Bacon Feb 23 '17 at 16:59
  • @PatrickBacon i tried it in SQLPLUS, it work! not instead of `SELECT SYSDATE FROM DUAL;` i have a procedure. I made my procedure in `fichier.sql` when i called my procedure with `@fichier.sql` the SQLPLUS return the next ligne of my end of my file! i don't understand why – josef Feb 23 '17 at 17:05

0 Answers0