| SQL - Questions and Answers Have a SQL question? Post it here. First do a search to see if someone hasn't already answered it. |
|
|||
regarding an error in pl/sql procedureHi,
I am getting the error ora-29280 in my procedure while reading data from the file...Its showing invalid directory path....Basically what all I need for a directory i mean permissions and where should I place my file???I am pasting my procedure..please check it out...Thanks CREATE OR REPLACE PROCEDURE exdept is fhandler utl_file.file_type; nextline varchar2(100); num2 number(10); num1 number(10); num number(10); i number(10):= 1; cnt number(10):= 1; txt_file varchar2(100):='deptno.txt'; BEGIN fhandler :=utl_file.fopen('C:\Program Files\Oracle',txt_file,'R'); if utl_file.is_open(fhandler) then loop begin utl_file.get_line(fhandler,nextline); if nextline is null then exit; end if; begin execute immediate 'insert into depart(deptid) values(nextline)'; end; end; end loop; end if; utl_file.fclose(fhandler); execute immediate 'insert into depart(seq) values(i)'; while i>0 loop begin select deptid into num from depart where rownum=i; if num is null then exit; end if; select deptid into num1 from depart where rownum=i; select deptid into num2 from depart where rownum=i+1; if num1=num2 then cnt:=cnt+1; execute immediate 'update depart set seq=cnt where rownum=i'; else cnt:=1; execute immediate 'update depart set seq=cnt where rownum=i+1'; end if; end; i:=i+1; end loop; END; / |
|
Be the first to answer this question! Click on the 'Post Reply' button below.
|