troubleshooting Question

Error while extracting data from a table to a csv file using utl_file

Avatar of rollingstar3535
rollingstar3535 asked on
Oracle Database
17 Comments2 Solutions1250 ViewsLast Modified:
Hello,

 I am trying to implement a code that is trying to get the data from a table and put it in a csv file on a database server. Am getting the following errors.

ORA-01840: input value not long enough for date format
ORA-06512: at "APPS.FND_DATE", line 55
ORA-06512: at "APPS.PACKAGE_NAME", line 49


PROCEDURE GET_DAILY_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)
IS
      CURSOR c_rates IS
         SELECT from_currency,
                to_currency,
                conversion_date,
                user_conversion_type,
                conversion_rate
           FROM gl_daily_rates_v
          WHERE conversion_date >= FND_DATE.CANONICAL_TO_DATE(p_from_date)
            AND conversion_date < (FND_DATE.CANONICAL_TO_DATE(p_to_date) + 1)
            AND conversion_type = p_conversion_type;

      output_file UTL_FILE.FILE_TYPE;

   BEGIN 
      output_file := utl_file.FOPEN('TEST_PATH','Test.CSV','W');

      fnd_file.put_line(fnd_file.log,'From Date: '||FND_DATE.CANONICAL_TO_DATE(p_from_date));
      fnd_file.put_line(fnd_file.log,'To Date:   '||FND_DATE.CANONICAL_TO_DATE(p_to_date));

      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 GET_DAILY_RATES;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 17 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 17 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