Why does using a pipe not work in powershell?

Question

I'm trying to execute the following line:

exit | sqlplus username/password@sid @test.sql

Works great from cmd but in powershell I get "An empty pipe element is not permitted"

Why is this and how do I fix it?

Answer 1

Well, the exit command has no output, so there is nothing to pass down the pipe, hence you get an empty pipeline element. So while there is nothing to pipe into the other command the question is whether it actually should be executed at all.

Also, shell-builtins from cmd, such as exit, dir, etc. may work differently in Powershell. The Aliases are just there to help getting accustomed.

And fixing this is probably easy. Because there is nothing to pass down the pipeline your statement has no real meaning. You probably want to run

sqlplus username/password@sid @test.sql
exit

ETA: As your comment indicates an entirely different problem. You copied the command line from this answer wrong. If you want to send the text "exit" to the command later in the pipeline, you would need to output it in some way. This can be done in both cmd and Powershell with echo. So you shouldn't leave out the echo here:

echo exit | sqlplus username/password@sid @test.sql

Alternatively you can send "exit" directly as a string down the pipeline:

"exit" | sqlplus username/password@sid @test.sql

Answer 2

Strange question. According to the linked SO post and the Oracle documentation, it sounds like you're simply trying to return control to the script environment after SQLPlus has finished reading the file.

Unlike in CMD, 'exit' is not a language keyword; it's a directive to the script environment. (You can see for yourself by running 'gcm exit' -- won't find anything). So I'm not surprised this won't work.

Why not append an EOF character to the end of the input stream like the Oracle documentation suggests? On Windows this would be ^Z (Ctrl+Z, ASCII 0x1A). Or you can insert the text 'exit' if you really want.

Do so by modifying the file on disk, or reading the file into a stream and using the | operator -- whichever makes more sense for your situation.

edit: adding some example scripts per request in the comments

# add EOF to file on disk
[char]0x1a >> test.sql
sqlplus username/password@sid @test.sql

# add 'exit' to file on disk
'exit' >> test.sql
sqlplus username/password@sid @test.sql

# add 'exit' in-memory
# assumes sqlplus reads from stdin as shown in other examples; don't have Oracle to test
(gc test.sql) + 'exit' | sqlplus username/password@sid

# wrapper function
function Invoke-SqlPlus($file) { (gc $file) + 'exit' | sqlplus username/password@sid }
new-alias sql Invoke-SqlPlus
# usage
sql test.sql