How to Redirect Input to SQL*Plus without Exiting using Windows PowerShell-CodePudding

Using Windows PowerShell, how do I redirect input into sqlplus (non-interactive stdin mode) such that once the redirected input is complete sqlplus is left open in interactive stdin mode without SQL*Plus exiting?

It appears as though the redirected input is issuing an implicit exit which SQL*Plus is processing.

Using Oracle 19c Enterprise Edition, Oracle InstantClient 19c 64-bit, Windows PowerShell Desktop 5.1.19041.1320, Microsoft Windows 10.0 build 19042.

Thank you in advance.

dev.sql

set echo on
set serveroutput on
exec dbms_output.put_line('hello world');

No redirection executes start script dev.sql and does not exit (notice the SQL> prompt as the last line instead of PS C:\Users\my>).

PS C:\Users\my> sqlplus my_username/my_password@"my_host:my_port/my_service" "@dev.sql"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 14:44:48 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 14:40:03 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> set serveroutput on
SQL> exec dbms_output.put_line('hello world');
hello world

PL/SQL procedure successfully completed.

SQL>

However, when redirecting the input, then SQL*Plus automatically exits (notice the PS C:\Users\my> prompt as the last line instead of SQL>). No matter how I redirect the input into sqlplus, SQL*Plus automatically exits.

Redirection with Get-Content.

PS C:\Users\my> Get-Content dev14.sql | sqlplus my_username/my_password@"my_host:my_port/my_service"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 14:48:42 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 14:44:49 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> SQL> SQL> hello world

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS C:\Users\my>

Redirection with here-string.

PS C:\Users\my> @"
>> set echo on
>> set serveroutput on
>> exec dbms_output.put_line('hello world');
>> "@ | sqlplus my_username/my_password@"my_host:my_port/my_service"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 14:50:14 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 14:48:44 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> SQL> SQL> hello world

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS C:\Users\my>

Single line redirection.

PS C:\Users\my> "select * from dual;" | sqlplus my_username/my_password@"my_host:my_port/my_service"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 15:03:10 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 15:00:34 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL>
D
-
X

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS C:\Users\my>

Redirect using Start-Process.

PS C:\Users\my> Start-Process sqlplus my_username/my_password@"my_host:my_port/my_service" -RedirectStandardInput dev.sql -NoNewWindow -Wait

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 15:06:20 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 15:06:07 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> SQL> SQL> hello world

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS C:\Users\my>

Redirecting using batch, still causes SQL*Plus to exit.

dev.bat

(
echo set echo on
echo set serveroutput on
echo exec dbms_output.put_line('hello world'^^^);
) | sqlplus my_username/my_password@"my_host:my_port/my_service"

PS C:\Users\my> c:dev.bat

C:\Users\my>(
echo set echo on
 echo set serveroutput on
 echo exec dbms_output.put_line('hello world'^);
)  | sqlplus my_username/my_password@"my_host:my_port/my_service"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 15:57:51 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 15:57:38 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> SQL> SQL> hello world

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS C:\Users\my>

Note: I tried using -noexit but that is an argument to executing powershell itself not sqlplus.

It is like sqlplus says "okay, no more stdin, I'm done". Maybe there is a way to direct stdin back to they keyboard once pipped input reaches end-of-file, so sqlplus is left waiting for the next keyboard input?

Note: It is also odd how we do not see the set serveroutput on getting echoed.

Thank you.

CodePudding user response：

Sqlplus automatically exits when its' input pipe (stdin) is closed. So the only option is to do not close pipe: you can write a program that starts sqlplus and feeds it's stdin and closes it only when you want.

I don't know why do you want to leave it open in non-interactive mode. For me it's much better to pipe to file and then execute it.

Don't know will it help you, but I have a couple of workarounds how to leave it open for some time: use host command. For example the following command will leave sqlplus opened for 15 seconds:

"host powershell Start-Sleep -s 15"  | sqlplus  user/pass@//host:port/service

Note that you can't do anything with interactive stdin within it, like

"host timeout /t 10" | sqlplus  user/pass@//host:port/service

because

SQL> ERROR: Input redirection is not supported, exiting the process immediately.