1 PACKAGE MSC_MANAGE_PLAN_PARTITIONS AUTHID CURRENT_USER AS
2 /* $Header: MSCPRPRS.pls 120.2.12010000.1 2008/05/02 19:06:48 appldev ship $ */
3
4 G_SUCCESS CONSTANT NUMBER := 0;
5 G_WARNING CONSTANT NUMBER := 1;
6 G_ERROR CONSTANT NUMBER := 2;
7
8 SYS_YES CONSTANT NUMBER := 1;
9 SYS_NO CONSTANT NUMBER := 2;
10
11 --
12 -- Called by Create Plan UI. This procedure will identify if there
13 -- is a free partition available in MSC_PLAN_PARTITIONS. If yes then
14 -- it returns the plan_id. Otherwise it create a new partition by
15 -- performing DDL on all the partitioned tables. It stores the new
16 -- plan_id in MSC_PLAN_PARTITIONS, marks it as being used and returns it
17 -- to the calling UI
18 -- Return Status:
19 -- Success: FND_API.G_RET_STS_SUCCESS
20 -- failure: FND_API.G_RET_STS_ERROR (x_msg_data contains error message)
21 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error. x_msg_data
22 -- empty
23 -- Note: P_plan_name has to be unique in MSC_PLAN_PARTITIONS
24 --
25
26 FUNCTION get_plan (P_plan_name IN VARCHAR2,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER;
29
30
31
32 --
33 -- This function returns a free instance
34 --
35 FUNCTION get_instance (
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER;
38
39 --
40 -- This function returns the partition name which stores the plan/instance
41 -- in the input table. p_is_plan = SYS_YES if partition belongs to a plan
42 -- otherwise it should be SYS_NO
43 --
44 -- Return Status:
45 -- Success: FND_API.G_RET_STS_SUCCESS
46 -- failure: FND_API.G_RET_STS_ERROR (x_msg_data contains error message)
47 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error. x_msg_data empty)
48
49
50 PROCEDURE get_partition_name (P_plan_id IN NUMBER,
51 P_instance_id IN NUMBER,
52 P_table_name IN VARCHAR2,
53 P_is_plan IN NUMBER,
54 P_partition_name OUT NOCOPY VARCHAR2,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_data OUT NOCOPY VARCHAR2);
57
58 --
59 -- creates partitions for instances
60 -- called before creating plans because of the HIGHVALUE bug in
61 -- ST code
62 PROCEDURE create_inst_partition( errbuf OUT NOCOPY VARCHAR2,
63 retcode OUT NOCOPY NUMBER,
64 instance_count IN NUMBER);
65 --
66 -- truncates partition for the input plan_id
67 --
68 -- Return Status:
69 -- Success: FND_API.G_RET_STS_SUCCESS
70 -- failure: FND_API.G_RET_STS_ERROR (x_msg_data contains error message)
71 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error. x_msg_data empty)
72
73 PROCEDURE purge_partition( P_plan_id IN NUMBER,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_data OUT NOCOPY VARCHAR2) ;
76
77 --
78 -- creates partition by force. called from a concurrent program
79 --
80 PROCEDURE create_force_partition(errbuf OUT NOCOPY VARCHAR2,
81 retcode OUT NOCOPY NUMBER,
82 partition_num IN number,
83 plan IN NUMBER := SYS_YES);
84
85 --
86 -- drops partition by force. called from a concurrent program
87 --
88 PROCEDURE drop_force_partition(errbuf OUT NOCOPY VARCHAR2,
89 retcode OUT NOCOPY NUMBER,
90 partition_num IN number,
91 plan IN NUMBER := SYS_YES);
92
93
94
95 --
96 -- create partitions for existing plans. used when upgrading a database
97 --
98 PROCEDURE create_exist_plan_partitions(errbuf OUT NOCOPY VARCHAR2,
99 retcode OUT NOCOPY NUMBER);
100 --
101 -- drop partitions for existing plans. used to clean an unknown state
102 -- because of errors when creating partitions
103 --
104 PROCEDURE drop_exist_plan_partitions(errbuf OUT NOCOPY VARCHAR2,
105 retcode OUT NOCOPY NUMBER);
106
107 --
108 -- creates partitions for existing instances
109 -- called before creating plans because of the HIGHVALUE bug in
110 -- ST code
111 PROCEDURE create_exist_inst_partitions( errbuf OUT NOCOPY VARCHAR2,
112 retcode OUT NOCOPY NUMBER);
113
114 FUNCTION get_partition_number(errbuf OUT NOCOPY VARCHAR2,
115 retcode OUT NOCOPY NUMBER,
116 x_plan_id IN NUMBER) RETURN NUMBER;
117 --
118 -- analyzes the new plan to maintain good CBO statistics
119 --
120 PROCEDURE analyze_plan(errbuf OUT NOCOPY VARCHAR2,
121 retcode OUT NOCOPY NUMBER,
122 x_plan_id IN NUMBER);
123
124 --
125 -- create partitions statically. to be called by the dba
126 --
127 PROCEDURE create_partitions(errbuf OUT NOCOPY VARCHAR2,
128 retcode OUT NOCOPY NUMBER,
129 plan_partition_count IN NUMBER,
130 inst_partition_count IN NUMBER);
131
132 END MSC_MANAGE_PLAN_PARTITIONS;