Other Server Changes¶
Other major technical server changes to be aware of are listed on this page.
Custom batch jobs conversion to Scheduled Tasks¶
All custom batch job needs to be converted to scheduled tasks. How to do this is explained in the example below.
Register a Scheduled Task.¶
PROMPT Register Batch Schedule Method "BATCH_SYS.FND_HEAVY_CLEANUP_"
DECLARE
schedule_method_id_ NUMBER := NULL;
info_msg_ VARCHAR2(32000) := NULL;
parameters_msg_ VARCHAR2(32000) := NULL;
mandatory_msg_ VARCHAR2(32000) := NULL;
non_mandatory_msg_ VARCHAR2(32000) := NULL;
BEGIN
-- Construct Mandatory Parameters Message
mandatory_msg_ := Message_SYS.Construct('');
-- Construct Non-Mandatory Parameters Message
non_mandatory_msg_ := Message_SYS.Construct('');
-- Construct Parameters Message
parameters_msg_ := Message_SYS.Construct('');
Message_SYS.Add_Attribute(parameters_msg_, 'MANDATORY', mandatory_msg_);
Message_SYS.Add_Attribute(parameters_msg_, 'NON_MANDATORY', non_mandatory_msg_);
-- Construct Main Message
info_msg_ := Message_SYS.Construct('');
Message_SYS.Add_Attribute(info_msg_, 'METHOD_NAME', 'BATCH_SYS.FND_HEAVY_CLEANUP_');
Message_SYS.Add_Attribute(info_msg_, 'DESCRIPTION', 'Heavy Cleanup');
Message_SYS.Add_Attribute(info_msg_, 'MODULE', 'FNDBAS');
Message_SYS.Add_Attribute(info_msg_, 'SINGLE_EXECUTION_DB', 'TRUE');
Message_SYS.Add_Attribute(info_msg_, 'ARGUMENT_TYPE_DB', 'NONE');
Message_SYS.Add_Attribute(info_msg_, 'PARAMETERS', parameters_msg_);
-- Register Batch Schedule Method
Batch_SYS.Register_Batch_Schedule_Method(schedule_method_id_, info_msg_);
-- Insert PROG value for Description
Basic_Data_Translation_API.Insert_Prog_Translation('FNDBAS', 'BatchScheduleMethod', 'BATCH_SYS.FND_HEAVY_CLEANUP_'||'^'||'DESCRIPTION', 'Heavy Cleanup');<br/>END;
Auto register a Scheduled Task that previously was ran as a Batch Job, also remove the Batch Job.¶
------------------------------------------------------------------------------------------
-- Tasks (Autostarted)
------------------------------------------------------------------------------------------
DECLARE
PROCEDURE New_Batch_Schedule (
method_name_ IN VARCHAR2,
schedule_name_ IN VARCHAR2,
execution_plan_ IN VARCHAR2 )
IS
schedule_method_id_ NUMBER;
schedule_id_ NUMBER;
rec_ Batch_Schedule_Method_API.public_rec;
next_execution_date_ DATE;
start_date_ DATE := sysdate;
CURSOR get_method IS
SELECT parameters
FROM batch_schedule_method_tab
WHERE method_name = upper(method_name_);
BEGIN
FOR rec IN get_method LOOP
Batch_SYS.New_Batch_Schedule(schedule_id_, next_execution_date_, start_date_, null, schedule_name_, method_name_, rec.parameters, 'TRUE', execution_plan_);
END LOOP;
EXCEPTION
WHEN others THEN
NULL;
END New_Batch_Schedule;
PROCEDURE Remove_Server_Process (
method_name_ IN VARCHAR2 )
IS
job_ NUMBER;
CURSOR get_job IS
SELECT job
FROM user_jobs
WHERE UPPER(what) like '%'||method_name_||'%';<br/> BEGIN<br/> FOR rec IN get_job LOOP<br/> dbms_job.remove(rec.job_);<br/> END LOOP;<br/> EXCEPTION<br/> WHEN OTHERS THEN<br/> NULL;<br/> END Remove_Server_Process;<br/>BEGIN<br/> --<br/> ------------------------------------------------------------------------------------------<br/> -- Light and Heavy cleanup (moved from General_SYS to Batch_SYS from release 3.1.0.<br/> ------------------------------------------------------------------------------------------<br/> New_Batch_Schedule('BATCH_SYS.FND_LIGHT_CLEANUP_', 'Light Cleanup', 'EVERY 00:10');<br/> Remove_Server_Process('GENERAL_SYS.FND_LIGHT_CLEANUP_');<br/>END;
Create a Scheduled Task by using Batch_SYS.New_Batch_Schedule¶
DECLARE
Schedule_Id_ NUMBER;
Next_Execution_Date_ DATE := SYSDATE;
Start_Date_ DATE := SYSDATE;
Msg_ VARCHAR2(2000);
Msg1_ VARCHAR2(2000);
Msg2_ VARCHAR2(2000);
BEGIN
Msg1_ := Message_Sys.Construct('');
Message_Sys.Add_Attribute(Msg1_, 'METHOD', 'ESTIMATE');
Message_Sys.Add_Attribute(Msg1_, 'SCHEMA', 'DEV310');
Msg2_ := Message_Sys.Construct('');
Message_Sys.Add_Attribute(Msg2_, 'ESTIMATE_PERCENT', 20);
Msg_ := Message_Sys.Construct('');
Message_Sys.Add_Attribute(Msg_, 'MANDATORY', Msg1_);
Message_Sys.Add_Attribute(Msg_, 'NON_MANDATORY', Msg2_);
Batch_Sys.New_Batch_Schedule(Schedule_Id_, Next_Execution_Date_,
Start_Date_, NULL, 'Test Schedule',
'DATABASE_SYS.EXECUTE_ANALYZE_SCHEMA__',
Msg_, 'TRUE', 'EVERY 00:59');
END;
Data types LONG and LONG ROW used in custom code must be converted to CLOB or BLOB¶
All tables having columns of data type LONG or LONG RAW should be replaced to CLOB respective BLOB.
In order to list which tables that has such columns you can use the following SQL statement:
SELECT *
FROM user_tab_columns c
WHERE data_type LIKE 'LONG%'
AND EXISTS (SELECT 1
FROM user_objects o
WHERE o.object_name = c.table_name
AND o.object_type = 'TABLE')
A method in Installation_SYS called Alter_Long_Column_To_Lob has been added that can be used to convert a LONG column to a LOB column:
DECLARE
table_name_ VARCHAR2(30) := 'BINARY_OBJECT_DATA_BLOCK_TAB';
BEGIN
Installation_SYS.Alter_Long_Column_To_Lob(table_name_, 'DATA', '&fnd_lob', TRUE, TRUE);
END;
/