12 Replies Latest reply on Jan 5, 2016 7:43 AM by PamNL

    submit_request fails with ORA-02289: sequence does not exist

    Uncle Buck

      RDBMS : 11.1.0.7.0

      Oracle Applications : 11.5.10.2

       

      I am having trouble with submitting a concurrent program from within a PL/SQL procedure.  I keep getting the 'ORA-02289: sequence does not exist' message.

       

      So as a test, I created a quick little procedure/executable/concurrent program, which does nothing except write to the concurrent program log.  This runs fine from concurrent manager.  There are no sequences in this procedure.

       

      When I try to submit it from within an anonymous block from TOAD, I get this same error.  So obviously I am missing something basic as I can't execute submit_request for multiple concurrent programs. What am I missing or doing wrong?

       

      The test package/procedure is defined in a custom schema XXX.  The anonymous block was run in TOAD while logged in to the same custom schema XXX.

       

      Thank you in advance.

       

      Anonymous block

      SET SERVEROUTPUT ON
      declare
          ln_req_id           NUMBER;
      BEGIN
          ln_req_id := fnd_request.submit_request(application => 'XXX',
                                                      program     => 'XXX_TEST',
                                                      description => 'TEST',
                                                      sub_request => FALSE,
                                                      argument1   => 'Y');
      
      
          dbms_output.put_line('ln_req_id: '||ln_req_id);
          IF ln_req_id = 0 THEN
            dbms_output.put_line(FND_MESSAGE.get);
          END IF;
      END;
      

       

      Package/Procedure registered with XXX_TEST concurrent program.

      CREATE OR REPLACE PACKAGE BODY XXX.XXX_TEST_PKG AS
      
      
        PROCEDURE write_xml_temp (ERRBUF OUT NOCOPY VARCHAR2,
                                  RETCODE OUT NOCOPY VARCHAR2,
                                  fv_debug IN VARCHAR2 DEFAULT 'N') IS
        
        BEGIN
          
          fnd_file.put_line(fnd_file.log,  'fv_debug='||fv_debug||'sysdate='||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
      
      
        END write_xml_temp;
        
      END XXX_TEST_PKG
      

      ;

        • 1. Re: submit_request fails with ORA-02289: sequence does not exist
          PamNL

          How did you call the first part? You can't submit it as a custom user .. you must be APPS and be logged in using fnd_global.apps_initialize (otherwise it returns 0 as request id).

          I assume actually you were using apps, otherwise you would have had to grant fnd_request to xxx ...

          • 2. Re: submit_request fails with ORA-02289: sequence does not exist
            PamNL

            Did you try your code also directly from SQLPlus? I tried it and it nicely returns

             

            ln_req_id: 0
            Cannot submit concurrent request for program XXX_TEST

            Check if the concurrent program is registered with Application Object Library.

            Check if you specified the correct application short name for
            your concurrent program.

            • 3. Re: submit_request fails with ORA-02289: sequence does not exist
              Uncle Buck

              Ahhh, well I executed the first anonymous block while logged in as the custom user in TOAD.  When I try to execute it while logged in as APPS, I get a different error

               

              'Encountered an error while getting the ORACLE user account for your concurrent request.'

              • 4. Re: submit_request fails with ORA-02289: sequence does not exist
                PamNL

                I tried to get the same error as you did by trying to submit it from a custom user, but you have to grant all kinds of stuff to it if you want this to work. I got

                 

                ln_req_id: 0
                ORACLE error -942 in SUBMIT_REQUEST

                Cause: SUBMIT_REQUEST failed due to ORA-00942: table or view does not exist.

                The SQL statement being executed at the time of the error was: &SQLSTMT and was
                executed from the file &ERRFILE.

                PL/SQL procedure successfully completed.

                 

                So I could imagine that would be your issue if you were trying to submit as custom user ..

                • 5. Re: Re: submit_request fails with ORA-02289: sequence does not exist
                  Uncle Buck

                  OK, let me try and explain a different way.  Lets forget about my test code created above; it was just created to try and troubleshoot the real problem.

                   

                  The real problem is this.

                   

                  Conc pgm XML is registered as a PL/SQL executable.  PL/SQL package/procedure is defined in custom schema XXX.  There is a synonym in the APPS schema pointing to the XXX package.  This conc pgm runs fine by itself when submitted from conc mgr.

                   

                  Conc pgm FTP is registered as a HOST executable.  Host file is an FTP.prog script.  This conc pgm runs fine by itself when submitted from conc mgr.

                   

                  The issue is that I want to submit the FTP conc pgm from within the XML conc pgm via the fnd_request.submit_request api.  The result from the submit_request I am getting is

                  ORACLE error -2289 in SUBMIT: others
                  
                  Cause: SUBMIT: others failed due to ORA-02289: sequence does not exist.
                  

                   

                  This is what I need to solve.  Thank you in advance for your efforts to help me.

                  • 6. Re: Re: submit_request fails with ORA-02289: sequence does not exist
                    PamNL

                    Ok, but common practice is to

                    - create objects that hold data under custom user (tables, sequences)

                    - grant those objects to apps

                    - create all other objects (views, packages) as apps

                     

                    Let's cross check some stuff .. since your procedures run fine directly from Oracle it seems you did all this, but just to be sure ... I'll check a little further to see if I can reproduce your error ..

                    I would first create your package under APPS directly as is common practice ... just to be sure.

                     

                    Your host script should be registered as a concurrent program under your custom application. so should the stored procedure executable.
                    I think you did that. So something like this
                    for the stored procedure

                     

                    And a host execution method for the other.

                     

                    The custom application should be registered and assigned to Standard Datagroup with user APPS

                     

                    The host script should start with

                     

                    # -----------------------------------------------------
                    # Obtain program arguments and populate FCDF variables
                    # -----------------------------------------------------
                    if (test "$GET_ARG" != "GET_ARG") then
                       arguments="$@"; export arguments
                       GET_ARG=GET_ARG; export GET_ARG
                       exec /bin/ksh -c "$0 $arguments"
                    fi

                    shift

                    for i in $1 $2 $3 $4 $5 $6 $7
                    do
                        eval $i
                        shift
                    done

                    export FCP_REQID FCP_LOGIN FCP_USERID FCP_USERNAME
                    export FCP_PRINTER FCP_SAVE_OUT FCP_NUM_COPIES

                    • 7. Re: Re: Re: submit_request fails with ORA-02289: sequence does not exist
                      Uncle Buck

                      Yes I think everything you listed I have done...otherwise the conc pgms would run fine on their own...I think.

                       

                      Main pl/sql conc executable registered under custom application '3i Custom Application'

                      Clipboard01.jpg

                       

                      FTP host conc executable registered under custom application '3i Custom Application'

                      Clipboard02.jpg

                       

                      Custom application registered and assigned to Standard Datagroup with user APPS

                      Clipboard03.jpg

                       

                      The host script does not start how you have it below, but since it works standalone, syntax should be ok.  I don't have the script handy because I don't have access to the server/folder where it is located.  DBA had to tweak it and place it until it worked.

                       

                      So what is missing?

                      • 8. Re: Re: Re: submit_request fails with ORA-02289: sequence does not exist
                        PamNL

                        Can you try dropping the package and re-create under apps and retry?

                        If you run it in the XXX schema I assume you had to grant fnd_request and fnd_message to your custom user to get it compiled didn't you?

                        • 9. Re: submit_request fails with ORA-02289: sequence does not exist
                          Pravin Takpire

                          Why don't you submit these programs as requests set ?

                          regards

                          Pravin

                          • 10. Re: submit_request fails with ORA-02289: sequence does not exist
                            Uncle Buck

                            Pam,

                             

                            Sorry for the delay, but I was out for the holidays.

                             

                            Let me try and create the package under apps and retry; I will reply back with the results.

                             

                            As for running it in the xxx schema, I only had to 1) grant execute privileges to APPS and 2) create package synonym in APPS schema.

                            • 11. Re: submit_request fails with ORA-02289: sequence does not exist
                              Uncle Buck

                              Hi Praven,

                               

                              Thank you for the suggestion, but the reason I can't use a request set is because the 1st conc pgm will be running through a cursor with file names, and will need to call the FTP conc pgm for each file in the cursor loop.