Link to home
Create AccountLog in
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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

ASKER

01-JAN-2012
01-JUL-2012

I enter them in oracle apps concurrent program
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.
The only info I can find for CANONICAL_TO_DATE accepts the date with a timestamp.
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

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.
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?
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
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
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.
The query returns data sdstuber... ive ran it as a seperate code,,, not inside a procedure
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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');
>>> .. 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?
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
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.