Symptoms
1. When selecting File/Export the data appears to export but the dialog box (to Open or Save) never appears.
2. The FND_GFM package body is at version 115.56.
STEPS
1. File > Export from any form
2. The transfer popup briefly flashes.
EXPECTED BEHAVIOR
Expect a dialog box to appear asking to Open or Save.
Cause
The package FND_GFM is failing to upload the exported data into the FND_LOBS table.
Per bug 5642586 sometimes the table index is corrupted but aflobbld.sql
does not see the corruption when building the FND_LOBS table.
Solution
1. Determine the total available tablespace storage space:
SQL> SELECT SUM(bytes) FROM DBA_FREE_SPACE WHERE tablespace_name='CTXD';
2. If storage is low, add a datafile to the problem tablespace:
SQL> ALTER TABLESPACE ADD DATAFILE 'filespec' SIZE xxM;
3. Re-compile invalid object
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
4. Then run $FND_TOP/sql/aflobbld.sql to build the FND_LOBS table.
There is two value input:
1) The name of the APPLSYS schema (ie: APPLSYS)
2) The name of the APPS schema (ie: APPS)
5. Retest for File > Export
Appendix
Detail of aflobbld.sql :
REM dbdrv: noneREM +==================================================================+REM | Copyright (c) 1999 Oracle Corporation Redwood Shores, California, USA |REM | All rights reserved. |REM +==================================================================+REM | FILENAMEREM | aflobbld.sqlREM |REM | DESCRIPTIONREM | This file both creates and maintains the FND_LOBS_CTXREM | intermedia index on the FND_LOBS table.REM |REM | Specifically, this script:REM |REM | 1) Creates all of the required interMedia preferencesREM | 2) Rebuilds FND_LOBS_CTX from scratch (but ONLY WHEN theREM | index is determined to be invalid or missing.REM | 3) Refreshes FND_LOBS_CTX online to pick up any data changesREM | made since the last refresh or rebuild.REM |REM | ARGUMENTSREM | This script takes two:REM |REM | 1) The name of the APPLSYS schema (ie: APPLSYS)REM | 2) The name of the APPS schema (ie: APPS)REM |REM | NOTESREM | Call this module after loading help files with FNDGFU or any timeREM | you want to refresh the interMedia index FND_LOBS_CTX on FND_LOBS.REM | This file will detect a missing or invalid index and rebuild fromREM | scratch when appropriate.REM |REM | This script encapsulates all of the logic previously found inREM | AFLOBCX2.sql, AFLOBCX3.sql and previous versions of aflobbld.sql.REM +==================================================================+REM $Header: aflobbld.sql 115.22 2004/09/02 01:43:31 mskees ship $set verify off;WHENEVER OSERROR EXIT FAILURE ROLLBACK;whenever sqlerror exit failure rollback;-- set serveroutput on size 100000declare myDebugFlag boolean := false; indx_schema varchar2(30); exec_schema varchar2(31); cursor c_multi_lexer is select pre_owner, pre_name from ctx_preferences where pre_name like 'FND_LOBS_G%'; cursor c_pref_list is select pre_owner, pre_name from ctx_preferences where pre_name like 'FND_LOBS%'; tspace varchar2(256); tspace2 varchar2(256); tspace3 varchar2(256); stmt varchar2(1000); mycount number; cursor oldIndex is select OWNER from all_indexes where INDEX_NAME = 'FND_LOBS_CTX' and OWNER in (select ORACLE_USERNAME from FND_ORACLE_USERID);begin indx_schema := '&1'; exec_schema := '&2'; -- Remove the comment on next line to force the index to be dropped and recreated -- myDebugFlag := true;-- **********************************************************************-- ** **-- ** BEGIN "create preferences" SECTION **-- ** **-- ** This section creates the interMedia preferences to be used when **-- ** when creating the fnd_lobs_ctx index on the FND_LOBS table. **-- ** Our interMedia preferences are owned by APPS. **-- ********************************************************************** -------------------------------- -- Remove any old preferences -- -- Remove any old preferences -- -------------------------------- -- dbms_output.put_line('Dropping old preferences ...'); for dropit in c_multi_lexer loop ad_ctx_ddl.drop_preference( dropit.pre_owner || '.' || dropit.pre_name ); end loop; for dropit in c_pref_list loop ad_ctx_ddl.drop_preference( dropit.pre_owner || '.' || dropit.pre_name ); end loop; ------------------------------------------ -- Create the basic wordlist preference -- ------------------------------------------ -- dbms_output.put_line('Create the wordlist ...'); ad_ctx_ddl.create_preference('FND_LOBS_BASIC_WORDLIST_2','BASIC_WORDLIST'); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_WORDLIST_2','STEMMER','AUTO'); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_WORDLIST_2','FUZZY_MATCH','AUTO'); ------------------------------------------ -- Create the charset_filter preference -- ------------------------------------------ ad_ctx_ddl.create_preference('FND_LOBS_CHARSET_FILTER_2','CHARSET_FILTER'); -- Set the default character set to be UTF8 when none specified ad_ctx_ddl.set_attribute('FND_LOBS_CHARSET_FILTER_2','CHARSET','UTF8'); ---------------------------------------------------------------------- -- Create the multi lexer preference and its constituent components -- ---------------------------------------------------------------------- -- dbms_output.put_line('Create the lexers ...'); ad_ctx_ddl.create_preference('FND_LOBS_GBL_LEXER_2','multi_lexer'); ad_ctx_ddl.create_preference('FND_LOBS_BLEXER_2', 'basic_lexer'); ad_ctx_ddl.create_preference('FND_LOBS_CLEXER_2', 'chinese_vgram_lexer'); ad_ctx_ddl.create_preference('FND_LOBS_JLEXER_2', 'japanese_vgram_lexer'); ad_ctx_ddl.create_preference('FND_LOBS_KLEXER_2', 'korean_lexer'); ad_ctx_ddl.add_sub_lexer('FND_LOBS_GBL_LEXER_2','JA','FND_LOBS_JLEXER_2', n ad_ctx_ddl.add_sub_lexer('FND_LOBS_GBL_LEXER_2','KO','FND_LOBS_KLEXER_2', n ad_ctx_ddl.add_sub_lexer('FND_LOBS_GBL_LEXER_2','ZHS','FND_LOBS_CLEXER_2', ad_ctx_ddl.add_sub_lexer('FND_LOBS_GBL_LEXER_2','ZHT','FND_LOBS_CLEXER_2', ad_ctx_ddl.add_sub_lexer('FND_LOBS_GBL_LEXER_2','default', 'FND_LOBS_BLEXER ----------------------------------------- -- Create the storage preference -- ----------------------------------------- -- use the table space where all of the other fnd indexes live -- select 'tablespace ' || TABLESPACE_NAME || ' storage(initial 4K next 4M minextents 1 maxextents unlimited)' into tspace from ALL_INDEXES where INDEX_NAME = 'FND_CONCURRENT_REQUESTS_U1'; -- use the default tablespace for APPLSYS for dynamically created LOBs -- select 'tablespace ' || DEFAULT_TABLESPACE || ' storage(maxextents unlimited)' into tspace2 from DBA_USERS where USERNAME = upper( indx_schema ); tspace3 := 'store as ' || '('||tspace2||')'; -- dbms_output.put_line('Create the storage preference ...'); ad_ctx_ddl.create_preference('FND_LOBS_BASIC_STORAGE_2', 'BASIC_STORAGE'); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_STORAGE_2', 'I_TABLE_CLAUSE', 'lob(token_info) ' || tspace3); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_STORAGE_2', 'K_TABLE_CLAUSE', tspace); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_STORAGE_2', 'R_TABLE_CLAUSE', 'lob(data) store as (cache) ' || tspace2 ); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_STORAGE_2', 'N_TABLE_CLAUSE', tspace); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_STORAGE_2', 'I_INDEX_CLAUSE', tspace); ad_ctx_ddl.set_attribute('FND_LOBS_BASIC_STORAGE_2', 'P_TABLE_CLAUSE', tspace);-- ****************************************-- ** **-- ** END "create Preferences" section **-- ** **-- ** All of the required interMedia **-- ** preferences now exist **-- ****************************************-------------------------------------------------------------------------------- **********************************************************************-- ** **-- ** BEGIN "create index" SECTION **-- ** **-- ** This section checks to see if the FND_LOBS_CTX index already **-- ** exists and is healthy. If so, we skip ahead to the "refresh **-- ** index" section. If not, we create the index from scratch. **-- ** index" section. If not, we create the index from scratch. **-- ** FND_LOBS_CTX lives in APPLSYS, along with the other FND indexes. **-- ********************************************************************** -------------------------------------------------------------------- -- Change to APPLSYS schema so that the index will be owned there -- -------------------------------------------------------------------- execute immediate 'alter session set current_schema = ' || indx_schema; if ( NOT myDebugFlag ) then ------------------------------------------------------------------------ -- check for healthy index and only rebuild from scratch if necessary -- ------------------------------------------------------------------------ select count(1) into mycount from all_indexes where owner = 'APPLSYS' and index_name = 'FND_LOBS_CTX' and table_name = 'FND_LOBS' and status = 'VALID'; if (mycount = 1) then begin select count(1) into mycount from fnd_lobs where contains(file_data, 'cat')>0; ---------------------------------------------------- -- Looks like a healthy index. Skip ahead to the -- -- "refresh index" section below -- ---------------------------------------------------- -- dbms_output.put_line('Index exists - Going straight to refresh'); goto l_Refresh; exception -- Need to rebuild from scratch so proceed to drop when others then null; end; end if; end if; ----------------------------- -- drop index if necessary -- ----------------------------- ----------------------------- for dropit in oldIndex loop -- dbms_output.put_line('Dropping Index ...'); execute immediate 'drop index '|| dropit.owner ||'.fnd_lobs_ctx force'; end loop; delete from fnd_lobs where file_id = -1; fnd_imutl.help_cleanup(); -- we need at least one blob to index it ... select count(file_id) into mycount from fnd_lobs; if (mycount = 0) then insert into fnd_lobs (file_id,file_content_type,oracle_charset,file_format) values (-1,'text/html','UTF8','TEXT'); end if; commit; begin ---------------------- -- create the index -- ---------------------- -- dbms_output.put_line('Create the Index ...'); stmt := ' create index fnd_lobs_ctx on fnd_lobs(file_data) '|| ' indextype is ctxsys.context '|| ' parameters(''lexer ' || exec_schema || '.fnd_lobs_gbl_lexer_2 '|| ' language column language '|| ' filter ' || exec_schema || '.fnd_lobs_charset_filter_2 '|| ' format column file_format '|| ' charset column oracle_charset '|| ' storage ' || exec_schema || '.fnd_lobs_basic_storage_2 '|| ' wordlist ' || exec_schema || '.fnd_lobs_basic_wordlist_2'')'; execute immediate stmt; -- -- This added as per BDE suggestion JEStone for improved performance -- -- dbms_output.put_line( 'Rebuild it compressed ...' ); execute immediate 'alter index DR$FND_LOBS_CTX$X rebuild compress 2'; exception when others then ---------------------------------------------------------------- -- Remove incomplete index. Otherwise, it may lock the table -- when others then ---------------------------------------------------------------- -- Remove incomplete index. Otherwise, it may lock the table -- ---------------------------------------------------------------- execute immediate 'drop index applsys.fnd_lobs_ctx force'; raise; end;-- ***************************************-- ** **-- ** END "create index" section **-- ** **-- ** The FND_LOBS_CTX index now exists **-- *************************************** return;----------------------------------------------------------------------------- **********************************************************************-- ** **-- ** BEGIN "refresh index" SECTION **-- ** **-- ** This section refreshes the existing FND_LOBS_CTX index. **-- ** As part of this refresh, we first remove any orphaned Help rows **-- **********************************************************************<<l_Refresh>> ----------------------------------------------------------- -- Change session back to APPS to run the refresh script -- ----------------------------------------------------------- execute immediate 'alter session set current_schema = ' || exec_schema; -- dbms_output.put_line('Refresh the Index ...'); fnd_imutl.maintain_index(p_index_name => 'fnd_lobs_ctx', p_callback => 'fnd_imutl.help_cleanup', p_app_short_name => 'FND', p_mode => 'sync');end;/commit;exit;
No comments:
Post a Comment