/******************************************************************
* Package: XX_EXTERNAL_DATA_LOADING_PKG
* Purpose: This package is used in concurrent program to:
* (1) Extract BLOB from FND_LOB to the specified directory.
* (2) Extract a FND message from FND_NEW_MESSAGES table and then
* parse this message as a SQL script.
* Written by: Christopher Ho 02-DEC-2009
*******************************************************************/
CREATE OR REPLACE PACKAGE XX_EXTERNAL_DATA_LOADING_PKG AS
PROCEDURE MAIN (errbuf OUT NOCOPY VARCHAR2,
rtcode OUT NOCOPY VARCHAR2,
p_entityName VARCHAR2,
p_datafileID NUMBER,
p_messageName VARCHAR2,
p_directory VARCHAR2);
END XX_EXTERNAL_DATA_LOADING_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_EXTERNAL_DATA_LOADING_PKG AS
TYPE T_ARRAY IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;
FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2)
RETURN T_ARRAY IS
i NUMBER := 0;
pos NUMBER := 0;
lv_str VARCHAR2 (2000) := p_in_string;
strings T_ARRAY;
BEGIN
-- determine first chuck of string
pos := INSTR (lv_str, p_delim, 1, 1);
-- while there are chunks left, loop
WHILE (pos != 0) LOOP
-- increment counter
i := i + 1;
-- create array element for chuck of string
strings (i) := SUBSTR (lv_str, 1, pos);
-- remove chunk from string
lv_str := SUBSTR (lv_str, pos + 1, LENGTH (lv_str));
-- determine next chunk
pos := INSTR (lv_str, p_delim, 1, 1);
-- no last chunk, add to array
IF pos = 0 THEN
strings (i + 1) := lv_str;
END IF;
END LOOP;
-- return array
RETURN strings;
END SPLIT;
PROCEDURE EXTRACT_LOB_TO_FILE (p_fileID NUMBER, p_directory VARCHAR2) IS
var_fileName VARCHAR2 (100);
blob_data BLOB;
num_startPos NUMBER := 1;
num_byteLen NUMBER := 32000;
len NUMBER;
my_vr RAW (32000);
x NUMBER;
l_output UTL_FILE.FILE_TYPE;
BEGIN
SELECT tab4.FILE_NAME, tab4.FILE_DATA
INTO var_fileName, blob_data
FROM FND_LOBS tab4
WHERE file_id = p_fileID;
-- define output directory
l_output := UTL_FILE.FOPEN (p_directory, var_fileName, 'wb', 32760);
num_startPos := 1;
num_byteLen := 32000;
-- get length of blob
SELECT DBMS_LOB.GETLENGTH (blob_data) INTO len FROM DUAL;
-- save blob length
x := len;
-- if small enough for a single write
IF len < 32760 THEN
UTL_FILE.put_raw (l_output, blob_data);
UTL_FILE.fflush (l_output);
ELSE -- write in pieces
num_startPos := 1;
WHILE num_startPos < len AND num_byteLen > 0 LOOP
DBMS_LOB.read (blob_data, num_byteLen, num_startPos, my_vr);
UTL_FILE.put_raw (l_output, my_vr);
UTL_FILE.fflush (l_output);
-- set the start position for the next cut
num_startPos := num_startPos + num_byteLen;
-- set the end position if less than 32000 bytes
x := x - num_byteLen;
IF x < 32000 THEN
num_byteLen := x;
END IF;
END LOOP;
END IF;
UTL_FILE.FCLOSE (l_output);
END EXTRACT_LOB_TO_FILE;
PROCEDURE PARSE_SQL_FROM_FILE (p_filename VARCHAR2
, p_directory VARCHAR2) IS
l_output UTL_FILE.FILE_TYPE;
var_text VARCHAR2 (200);
var_sql VARCHAR2 (2000) := '';
err_num NUMBER;
err_msg VARCHAR2 (100);
BEGIN
l_output := UTL_FILE.FOPEN (p_directory, p_filename, 'R');
LOOP
UTL_FILE.get_line (l_output, var_text);
var_text := RTRIM (var_text);
IF var_text = '' THEN
EXIT;
END IF;
IF var_text = '/' THEN
BEGIN
FND_FILE.PUT_LINE (FND_FILE.LOG, var_sql);
EXECUTE IMMEDIATE var_sql;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLCODE || '-' || err_msg);
END;
var_sql := '';
ELSE
var_sql := var_sql || var_text;
END IF;
END LOOP;
UTL_FILE.FCLOSE (l_output);
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLCODE || '-' || err_msg);
END PARSE_SQL_FROM_FILE;
PROCEDURE PARSE_SQL_FROM_MESSAGE (p_sqlMsg VARCHAR2) IS
arry_sqlStr T_ARRAY;
var_line VARCHAR2 (100);
var_sql VARCHAR2 (2000) := '';
err_num NUMBER;
err_msg VARCHAR2 (100);
BEGIN
arry_sqlStr := SPLIT (p_sqlMsg, CHR (10));
FND_FILE.PUT_LINE (FND_FILE.LOG, 'No of line: ' || arry_sqlStr.COUNT);
FOR i IN 1 .. arry_sqlStr.COUNT LOOP
var_line := TRIM (arry_sqlStr (i));
IF LENGTH (var_line) > 0 THEN
IF SUBSTR (var_line, 1, 1) = '/' THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, var_sql);
FND_FILE.PUT_LINE (FND_FILE.LOG, '-------------------------------');
BEGIN
EXECUTE IMMEDIATE var_sql;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLCODE || '-' || err_msg);
END;
var_sql := '';
ELSE
var_sql := var_sql || var_line;
END IF;
END IF;
END LOOP;
END PARSE_SQL_FROM_MESSAGE;
PROCEDURE MAIN (errbuf OUT NOCOPY VARCHAR2,
rtcode OUT NOCOPY VARCHAR2,
p_entityName VARCHAR2,
p_datafileID NUMBER,
p_messageName VARCHAR2,
p_directory VARCHAR2) IS
var_fileName VARCHAR2 (100);
var_directoryPath VARCHAR2 (200);
var_appShortName VARCHAR2 (30);
BEGIN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_entityName=' || p_entityName);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_datafileID=' || p_datafileID);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_messageName=' || p_messageName);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_directory=' || p_directory);
SELECT DIRECTORY_PATH
INTO var_directoryPath
FROM DBA_DIRECTORIES
WHERE DIRECTORY_NAME = p_directory;
SELECT FILE_NAME
INTO var_fileName
FROM FND_LOBS
WHERE file_id = p_datafileID;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Extract ' || var_fileName || ' to ' || var_directoryPath);
EXTRACT_LOB_TO_FILE (p_datafileID, p_directory);
IF p_messageName IS NOT NULL THEN
SELECT APPLICATION_SHORT_NAME
INTO var_appShortName
FROM FND_APPLICATION tab1, FND_NEW_MESSAGES tab2
WHERE tab1.application_id = tab2.application_id
AND tab2.message_name = p_messageName;
FND_MESSAGE.SET_NAME (var_appShortName, p_messageName);
FND_MESSAGE.SET_TOKEN ('DIRECTORY_NAME', p_directory, FALSE);
FND_MESSAGE.SET_TOKEN ('DATAFILE_NAME', var_fileName, FALSE);
PARSE_SQL_FROM_MESSAGE (FND_MESSAGE.GET);
END IF;
END MAIN;
END XX_EXTERNAL_DATA_LOADING_PKG;
/
XX_EXTERNAL_DATA_LOADING_PKG