How to run PL/SQL Package Procedure in OTM?

[khushal]

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

[cunninghamg]

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

[khushal]

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.

[cunninghamg]

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

[khushal]

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

Tried same error

[cunninghamg]

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

Hi

Make sure the procudure is declared in the Procedure Header Section

Gary

[cheekythought]

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]

[ianlo]

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

[khushal]

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.

[prakashdas]

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.

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

[Anurag_Saini]

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

Grant EXECUTE privileges on Package to GLOGOWNER and it will start working.