Ssh remote connection and not lose prompt

...so I can execute local sql scripts. Can that type of connection be made with ssh?

Short answer: No. For the sqlplus program to see your local scripts, the entire program needs to be running locally on your machine – which directly contradicts the usage of SSH, whose purpose is to run commands on the remote server instead of locally.

Longer answer: There are two possible ways to achieve this:

• Install and run sqlplus locally, on your machine, but use SSH for TCP connection forwarding so that the local sqlplus could connect to the remote Oracle server.

  Maybe you don't even need SSH for this at all – like if you're already working through a VPN, then perhaps your PC running sqlplus can just directly connect to the Oracle SQL service over network. But if a direct connection is not possible, then TCP tunneling using ssh -L would still make it work, e.g.:

  1. Run ssh in background with one -L tunnel.

     $ ssh ora_server -f -N -M -S ~/.ssh/ora_server.mux -L 1521:localhost:1521
     

     (This listens on port 1521 of the client and tunnels it to "localhost:1521" of the server.)

  2. Connect to the local side of the tunnel (sorry, I couldn't figure out which of the 4-5 different syntaxen I found on Google is correct).

     $ sqlplus @localhost:1521
     

• Or, run sqlplus remotely over SSH as you're currently doing, but additionally use SSH's reverse tunnel (-R option) so that you could use sshfs from the server back to your PC (similar to how Windows' Remote Desktop lets you access local drives from the remote session), and that way access your local files from the remote shell.

  I would not recommend this at all in production systems; it's a bit messy and probably a security risk (more so than RDP since there's no already-open back channel; the server literally has to SSH back to the client). But it's an option and it's probably fine in some situations.