insufficient privileges

[hariyadav]

Hi I am getting the runtime while running the following procedure.

CREATE OR REPLACE
PROCEDURE UPDATE_LOCATION_GEOAREA AS
BEGIN
Declare
fHandler UTL_FILE.FILE_TYPE;
fileHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
delim Number;
len Number;
loc_val varchar2(20);
geo_val varchar2(20);
errmsg varchar2(50);
dtflag number := 0;
l_Directory varchar2(50):='TEST';
infile_location varchar2(50):= '/prodevel/workfile';
outfile_location varchar2(50):= '/prodevel/workfile';
BEGIN
dbms_output.put_line(infile_location||outfile_location);
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || infile_location || '''';
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || outfile_location || '''';
EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY ' || l_Directory ||
' TO PUBLIC';
EXECUTE IMMEDIATE 'GRANT WRITE ON DIRECTORY ' || l_Directory ||
' TO PUBLIC';
fHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEO.TXT', 'R');
loop
UTL_FILE.GET_LINE(fHandler, buf);
len := length(buf);
delim := instr(buf,',');
loc_val := substr(buf,0,delim-1);
geo_val := substr(buf,delim+1,len);
UPDATE TEMPLOC set GEOAREA = geo_val where Location = loc_val;
IF SQL%NOTFOUND THEN
errmsg := 'LOCATION:'||loc_val||' IS INVALID';
fileHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEOOUTPUT.TXT','A');
If dtflag = 0 Then
dtflag := dtflag +1;
UTL_FILE.PUTF(fileHandler, '************'||to_char(to_date(sysdate),'MON,DD,YYYY')||'*************\n\n');
End if;
UTL_FILE.PUTF(fileHandler, errmsg);
UTL_FILE.FCLOSE(fileHandler);
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND then
UTL_FILE.FCLOSE(fHandler);
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
End;
END UPDATE_LOCATION_GEOAREA;
/


IF I run the procedureexec UPDATE_LOCATION_GEOAREA;
ERROR AS

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "CONSDB_DATABASE_PRODEVEL.UPDATE_LOCATION_GEOAREA", line 18
ORA-06512: at line 1


IF I run the just script in sql *plus I am not getting any error its working fine.

The Script:

Declare
fHandler UTL_FILE.FILE_TYPE;
fileHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
delim Number;
len Number;
loc_val varchar2(20);
geo_val varchar2(20);
errmsg varchar2(50);
dtflag number := 0;
infile_location varchar2(50):= '/prodevel/workfile';
outfile_location varchar2(50):= '/prodevel/workfile';
l_Directory varchar2(50):='TEST';
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || infile_location || '''';
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || outfile_location || '''';
fHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEO.TXT', 'R');
loop
UTL_FILE.GET_LINE(fHandler, buf);
len := length(buf);
delim := instr(buf,',');
loc_val := substr(buf,0,delim-1);
geo_val := substr(buf,delim+1,len);
UPDATE TEMPLOC set GEOAREA = geo_val where Location = loc_val;
IF SQL%NOTFOUND THEN
errmsg := 'LOCATION:'||loc_val||' IS INVALID';
fileHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEOOUTPUT.TXT','A');
If dtflag = 0 Then
dtflag := dtflag +1;
UTL_FILE.PUTF(fileHandler, '************'||to_char(to_date(sysdate),'MON,DD,YYYY')||'*************\n\n');
End if;
UTL_FILE.PUTF(fileHandler, errmsg);
UTL_FILE.FCLOSE(fileHandler);
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND then
UTL_FILE.FCLOSE(fHandler);
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
End;

Please help me.

Thanks in advance.

[waitecj]

Do you have execute privileges on the procedure?

[marist89]

You are probably getting the privs to grant through a role and not directly.

[davey23uk]

If u r running the procedure which was created by the same ,it shud not give this error else u may not have execute privileges on that procedure.

do try to learn what you are talking about