How to restore a accidentally dropped Stored Procedure

This recovery is very easy. The principle is that the source code of all stored procedures in oracle is stored in dba_source. When you drop a stored procedure

This recovery is very easy. The principle is that the source code of all stored procedures in oracle is stored in dba_source. When you drop a stored procedure

Today, a colleague wrote to me: "I mistakenly dropped a stored procedure: P_IPACCHECK_NC about 10 minutes ago. The source code of this stored procedure is not backed up on my local machine, please recover it. Thank you"

The complete recovery process is as follows:

Log On with the sys user and execute the following query:

SQL>Select text from dba_source as of timestamp to_timestamp ('2017-03-06 09:45:00 ', 'yyyy-MM-DD HH24: MI: ss ') where owner = 'ipa' and name = 'P _ IPACCHECK_NC 'order by line;

TEXT

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

Procedure P_IPACCHECK_NC (n_flag out number,

Vc_message out varchar2) is

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

-- Procedure name: P_IPACCHECK_NC --

-- Name in sysmth: NONE --

-- DESCRIPTION: checks the validity of IWBIBT records. If no error occurs, set the flag

--

-- INVOKED :--

-- Programmed by: ZhouXin DATE 2008/12/02 --

-- Modified:

-- TYPE: ONLINE --

-- COPYRIGHT 1997 ~ 2008 ACCA-ARK --

----

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

Vc_ipastc varchar2 (20 );

N_errcount number: = 0;

Begin

For rec_pac in (select * from iwbpac where ipastc is null) loop

TEXT

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

N_errcount: = 0;

Vc_ipastc: = rec_pac.ipastc;

-- Check the liquidation month

