troubleshooting Question

UTL_FILE procedure error,,

Avatar of rollingstar3535
rollingstar3535 asked on
Oracle Database
18 Comments2 Solutions1247 ViewsLast Modified:
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

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;

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
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros