Batch file pass xml file contents to sqlplus

Question

I have XML files that I want to upload into an Oracle DB through a Batch file process using sqlplus. I am looping through my XML file like this:

for %%f in (%FILE%) do (
call :load_file %%f
)

%FILE% may be an issue as well, not 100% sure. This variable is being set like:

set FILE=%CURRENT_DIRECTORY%\File1_*.xml

..And should be pulling any files that match the pattern.

In :load_file I need the actual file contents (as a string) to pass to sqlplus. I have the following where %1 is the file name itself. I actually need the XML file contents to be passed to my sqlplus command so the contents can actually be inserted into the Oracle DB.

:load_file
set FILECONTENTS=?    

sqlplus -S %DB_USER%/%DB_PASSWORD%@%DB_SID% @insert_xml_into_db.sql %1 %FILECONTENTS%

I may be looking/thinking about this wrong as well. Any suggestions would be appreciated. Is there an easier way?

That is not going to work. You cannot do that. See this for a proper solution - docs.oracle.com/database/121/ADXDB/xdb25loa.htm#ADXDB2900 — OldProgrammer
That's sad. Is there no way to at least read the file line by line into a batch variable, then pass that variable through sqlplus? — Adam

Tenzin Tenzin · Accepted Answer · 2017-10-11T10:00:56

I don't think that reading in a XML file as a whole would be a good idea, but since you are also asking on how to read in lines from a file to an sql server. How about this example:

File (Dark.txt):

SELECT ('ABC') Row1 FROM Dual;
SELECT ('DEF') Row2 FROM Dual;

Batch script (Dark.bat):

@echo off

SET fileA=Dark.txt
SET USER=userName
SET PASSWORD=myPassword
SET DATABASE=myDatabase

for /f "tokens=*" %%a in (%fileA%) do (
    echo %%a | sqlplus %USER%/%PASSWORD%@%DATABASE%
)

pause

Batch file pass xml file contents to sqlplus

1 Answers