insufficient privileges
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: insufficient privileges

Hybrid View

  1. #1
    Join Date
    Feb 2007
    Posts
    1

    insufficient privileges

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Do you have execute privileges on the procedure?
    Assistance is Futile...

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    You are probably getting the privs to grant through a role and not directly.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Quote Originally Posted by jagsy
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width