The host concurrent program is registered with Oracle Application.
When fnd_file.put_line(fnd_file.output,'String') is called from within the Host program it fails to put a message in the output file.
I have tried using dbms_output.put_line also, but this produces a message in log file instead of output file.Thanks in advance.
Do you get any ORA-XXXXX messages?
Could you please verify that $APPLTMP, $APPLPTMP variables and UTL_FILE_DIR database parameter are valid and pointing to directory where there is enough space and applmgr/oracle users have written permission?
Did you try using FND_FILE.PUT_LINE in the exception part of the code and see what it returns?
Haven't confirmed this, but might be along the right track...
Host is a shell script and doesn't have fnd_file.put_line syntax, so you must be calling a PL/SQL script from within your host script. In which case you have probably lost the session information from the original Apps session.
Within the PL/SQL program execute fnd_global.apps_initialize by passing in the user_id, resp_id and resp_appl_id as parameters to the host program.
fnd_global.initialize(y_session_id, null, null, null,
null, -1, null, null, null, null, $REQUEST_ID,
Where y_session_id is the session_id and $REQUEST_ID is the request_id of the host current program (you'll have to pass that in too).
Then run your logic and call fnd_file.put_line
As Gareth said, starting a Host CP (ie shell script) and then connection to the database from within that, your new connection will not have the concurrent request session information. The fnd_global calls that Gareth suggested should ensure that this is set.
The fnd_file.put_line(fnd_file.output/log, 'xxxx') procedure relies on fnd_global.conc_request_id being set to your running concurrent request - that is how it identifies which file it is meant to be writing to (from the fnd_concurrent_requests table).
I had the same requirement in which I was calling a pl/sql procedure from Host script which was using fnd_file.put_line to create lines in log file. Initializing requiest id in pl/sql block in Host script did not work.
I did the following and it worked for me.
PL/SQL concurrent prog (using fnd_file) write tmp log file (i.e. l<requiest_id>.tmp) in $APPLPTMP.
I used fnd_file.Put_names to set directory and filename for writing by fnd_file (inside anonymous pl/sql block in host script) and then later appended content from fnd_file written .tmp into Host's log file.
# conc request is is 0123456
(sqlplus -s $uidpwd << eof
cat $APPLPTMP/l0123456.tmp >> $APPLCSF/$APPLLOG/l0123456.req
Edited by: rawat75 on Nov 25, 2010 6:54 PM
To make fnd_file.put_line (fnd_file.output or fnd_file.put_line (fnd_file.log , you need to do few steps both in plsql procedure and shell script
--> Include concurrent request id for shell script run as a IN parameter
eg: p_conc_req IN NUMBER
---> Declare variable
v_conc_req VARCHAR2 (20) := 'o' || p_conc_req || '.tmp';
---> Include this in the cursor begin loop
fnd_file.put_names (NULL, v_conc_req, '/usr/tmp');
fnd_file.put_line (fnd_file.output, ' ');
----> Goto Shell script
----> I hope that u have this parameter
-----> Call this parameter into our input parameter in plsql code
sqlplus $1 << !
set serveroutput on size 1000000
Now include this line
If u want to mail then include
uuencode $APPLCSF/$APPLOUT/o$CONC_REQ_ID.out $file_name$dt.txt | mailx -s $subject $to_EMAIL_ID
Hope it helps u