Previously, we discussed viewing stored procedures in sqlplus.

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.,

foo_owner@FOO> @getcode PROCS_DATE
foo_owner@FOO> edit PROCS_DATE

foo_owner@FOO> @PROCS_DATE

Package created.

Package body created.

foo_owner@FOO>