If rec_pac.ipalrm> to_number (to_char (sysdate, 'yyyymm') then

Vc_ipastc: = vc_ipastc | 'a ';

N_errcount: = n_errcount + 1;

End if;

-- Check the name opening Company

If f_masaln_existawbprefix (rec_pac.ip1_r )! = True then

Vc_ipastc: = vc_ipastc | 'B ';

N_errcount: = n_errcount + 1;

End if;

-- Check the actual account opening Company

If f_masaln_existawbprefix (rec_pac.ipacar )! = True then

Vc_ipastc: = vc_ipastc | 'C ';

N_errcount: = n_errcount + 1;

End if;

-- Check the account opening Company

If f_masaln_existawbprefix (rec_pac.ipairl )! = True then

Vc_ipastc: = vc_ipastc | 'E ';

N_errcount: = n_errcount + 1;

TEXT

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

End if;

-- Check the name opening Company

If rec_pac.ipalas <> 'P' then

Vc_ipastc: = vc_ipastc | 'F ';

N_errcount: = n_errcount + 1;

End if;

-- Check the bill input date

If rec_pac.ipanpd> to_number (to_char (sysdate, 'yyyymmdd') then

Vc_ipastc: = vc_ipastc | 'G ';

N_errcount: = n_errcount + 1;

End if;

-- Check the billing month

If rec_pac.ipailm> to_number (to_char (sysdate, 'yyyymm') then

Vc_ipastc: = vc_ipastc | 'H ';

N_errcount: = n_errcount + 1;

End if;

-- Check the original opening amount

If rec_pac.ipaemk = 'B' and rec_pac.ipaamt is null then

Vc_ipastc: = vc_ipastc | 'I ';

N_errcount: = n_errcount + 1;

End if;

TEXT

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

-- Check the liquidation period

If to_number (rec_pac.ipacpr) <1 or to_number (rec_pac.ipacpr)> 4 then

Vc_ipastc: = vc_ipastc | 'J ';

N_errcount: = n_errcount + 1;

End if;

-- Check the opening period

If to_number (rec_pac.ipabpr) <1 or to_number (rec_pac.ipabpr)> 4 then

Vc_ipastc: = vc_ipastc | 'K ';

N_errcount: = n_errcount + 1;

End if;

-- No error. Set the flag to '0'

If n_errcount = 0 then

Update iwbpac

Set ipastc = '0'

Where ipacpr = rec_pac.ipacpr

And ipairl = rec_pac.ipairl

And ipacar = rec_pac.ipacar

And ipanvn = rec_pac.ipanvn

And ipanva = rec_pac.ipanva

And ipalrm = rec_pac.ipalrm;

Else

TEXT

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

Update iwbpac

Set ipastc = vc_ipastc

Where ipacpr = rec_pac.ipacpr

And ipairl = rec_pac.ipairl

And ipacar = rec_pac.ipacar

And ipanvn = rec_pac.ipanvn

And ipanva = rec_pac.ipanva

And ipalrm = rec_pac.ipalrm;

End if;

End loop;

Exception

When others then

N_flag: = 0;

Vc_message: = substr (sqlerrm, 1, 1000 );

End P_IPACCHECK_NC;

100 rows selected

Supplement:

Sys @ ORCL> select text from dba_source where owner = 'lsf 'and order by line;

TEXT
Certificate -----------------------------------------------------------------------------------------------------------------------------------
Procedure emp_sal
Is
V_last_name employee. last_name % type;
V_employee_id employee. employee_id % type;
V_salary employee. salary % type;
Cursor cursor_sal is
Select last_name, employee_id, salary from employee where salary between 2000 and 3000;
Begin
Open cursor_sal;
Loop
Fetch cursor_sal into v_last_name, v_employee_id, v_salary;
Exit when cursor_sal % notfound;
Update employee set salary = salary * 1.2 where last_name = v_last_name and employee_id = v_employee_id;
End loop;
Close cursor_sal;
Commit;
End;

17 rows selected.

SQL> show user
USER is "LSF"
SQL> select username from user_users;

USERNAME
------------------------------
LSF

SQL> select text from user_source where order by line;

TEXT
--------------------------------------------------------------------------------
Procedure emp_sal
Is
V_last_name employee. last_name % type;
V_employee_id employee. employee_id % type;
V_salary employee. salary % type;
Cursor cursor_sal is
Select last_name, employee_id, salary from employee where salary between 2000 and
3000;

Begin
Open cursor_sal;
Loop
Fetch cursor_sal into v_last_name, v_employee_id, v_salary;
Exit when cursor_sal % notfound;
Update employee set salary = salary * 1.2 where last_name = v_last_name and employee_ I
D = v_employee_id;

End loop;
Close cursor_sal;
Commit;
End;

17 rows selected.

SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'yy
-------------------
14:46:24

SQL> drop procedure emp_sal;

Procedure dropped.

SQL> select text from user_source where order by line;

No rows selected

SQL> select text from user_source as of timestamp to_timestamp ('2017-08-10 14:46:24 ', 'yyyy-MM-DD HH24: MI: ss') where order by line;
Select text from user_source as of timestamp to_timestamp ('2017-08-10 14:46:24 ', 'yyyy-MM-DD HH24: MI: ss') where order by line
*
ERROR at line 1:
ORA-01031: insufficient privileges

Sys @ ORCL> select text from dba_source as of timestamp to_timestamp ('2017-08-10 14:46:24 ', 'yyyy-MM-DD HH24: MI: ss ') where owner = 'lsf 'and order by line;

TEXT
-----------------------------------------------------------
Procedure emp_sal
Is
V_last_name employee. last_name % type;
V_employee_id employee. employee_id % type;
V_salary employee. salary % type;
Cursor cursor_sal is
Select last_name, employee_id, salary from employee where salary between 2000 and 3000;
Begin
Open cursor_sal;
Loop
Fetch cursor_sal into v_last_name, v_employee_id, v_salary;
Exit when cursor_sal % notfound;
Update employee set salary = salary * 1.2 where last_name = v_last_name and employee_id = v_employee_id;
End loop;
Close cursor_sal;
Commit;
End;

17 rows selected.