oracle spool script works in cmd.exe, but hangs when called from code

Question

I'm trying to create oracle data dump files programmatically, using Oracle's SQLcl. The script looks like:

SET SQLFORMAT XML

SPOOL 'ABC.XML'
SELECT * FROM ABC;
SPOOL OFF

SPOOL 'XYZ.XML'
SELECT * FROM XYZ;
SPOOL OFF

//MORE TABLES BELOW

The command looks like:

exit | "path\to\sqlcl\folder\bin\sql.exe" username/password@connection_string @"path\to\data\dump\script\datadumpscript.sql"

By executing the command in CMD (windows 10), it works just fine.

The code executing logic is from this thread, converted to vb.net:

Private Shared Sub ExecuteCommand(ByVal command As String)
    Dim exitCode As Integer
    Dim processInfo As ProcessStartInfo
    Dim process As Process
    processInfo = New ProcessStartInfo("cmd.exe", "/c """ & command & """")
    processInfo.CreateNoWindow = True
    processInfo.UseShellExecute = False
    processInfo.RedirectStandardError = True
    processInfo.RedirectStandardOutput = True
    process = Process.Start(processInfo)
    process.WaitForExit()
    Dim output As String = process.StandardOutput.ReadToEnd()
    Dim [error] As String = process.StandardError.ReadToEnd()
    exitCode = process.ExitCode
    System.Diagnostics.Debug.WriteLine("output>>" & (If(String.IsNullOrEmpty(output), "(none)", output)))
    System.Diagnostics.Debug.WriteLine("error>>" & (If(String.IsNullOrEmpty([error]), "(none)", [error])))
    System.Diagnostics.Debug.WriteLine("ExitCode: " & exitCode.ToString(), "ExecuteCommand")
    process.Close()
End Sub

When calling from code, by passing in the command above, the program does go to "process.WaitForExit()" and blocks, but only the first file in the spool script gets created with size of 0, the file gets locked by JAVA binary, and nothing happens after that. The exporting process doesn't seem to be running.

Any suggestion is appreciated. Thank you.

You might try the asynchronous logging option mentioned in that thread you linked? Or try a `WriteLine(processInfo.Arguments)` to make sure the quoting is working properly... — kfinity, Jan 07 '21 at 15:54
Are you an Admin? VS will not default to Admin privileges. If you are running inside VS you must right click the VS shortcut and select Run As Admin. — jdweng, Jan 07 '21 at 16:53

score 0 · Answer 1 · answered Jan 07 '21 at 17:03

After tweaking the parameters, I found when set processInfo.UseShellExecute = True fixed the no-running issue. However in this way a console window is shown even with processInfo.CreateNoWindow = True.

I'm looking for a way to hide the console window but if I can't it is still acceptable.

score 0 · Answer 2 · answered Jan 07 '21 at 17:07

Creating a stored procedure/anonymous PL/SQL block that uses UTL_FILE utility to dump the data from table to file(s) will be an alternative.Script below is a basic one to pull data from one column of a table, but can be extended to more than one or all columns.

DECLARE
  fhandle  utl_file.file_type;

BEGIN 

// File1
 BEGIN
  fhandle := utl_file.fopen(
                '/user/home'     -- Folder location or an Oracle directory
               , 'filename1.XML' -- File name
               , 'w' -- writemode Mode 
                  );
 
   FOR V1 IN (Select colA from TableA)
   LOOP 
    utl_file.put_line(fhandle, v1.COLA);
   END LOOP;
   utl_file.fclose(fhandle);
 exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE 
                      || ' - ' || SQLERRM);
    raise;
 end;

// File 2
BEGIN

  fhandle := utl_file.fopen(
                '/user/home'     -- Folder location or an Oracle directory
               , 'filename2.XML' -- File name
               , 'w' -- Write Mode 
                  );
 
   FOR V1 IN (Select colA from TableA)
   LOOP 
    utl_file.put_line(fhandle,v1.COLA);
   END LOOP;
   utl_file.fclose(fhandle);
 exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE 
                      || ' - ' || SQLERRM);
    raise;
 end;

END;

oracle spool script works in cmd.exe, but hangs when called from code

2 Answers2