Home > Blockchain >  How to Redirect Input to SQL*Plus without Exiting using Windows PowerShell
How to Redirect Input to SQL*Plus without Exiting using Windows PowerShell

Time:02-17

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.

  • Related