Must run a SQLPlus query in bat file, can't get query to append the query results a txt file us-CodePudding

I know this is something minor I'm screwing up. I didn't write the SQL -it's as is - just the .BAT file. I have to run it using a .BAT file as there's more stuff that happens afterwards.

I have a query. It's supposed to insert a bunch of values from different tables into a temp table, then return all four fields separated by commas, and no header row. It works like it's supposed to. I tried to anonymize it, so it might look weird.

SQL File Name: c:\Directory\BA_ExportAll.sql

THESE TWO LINES are at the top of the query and I don't know why they aren't properly appearing to be in the sql query itself.

START /data/admin/connect.sql

START today.sql

  INSERT INTO
          My_Table_TMP
  SELECT
          'A',
          'Sequence',
          AUX.P_ID,
          NULL
  FROM
          (
          SELECT
                  P_ID
          FROM
                  Table1
          WHERE
                  feedDate = (SELECT MAX(feedDate) FROM Table1)
          MINUS
          SELECT
                  P_ID
          FROM
                  Table2
          WHERE
                  objstate NOT LIke 'C%'
          ) AUX
  ;
     --Then a bunch of other queries that build the temp table  
  
  COMMIT;
  
  
  SET COLSEP ''
  SET SPACE 0
  SET LIN 150
  SET PAGES 0
  SET VERIFY OFF
  SET FEEDBACK OFF
  SET ECHO OFF;
  
  COLUMN Value FORMAT A11
  
  SPOOL segment_after.&today 
  
  SELECT
          flag || ',',
          segment || ',',
          value || ',' as value,
          '"' || description || '"'
  FROM
          My_Table_TMP
  ;
  SPOOL OFF;
  EXIT;

So far, so good.

Now, the .BAT file.

cd C:\Directory\

sqlplus /nolog @BA_ExportAll.sql

ECHO c:\Directory\BA_ExportAll.sql >> "c:\Directory\BA_HeaderTest.txt"

The .BAT file will eventually kick off an email script and some other stuff.

This just appends the actual line "c:\Directory\BA_ExportAll.sql" into the "c:\Directory\BA_HeaderTest.txt" text file. I have googled and checked here, obviously, over and over and can't find any similar questions. I'm assuming it's completely simple!!

Thanks, all!

CodePudding user response：

If I understood you correctly, you'd like to append result of that SELECT statement into a file which already contains some data.

If that's so, then it is the SPOOL command you should modify (the last ECHO won't do any good):

SPOOL segment_after.&today APPEND

i.e.

  ...
  SET FEEDBACK OFF
  SET ECHO OFF;
  
  COLUMN Value FORMAT A11
  
  SPOOL segment_after.&today APPEND 
  
  SELECT ...
  FROM ...;
  SPOOL OFF;
  EXIT;

CodePudding user response：

sqlplus /nolog @BA_ExportAll.sql >> "c:\Directory\BA_HeaderTest.txt"

should append any console output to the text file.

Your echo line merely appends the literal c:\Directory\BA_ExportAll.sql to the text file

TYPE c:\Directory\BA_ExportAll.sql >> "c:\Directory\BA_HeaderTest.txt"

would append the contents of the .sql file to the .txt file.