Solved

Plsqlap_Server_API.Ping_Result__ returns 0

  • 18 January 2021
  • 10 replies
  • 763 views
N
Rank
Userlevel 3
Badge +8

When I execute “Application Monitoring” - “PL/SQL Access Provider configuration test” is returning 0 instead of 1 (SELECT Plsqlap_Server_API.Ping_Result__ FROM sys.dual)

 

I checked connection string, ACL and SSL setup and everything seems OK and rest of IFS is working fine (except IFS Business Reporting report ordering).

 

I started Debug log while executing Plsqlap_Server_API.Ping_Result__ and it shows timeout from AQ.

Any idea what can be problem?

 

6^283439910^FRAMEWORK^DEBUG^Framework^Invoking operation through AQ...
12^283439914^FRAMEWORK^INFORMATION^Framework^Batch_Processor_Jms_API: Dbms_AQ.Enqueue on [BATCH_PROC_QUEUE_MAIN] completed in [.02] sec
10^283441415^FRAMEWORK^DEBUG^Framework^Timeout [15 sec] occured while waiting for [1125193]
12^283441415^CALLSTACK^DEBUG^^__anonymous_block at line 5
PLSQLAP_SERVER_API.PING_RESULT__ at line 715
PLSQLAP_SERVER_API.INVOKE_RECORD_ at line 573
PLSQLAP_SERVER_API.INVOKE___ at line 1975
PLSQLAP_SERVER_API.INVOKE_AQ___ at line 1839
PLSQLAP_SERVER_API.INVOKE_AQ___.PING at line 1716
PLSQLAP_SERVER_API.PING__ at line 657
PLSQLAP_SERVER_API.RECEIVE_AQ_RESPONSE___ at line 1644
ERROR_SYS.APPL_GENERAL at line 331
ERROR_SYS.RAISE_APPLICATION_ERROR___ at line 127
ERROR_SYS.RAISE_APPLICATION_ERROR___.FORMAT_CALL_STACK___ at line 121

9^283441415^FRAMEWORK^DEBUG^Framework^Updating last ping result for [MAIN] ...
10^283441415^CALLSTACK^DEBUG^^__anonymous_block at line 5
PLSQLAP_SERVER_API.PING_RESULT__ at line 715
PLSQLAP_SERVER_API.INVOKE_RECORD_ at line 573
PLSQLAP_SERVER_API.INVOKE___ at line 1975
PLSQLAP_SERVER_API.INVOKE_AQ___ at line 1839
PLSQLAP_SERVER_API.INVOKE_AQ___.PING at line 1720
ERROR_SYS.APPL_GENERAL at line 331
ERROR_SYS.RAISE_APPLICATION_ERROR___ at line 127
ERROR_SYS.RAISE_APPLICATION_ERROR___.FORMAT_CALL_STACK___ at line 121
 

 

icon

Best answer by kjrono 19 January 2021, 15:51

View original
This topic has been closed for comments

10 replies

dhlelk
Rank
Userlevel 6
Badge +15

Hi @NZCNESOSE,

This error might be caused due to several issues.
However you could have a look at the following points,
1. You are able to login with username "IFSPLSQLAP" to oracle.
2. IFSPLSQLAP user account is not locked.

You can further verify if the PL/SQL Access Provider is configured properly by having a look at the below documentation,
Configure PL/SQL Access Provider

You might might need to check the below article on how to convert the above link to your customer specific f1doc URL,
How to access wit.ifsworld or docs.ifs links on this site

SELECT t.*
FROM   plsqlap_environment_tab t
;

Cheers !
Dhananjaya.

https://www.linkedin.com/in/kludl/
N
Rank
Userlevel 3
Badge +8

Thanks for answer. 

I already checked IFSPLSQLAP user, it is active and I can log on to Oracle with that user.

We are using IFS10 UPD6.

 

Neno

tobese
Rank
Userlevel 3
Badge +7

Hi

I know that I’ve seen that error, the PLSQLAP implementation has had a redesign since UPD6.

Cheers /Tommy
dhlelk
Rank
Userlevel 6
Badge +15

Thanks for answer. 

I already checked IFSPLSQLAP user, it is active and I can log on to Oracle with that user.

We are using IFS10 UPD6.

 

Neno

Hi @NZCNESOSE,

Have a look at the below article,
Tip: Things to check when PL/SQL Access Provider is RED

Cheers !
Dhananjaya.

https://www.linkedin.com/in/kludl/
Yasas Kasthuriarachchi
Rank
Userlevel 7
Badge +30

Hi @NZCNESOSE,
Incase you don’t have access to the mentioned URL above please refer the KBA on this which contain the same information in detail: What should I verify when PL/SQL Access Provider is RED and how to make it GREEN ?
Best Regards,
Yasas

K
Rank
Userlevel 1
Badge +5

For app10 upd5 and onwards it has change from http calls from the database to jms messages executed by an batch processor in the mainserver. Look at the oracle queue BATCH_PROC_QUEUE_MAIN and verify that it is enabled for enqueuing. (Search for the the screen Oracle Queues in IFS EE navigator) Try right click on the queue, stop and start the queue, you could also try to purge the queue table. 

tobese
Rank
Userlevel 3
Badge +7

Ok, I remembered it as was for UPD7.

Cheers /Tommy
N
Rank
Userlevel 3
Badge +8

I checked  oracle queue BATCH_PROC_QUEUE_MAIN and there was a lot of messages in queue. I did purge, stop and start and tested again but still same issue. 

After  Plsqlap_Server_API.Ping_Result__ number of messages increased so apparently, messages are not picked from the queue. I will try to restart Main server during the service window and try to test again.

K
Rank
Userlevel 1
Badge +5

Look into the server.log for the mainserver1.log se if you get log entries for the batchprocessor. (often lots of errors messages i logged) It could also be that it has got stucked and a restart of the mainserver1 is needed. It hard to say without access to the server logs….

N
Rank
Userlevel 3
Badge +8

After restart of MainServer1 PING works fine (and it is possible to order Business Report from IFS client). Thank you for support!

Terms & ConditionsCookie settings