0

I have a requirement where I need to run a .sql file from a .bat file. The output of the .sql file should be redirected to a .txt file. I'm able to do this however the sql file is printing column names underlined by '_' and at the end of the file it is printing '3 rows selected'. I don't want the column names and '3 rows selected' to appear in the output file. Can you please point what am I doing wrong here? Below are the details of my code. Note that my sql has to run in oracle db using sqlplus.

sql_file.sql

SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0
SET UNDERLINE off
select 'This '|| emp_name || ' belongs to ' || dept_name || ' department' as emp_data from emplyoee;

exit

batch_file.bat

@echo off

sqlplus -s -l scott/tiger@myoracledb @"C:\Data Files\sql_file.sql" >> "C:\Data Files\output_file.txt"

I want the output_file.txt to look like

This Charles employee belongs to Finance department
This Chris employee belongs to Sales department
This John employee belongs to Engineering department

However my code is producing the output as 

emp_data
--------------------------------------------------
This Charles employee belongs to Finance department
This Chris employee belongs to Sales department
This John employee belongs to Engineering department

3 rows selected
NeedBasedLearner
  • 43
  • 2
  • 9
  • Why you could probably use batch file code to strip the header and trailer records, I believe you just change your query so that it does not output that information. You should change your question tags to include something like sql query. My DB people output queries for me all the time and it never outputs header and trailer information. – Squashman Mar 08 '17 at 14:19
  • that's weird - i tested your sql script (including the one-liner of sets) and it's working for me. what version are you using? (I tested it on 11.2.0.1) – Zohar Elkayam Mar 08 '17 at 14:22
  • This kind of looks like your problem: http://stackoverflow.com/questions/28689851/remove-column-header-into-the-ouput-text-file – Squashman Mar 08 '17 at 14:27
  • @Zohar - 11.2.0.1 is the ora DB version? My DB is oracle 12.1.0.2.0 EE. – NeedBasedLearner Mar 08 '17 at 14:32
  • @Sqaushman - I read that link you sent earlier but their solution doesn't work for me. – NeedBasedLearner Mar 08 '17 at 14:33
  • @Vicky, i tested it on 12.1.0.2 ee as well - and it's working as well. I can post my scripts if you'd like but they're pretty much the same as yours. did you try to run the script using regular connection and see if it outputs the data without the headings? – Zohar Elkayam Mar 08 '17 at 14:57
  • I realise it is no consolation but I have just performed the same code on Oracle 11.2.0.4 and I get the results as you want them. Are you running this from a Windows command line or from some other tool. – BriteSponge Mar 08 '17 at 15:00
  • @Zohar - I'm running the batch file from ODI 11g. Is that the problem? Please provide your scripts if you don't mind. – NeedBasedLearner Mar 08 '17 at 15:04
  • @Vicky, that is the answer to your question. It basically says you cannot do what you are trying to do. – Squashman Mar 08 '17 at 15:08
  • @Squashman - Its hard to believe that ODI is the culprit. – NeedBasedLearner Mar 08 '17 at 15:15

1 Answers1

0

I am personally not used to the SET commands on one line... maybe try like this:

SET HEADING OFF 
SET FEEDBACK OFF 
SET ECHO OFF 
SET PAGESIZE 0
Randy
  • 16,480
  • 1
  • 37
  • 55
  • I tried this but it didn't work. What I'm feeling is these set options are simply being ignored. If I run the content of the sql_file.sql in sql developer it produces the out the way I want. But my requirement is to run this from a batch file. – NeedBasedLearner Mar 08 '17 at 14:26
  • This link kind of says this idea of ignoring the set commands. What I'm not understanding in this post is - would the code be part of batch file or .sql file. More over they have given example of shell script (unix/linux) not batch script (windows). http://www.orafaq.com/forum/t/69493/ – NeedBasedLearner Mar 08 '17 at 14:39
  • Yes - these are SQL PLus commands - so they go in a SQL file that is to be run by SQLPlus... – Randy Mar 09 '17 at 13:47