Disable interactive prompts at SQL*Plus

Whenever we have ampersand "&" in our script, SQL Plus prompts for a value to be entered. There are several ways to avoid that prompt as discussed below

1) user chr(38) instead of &

SELECT 'I love SQL '||chr(38)||' PLSQL' from dual; 

2) Use & at the end just below the single quotes

SELECT 'I love SQL &'||' PLSQL' from dual;  

3) SET DEFINE OFF can be use to disable the prompt

SET DEFINE OFF
SELECT 'I love SQL & PLSQL ' from dual; 
SET DEFINE ON

Change SQL prompt oracle SQL*Plus (pre 10g)

Here we discussed how to change prompt for release 10g and up. Now we will discuss how to achieve same in pre 10g releases

Enter following commands in glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory or execute them one by one at SQL Prompt.

col username new_value username
col dbname new_value dbname
set termout off
SELECT lower(user) username,
       substr(global_name, 1, instr(global_name, '.')-1) dbname
FROM   global_name
/
set termout on
set sqlprompt '&&username@&&dbname> '

Change SQL Prompt in oracle SQL*Plus (10g and up)

Below is an example to change the SQL*Plus prompt (10g and up), simple and yet very useful.
SET SQLPROMPT command is used to change the default SQL> prompt

1) Display username

set sqlprompt '_user>' 

2) Display database name

set sqlprompt '_connect_identifier_privilege>' 

Step 2 and 3 can be combined together to display username and database name together.
3) Display username and database name (e.g. apps@dbname> )

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"

4) To set the time at sqlprompt

set time on 

Now the best part is to avoid typing this command everytime you open a new SQL*Plus session, edit glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory as follows.

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"
set time on

Related Post: For Pre 10g Releases

Display line number in VI editor in Unix

Following command can be used to display line numbers

:set number 

Following command can be used to hide the line numbers

:set nonumber 

Example of DBMS_XMLGEN.getxml to generate XML Tag using oracle query

Below is an example of DBMS_XMLGEN.getxml to generate XML tags directly out of the query

SELECT DBMS_XMLGEN.getxml(
'SELECT CURSOR(SELECT oha.order_number,
                     ola.ordered_item,
                     ola.ordered_quantity
                FROM ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id 
                 and oha.order_number in (&order_number) order by ola.line_id) as order_detail,
       CURSOR(SELECT ohd.name, ohs.hold_comment
                FROM ont.oe_hold_sources_all ohs,
                     ont.oe_order_holds_all ohld,
                     ont.oe_hold_definitions ohd,
                     ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id
                 AND ola.line_id = ohld.line_id 
                 and ohld.hold_release_id is null
                 AND ohld.hold_source_id = ohs.hold_source_id
                 AND ohs.hold_id =  ohd.hold_id
                 AND oha.order_number = &&order_number) as holds_detail
FROM DUAL')
FROM DUAL

Output