Surprise from runing a SQL* Plus script

Last night was my first day working for a new client. It is a night shift on-call production support job. I was asked to add space to a bunch of databases. For one database, in SQL* Plus command line, I typed:

SQL> ho vi temp.sql

Then in the temp.sql I edited some commands which is nothing but to select from some DBA_* views to find out space usage. So after ediinbt, I did:

SQL> @temp

To my surprise, Oracle gives error message, something like ' Procedure created with compilation error'. I immediately reallized that there must be a script called 'temp.sql' in the SQLPATH. I was scared as this is a production database. Have I modified a procedure that is essential to bussiness? I was picturing the worst-case scenario, bussiness failed etc. Really scared. However later on, I found I connect as SYS user, the procedure was thus created under SYS schema ( what I thought at that time, should be confirmed), which is unlikely used by application user. Anyway mistake has already been made. So I moved on. But I made another mistake - I did not write down in which database I created the procedure. Now I can not recall. Tonight I shall find out and confirm that there should be no impact by creating this procedure under SYS.

I frequently use the name temp.sql to edit SQL* Plus statments in it and run the script as described above. In the furture I should be more careful. It appears that I am not the only one like the name 'temp.sql'. Probably I should give up this habit and use copy/paste instead.

God bless me!

SQLPLUS sample login.sql

--- login.sql ---
define_editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) '@' substr( global_name, 1, decode( dot, 0,
length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name
);
set sqlprompt '&gname> '
set termout on

~
---- conn.sql ---
set termout off
connect &1
@login
set termout on

SQLPLUS CHR(n) function - generate linefeed

Problem:
========
Want to create sql statement with the format as follows:

UPDATE cf_contact_function_type
SET description = 'DFT30'
WHERE contact_function_type_code = 'DCF'
/

Solution:
=========
Using CHR(10) to provide linefeed character

select
'UPDATE cf_contact_function_type' chr(10)
' SET description = ''' description '''' chr(10)
' WHERE contact_function_type_code = ''' contact_function_type_code
'''' chr(10)
'/'
from cf_contact_function_type;

Example output:

repadmin@GENQB> select
2 'UPDATE cf_contact_function_type' chr(10)
3 ' SET description = ''' description '''' chr(10)
4 ' WHERE contact_function_type_code = '''
contact_function_type_code ''''
5 '/'
6 from cf_contact_function_type;

'UPDATECF_CONTACT_FUNCTION_TYPE'CHR(10)'SETDESCRIPTION='''DESCRIPT
ION''''CHR

------------------------------------------------------------------------
--------------

UPDATE cf_contact_function_type
SET description = 'Accounts Payable'
WHERE contact_function_type_code = 'ATP'
/

UPDATE cf_contact_function_type
SET description = 'After Hours'
WHERE contact_function_type_code = 'AFH'
/

UPDATE cf_contact_function_type
SET description = 'Billing'
WHERE contact_function_type_code = 'BLG'
/

UPDATE cf_contact_function_type
SET description = 'Bookkeeper'
WHERE contact_function_type_code = 'BKE'
/

UPDATE cf_contact_function_type
SET description = 'Controller'
WHERE contact_function_type_code = 'CON'
/

UPDATE cf_contact_function_type
SET description = 'Legal'
WHERE contact_function_type_code = 'LEG'
/

UPDATE cf_contact_function_type
SET description = 'Manager'
WHERE contact_function_type_code = 'MGR'
/