I tried to store date in loop and after that dataset set to OUT SYS_REFCURSOR. In this time I declared array variable and I tried to store data in loop by each iteration. and after, I want to assign that array to OUT SYS_REFCURSOR for get array dataset to my application form.
My Query as:
CREATE OR REPLACE PROCEDURE aaaatest (p_result OUT SYS_REFCURSOR)
IS
input_file UTL_FILE.FILE_TYPE;
input_buffer VARCHAR2(4000);
amount VARCHAR2(4000);
account_no VARCHAR2(4000);
name VARCHAR2(4000);
bank_code VARCHAR2(4000);
type namesarray IS VARRAY(200000) OF VARCHAR2(4000);
names namesarray;
BEGIN
DELETE FROM aatest;
input_file := UTL_FILE.FOPEN ('VFILE_DIR', 'test.txt', 'R');
IF UTL_FILE.IS_OPEN(input_file) THEN
FOR i in 1 .. total
LOOP
BEGIN
UTL_FILE.GET_LINE(input_file, input_buffer);
amount := SUBSTR(input_buffer,46,19)||'.'||SUBSTR(input_buffer,65,2);
account_no := SUBSTR(input_buffer,12,12);
name := SUBSTR(input_buffer,24,20);
bank_code := SUBSTR(SUBSTR(input_buffer,5,7),1,4)||'-'||SUBSTR(SUBSTR(input_buffer,5,7),5,3);
names(i) := amount + account_no + name + bank_code;
IF input_buffer IS NULL
THEN
EXIT;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
END IF;
COMMIT;
UTL_FILE.FCLOSE(input_file);
OPEN p_result FOR 'SELECT * FROM DUAL';
END;
SELECT
statement?UTL_FILE.GET_LINE(input_file, input_buffer);
retrieve the above mentioned text file data as line by line. So I want to store that each line into the defined array. After finishing the iteration, I want to OUT that array dataset to OUT SYS_REFCURSOR. If you have another solution. I hope that. Request your solution or advices.