1

I've been reading some questions on SO about how to query an Oracle database with a batch script, such as this one.

This command seems to work well to get the result of a single query:

<query>; | sqlplus <username>/<password>@<database>

But can I modify this such that:

  1. A connection to the database and the query are separate commands

  2. I can use this command in a loop. (This command doesn't seem to work well in loops. I keep getting an error that the | character was not recognized.)

A bit of context as to what I'm trying to accomplish:

I have a text file which contains a list of table names and column names in my Oracle database, e.g. the contents of this file would be:

table1, columnA
table1, columnB
table2, columnC
table2, columnD

I want to query the database to look at every table/column pair in this file and return the datatype and data precision.

I don't need help with the actual query. This is the query that gives the results I need:

SELECT DATA_TYPE,DATA_PRECISION FROM ALL_TAB_COLUMNS
   WHERE TABLE_NAME='<tableName>' AND COLUMN_NAME='<columnName>';

And the reason I want to have a separate command for database connection and database query is because, since multiple queries will be called, it makes sense to only connect to the database once, perform all the queries, and then quit the connection.

Something like this seems to be what I want to accomplish, but it isn't working properly. See below my current batch script.

REM Required for dynamic values in for loop
setlocal enabledelayedexpansion

REM For all of the lines in my input text file
for /f "tokens=1,2 delims=, " %%i in (inputFile.txt) do (

   REM Store column name and table name
   set tableName=%%i
   set columnName=%%j

   REM Perform query
   for /f "tokens=1,2 skip=2" %%k in ('SELECT DATA_TYPE,DATA_PRECISION FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''!tableName!'' AND COLUMN_NAME=''!columnName!''; | sqlplus <username>/<password>@<database>') do (

      REM Only care about first result, since it will only return one row
      set dataType=%%k
      set dataPrecision=%%l

      REM Process dataType and dataPrecision as necessary
      REM ...
   )
)
Squashman
  • 13,649
  • 5
  • 27
  • 36
ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51
  • 1
    You have to escape the pipe. `^|` – Squashman Oct 31 '18 at 15:23
  • @Squashman I tried that just now, but I'm getting a separate error now. Now it says that *SELECT is not a recognized command*. – ImaginaryHuman072889 Oct 31 '18 at 16:00
  • Correct. It is not a batch file command, nor is it a Windows command. – Squashman Oct 31 '18 at 16:01
  • Try `( echo "SELECT ... " ^| sqlplus ...)` ? Good luck. – shellter Oct 31 '18 at 16:02
  • @shellter, is correct. And that is the exact syntax in the question you linked to. – Squashman Oct 31 '18 at 16:18
  • 1
    Why wouldn't you just build a file with all those select statements and then run: `sqlplus myuser/mypassword>@mydb@queries.txt` – Squashman Oct 31 '18 at 16:24
  • I hope that helped. One comment to the O.P., A well written question, but a little too long. Read [mcve] and try to apply those principles to your Qs going forward. Good luck to all. – shellter Oct 31 '18 at 16:24
  • @shelter Should `echo "SELECT ... " ^| sqlplus ...` be enclosed in single quotes? I tried both, neither are working. If it is enclosed in single quotes, I am getting some standard text (e.g. "Connected to Oracle" or whatever appears on command prompt when you connect with `sqlplus`) but it is not displaying the result of the query. For now I even tried just a basic query of `SELECT SYSDATE FROM DUAL;` just to simplify the problem for the time being, still not producing query results. – ImaginaryHuman072889 Oct 31 '18 at 16:30
  • @Squashman That's a good idea to build the queries in a text file and then just run the entire text file. I didn't think of this. That would probably be better anyway because that would only require one connection to the database rather than needing to re-establish a connection with each separate query. – ImaginaryHuman072889 Oct 31 '18 at 16:34
  • 1
    Because you have single quotes within your `SELECT` query you need to instruct the `FOR` command to use back quotes for the command it needs to execute. `for /f "usebackq tokens=1,2 skip=2" %%k in (\`SELECT DATA_TYPE,DATA_PRECISION FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''!tableName!'' AND COLUMN_NAME=''!columnName!''; | sqlplus /@\`) do` – Squashman Oct 31 '18 at 17:29
  • @Squashman I just tried the `for` text you provided. It doesn't work for the same reason I experienced earlier. It said that `|` was unexpected. When I changed this to `^|`, it said that `SELECT` was not recognized. When I placed `echo` in front of `SELECT`, the batch script works but only returns standard text letting me know that I successfully connected to Oracle, but doesn't actually display the result of the `SELECT` statement. – ImaginaryHuman072889 Oct 31 '18 at 19:27
  • You sure you need two sets of single quotes around your parameters? I would also change this `"tokens=1,2 skip=2"` to this: `"delims="` just to rule that out as a problem. – Squashman Oct 31 '18 at 19:32
  • @Squashman Using one vs. two single quotes doesn't seem to make a difference. But just to take that out of the equation, here is the exact command I am using: `for /f "tokens=1 delims=" %%a in ('echo SELECT SYSDATE FROM DUAL; ^| sqlplus user/pwd@db') do ( echo %aa )`. This displays that I connected to Oracle, but doesn't display the result of the query. – ImaginaryHuman072889 Nov 01 '18 at 11:14
  • @Squashman But honestly I'm not sure if it matters because your other suggestion of just placing all of the queries into a file and then calling the file is working correctly. – ImaginaryHuman072889 Nov 01 '18 at 11:36
  • @Squashman Also sorry I meant `echo %%a` in my previous comment not `%aa`. Thanks for all your help. – ImaginaryHuman072889 Nov 01 '18 at 14:00

0 Answers0