Home » SQL & PL/SQL » Client Tools » SQL*Plus - simulate "echo -n" (prompt with no newline) (10.2, 11.2, 12.1 - Linux)
SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634449] Tue, 10 March 2015 04:07 Go to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is there a way or a trick to display a message with no newline at the end in SQL*Plus like "echo -n" or "echo \c" in Unix shell?

[Updated on: Wed, 11 March 2015 04:57]

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634456 is a reply to message #634449] Tue, 10 March 2015 04:50 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
Do you mean to the screen or to a spool file, Michel?
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634465 is a reply to message #634456] Tue, 10 March 2015 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, forgot to mention, on screen.
The aim is to have something like:
Prompt "-n" Doing this...
<something is done>
Prompt done
And on the screen I see
Doing this... done

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634470 is a reply to message #634465] Tue, 10 March 2015 06:51 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
It depends on what "<something is done>" is, I guess. How about something like this:
SET FEED OFF
HOST echo -n "Doing this..."
<something is done>
HOST echo "done"

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634476 is a reply to message #634470] Tue, 10 March 2015 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It's worth to test it.
I'll do it and come back, thanks for the idea.

By the way, "<something is done>" is execution of SQL scripts.

[Updated on: Tue, 10 March 2015 07:23]

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634477 is a reply to message #634476] Tue, 10 March 2015 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unfortunately, SQL*Plus adds a new line after the end of host command.

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634482 is a reply to message #634477] Tue, 10 March 2015 08:41 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
Yes, I've just tried it myself and you are correct. Looks like it isn't possible with SQL*Plus.
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634483 is a reply to message #634482] Tue, 10 March 2015 08:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
I tried it too. No clue either. Even "-n" with HOST is just considered as a message to be displayed. I tried both ways, executing it as a script and directly on command line.

Now I am curious to see if Michel finds a way to do it.
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634484 is a reply to message #634483] Tue, 10 March 2015 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Even "-n" with HOST is just considered as a message to be displayed.


Not with my bash/Linux, maybe yours that accepts "\c" instead of "-n".

Quote:
Now I am curious to see if Michel finds a way to do it.


Believe me, I have searched before create this topic. Wink

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634485 is a reply to message #634483] Tue, 10 March 2015 09:09 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
Even "-n" with HOST is just considered as a message to be displayed.

HOST accepts no parameters. As I understand it, Michel used "HOST -n" as an example - he was comparing it to "echo -n".
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634487 is a reply to message #634484] Tue, 10 March 2015 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also it does not work using dbms_output.put (without new_line or put_line) because it seems what is inside the buffer and not ended with a new line is ignored.

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634493 is a reply to message #634487] Tue, 10 March 2015 15:59 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about this?

P1.SQL is doing something; you call it from the main script:
begin
  dbms_lock.sleep(5);
end;
/


The main .SQL script looks like this:
host cls
prompt Doing this ...
set termout off
@p1.sql
host cls
set termout on
prompt
prompt Doing this ... done!
prompt


