Avatar of rollingstar3535
rollingstar3535
 asked on

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

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;

Open in new window

Oracle Database

Avatar of undefined
Last Comment
gatorvip

8/22/2022 - Mon
slightwv (䄆 Netminder)

What are the values you are passing in for p_from_date and p_to_date?
rollingstar3535

ASKER
01-JAN-2012
01-JUL-2012

I enter them in oracle apps concurrent program
Sean Stuber

does FND_DATE.CANONICAL_TO_DATE  accept dates in that format?

Does it return a date? or does it return a string that is then being implicitly converted into a date?

most likely your error is occurring because of an implicit conversion somewhere.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

The only info I can find for CANONICAL_TO_DATE accepts the date with a timestamp.
rollingstar3535

ASKER
Hello slightwv and sdstuber,

yes,  i have found that error and changed it as below,,,
there are no errors but there is no data in the csv file,, can u help??

PROCEDURE daily_rates(x_retmsg          OUT VARCHAR2,
                                    x_retcode         OUT BINARY_INTEGER,
                                    p_from_date       IN  VARCHAR2,
                                    p_to_date         IN  VARCHAR2,
                                    p_conversion_type IN  VARCHAR2)
IS
      CURSOR c_rates IS
         SELECT from_currency,
                to_currency,
                conversion_date,
                conversion_type,
                conversion_rate
           FROM gl_daily_rates
          WHERE conversion_date >= TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS')
            AND conversion_date < TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS') + 1
            AND conversion_type = p_conversion_type;

      output_file UTL_FILE.FILE_TYPE;

   BEGIN 
      output_file := utl_file.FOPEN('path','test.CSV','W');

      fnd_file.put_line(fnd_file.log,'From Date:       '||p_from_date);
      fnd_file.put_line(fnd_file.log,'To Date:         '||p_to_date);
      fnd_file.put_line(fnd_file.log,'Conversion Type: '||p_conversion_type);

      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.conversion_type ||'|'||
                                       r_rates.conversion_rate);
      END LOOP;
      utl_file.FCLOSE(output_file);
   END daily_rates;

Open in new window

Sean Stuber

your code says this...

TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS')

the input values you posted earlier say this...

01-JAN-2012
01-JUL-2012


you should be getting an error since 2012 is an invalid DD value.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

Not familiar with fnd_file but since you open the file with utl_file, you likely need utl_file to write to it:

utl_file.put_line(output_file,'From Date:       '||p_from_date);


Does the cursor return rows?
rollingstar3535

ASKER
Hello sdstuber,

I have been entering the paramter in the following format now

2010/01/01 00:00:00
2012/07/01 00:00:00

do i need to make any changes to the code so that i can bring data into the csv file
Sean Stuber

the utl_file portion looks correct,  slightwv has valid points above.

  You're calling other procedures, were those supposed to be utl_file calls?

If the query doesn't return data, then you won't write anything to the file in the cursor for loop
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
rollingstar3535

ASKER
Oops. That fnd_file command is used to see if the parameters is being picked up by the program. I am running this program in Oracle apps and this fnd_file command prints the parameters that the program takes in.
It is just for testing. I took out those fnd_file lines and ran the program but still no data in the csv file.

For your testing, you can take out those lines.
rollingstar3535

ASKER
The query returns data sdstuber... ive ran it as a seperate code,,, not inside a procedure
SOLUTION
slightwv (䄆 Netminder)

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
Sean Stuber

does your query return any data?

if you just want to test the utl_file portion

add this line somewhere before or after (BUT NOT INSIDE) the for loop


UTL_FILE.PUT_LINE(output_file,'This is a test');
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

>>> .. ive ran it as a seperate code,,, not inside a procedure

sorry, I was typing while you were posting.

are you sure your query was exactly the same?  can you post exactly what query you ran
and exactly what your procedure invocation looks like?
rollingstar3535

ASKER
sdstuber...

 ive put this line
UTL_FILE.PUT_LINE(output_file,'This is a test');
       after the FOPEN command and before the FOR loop and 'This is a test' is inserted into the csv file.
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.
gatorvip

Just a quick note - fnd_file is an Oracle Apps package equivalent to utl_file (which I believe is used in the underlying code) and is not implemented in a default Oracle installation.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck