Avatar of rollingstar3535
rollingstar3535
 asked on

UTL_FILE procedure error,,

Hello, I am working on getting data from a table to a csv file.
I am running this on oracle apps.
I have created the directory path and have used that path in the program below in the util_file.FOPEN.



CREATE DIRECTORY path_dir AS '/db_name/app/applmgr/DB_NAME/apps_st/appl/ap/12.0.0/forms/US';

GRANT ALL ON DIRECTORY PATH_DIR TO PUBLIC

Open in new window


CREATE OR REPLACE PROCEDURE get_rates(p_from_date       IN  VARCHAR2,
                                      p_to_date         IN  VARCHAR2,
                                      p_conversion_type IN  VARCHAR2,
                                      x_retcode         OUT BINARY_INTEGER,
                                      x_retmsg          OUT VARCHAR2)
AS
   CURSOR c_rates IS
      SELECT from_currency,
             to_currency,
             conversion_date,
             user_conversion_type,
             conversion_rate
        FROM gl_daily_rates_v
       WHERE conversion_date >= TO_DATE(p_from_date,'MM/DD/YYYY')
         AND conversion_date < TO_DATE(p_to_date,'MM/DD/YYYY')+1
         AND conversion_type = p_conversion_type;

   output_file UTL_FILE.FILE_TYPE;
   
BEGIN 
      output_file := utl_file.FOPEN('PATH_DIR','Get_rates.csv','W');

   FOR r_rates IN c_rates
   LOOP
      UTL_FILE.PUT_LINE(output_file,r_rates.from_currency        ||'|'||
                                    r_rates.to_currency          ||'|'||
                                    r_rates.conversion_date      ||'|'|| 
                                    r_rates.user_conversion_type ||'|'||
                                    r_rates.conversion_rate);
   END LOOP;
   utl_file.FCLOSE(output_file);
END;

Open in new window


I am getting the following error for a long time now, I have also assigned grant acccess on the folder too. i would really appreciate if you can help me take out these errors.

thank you
Oracle Database

Avatar of undefined
Last Comment
rollingstar3535

8/22/2022 - Mon
rollingstar3535

ASKER
Forgot to mention the error, here is the error


Cause: FDPSTP failed due to ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "APPS.GET_RATES", line 21
ORA-06512: at line 1

thank you in advance
ThomasMcA2

It looks like a permissions issue. You say you granted rights to the folder, but was it for the correct user? Or maybe the file already exists.
andertst

Is that error happening when you create the procedure or when you try to call it?

Did the CREATE directory command work?

Try explicitly granting READ, WRITE to USER where USER is the owner of the procedure.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
johnsone

Does the directory exist on the database server?  Are the permissions set on the directory so that the oracle user can write to that directory?
rollingstar3535

ASKER
Hello ThomasMcA2,

 Thank you for responding.
 I am logged in as APPS and have granted the rights to public.
 As you can see, i have assigned grants to the public as
GRANT ALL ON DIRECTORY PATH_DIR TO PUBLIC

but when i do
GRANT ALL ON DIRECTORY PATH_DIR TO APPS.. it throwing out an error saying that you cannot assign grants as yourself.

So, I dont know if it for the correct user.

THe file does not exist. I was hoping that the procedure creates the file.

Later, i also tried by creating a file manually and later running the program. But stil the same errors.

Appreciate your help
thank you
rollingstar3535

ASKER
Hello Anderst,

 >>Is that error happening when you create the procedure or when you try to call it?
I am getting that error when i try to run that program using Oracle Apps.

>>Did the CREATE directory command work?
Yes, the CREATE directory command worked

Try explicitly granting READ, WRITE to USER where USER is the owner of the procedure.
>> yes, i have tried all of these.. but as you say, if I do this,

GRANT READ ON DIRECTORY PATH_DIR TO APPS
i am gettin this error

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
 
apps is the owner for the procedure.

thank you
appreciate it
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rollingstar3535

ASKER
Hello  johnsone,

 thank you for responding.

>>Does the directory exist on the database server?
Yes, the directory exists on the server

>> Are the permissions set on the directory so that the oracle user can write to that directory?
yes, the permission i have checked, they are read write for everyone,
SOLUTION
andertst

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
rollingstar3535

ASKER
Hello Andertst,

 I have been trying various other methods hence couldnt get back.

 I dont have any other login other than APPS and when i try to execute grant from APPS, its throwing back error. .

>>Also, make sure the procedure is valid:  
>>select owner, object_name, object_type, status
>>from dba_objects
>>where object_name = 'GET_RATES'

Yes, I have checked this and the procedure status is valid, still get the same errors.

Thank you
johnsone

To me it has to be a permission error on the directory.  You have the basic commands there.  I used the sequence of commands you have to create this simple test:

create directory x_dir as 'c:\eric\temp';
create or replace procedure x as
output_file utl_file.file_type;
begin
output_file := utl_file.fopen('X_DIR','x.csv', 'w');
utl_file.put_line(output_file, 'test');
utl_file.fclose(output_file);
end;
/

Open in new window


That compiles and works just fine.

Can you post the results of the following commands:

id
ls -l /db_name/app/applmgr/DB_NAME/apps_st/appl/ap/12.0.0/forms/US
ls -ld /db_name/app/applmgr/DB_NAME/apps_st/appl/ap/12.0.0/forms/US
ls -ld /db_name/app/applmgr/DB_NAME/apps_st/appl/ap/12.0.0/forms
ls -ld /db_name/app/applmgr/DB_NAME/apps_st/appl/ap/12.0.0
ls -ld /db_name/app/applmgr/DB_NAME/apps_st/appl/ap
ls -ld /db_name/app/applmgr/DB_NAME/apps_st/appl
ls -ld /db_name/app/applmgr/DB_NAME/apps_st
ls -ld /db_name/app/applmgr/DB_NAME
ls -ld /db_name/app/applmgr
ls -ld /db_name/app
ls -ld /db_name

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
andertst

To help rule out a privileges issue, please provide the output to the below:

id

ls -ltr /db_name/app/applmgr/DB_NAME/apps_st/appl/ap/12.0.0/forms/US

Thanks
johnsone

Isn't that what I already asked for?
andertst

johnsone: Sorry, I didn't notice your comment when I posted mine.  Not sure the value of most of the rest of the commands since the file would be written only in the directory specified.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rollingstar3535

ASKER
Hello Andertst and Johnsone,,,

here are the results for that query


[userid@hoststring ~]$ ls -ld /dbname/app/applmgr/DBNAME/apps_st/appl/ap/12.0.0/forms/US
drwxrwxr-x+ 2 appldev dba 4096 Nov 21  2011 /dbname/app/applmgr/DBNAME/apps_st/appl/ap/12.0.0/forms/US

thank you
ThomasMcA2

They also asked for the output of the "id" command.
rollingstar3535

ASKER
[userid@hoststring ~]$ id
uid=3###(userid) gid=3029(developer) groups=3029(developer)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rollingstar3535

ASKER
Hello Andertst and Johnsone,
Thank you both for your continuous help,

Actually that UTL_FILE error was on the dba/admin side and there was nothing that could have been done from my side, although your solutions helped me take out some errors.

the dba provided some folders by doing some commands from his side, and provided us with some folders that had UTL_FILE accepted.

i have split points equally between you two. thank you,