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? 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.
    – Community Bot
    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.
    – Neo4242
    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? Nov 14, 2021 at 11:07

0

Your Answer

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