ROWTYPE AND FETCH COMMAND ERROR

Create or replace package body file as

procedure filehandler as
declare

V_PERSON PERSON%ROWTYPE;

FID UTL_FILE.FILE_TYPE;
FOLDER_NAME VARCHAR(50) := 'GID_FILE';
FILE_NAME VARCHAR(50) :='bhanu.txt';

cusor test_persondetails is 

select  personid, personnum,fullnm from person where rownum<=10;

begin

open test_persondetails;

FID := UTL_FILE.FOPEN (FOLDER_开发者_运维百科NAME, FILE_NAME, 'A');

fetch test_persondetails into V_person;

DBMS_OUTPUT.PUT_LINE(V_PERSON);

UTL_FILE.PUT_LINE (FID,V_PERSON);

UTL_FILE.FCLOSE (FID);

close test_persondetails;

end test_filehandler;

end test_file;

i am getting some errors related to the fetch statement , can you please suggest me the changes that can be done that code......

You need to tell us which errors you get, as looking at your source there are potentially many of them.

For instance your cursor will select up to ten rows. However you are not executing a loop, so your fetch will hurl TOO_MANY_ROWS if there is more than one row in the PERSON table.

Also, you need to explicitly reference the individual elements of the cursor not the %ROWTYPE variable.

Furthermore, the projection of the query must match the variable you fetch into (number of columns, datatype of columns, etc). It is easier to use the CURSOR FOR syntax, and let Oracle handle it for us. This also solves the TOO_MANY_ROWS problem.

So I would rewrite your code to look like this:

procedure filehandler as


    FID UTL_FILE.FILE_TYPE;
    FOLDER_NAME VARCHAR(50) := 'GID_FILE';
    FILE_NAME VARCHAR(50) :='bhanu.txt';

begin

    FID := UTL_FILE.FOPEN (FOLDER_NAME, FILE_NAME, 'A');

    for V_PERSON in ( select  personid, personnum,fullnm 
                        from person where rownum<=10 )
    loop

        DBMS_OUTPUT.PUT_LINE(V_PERSON.personid);

        UTL_FILE.PUT_LINE (FID,V_PERSON.personid
               ||' '||V_PERSON.personnum
               ||' '||V_PERSON.fullnm);

    end loop;

    UTL_FILE.FCLOSE (FID);

end test_filehandler;

Opening and closing the file for each line is not wrong, just slower than need be.

My guess is that the table contains more than the 3 columns you are selecting, and so trying to fetch those 3 columns into a record whose structure included all the columns of the table will fail. Change the record definition to:

V_PERSON test_persondetails%rowtype;

and move to to after the cursor definition.