Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    49

    Unanswered: removing file from O.S.

    Hi everybody,
    i have this problem: i have a PL-SQL stored procedure loading data from a file (file coming from a separated ftp process) to a Oracle 9i db. at the end of the loading process, a UTL_FILE.REMOVE(flocation, filename) should remove the file from the Solaris 8 O.S.
    UTL_FILE_DIR is correctly set. i had some rouble with the O.S. permission because the ftp user and the calling procedure owner are different so that the procedure couldn't remove the file because of the permissions set by the ftp user; working on /etc/ftpd we solved this problem and operating system permissions are alright.
    in fact... if i run the procedure from PLSQL, it works and deletes the file at the end of the loading procedure but.... if i call the procedure from my jsp/servlet web application (running on Bea Weblogic), the procedure loads data correctly on the db but i get this error while trying to delete the file:

    ORA-29282 invalid file ID
    ORA-06512 at SYS.UTL_FILE
    ORA-06512 at CMOCADMIN.LOADER
    ORA-29283 invalid file operation

    where CMOCADMIN is the procedure owner and LOADER is the procedure name.

    this thing is driving me mad....


    thank you,
    matte

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I think your problem is the environment that the stored procedure is running in. What user is the stored procedure executed as? Does the stored procedure understand all the environmental variables that you have access to? Do the variables have the same values.

    HTH
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Aug 2003
    Posts
    49
    the stored procedure is owned by CMOCADMIN user but it's running as CMOC user. obviously CMOC has been granted for executing that procedure.
    the CMOC environment variables should be alright because if i connect to Solaris as CMOC, i run sqlplus as CMOC and i try to execute the stored procedure... it works perfectly, deleting the file.

    once i try from jsp application, via jdbc (and the user registrating on oracle is cmoc - Bea runs as cmoc user) , the procedure works but the UTL_FILE.REMOVE fails.....

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Any way to verify your assumptions? I know they "should" be right, but they are not always.

    Anyway to identify the file id that is being choked on? The invalid file operation error gives me pause, but since it probably is a cascaded event I would figure out the "file ID". If you have already then I am feeling as stumped though not as frustrated as you.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    instead of your appliaction, try writing a small java program and execute the proc using JDBC from this java program...run this java program from the server (where you have jsp-java application server installed).. run this java program from your machine...

    maybe .. maybe not !!!
    Oracle can do wonders !

  6. #6
    Join Date
    Aug 2003
    Posts
    49
    you can feel stumped.... :-(


    Originally posted by Todd Barkus
    Any way to verify your assumptions? I know they "should" be right, but they are not always.

    Anyway to identify the file id that is being choked on? The invalid file operation error gives me pause, but since it probably is a cascaded event I would figure out the "file ID". If you have already then I am feeling as stumped though not as frustrated as you.

  7. #7
    Join Date
    Aug 2003
    Posts
    49
    i'm already running the stored procedure using JDBC from a java servlet on the server....




    Originally posted by cmasharma
    instead of your appliaction, try writing a small java program and execute the proc using JDBC from this java program...run this java program from the server (where you have jsp-java application server installed).. run this java program from your machine...

    maybe .. maybe not !!!

Posting Permissions

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