SQL Recipes
A FREE cookbook for SQL queries and examples
Register FAQ Search Today's Posts Mark Forums Read

SQL - Questions and Answers Have a SQL question? Post it here. First do a search to see if someone hasn't already answered it.

Go Back   SQL Recipes a FREE cookbook of SQL queries and examples > SQL queries and examples > SQL - Questions and Answers

Reply
 
LinkBack Thread Tools Search this Thread
  #1 (permalink)  
Old 08-26-2008, 06:52 AM
yagnasri yagnasri is offline
Junior Member
 
Join Date: Aug 2008
Posts: 3
yagnasri is on a distinguished road
Default ANY dialect question:

regarding an error in pl/sql procedure


Hi,
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;
/
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Be the first to answer this question! Click on the 'Post Reply' button below.
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT. The time now is 12:21 AM.


Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.0.0
Copyright (c) 2006-2008 SQL Recipes

1 2 3 4 5 6 7 8