• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • PowerServer 2021 - Issues with Oracle StoredProcedure

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved PowerServer 2021 - Issues with Oracle StoredProcedure

  1. Issue
0
Votes
Undo
  1. ORESTE MUSCELLA
    ORESTE MUSCELLA
  2. PowerServer
  3. Wednesday, 1 June 2022 08:06 AM UTC

Hello everybody,
I'm testing my PB Client / Server application (PB2021) compiled with PowerServer2021. The data server is Oracle 12c.

The application makes use of calls to Oracle StoredProcedure (PL/SQL) and in the PowerServer version I have some problems:

  • The calls to the StoredProcedure require the schema in the DECLARE (eg: <schema>. <StoreProcedureName> ...), without the schema an "object does not exist" error is returned.

  • in some cases, before the call to the StoredProcedure, it is necessary to insert data in a table, but in the PowerServer version these are not seen by the StoredProcedure and a COMMIT is required after the INSERT of the rows. In my process the COMMIT / ROLLBACK must be done only after the execution of the StoredProcedure and according to the outcome of the same.

Am I doing something wrong ? In the DB configuration of the PowerServer Profile I checked "Allows dynamic connection using ...".


Can anyone give me some suggestions?
The application is very large (over 400 .pbl), in use for over 20 years and uses many calls to Oracle StoredProcedure (PL / SQL). I would have a lot of difficulty having to modify the logic considering the dimensions and the possible regressions.

Thank you in advance.

Best regards
Oreste Muscella

(sorry for my english, i used google translate)

  1. Oracle
  2. PowerServer
  3. Stored Procedure
Multi-browser plug-in bug causing entire computer ...
Can't connect using ODBC for the PowerServer appli...
Responses (2)
David Xiong @Appeon
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Thursday, 2 June 2022 10:17 AM UTC
  2. PowerServer
  3. # 1

Hi Oreste,

For the first issue, PowerServer Cloud App works the same as PowerBuilder Native App. You don't need to specify a schema when defining the stored procedure. But you need to make sure that the logon user is the owner of this stored procedure. Otherwise, you will get the "object does not exist" error.

For the second issue, this may be caused by a difference on DB connection between PowerServer Cloud App and PowerBuilder Native App. Refer to DB connection - - PowerServer 2021 Help (appeon.com). Since we planned to support this long-connection feature, We will contact with you to know more details about your situation later.

Regards, David

Comment
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Wednesday, 1 June 2022 11:56 AM UTC
  2. PowerServer
  3. # 2

For you first problem:

You can use a public or private synonym.

Another way is to change the default schema for your session.

 

For your second problem:

Seems that your Insert and your StoredProcedure call are running in different DB transactions if your application runs in PowerServer. I don't use PowerServer so I cant give you a hint.

Comment
ORESTE MUSCELLA
  1. ORESTE MUSCELLA
  2. Monday, 6 June 2022 06:34 AM UTC
Hi Renè,

all StoredProcedures (such as tables and other application objects) have a public synonym and grant for all oracle-user enabled for the application.

It is not possible to change the scheme because every user of the application has an oracle account, access to the DB is the first level of security, and in the application there are controls linked to the account (user).

The problem is that in PowerBuilder everything works correctly while with PowerServer it does not..
  1. Helpful
René Ullrich
  1. René Ullrich
  2. Monday, 13 June 2022 05:42 AM UTC
AFAIK PowerServer runs with connection sharing. I think you have to rewrite your application because different oracle accounts for the users will not work in this case.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.