When you run it, it
  • clears screen (so that you'd be sure that message is always displayed on the same place)
  • displays the first part of the message
  • calls a script which actually does something smart
  • clears the screen again
  • displays "expanded" message (actually, adds "done!"), but end user doesn't know that

It means that screen looks like
Doing this ...
at the beginning, and
Doing this ... done!

SQL>
at the end.

What do you say?
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634495 is a reply to message #634493] Tue, 10 March 2015 16:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Could be a solution if you have only one or a couple of called scripts.
In my case there are a bunch of them and they depend on parameters, environment, execution of previous scripts in the global one.
So currently I have something like:
executing X1, log file is Y1...
executing X2, log file is Y2...
executing X5, log file is Y5...
...
I want to add a short feedback from the called scripts something like:
executing X1, log file is Y1... <done  or W1 warnings, E1 errors or "condition Z1 is met"...>
executing X2, log file is Y2... <done  or W2 warnings, E2 errors or "condition Z2 is met"...>
executing X5, log file is Y5... <done  or W5 warnings, E5 errors or "condition Z5 is met"...>
...
which will allow the operator to see the evolution of the scripts, if he has to investigate in some logs and/or why the next is X5 and not X3... and if he has to stop or undo what some scripts have done in case of errors.
Some scripts take a few seconds, others may last an hour or more, so the operator can't always be there to follow all the messages that will appear at the top of his screen.
But this is an idea, I will check if I can use some control characters to move the cursor one line up (instead of clearing the screen) and prompt the whole message.

Unfortunately, I will be at this client site only next Monday to test if this is possible within SQL*Plus in his environment, I know it is possible at shell level as I already did it; the terminal they use accepts ANSI escape codes.

Thanks for the idea.

[Updated on: Tue, 10 March 2015 16:33]

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634529 is a reply to message #634495] Wed, 11 March 2015 03:53 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
As a "good enough" solution, would it really be that bad to have, for example, ...

executing X1, log file is Y1... 
                          .... done
executing X2, log file is Y2... 
                          .... done
executing X5, log file is Y5... 
                          .... done


Instead of having them on one line?
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634531 is a reply to message #634529] Wed, 11 March 2015 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It could be but I wanted to make it look smart. Smile

I will test the idea I mentioned in my previous post; there is a good chance it works but I can't test it now not having the environment to do so.

For those who can, the escape code to move the cursor one line up is "<esc>[A", <esc> is the escape character: \033. This works in shell script but I don't know if you can use it in SQL*Plus PROMPT command.
The test script could then be:
Prompt waiting...
exec dbms_lock.sleep(3);
Prompt <esc>[Awaiting... done

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634536 is a reply to message #634449] Wed, 11 March 2015 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I also posted this question in DBA-Village.
Another way raised, the script could be something like (which I can't test for the moment):
Prompt waiting...
exec dbms_lock.sleep(3);
host tput cuu1
Prompt waiting... done

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634538 is a reply to message #634536] Wed, 11 March 2015 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bruno Vroman has just posted a working solution using tput (and echo instead of SQL*Plus prompt command).

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634541 is a reply to message #634538] Wed, 11 March 2015 04:55 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, TPUT is not recognized on Windows (although, I'm sure, works fine on Unix).
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634543 is a reply to message #634541] Wed, 11 March 2015 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, I first searched for a generic, OS-independent solution but, for the moment, I'm happy with a Linux/Unix one.
If I find one for Windows I will post it.

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634737 is a reply to message #634543] Sat, 14 March 2015 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Littlefoot, it works well on Windows and using ANSI escape codes as I show above, if you use ANSICON instead of CMD.EXE.
More specifically, once ANSICON installed, you create a shortcut on your desktop, for instance, and put "%SystemRoot%\system32\cmd.exe /C ansicon" in the command line (or you can set it permanently using cmd.exe Autorun registry value).

Note: I tested it against virus, malware, adware, found nothing but make your own test.

[Updated on: Sat, 14 March 2015 05:16]

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634760 is a reply to message #634737] Sun, 15 March 2015 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Michel!
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634772 is a reply to message #634760] Sun, 15 March 2015 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can also play with the colors.
With the following script (also attached, before each [ there is the escape character which may not be displayed on your screen):
set pages 0 feedback off head off  serveroutput on
Prompt Sleeping...
exec dbms_lock.sleep(5);
Prompt Sleeping... not enough
Prompt Sleeping again...
exec dbms_lock.sleep(5);
Prompt Sleeping again... done
exec dbms_lock.sleep(5);
Prompt Now I need a coffee!

I get:
/forum/fa/12549/0/
/forum/fa/12551/0/
/forum/fa/12552/0/
/forum/fa/12550/0/

Smile
  • Attachment: OraFAQ1.jpg
    (Size: 3.61KB, Downloaded 243 times)
  • Attachment: OraFAQ4.jpg
    (Size: 8.75KB, Downloaded 248 times)
  • Attachment: OraFAQ2.jpg
    (Size: 6.30KB, Downloaded 239 times)
  • Attachment: OraFAQ3.jpg
    (Size: 6.84KB, Downloaded 242 times)
  • Attachment: t1.sql
    (Size: 0.30KB, Downloaded 115 times)
Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634780 is a reply to message #634772] Mon, 16 March 2015 01:06 Go to previous message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cool!
Previous Topic: show error when Query table
Next Topic: Spool Command
Goto Forum:
  


Current Time: Sat Oct 20 18:15:37 CDT 2018