>To all concern:
>
>My applications consist of many Stored Procedures/Functions installed
>in Oracle...My problem is:
>
>Is there any way to "Backup" and "Recover" Stored Procedures aside from
>thje usual Copy/Paste Function copied into a notepad?
>
>FEI

I create script files that I can run from SQL Plus to create tables, sequences, triggers and SPs at any time. These are simple text files with an SQL extension. From SQL Plus command prompt, you type [.SQL extension is not needed]: @C:\myapp\scripts\my_script_filename

An example script file of mine:

set feedback off
--
-- by setting feedback off, you only get errors echoed to the screen
--
drop public synonym WATERBODY;
drop public synonym WATERBODY_PARMS;
drop public synonym PARAMETER_CODES;
drop public synonym S_NPDES_WATERBODY;
drop public synonym S_NPDES_WB_PARMS;
drop public synonym S_NPDES_PARM_CODES;

drop trigger WATERBODY_BEFORE_INSUPDT;
drop trigger WB_PARMS_BEFORE_INSERT;
drop trigger PARM_CODES_BEFORE_INSERT;

drop sequence s_npdes_WATERBODY;
drop sequence s_npdes_WB_PARMS;
drop sequence s_npdes_PARM_CODES;

drop table waterbody;
drop table waterbody_parms;
drop table parameter_codes;

create table waterbody
(KeyID Number(8),
Permit_ID Number(8) CONSTRAINT fk_waterbody_permit REFERENCES NPDES.Permits(KeyID),
TMDL_Proposed Date,
TMDL_Complete Date,
updated_by char(8),
last_update date DEFAULT SYSDATE,
notes long)
TABLESPACE NPDES;

create table waterbody_parms
(KeyID Number(8),
WaterBody_ID Number(8),
Parm_ID Number(8))
Tablespace NPDES;

create table parameter_codes
(KeyID Number(8),
State Char(2),
ParmCode Varchar2(8),
Descript Varchar2(60))
Tablespace NPDES;

create sequence s_npdes_WATERBODY increment by 1 start with 1 nocache;
create sequence s_npdes_WB_PARMS increment by 1 start with 1 nocache;
create sequence s_npdes_PARM_CODES increment by 1 start with 1 nocache;

create or replace trigger WATERBODY_BEFORE_INSUPDT
BEFORE INSERT OR UPDATE ON NPDES.WATERBODY FOR EACH ROW
DECLARE
v_Id Number;
v_Count Number;
v_Duplicate_Record_Excep EXCEPTION;
BEGIN
:new.Updated_By := USER;
:new.Last_Update := SYSDATE;
IF :new.KeyID Is Null or :new.KeyID < 1 THEN
select count(*) into v_Count from waterbody where permit_id = :new.permit_id;
IF (v_Count > 0) THEN
RAISE v_Duplicate_Record_Excep;
ELSE
select s_npdes_WATERBODY.nextval into v_Id from dual;
:new.KeyID := v_Id;
END IF;
END IF;
EXCEPTION
WHEN v_Duplicate_Record_Excep THEN
RAISE_APPLICATION_ERROR(-20111, 'Waterbody record for selected permit already exists.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20122, 'Unhandled error occurred in WaterBody Insert/Update Trigger.');
END;
/

create or replace trigger WB_PARMS_BEFORE_INSERT
BEFORE INSERT ON NPDES.WATERBODY_PARMS FOR EACH ROW
DECLARE
v_Id Number;
BEGIN
IF :new.KeyID Is Null or :new.KeyID < 1 THEN
select s_npdes_WB_PARMS.nextval into v_Id from dual;
:new.KeyID := v_Id;
END IF;
END;
/

create or replace trigger PARM_CODES_BEFORE_INSERT
BEFORE INSERT ON NPDES.PARAMETER_CODES FOR EACH ROW
DECLARE
v_Id Number;
BEGIN
IF :new.KeyID Is Null or :new.KeyID < 1 THEN
select s_npdes_PARM_CODES.nextval into v_Id from dual;
:new.KeyID := v_Id;
END IF;
END;
/

create public synonym WATERBODY for NPDES.WATERBODY;
create public synonym WATERBODY_PARMS for NPDES.WATERBODY_PARMS;
create public synonym PARAMETER_CODES for NPDES.PARAMETER_CODES;

create public synonym S_NPDES_WATERBODY for NPDES.S_NPDES_WATERBODY;
create public synonym S_NPDES_WB_PARMS for NPDES.S_NPDES_WB_PARMS;
create public synonym S_NPDES_PARM_CODES for NPDES.S_NPDES_PARM_CODES;

grant select on npdes.waterbody to npdes_read;
grant select on npdes.waterbody_parms to npdes_read;
grant select on npdes.parameter_codes to npdes_read;
grant select on npdes.s_npdes_waterbody to npdes_edit;
grant select on npdes.s_npdes_wb_parms to npdes_edit;
grant select on npdes.s_npdes_parm_codes to npdes_admin;

grant insert, update, delete on npdes.waterbody to npdes_edit;
grant insert, update, delete on npdes.waterbody_parms to npdes_edit;
grant insert, update, delete on npdes.parameter_codes to npdes_admin;

set feedback on