OraFAQ Forum: Client Tools » SQL*Plus - simulate "echo -n" (prompt with no newline)

Home » Other » Client Tools » SQL*Plus - simulate "echo -n" (prompt with no newline) (10.2, 11.2, 12.1 - Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634449]

Tue, 10 March 2015 04:07

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Do you mean to the screen or to a spool file, Michel?

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634465 is a reply to message #634456]

Tue, 10 March 2015 06:28

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634470 is a reply to message #634465]

Tue, 10 March 2015 06:51

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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"

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634476 is a reply to message #634470]

Tue, 10 March 2015 07:22

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634482 is a reply to message #634477]

Tue, 10 March 2015 08:41

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Yes, I've just tried it myself and you are correct. Looks like it isn't possible with SQL*Plus.

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634483 is a reply to message #634482]

Tue, 10 March 2015 08:56

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634484 is a reply to message #634483]

Tue, 10 March 2015 09:05

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634485 is a reply to message #634483]

Tue, 10 March 2015 09:09

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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".

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634487 is a reply to message #634484]

Tue, 10 March 2015 12:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634493 is a reply to message #634487]

Tue, 10 March 2015 15:59

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634495 is a reply to message #634493]

Tue, 10 March 2015 16:28

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

ThomasG
Messages: 3212
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?

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634531 is a reply to message #634529]

Wed, 11 March 2015 04:04

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634536 is a reply to message #634449]

Wed, 11 March 2015 04:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634538 is a reply to message #634536]

Wed, 11 March 2015 04:46

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634541 is a reply to message #634538]

Wed, 11 March 2015 04:55

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634543 is a reply to message #634541]

Wed, 11 March 2015 04:59

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634737 is a reply to message #634543]

Sat, 14 March 2015 05:15

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Thank you, Michel!

Report message to a moderator

Re: SQL*Plus - simulate "echo -n" (prompt with no newline) [message #634772 is a reply to message #634760]

Sun, 15 March 2015 11:26

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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 [ASleeping... [33;1mnot enough[0m
Prompt Sleeping again...
exec dbms_lock.sleep(5);
Prompt [ASleeping again... [32;1mdone[0m
exec dbms_lock.sleep(5);
Prompt [31;1mNow I need a coffee![0m

I get: