1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cursor for loop & utl_file

Discussion in 'SQL PL/SQL' started by ericqo, Jun 16, 2013.

  1. ericqo

    ericqo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I am running the block below and expect to retrieve multiple files depending on the first cursor

    CURSOR bill_to_no IS
    SELECT lookup_code FROM fnd_lookup_values_vl WHERE lookup_type = 'tst';

    CURSOR invoice_trx (bill_to_num N fnd_lookup_values_vl.lookup_code%TYPE) IS
    SELECT trx_number from ra_customer_trx_all WHERE trx_number in ('1234', '5456');

    BEGIN
    FOR c_rec in bill_to_no



    END;
     
  2. ericqo

    ericqo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Please disregard previous entry. I am looking to generate multiple files based on the records from the first cursor but I am getting either an empty file on the second file. Could you please look at the block below and let me know what I am missing.

    DECLARE

    ufile_type UTL_FILE.file_type;

    CURSOR bill_to_no IS
    SELECT lookup_code, meaning file_name
    FROM fnd_lookup_values_vl WHERE lookup_type = 'tst';

    CURSOR invoice_trx (bill_to_num IN fnd_lookup_values_vl.lookup_code%TYPE) IS
    SELECT trx_number from ra_customer_trx_all WHERE trx_number in ('1234', '5456');

    BEGIN
    FOR c_rec in bill_to_no
    LOOP
    FOR inv_rec in invoice_trx
    LOOP
    cfilename := c_rec.file_name||TO_CHAR (SYSDATE, 'YYYYMMDD')||'.csv';
    ufile_type := UTL_FILE.fopen (coutputpath, cfilename, 'W');
    UTL_FILE.PUT_LINE(ufile_type, c_rec.lookup_code||','||inv_rec.trx_number);


    END loop;

    END LOOP;

    utl_file.fclose(ufile_type);

    END;
     
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    I don't think that you may be able to even compile the above code. Have you tried to compile it in SQL*Plus?

    First you define a cursor which accepts an argument named bill_to_num.
    Code (SQL):

    CURSOR invoice_trx (bill_to_num IN fnd_lookup_values_vl.lookup_code%TYPE) IS
    SELECT trx_number FROM ra_customer_trx_all WHERE trx_number IN ('1234', '5456');
     
    But if you look carefully your code, there is actually no argument provided for this cursor once the cursor has been opened (within the second inner FOR LOOP). This will not work because there is no effective parameter provided unless you have already declared a default value for the formal parameter in the cursor definition which is not the case in your example. The following example may give you an idea how to deal with this kind of cursor
    Code (SQL):

    DECLARE
        CURSOR empsCur(p_depId PLS_INTEGER)
        RETURN hr.employees%ROWTYPE IS
        SELECT *
        FROM hr.employees
        WHERE department_id = p_depId;
       
        emp empsCur%ROWTYPE;
    BEGIN
        OPEN empsCur(p_depId=>100); -- Here you provide the effective parameter for the cursor
        LOOP
            FETCH empsCur INTO emp;
            EXIT WHEN empsCur%NOTFOUND;
            sys.dbms_output.put_line
            (
                'employee_id = ' || emp.employee_id || CHR(9) ||
                'first_name = '  || emp.first_name  || CHR(9) ||
                'salary = '      || emp.salary
            );
        END LOOP;
        CLOSE empsCur;
    END;
    /



    employee_id = 108   first_name = Nancy            salary = 12008
    employee_id = 109   first_name = Daniel           salary = 9000
    employee_id = 110   first_name = John             salary = 8200
    employee_id = 111   first_name = Ismael           salary = 7700
    employee_id = 112   first_name = Jose Manuel      salary = 7800
    employee_id = 113   first_name = Luis             salary = 6900

    PL/SQL PROCEDURE successfully completed.

    SQL>

     
    And the same is true if you want to open/explore/close implicitly the cursor within a FOR LOOP
    Code (SQL):

    DECLARE
        CURSOR empsCur(p_depId PLS_INTEGER)
        RETURN hr.employees%ROWTYPE IS
        SELECT *
        FROM hr.employees
        WHERE department_id = p_depId;
       
        emp empsCur%ROWTYPE;
    BEGIN
        FOR emp IN empsCur(p_depId=>100) LOOP
            sys.dbms_output.put_line
            (
                'employee_id = ' || emp.employee_id || CHR(9) ||
                'first_name = '  || emp.first_name  || CHR(9) ||
                'salary = '      || emp.salary
            );
        END LOOP;
    END;
    /
     
    Take a look at the online documentation which goes through this will enough details and examples.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#CIHFAHFE

    Looking at your nested loops, I'm not sure whether you cannot proceed in a more efficient way, that is, instead of opening within the inner loop the invoice_trx cursor for each entry of the bill_to_no cursor (which means that its SQL query is evaluated once for each row of the query of the bill_to_no cursor) you may be able to do some correlated query for a single cursor including the whole information. This also has been explained in the documentation

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#i3317
    Other point is when you close the files. If there is one file per each first loop entry, then I think instead of writing
    Code (SQL):

    FOR . . .
        FOR . . .
            . . .
        END LOOP;
    END LOOP;
    utl_file.fclose(ufile_type);
     
    You should rather write

    Code (SQL):

    FOR . . .
        FOR . . .
            . . .
        END LOOP;
        utl_file.fclose(ufile_type);
    END LOOP;
     
    Otherwise, for the next iteration the file resource still references the resource manipulated during the previous iteration and possibly the output has not been completely flushed yet.

    I don't have details about your tables, your data so I cannot go further to explain about the real cause.