Re: Is it possible to make the SQL*Plus prompt with "newline" character ?

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<4028e2e9$0$7064$ed9e5944_at_reading.news.pipex.net>...

> SQL>define prompt ='not connected'
> SQL>column x new_value prompt
> SQL>set termout off
> SQL>select global_name||chr(10)||'>' x from global_name;
> SQL>set sqlprompt '&&prompt'

> DBNAME_REMOVED
> >select 1 from dual;
>
> 1
> ----------
> 1
>
> Note the caveat (pre 10g) that if you put this in glogin.sql then issue conn
> dbname/pass_at_db the script doesn't get reexecuted and you end up thinking you
> are on test instead of production or whatever. Apparently 10g fixes this
> behaviour, but I have just hosed my linux box so can't verify this.

SQL*Plus 10g does re-read the glogin.sql and login.sql after every CONNECT (unless SET SQLPLUSCOMPATIBILITY is less than 10). See http://download-west.oracle.com/docs/cd/B12037_01/server.101/b12170/ch13.htm#i2697450

Also in SQL*Plus 10g, the SQLPROMPT can contain substitution variables similar to TTITLE. See
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b12170/ch13.htm#sthref2861

Here's an example the uses the internal substitution variable _CONNECT_IDENTIFIER and the user substitution variable CNV to set the prompt:

	SQL> connect scott/tiger_at_ORCL
	Connected.
	SQL> col c new_value cnv
	SQL> select chr(10) c from dual;

	C
	-


	SQL> set sqlprompt '>> _connect_identifier cnv> '
	>> ORCL 
	> connect scott/tiger_at_MYDB
	Connected.
	>> MYDB 
	> 

Like TTITLE and BTITLE you don't need (and I think will not want to) prefix SQLPROMPT substitution variables with ampersands. See http://otn.oracle.com/support/tech/sql_plus/htdocs/sub_var5.html#5_7_1 for comments on variables in TTITLE.

While I'm browsing the SQL*Plus 10g manual here's the URL for the new/changed features:
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b12170/whatsnew.htm#sthref13. (Reminder to gentle readers: this is SQL*Plus features. SQL and PL/SQL
changes are covered elsewhere.)

• CJ