5 Replies Latest reply: Mar 15, 2012 8:01 AM by 851246 RSS

    fnd_file.output fails to work when called from a Host concurrent program

    561161
      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.
        • 1. Re: fnd_file.output fails to work when called from a Host concurrent program
          Hussein Sawwan-Oracle
          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?
          • 2. Re: fnd_file.output fails to work when called from a Host concurrent progra
            gareth.roberts
            Hi,

            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.
            Then call:
            fnd_global.initialize(y_session_id, null, null, null,
            null, -1, null, null, null, null, $REQUEST_ID,
            null,null,null,null,null,null,-1);
            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

            Regards,
            Gareth

            Blog: http://garethroberts.blogspot.com/
            • 3. Re: fnd_file.output fails to work when called from a Host concurrent progra
              561237
              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).
              • 4. Re: fnd_file.output fails to work when called from a Host concurrent progra
                379093
                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
                begin
                fnd_file.put_names('l0123456.tmp',null,'$APPLPTMP');
                yourpackage;
                end;
                eof
                )

                cat $APPLPTMP/l0123456.tmp >> $APPLCSF/$APPLLOG/l0123456.req
                rm $APPLPTMP/l0123456.tmp

                *

                Edited by: rawat75 on Nov 25, 2010 6:54 PM
                • 5. Re: fnd_file.output fails to work when called from a Host concurrent program
                  851246
                  Hi all,
                  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

                  In plsql
                  --> 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
                  CONC_REQ_ID=$4
                  -----> Call this parameter into our input parameter in plsql code
                  sqlplus $1 << !
                  set serveroutput on size 1000000
                  exec xxxx_procedure('$param1','$param2','$param',$CONC_REQ_ID);

                  Now include this line
                  cat /usr/tmp/o$CONC_REQ_ID.tmp>>$APPLCSF/$APPLOUT/o$CONC_REQ_ID.out
                  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

                  rm o$CONC_REQ_ID.tmp


                  Hope it helps u