Hello Gurus,
Need help of a error which I am getting:
ORA-30625: method dispatch on NULL SELF argument is disallowed
In a Package Specification I have defined as:
CREATE OR REPLACE PACKAGE APPS.XXTEST_IMPORT_PKG
AS
TYPE file_type IS RECORD (file_name VARCHAR2 (500));
TYPE file_type_tbl IS TABLE OF file_type;
PROCEDURE download_files (p_file_type_tbl OUT file_type_tbl);
PROCEDURE read_data (p_file_type_tbl IN OUT file_type_tbl);
PROCEDURE MAIN (errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_process_recs IN VARCHAR2
,p_gl_date IN VARCHAR2
);
END XXTEST_IMPORT_PKG;
/
In a Package Body I have a sub procedure which been called like as below:
CREATE OR REPLACE PACKAGE BODY APPS.XXTEST_IMPORT_PKG
AS
g_error_flag VARCHAR2 (10) := 'N';
g_conc_request NUMBER := FND_GLOBAL.conc_request_id;
PROCEDURE download_files (p_file_type_tbl OUT file_type_tbl)
IS
l_list test_s3_pkg.t_object_list;
l_url VARCHAR2 (2000);
l_blob BLOB;
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_returnvalue CLOB;
l_raw RAW (32767);
l_file UTL_FILE.file_type;
l_buffer VARCHAR2(32767);--CLOB; --raw(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_clob_len INTEGER;
l_sl_aws_access VARCHAR2 (2000) := FND_PROFILE.VALUE ('TEST_ACCESS');
l_proxy_server VARCHAR2 (200) := FND_PROFILE.VALUE ('TEST_WEB_PROXY_SERVER');
l_gmt_offset_hrs NUMBER := FND_PROFILE.VALUE ('TEST_OFFSET');
l_sl_bucket VARCHAR2 (100);
l_aws_key VARCHAR2 (30);
l_aws_sec_key VARCHAR2 (100);
i NUMBER := 0;
l_file_type_tbl file_type_tbl := file_type_tbl ();
l_clob_val CLOB;
CURSOR c_sl_files
IS
SELECT key
FROM TABLE (test_s3_pkg.get_object_tab (l_sl_bucket)) t
WHERE NOT EXISTS
(SELECT 1
FROM apps.TEST_INVOICE_IMPORT
WHERE file_name = t.key);
BEGIN
SELECT SUBSTR (l_sl_aws_access, 1, INSTR (l_sl_aws_access, ';', 1) - 1) sl_bucket
,SUBSTR (l_sl_aws_access
,INSTR (l_sl_aws_access, ';', 1) + 1
, (INSTR (l_sl_aws_access
,';'
,1
,2
)
- 1)
- (INSTR (l_sl_aws_access, ';', 1))
)
aws_key
,SUBSTR (l_sl_aws_access
, (INSTR (l_sl_aws_access
,';'
,1
,2
)
+ 1)
)
aws_sec_key
INTO l_sl_bucket
,l_aws_key
,l_aws_sec_key
FROM DUAL;
test_auth_pkg.init (l_aws_key, l_aws_sec_key, p_gmt_offset => l_gmt_offset_hrs);
UTL_HTTP.set_proxy (l_proxy_server);
debug_pkg.debug_on;
l_list := test_s3_pkg.get_object_list (l_sl_bucket);
IF l_list.COUNT > 0
THEN
FOR i IN 1 .. l_list.COUNT
LOOP
debug_pkg.printf ('list(%1) = %2, last modified = %3'
,i
,l_list (i).key
,l_list (i).last_modified
);
END LOOP;
END IF;
i := 1;
FOR c_sl_files_rec IN c_sl_files
LOOP
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Downloading File: '||c_sl_files_rec.key);
--Download File
l_url := test_s3_pkg.get_download_url (l_sl_bucket, c_sl_files_rec.key, SYSDATE + 1);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Download URL:' || l_url);
l_http_request := UTL_HTTP.begin_request (l_url);
l_http_response := UTL_HTTP.get_response (l_http_request);
FND_FILE.PUT_LINE (FND_FILE.LOG, l_http_response.status_code);
FND_FILE.PUT_LINE (FND_FILE.LOG, l_http_response.reason_phrase);
--l_blob := http_util_pkg.get_blob_from_url (l_url);
DBMS_LOB.createtemporary (l_clob_val, FALSE);
-- Read response and store as blob
BEGIN
LOOP
UTL_HTTP.read_line (l_http_response, l_returnvalue, FALSE);
FND_FILE.PUT_LINE (FND_FILE.LOG, l_returnvalue);
DBMS_LOB.writeappend (l_clob_val, DBMS_LOB.getlength (l_returnvalue), l_returnvalue);--DBMS_LOB.SUBSTR (l_returnvalue, 32767, 1));
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
UTL_HTTP.end_response (l_http_response);
END;
-- Read response and store as blob
l_clob_len := DBMS_LOB.getlength (l_clob_val);
FND_FILE.PUT_LINE (FND_FILE.LOG, l_clob_len);
l_file := UTL_FILE.fopen ('TEST_SL_DIR', c_sl_files_rec.key, 'w');
l_pos := 1;
WHILE l_pos < l_clob_len
LOOP
FND_FILE.PUT_LINE (FND_FILE.LOG,'l_pos:'||l_pos);
DBMS_LOB.read (l_clob_val
,l_amount
,l_pos
,l_buffer
);
--UTL_FILE.GET_LINE(F1,V1,32767)
utl_file.put_raw (l_file, UTL_RAW.cast_to_raw(l_buffer), true);
FND_FILE.PUT_LINE (FND_FILE.LOG, l_buffer);
--UTL_FILE.put_line (l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
FND_FILE.PUT_LINE (FND_FILE.LOG,'l_pos:'||l_pos);
END LOOP;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Assiging File Names' || i);
l_file_type_tbl.EXTEND;
l_file_type_tbl (i).file_name := c_sl_files_rec.key;
i := i + 1;
UTL_FILE.fclose (l_file);
END LOOP;
p_file_type_tbl := l_file_type_tbl;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error while downloading files. ' || SQLCODE || SQLERRM);
g_error_flag := 'Y';
END download_files;
PROCEDURE MAIN (errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_process_recs IN VARCHAR2
,p_gl_date IN VARCHAR2
)
IS
download_files (x_file_type_tbl);
IF x_file_type_tbl.COUNT > 0
THEN
read_data (x_file_type_tbl);
END IF;
/****There are code for validation****/
END main;
END XXTEST_IMPORT_PKG;
Please can you advise in the above pseudocode of the Package Body if there is NULL encountered in the condition
IF x_file_type_tbl.COUNT > 0 how to bypass the collection count if 0 or is it failing in the dowload_files procedure where the downloading of files happen which is causing the ::ORA-30625: method dispatch on NULL SELF argument is disallowed??
Please help