The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT value INTO g_Dump_Dir
FROM v$parameter WHERE name = 'user_dump_dest';
SELECT TRANSLATE(LTRIM(value), ',', ' ') INTO g_Log_Dir
FROM v$parameter
WHERE name = 'utl_file_dir';
FOR multi_lexer_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_ASSET_MULTI_LEXER' )
LOOP
ad_ctx_ddl.drop_preference (multi_lexer_rec.pre_owner ||'.'|| multi_lexer_rec.pre_name);
FOR sub_lexer_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name LIKE 'EAM_ASSET_LEXER%' )
LOOP
ad_ctx_ddl.drop_preference (sub_lexer_rec.pre_owner ||'.'|| sub_lexer_rec.pre_name);
FOR wordlist_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_ASSET_WORDLIST' )
LOOP
ad_ctx_ddl.drop_preference (wordlist_rec.pre_owner ||'.'|| wordlist_rec.pre_name);
FOR stoplist_rec IN ( SELECT spl_owner, spl_name
FROM ctxsys.ctx_stoplists
WHERE spl_name = 'EAM_ASSET_STOPLIST' )
LOOP
--ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.EAM_ASSET_STOPLIST');
FOR section_group_rec IN ( SELECT sgp_owner, sgp_name
FROM ctxsys.ctx_section_groups
WHERE sgp_name = 'EAM_ASSET_SECTION_GROUP' )
LOOP
ad_ctx_ddl.Drop_Section_Group (section_group_rec.sgp_owner ||'.'|| section_group_rec.sgp_name);
FOR datastore_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_ASSET_DATASTORE' )
LOOP
ad_ctx_ddl.drop_preference (datastore_rec.pre_owner ||'.'|| datastore_rec.pre_name);
FOR storage_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_ASSET_STORAGE' )
LOOP
ad_ctx_ddl.drop_preference (storage_rec.pre_owner ||'.'|| storage_rec.pre_name);
SELECT 'tablespace ' || tablespace_name ||
' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
INTO tspace_tbl_param
FROM all_tables
WHERE owner = g_Prod_Schema AND table_name = 'EAM_ASSET_TEXT';
SELECT 'tablespace ' || tablespace_name ||
' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
INTO tspace_idx_param
FROM all_indexes
WHERE owner = g_Prod_Schema
AND index_name = 'EAM_ASSET_TEXT_U1'
AND table_name = 'EAM_ASSET_TEXT';
FOR index_rec IN ( SELECT owner, index_name, status, domidx_status, domidx_opstatus
FROM all_indexes
WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
AND table_name = 'EAM_ASSET_TEXT'
AND index_name = p_Index_Name )
LOOP
-- Check index schema
--
IF ( index_rec.owner <> g_Index_Owner )
THEN
Log_Line (l_api_name || ': Error: index exists in wrong schema: ' || index_rec.owner, FND_LOG.LEVEL_EVENT, l_api_name);
SELECT par_value INTO l_Index_Memory_Max
FROM ctx_parameters
WHERE par_name = 'MAX_INDEX_MEMORY';
SELECT idx_docid_count, idx_status
INTO l_idx_docid_count, l_idx_status
FROM ctxsys.ctx_indexes
WHERE idx_owner = g_Prod_Schema AND idx_name = 'EAM_ASSET_TEXT_CTX1'
AND idx_table = 'EAM_ASSET_TEXT';
FOR multi_lexer_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_WORK_ORDER_MULTI_LEXER' )
LOOP
ad_ctx_ddl.drop_preference (multi_lexer_rec.pre_owner ||'.'|| multi_lexer_rec.pre_name);
FOR sub_lexer_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name LIKE 'EAM_WORK_ORDER_LEXER%' )
LOOP
ad_ctx_ddl.drop_preference (sub_lexer_rec.pre_owner ||'.'|| sub_lexer_rec.pre_name);
FOR wordlist_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_WORK_ORDER_WORDLIST' )
LOOP
ad_ctx_ddl.drop_preference (wordlist_rec.pre_owner ||'.'|| wordlist_rec.pre_name);
FOR stoplist_rec IN ( SELECT spl_owner, spl_name
FROM ctxsys.ctx_stoplists
WHERE spl_name = 'EAM_WORK_ORDER_STOPLIST' )
LOOP
--ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.EAM_WORK_ORDER_STOPLIST');
FOR section_group_rec IN ( SELECT sgp_owner, sgp_name
FROM ctxsys.ctx_section_groups
WHERE sgp_name = 'EAM_WORK_ORDER_SECTION_GROUP' )
LOOP
ad_ctx_ddl.Drop_Section_Group (section_group_rec.sgp_owner ||'.'|| section_group_rec.sgp_name);
FOR datastore_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_WORK_ORDER_DATASTORE' )
LOOP
ad_ctx_ddl.drop_preference (datastore_rec.pre_owner ||'.'|| datastore_rec.pre_name);
FOR storage_rec IN ( SELECT pre_owner, pre_name
FROM ctxsys.ctx_preferences
WHERE pre_name = 'EAM_WORK_ORDER_STORAGE' )
LOOP
ad_ctx_ddl.drop_preference (storage_rec.pre_owner ||'.'|| storage_rec.pre_name);
SELECT 'tablespace ' || tablespace_name ||
' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
INTO tspace_tbl_param
FROM all_tables
WHERE owner = g_Prod_Schema AND table_name = 'EAM_WORK_ORDER_TEXT';
SELECT 'tablespace ' || tablespace_name ||
' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
INTO tspace_idx_param
FROM all_indexes
WHERE owner = g_Prod_Schema
AND index_name = 'EAM_WORK_ORDER_TEXT_U1'
AND table_name = 'EAM_WORK_ORDER_TEXT';
FOR index_rec IN ( SELECT owner, index_name, status, domidx_status, domidx_opstatus
FROM all_indexes
WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
AND table_name = 'EAM_WORK_ORDER_TEXT'
AND index_name = p_Index_Name )
LOOP
-- Check index schema
--
IF ( index_rec.owner <> g_Index_Owner )
THEN
Log_Line (l_api_name || ': Error: index exists in wrong schema: ' || index_rec.owner, FND_LOG.LEVEL_EVENT, l_api_name);
SELECT par_value INTO l_Index_Memory_Max
FROM ctx_parameters
WHERE par_name = 'MAX_INDEX_MEMORY';
SELECT idx_docid_count, idx_status
INTO l_idx_docid_count, l_idx_status
FROM ctxsys.ctx_indexes
WHERE idx_owner = g_Prod_Schema AND idx_name = 'EAM_WORK_ORDER_TEXT_CTX1'
AND idx_table = 'EAM_WORK_ORDER_TEXT';
2 - Update / Rebuild
3 - Optimize
4 - Drop
5- When work order status code is updated from user defined statuses form. The Status_Id which was updated will be passed in p_dummy1 parameter
*/
PROCEDURE Build_Text_Index
(
ERRBUF OUT NOCOPY VARCHAR2
, RETCODE OUT NOCOPY NUMBER
, p_text_context IN NUMBER
, p_Action IN NUMBER
, p_dummy1 IN NUMBER DEFAULT NULL
, p_optlevel IN NUMBER DEFAULT NULL
, p_dummy2 IN NUMBER DEFAULT NULL
, p_maxtime IN NUMBER DEFAULT AD_CTX_DDL.Maxtime_Unlimited
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Build_Text_Index';
Log_Line ('Calling Eam_Text_Util.Process_Status_Update_Event ...', FND_LOG.LEVEL_EVENT, l_api_name);
Eam_Text_Util.Process_Status_Update_Event(p_status_id => p_dummy1, --User Defined Status Id whose status code has been updated
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status);
Log_Line ('after calling update status event ...Return Status is : '|| l_return_status, FND_LOG.LEVEL_EVENT, l_api_name);