I am registering this package as concurrent program in Oracle.
Here in the directory 'DATA_NOT_RETAIL_INCOME' i will have only one file but I don't know the full name of the file.
While registering as concurrent program in oracle how can I pass the parameter to the file name as the file name is not fixed. But in the directory we have only one file.
CREATE OR REPLACE PACKAGE BODY grm_detail(p_filename1 IN VARCHAR2) IS
v_file UTL_FILE.FILE_TYPE;
v_source VARCHAR2(4);
v_acc_date VARCHAR2(10);
v_account VARCHAR2(40);
v_transit VARCHAR2(40);
v_trans_type VARCHAR2(40);
v_amt_type VARCHAR2(40);
v_currency VARCHAR2(40);
v_amount VARCHAR2(40);
v_sign VARCHAR2(40);
g_grm_batch_id NUMBER;
v_file_path VARCHAR2(1000);
v_datstring VARCHAR2(32767) := NULL;
v_file_date VARCHAR2(10);
v_invalid_file_exception EXCEPTION;
v_file_name VARCHAR2(1000) := p_filename1;
BEGIN
SELECT xxbns_grm_batch_id_seq.NEXTVAL
INTO g_grm_batch_id
FROM dual;
v_file := utl_file.fopen(location => 'DATA_NOT_RETAIL_INCOME',
filename => v_file_name,
open_mode => 'r',
max_linesize => 32767);
LOOP
BEGIN
utl_file.get_line(v_file, v_datstring);
IF substr(v_datstring, 1, 4) = 'Test' THEN
v_source := substr(v_datstring, 1, 4);
v_acc_date := substr(v_datstring, 5, 6);
v_account := substr(v_datstring, 11, 7);
v_transit := substr(v_datstring, 18, 5);
v_trans_type := substr(v_datstring, 23, 1);
v_amt_type := substr(v_datstring, 24, 1);
v_currency := substr(v_datstring, 25, 3);
v_amount := substr(v_datstring, 28, 27);
v_sign := substr(v_datstring, 55, 1);
END IF;
BEGIN
INSERT INTO XXBNS_GRM_DATA_LOAD
(FILE_NAME,
BATCH_ID,
SOURCE,
ACCOUNTING_DATE,
ACCOUNT,
TRANSIT,
TRANS_TYPE,
AMT_TYPE,
CURRENCY,
AMOUNT,SIGN)
VALUES(v_file_name,
g_grm_batch_id,
v_source,
v_acc_date,
v_account,
v_transit,
v_trans_type,
v_amt_type,
v_currency,
v_amount,
v_sign);
COMMIT;
END;
END LOOP;
v_datstring := NULL;
IF utl_file.is_open(v_file) THEN
utl_file.fclose(v_file);
END IF;
END grm_detail;
I assume this is related to question
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9536325800346804830 In that case, change
PROCEDURE grm_detail(p_filename1 IN VARCHAR2)
to
PROCEDURE grm_detail(p_filename1 IN VARCHAR2 default null)
and then add some processing which will work out the filename if not passed, ie,
if p_filename1 is null then
select file_name
into v_filenae
from external_table_that_does_a_dir_listing
where rownum = 1;
else
v_filename := p_filename1;
end if;
v_file := utl_file.fopen(location => 'DATA_NOT_RETAIL_INCOME',
filename => v_filename,
open_mode => 'r',
max_linesize => 32767);
That way your routine will pick up the right filename by default, but if you ever want to explicitly process a known file you can also do it.