Question
Issue in Calling DB function Using RDB-Open after upgrading from 6.2 SP2 to Pega 7.2.2
Hello,
We have upgrade our application from Pega 6.2 SP2 to Pega 7.2.2 and in our application using RDB-Open we are running below query:
select GETPVIID() as "tempPVIID" from DUAL where rownum = 1
where GETPVIID() is a DB function but after upgrade this script started throwing below error:
java.sql.SQLSyntaxErrorException: ORA-00904: "GETPVIID": invalid identifier
I am attaching also the complete log file. Please let us know why above query is giving error.
Hello,
Yes the user configured in the data source has the execute command privilege on this function and using SQL Developer I have executed the same from user configured in the data source and query is running without any problem from data source user also.
Thanks,
Sudhanshu Kumar Yadav
Well the error is coming from the database so if you can successfully execute it outside then either the database user or the command is not what you expect when you run it from within pega. In the RDB rule click on test connectivity to confirm that database rule that is being used for the connection and then confirm that rule specifies the database user you expect. You can also run a trace with "db cache" and db_query" options to try to figure out where the error is coming from.
What database user are you using in your datasource? Does that user have permission to execute this function? Try connecting to the database as the same user in your datasource using some 3rd party tool and run the query.