UTL_FILE WITH %ROWTYPE .. ERROR

Question

I want to create procedure that get rows from employees record whose salary more then input number. And then get that output to text file. I don’t understand where i am doing wrong. Please help me out. Thanks in advance.

I have employees table as structured below. Desc TABLE employees ; Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

and when I want to run below script.

    CREATE OR REPLACE PROCEDURE P_TEST3 ( P_SAL NUMBER) IS

    TYPE EMP_TEMP IS TABLE OF employees%ROWTYPE;
   V_EMP_ROW     EMP_TEMP ;


 V1 utl_file.file_type;

 E1 utl_file.file_type;

    BEGIN


 V1 := utl_file.fopen('ABC','VALID.txt','W');
E1 := utl_file.fopen('ABC','ERROR.txt','W');

SELECT *  BULK COLLECT INTO V_EMP_ROW   FROM Employees  WHERE salary > p_sal   ;

FOR i IN  V_EMP_ROW.FIRST .. V_EMP_ROW.LAST 
    LOOP
    UTL_FILE.PUT_LINE (V1, V_EMP_ROW(I).EMPLOYEE_ID ||',' || V_EMP_ROW(I).FIRST_NAME ||','|| V_EMP_ROW(I).SALARY );
    END LOOP;

  dbms_output.put_line ('Total row inserted '||sql%rowcount || '.  Please see valid.txt file ' );


EXCEPTION

WHEN OTHERS THEN
raise_application_error( -20002,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

utl_file.put_line (E1,SQLCODE || '---' || SQLERRM);

DBMS_OUTPUT.PUT_LINE ('PLEASE SEE Error.txt FILE');

utl_file.fclose(v1);
utl_file.fclose(E1);

 END P_TEST3;

/

And the compiler give below error. [Warning] ORA-24344: success with compilation error 21/42 PLS-00302: component 'EMPLOYEE_ID' must be declared 21/9 PL/SQL: Statement ignored (1: 0): Warning: compiled but with compilation errors

score 0 · Answer 1 · answered Feb 22 '13 at 19:47

The line giving you the error is 21, which appears to be:

UTL_FILE.PUT_LINE (V1, V_EMP_ROW(I).EMPLOYEE_ID ||',' || V_EMP_ROW(I).FIRST_NAME ||','|| V_EMP_ROW(I).SALARY );

I would suggest that the issue is that either no rows are being returned in your BULK SELECT statement, or they aren't in the format you expect.

Try putting in something like:

dbms_output.put_line('Record is: '|| V_EMPROW(i));

as the first line in your loop.

Or, maybe do a record count of the bulk select results prior to the loop:

dbms_output.put_line('Record count is: '|| V_EMPROW.COUNT);

UTL_FILE WITH %ROWTYPE .. ERROR

1 Answers1