Home » Developer & Programmer » Reports & Discoverer » Fatal pl/sql error occurred
Fatal pl/sql error occurred [message #282463] Thu, 22 November 2007 01:14 Go to next message
anitav
Messages: 6
Registered: September 2006
Location: South Africa
Junior Member
hi

Please can someone help regard :

I am getting an error in my rdf...'cf_create_bankserv_fileformula' : Fatal PL/SQL error occurred.

in the formula cf_create_bankserv_fileformula I execute a 2 custom written packages each one create a file on the server using utl_file concept.

I am trying to create 2 files in the same formula using 2 different
package and the problem is only one file is being created..
.

any advice on what to do so that both the files are created

I'm using fopen, putline and flose_all ....in both packages
but I need something else

thanx
Re: Fatal pl/sql error occurred [message #282475 is a reply to message #282463] Thu, 22 November 2007 01:51 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you didn't post actual code you used, it is practically impossible to guess what might be wrong.

However, as one file is created and another one is not, those two custom written packages work differently and one of them causes an error. Which one? The one whose file doesn't get created.
Re: Fatal pl/sql error occurred [message #282488 is a reply to message #282475] Thu, 22 November 2007 02:39 Go to previous messageGo to next message
anitav
Messages: 6
Registered: September 2006
Location: South Africa
Junior Member
hi

You right its difficult without the code ....below is the code for the formula but not the packages....I copied the one package and created another one... lets call it package pacs and cams in order to explain my problem

the strange thing is pacs runs and creates a pac file
package cams does not create a file

If I reverse the order .....package cams runs and creates a cams file...
package pacs does not

In both variation it errors on the FATAL PL/SQL error
If I run it individually on its own it works...

Any advice will be highly appreciated

thanx



this is the formula code where I am calling the packages


FUNCTION CF_CREATE_BANKSERV_FILEFormula RETURN CHAR IS

l_file_name VARCHAR(300);

BEGIN
SRW.MESSAGE('97','in CF_CREATE_BANKSERV_FILEFormula');

SRW.MESSAGE('97','calling pacs package');
AP_EFT_INTERFACE_PKG.main_prc(:P_PAYMENT_BATCH);

SRW.MESSAGE('97','calling cams package');
AP_EFT_INTERFACE_CAMS_PKG.main_prc(:P_PAYMENT_BATCH); -- anita calling new package
SRW.MESSAGE('97','after package');

l_file_name := 'AP_EFT_'||FND_GLOBAL.CONC_REQUEST_ID||'-' || 'EFT file has been created on Server';
l_file_name := NULL;

SRW.MESSAGE('97','at end CF_CREATE_BANKSERV_FILEFormula');

RETURN(l_file_name);

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(1,'Error Code: '||SQLCODE||'Error Message: '||SQLERRM);

END CF_CREATE_BANKSERV_FILEFormula;

[Updated on: Thu, 22 November 2007 06:41]

Report message to a moderator

Re: Fatal pl/sql error occurred [message #282500 is a reply to message #282488] Thu, 22 November 2007 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do those packages create a file with the same name? Does, perhaps, package starts creating one file and when the second package tries to do the same, finds an open file, opened by another session (i.e. the first package). If so, could you rename one of the files?

You could also remove exception handling section and let Oracle take care about it - the report will fail, of course, but you might be able to see additional Oracle error code(s) which might help identifying an error.

By the way, function always returns NULL.
Re: Fatal pl/sql error occurred [message #282503 is a reply to message #282500] Thu, 22 November 2007 03:33 Go to previous messageGo to next message
anitav
Messages: 6
Registered: September 2006
Location: South Africa
Junior Member
hi Littlefoot

thanx for ur help

NO, I am creating files with different names
below is the code creating the one file ....this code exist in both packages but the file name is different..

I suspect the 'utl_file.fclose_all' maybe causing the problem
after the first package runs and somehow the second file is not produced.

u suggesting I remove the exception in the formula column ...will try that


this statement 'l_file_name := NULL;' ......hahahahaha
was me when trying to debug error...must be removed

thanx

l_file_name := 'AP_EFT_'||fnd_global.conc_request_id||'.txt';
l_dir_name := '/data01/fin/ap/out';

l_file_id := utl_file.fopen(l_dir_name, l_file_name,'W');
utl_file.put_line (l_file_id,create_inst_hdr_rec_line_fn(l_generation_number));
utl_file.put_line (l_file_id,create_usr_hdr_rec_line_fn(l_generation_number,l_sequence_number));
create_std_trx_rec_line_fn(l_file_id,l_sequence_number,l_hash_total,l_amount_total,l_record_count);
utl_file.put_line (l_file_id,create_contra_rec_line_fn(l_sequence_number,l_amount_total));
utl_file.put_line (l_file_id,create_usr_trailer_rec_line_fn(l_generation_number,l_record_count,l_amount_total,l_hash_total));
utl_file.put_line (l_file_id,create_inst_trail_rec_line_fn(l_generation_number,l_record_count));
utl_file.fclose_all;

[Updated on: Thu, 22 November 2007 06:40]

Report message to a moderator

Re: Fatal pl/sql error occurred [message #282518 is a reply to message #282503] Thu, 22 November 2007 04:23 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

I suspect the 'utl_file.fclose_all' maybe causing the problem

Why do you think so? What happens if you change it to 'utl_file.fclose' (or even remove it completely, just to make sure that it is to blame).

Did you remove EXCEPTION section? Any additional error message?

I'm sorry, but I don't see anything strange. If this code was wrong, no file would be created at all.
Re: Fatal pl/sql error occurred [message #282524 is a reply to message #282518] Thu, 22 November 2007 04:30 Go to previous messageGo to next message
anitav
Messages: 6
Registered: September 2006
Location: South Africa
Junior Member
hi
I removed exception handling....but no extra info...see below output ....
am now going to remove the close and try

....I'm at my wits end with this error so am grasping at straws

ciao

+---------------------------------------------------------------------------+
Payables: Version : 11.5.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

APEFT module: EFT Payments Remittance Advice Report
+---------------------------------------------------------------------------+

Current system time is 22-NOV-2007 12:25:20

+---------------------------------------------------------------------------+


+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
P_PAYMENT_BATCH='anitest22'
------------

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
AMERICAN_AMERICA.WE8ISO8859P1

'.,'

MSG-00800: Getting Payment Batch details....
MSG-00007: After Custom_Init
MSG-00007: before break
MSG-00097: in CF_CREATE_BANKSERV_FILEFormula
MSG-00097: calling pacs package
MSG-00097: calling cams package
REP-1401: 'cf_create_bankserv_fileformula': Fatal PL/SQL error occurred.

Report Builder: Release 6.0.8.27.0 - Production on Thu Nov 22 12:25:22 2007

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter Username:
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Payment Batch : anitest22
Bank Name :AP Creditors (EFT)
Account Number :620351
Branch :251905
Type :1
User Code :A327

Displaying suppliers details with incorrect Bank Details:
Site Code, Vendor Number, Vendor Name, Bank Number, Account Number, Account Type

Purge Date :071127
First Date :071122
Last Date :071123
Database Name : ERPDV
Directory Name : /data01/fin/ap/out
File Name : AP_EFT_1863546.txt
Request ID : 1863546

Site Code, Vendor Number, Vendor Name, Check Amount, Payment Date
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Program exited with status 1
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 1863546.

Review your concurrent request log and/or report output file for more detailed information.


+---------------------------------------------------------------------------+
Executing request completion options...


Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 22-NOV-2007 12:25:26

+---------------------------------------------------------------------------+

[Updated on: Thu, 22 November 2007 06:38]

Report message to a moderator

Re: Fatal pl/sql error occurred [message #282538 is a reply to message #282524] Thu, 22 November 2007 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm. Could you split one formula column into two formula columns and create one file per formula column? (Just to say it once again, I like the way it sounds: "formula column". My style is just too bad, but I can't help it).

The output really doesn't help much ... "REP-1401: 'cf_create_bankserv_fileformula': Fatal PL/SQL error occurred." can be anything.
Re: Fatal pl/sql error occurred [message #282540 is a reply to message #282538] Thu, 22 November 2007 05:04 Go to previous messageGo to next message
anitav
Messages: 6
Registered: September 2006
Location: South Africa
Junior Member
hi

finally the end ....see the light at the end of the tunnel ...

its the fclose_all statement....I had commented it out of both packages...and it works...

I initially had two "formula column". then for the past 3 days have been trying all sorts of coding...


thank you for being there to help

THANK GOD
Re: Fatal pl/sql error occurred [message #282565 is a reply to message #282540] Thu, 22 November 2007 06:44 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you made it work!

However, I'd like to know why all this happened ...
Do you plan to include 'fclose' now that you had removed 'fclose_all'?
Re: Fatal pl/sql error occurred [message #282568 is a reply to message #282565] Thu, 22 November 2007 06:50 Go to previous message
anitav
Messages: 6
Registered: September 2006
Location: South Africa
Junior Member
hi

yes I have changed the fclose_all to fclose...

this happened because I introduced the second package to create another file...although I dont understand YYYYY fclose_all cause it to crash.

we are changing our EFT software to the bank from a PACS software to CAMS software....but intend running parallel for 3 months so both files have to be produced...


This was urgent and is going live DEC 3...

thanx for the quick response and help

ciao from the sunshine land

[Updated on: Thu, 22 November 2007 06:52]

Report message to a moderator

Previous Topic: attach report to window schedular
Next Topic: Vertical justify
Goto Forum:
  


Current Time: Mon Nov 04 05:54:48 CST 2024