Announcement

Collapse
No announcement yet.

How to run PL/SQL Package Procedure in OTM?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to run PL/SQL Package Procedure in OTM?

    Hello Experts,

    I have a client specific requirement for which I to run a pl/sql package procedure through the front-end. I have developed pl/sql package procedure for this but not able to call it through OTM front end. Now I want this backend package procedure to run from OTM front end. Further if possible I want to schedule this process (of running package procedure from front end) on daily basis so that there is no manual need to go in front end then run the package.

    It would be great help if anyone knows the solution.

    Waiting for your reply.



    Thanks,
    Khushal
    Last edited by khushal; November 15, 2007, 11:21.

  • #2
    Re: How to run PL/SQL Package Procedure in OTM?

    Hi

    What is the requirement that needs to have a PL/SQL Procudure?
    Having this level of customisation in OTM is not "Out of the Box" functionality and makes support difficult at best.

    There are two ways of having a Procedure run regularly..
    1) Oracle dbms job
    2) Recurring Process in OTM
    the later needing an object to reference in the data query so an agent event can be triggered.

    Is there no way your requirements can be dealt with using automation agents?

    Cheers
    Gary
    Regards
    Gary Cunningham
    www.cbmc.co.uk
    www.mavenwire.com

    Comment


    • #3
      Re: How to run PL/SQL Package Procedure in OTM?

      Hi Gary,
      Thanks for the reply.
      Yes as of now (to avoid any customization), I am trying to run package procedure through Automation agent. But while running the package procedure I am getting error. For this the process I am following is as follows:Let me know if I am doing something wrong in it.
      1. In Agent Header, LOCATION - MODIFIED is given in Agent events.
      2. In Actions>>Direct SQL Update
      3. In SQL Statement "call XXPO_OTM_TEST_PKG.main"
      4. In statement Type "Stored Procedure".

      Now I am doing some update in Location and in the logs I am getting many debug level errors. Mainly error is of type:
      PLS-00201: identifier 'XXPO_OTM_TEST_PKG.MAIN' must be declared

      Whether I am following wrong procedure to call this package? What might be wrong?

      Regarding second way of approach for run procedure regularly, how we can achieve it? Please let me know in details.

      Thanks again.

      Comment


      • #4
        Re: How to run PL/SQL Package Procedure in OTM?

        Hi

        Try prefixing the procedure with the schema owner.

        like glogowner.XXPO_OTM_TEST_PKG.MAIN

        Gary
        Regards
        Gary Cunningham
        www.cbmc.co.uk
        www.mavenwire.com

        Comment


        • #5
          Re: How to run PL/SQL Package Procedure in OTM?

          Tried same error

          Comment


          • #6
            Re: How to run PL/SQL Package Procedure in OTM?

            Hi

            Make sure the procudure is declared in the Procedure Header Section

            Gary
            Regards
            Gary Cunningham
            www.cbmc.co.uk
            www.mavenwire.com

            Comment


            • #7
              Re: How to run PL/SQL Package Procedure in OTM?

              Hi,

              Try through sql backdoor, if you can run the procedure by following command

              exec <procedure name>

              I think, you will get the same error as you are getting in the logs.

              This will mean, you need to get the procedure corrected before triggering it through application [Automation agents]

              Comment


              • #8
                Re: How to run PL/SQL Package Procedure in OTM?

                Hi Khushal,

                Please also ensure that the stored procedure is granted execute priviledges to GLOGDBA.

                Ian

                Comment


                • #9
                  Re: How to run PL/SQL Package Procedure in OTM?

                  Hi Ian and all,
                  Thanks for all the replies. Actually the stored procedure was not having the execute privileges. Now after assign permissions its working fine.

                  Thanks once again.

                  Comment


                  • #10
                    Re: How to run PL/SQL Package Procedure in OTM?

                    Originally posted by khushal View Post
                    Hi Ian and all,
                    Thanks for all the replies. Actually the stored procedure was not having the execute privileges. Now after assign permissions its working fine.

                    Thanks once again.

                    Hi All,

                    i am too facing the same problem ..and this time i have given GLOGDBA privileges also but its not working .

                    please help if i am missing some thing
                    Last edited by prakashdas; July 12, 2014, 10:58.

                    Comment


                    • #11
                      Re: How to run PL/SQL Package Procedure in OTM?

                      Grant EXECUTE privileges on Package to GLOGOWNER and it will start working.
                      -------
                      Thanks and Regards
                      Anurag Saini
                      MavenWire

                      Comment

                      Working...
                      X