0

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;
4
  • 1
    Using a Cursor for your form may not be the best method. What framework are you using? Can it use a SELECT statement? Commented Nov 9, 2021 at 10:11
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Commented Nov 9, 2021 at 13:59
  • In this time 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. Commented Nov 10, 2021 at 10:58
  • A simpler approach might be to define an external table over the file, and then just query it. Would that be possible? Commented Nov 14, 2021 at 11:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.