1. Charles Winters
  2. PowerBuilder
  3. Thursday, 12 December 2019 22:21 PM UTC

I need to call a oracle procedure from a package, but one of the parameters is a defined type in Oracle.

 

Is this possible in PowerBuilder?  If so, can you point me to the proper syntax?

 

I have already created a transaction, and used the  RPCFUNC declaration.

 

For example, I just noticed that this procedure below uses a created oracle type called contact_tabtype:

PROCEDURE copy_mystuff(p_source_org_id NUMBER,

                  p_destination_org_id NUMBER,

                  p_contacts contact_tabtype,

                  p_effective_date DATE,

                  p_underwriter VARCHAR2,

                  p_financialadvisor VARCHAR2,

                               p_notes VARCHAR2,

                  p_mat_message OUT VARCHAR2,

                  p_return_value OUT NUMBER);

 

In Oracle this is defined as:

TYPE contact_rectype IS RECORD
(CTC_ID NUMBER(8),
CTC_ORG_ID NUMBER(8),
CTC_STATUS VARCHAR2(1),
CTC_FIRST_NAME VARCHAR2(40)
);

TYPE contact_tabtype IS TABLE OF contact_rectype INDEX BY BINARY_INTEGER;

 

Thanks.

Charles Winters Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 13:55 PM UTC
  2. PowerBuilder
  3. # 1

Thanks, this could be a work around, if we decide to stay with PowerBuilder.

 

Comment
  1. Armeen Mazda @Appeon
  2. Friday, 13 December 2019 17:36 PM UTC
Hi Charles, if your organization needs to migrate to a different language, you might want to consider the PowerScript Migrator, which automates migrating about 80% of non-visual to C#. https://youtu.be/PXyiDb4Znn4
  1. Helpful
  1. Charles Winters
  2. Friday, 13 December 2019 17:50 PM UTC
Thanks, I will look into this option too.
  1. Helpful
There are no comments made yet.
Charles Winters Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 13:53 PM UTC
  2. PowerBuilder
  3. # 2

Thanks Chris, I was afraid of that, but I kept reading and noticed that a supported type  if I used the RPCFUNC was this: 

Pass and return PowerScript arrays (PL/SQL tables)

and PL/SQL Tables.

 

I am not quire sure what this means, nor how to program this.

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 09:51 AM UTC
  2. PowerBuilder
  3. # 3

What you can do, is create a new stored procedure to which you pass the needed data expressed as standard datatypes and then have that one call the other stored procedure and return it's data.

That way any other calls by any (other) programs to the original stored procedure will remain intact.

regards

Comment
  1. Charles Winters
  2. Friday, 13 December 2019 17:49 PM UTC
Thanks, I will try this first, before abandoning PowerBuilder.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 12 December 2019 23:27 PM UTC
  2. PowerBuilder
  3. # 4

Hi Charles;

  User defined DBMS data types are not supported within the PB realm. Only standard ANSI "simple" data types.

Regards ... Chris

Comment
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.