sqlplus, edit stored procedures

It came to my attention in an Ask Tom article of a very handy way to edit a package in sqlplus.

Set your $SQLPATH environment variable to wherever you wish to store useful scripts, including a login.sql -- make sure to set an editor in login.sql, e.g.,

define _editor=vim

In the Ask Tom example, simply create a getcode.sql and save somewhere in your $SQLPATH -- for reference,

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100

Then, in sqlplus, you can edit an existing procedure/package/function and update it in the database, e.g.,