The purpose of the concurrent program is to drop the table/tables from a specific schema.
The program always holds a default value <Drop all tables older than 1 Month>, which will drop all the tables that are older than one month. Either you can go with the default value or you can provide the program with a valid value (tablename).
STEP 1: Create the Package and Package Body.
create or replace PACKAGE XXFND_UTILITIES
AS
PROCEDURE DROP_BKP_TABLES (errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_bkp_table IN VARCHAR2);
END XXFND_UTILITIES;
create or replace PACKAGE BODY XXFND_UTILITIES
AS
PROCEDURE DROP_BKP_TABLES (errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_bkp_table IN VARCHAR2)
IS
l_found BOOLEAN := FALSE;
CURSOR c_bkp_tables
IS
SELECT object_name, created, status
FROM dba_objects
WHERE object_type = 'TABLE' AND owner = 'XXBKP'
AND object_name = UPPER (
DECODE (
p_bkp_table,
'<Drop all tables older than 1 Month>', object_name,
p_bkp_table))
AND created <
DECODE (
p_bkp_table,
'<Drop all tables older than 1 Month>', SYSDATE- 30,
created + 1)
ORDER BY 1;
BEGIN
FND_FILE.
PUT_LINE (FND_FILE.LOG, ('Parameter: p_bkp_table = ' || p_bkp_table));
FND_FILE.
PUT_LINE (FND_FILE.LOG, ('======================================='));
IF p_bkp_table = '<Drop all tables older than 1 Month>'
THEN
FND_FILE.
PUT_LINE (FND_FILE.LOG,
('Dropping backup tables older than 1 month'));
END IF;
FOR c_bkp_table_rec IN c_bkp_tables
LOOP
FND_FILE.
PUT_LINE (FND_FILE.LOG,
('Dropping table XXBKP.' || c_bkp_table_rec.object_name));
EXECUTE IMMEDIATE 'DROP TABLE XXBKP.' || c_bkp_table_rec.object_name;
l_found := TRUE;
END LOOP;
IF l_found = FALSE
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ('No table(s) found to Drop.'));
END IF;
FND_FILE.
PUT_LINE (FND_FILE.LOG, ('======================================='));
EXCEPTION
WHEN OTHERS
THEN
errbuf := SQLERRM;
retcode := '2';
FND_FILE.
PUT_LINE (
FND_FILE.LOG,
('Unknown Exception occurred - ' || SUBSTR (SQLERRM, 1, 100)));
RAISE;
END;
END XXFND_UTILITIES;
STEP 2: Register Executable.
System Administrator > Concurrent > Executable
STEP 3: Register Program.
System Administrator > Concurrent > Program
STEP 4: Provide appropriate parameters.
System Administrator > Concurrent > Program:Concurrent Program Parameters
STEP 5: Attach this concurrent program to a Request group.
System Administrator > Security > Responsibility > Request
STEP 6: Submit the Concurrent Program.
System Administrator > Concurrent > Requests
When Backup Table value is default "<Drop all tables older than 1 Month>"
+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XXFND_DROP_BKP_TABLES module: GP Drop Backup Tables
+---------------------------------------------------------------------------+
Current system time is 03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
**Starts**03-JUL-2014 09:26:27
**Ends**03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Parameter: p_bkp_table = <Drop all tables older than 1 Month>
=======================================
Dropping backup tables older than 1 month
No table(s) found to Drop.
=======================================
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Executing request completion options...
Printing output file.
Request ID : 13313415
Number of copies : 0
Printer : noprint
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
When Backup Table value is "T1", below is log file.
+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XXFND_DROP_BKP_TABLES module: GP Drop Backup Tables
+---------------------------------------------------------------------------+
Current system time is 03-JUL-2014 09:42:14
+---------------------------------------------------------------------------+
**Starts**03-JUL-2014 09:42:14
**Ends**03-JUL-2014 09:42:14
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Parameter: p_bkp_table = T1
=======================================
Dropping table XXBKP.T1
=======================================
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Executing request completion options...
Printing output file.
Request ID : 13313419
Number of copies : 0
Printer : noprint
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 03-JUL-2014 09:42:15
+---------------------------------------------------------------------------+
References:
http://jayantaapps.blogspot.in/2012/10/how-to-registration-pl-sql-stored.html
http://www.techmandate.com/steps-to-create-a-concurrent-program-of-type-plsql-stored-procedure/
