Home » SQL & PL/SQL » Client Tools » SQLPlus commands (General Question)
SQLPlus commands [message #336304] Fri, 25 July 2008 10:22 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
set termout off
define new_prompt='nolog'
column value new_value new_prompt


select

   username ||

   ': ' ||

   substr(substr(global_name,1,30),1,
     instr(substr(global_name,1,30),'.')-1)

   value

from

   user_users,

   global_name;



set sqlprompt "&new_prompt> "
set termout on


i have two questions .
1)column value new_value new_prompt
what exactly this do ?

2)how &new_prompt get value of query output .

Thanks.
Re: SQLPlus commands [message #336315 is a reply to message #336304] Fri, 25 July 2008 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm

Regards
Michel
Re: SQLPlus commands [message #336367 is a reply to message #336304] Fri, 25 July 2008 23:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following is an excerpt from the online 11g SQL*Plus User's Guide and Reference in the section regarding the column command:

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch12013.htm#SQPUG034

"NEW_V[ALUE] variable

Specifies a variable to hold a column value. You can reference the variable in TTITLE commands. Use NEW_VALUE to display column values or the date in the top title. You must include the column in a BREAK command with the SKIP PAGE action. The variable name cannot contain a pound sign (#).

NEW_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause. See the example at the end of this command description.

Variables specified with NEW_V[ALUE] are expanded before TTITLE is executed. The resulting string is stored as the TTITLE text. During subsequent execution for each page of the report, the expanded value of a variable may itself be interpreted as a variable with unexpected results.

You can avoid this double substitution in a TTITLE command by not using the & prefix for NEW_V[ALUE] variables that are to be substituted on each page of the report. If you want to use a substitution variable to insert unchanging text in a TTITLE, enclose it in quotes so that it is only substituted once.

For information on displaying a column value in the bottom title, see OLD_V[ALUE] variable below. For more information on referencing variables in titles, see the TTITLE command. For information on formatting and valid format models, see FOR[MAT] format above."

[Updated on: Fri, 25 July 2008 23:44]

Report message to a moderator

Re: SQLPlus commands [message #339579 is a reply to message #336304] Thu, 07 August 2008 20:46 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Thanks. I'm glad to see Oracle is still adding new features to SQL*Plus
Re: SQLPlus commands [message #339647 is a reply to message #339579] Fri, 08 August 2008 01:11 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still adding? This exists since the beginning of SQL*Plus!

Here the reference for Oracle7 (the oldest I found on Internet):
http://download.oracle.com/docs/cd/A57673_01/DOC/dcommon/oin/index.htm

Regards
Michel
Previous Topic: Book(s) on Crystal Reports
Next Topic: hi - I need help with while santence
Goto Forum:
  


Current Time: Fri Dec 09 15:18:13 CST 2016

Total time taken to generate the page: 0.12451 seconds