Using Oracle user-defined types – PB Docs 2022

To create and use Oracle user-defined types:

1. In the ISQL view of the Database painter, create two Oracle
   user-defined types: ball_stats_type and player_type.

   Here is the Oracle syntax to create ball_stats_type. Notice that
   the ball_stats object of type ball_stats_type has a method associated
   with it called get_avg.

   PowerBuilder

   CREATE OR REPLACE TYPE ball_stats_type AS OBJECT (bat_avg NUMBER(4,3),rbi NUMBER(3),MEMBER FUNCTION get_avg RETURN NUMBER,PRAGMA RESTRICT_REFERENCES (get_avg,WNDS,RNPS,WNPS)); CREATE OR REPLACE TYPE BODY ball_stats_type ASMEMBER FUNCTION get_avg RETURN NUMBER ISBEGINRETURN SELF.bat_avg; END; END;

   1 2 3 4

   CREATE OR REPLACE TYPE ball_stats_type AS OBJECT (bat_avg NUMBER(4,3),rbi NUMBER(3),MEMBER FUNCTION get_avg RETURN NUMBER,PRAGMA RESTRICT_REFERENCES (get_avg,WNDS,RNPS,WNPS)); CREATE OR REPLACE TYPE BODY ball_stats_type ASMEMBER FUNCTION get_avg RETURN NUMBER ISBEGINRETURN SELF.bat_avg; END; END;

   Here is the Oracle SQL syntax to create player_type. Player_type
   references the user-defined type ball_stats_type. PowerBuilder
   supports such nesting graphically in the Database, DataWindow, and
   Table painters (see step 3).

   PowerBuilder

   CREATE TYPE player_type AS OBJECT (player_no NUMBER(2),player_name VARCHAR2(30),ball_stats ball_stats_type);

   1	CREATE TYPE player_type AS OBJECT (player_no NUMBER(2),player_name VARCHAR2(30),ball_stats ball_stats_type);

2. In the Database painter, create a table named lineup that
   references these user-defined types.

   Here is the Oracle SQL syntax to create the lineup table and
   insert a row. Lineup references the player_type user-defined
   type.

   PowerBuilder

   CREATE TABLE lineup (position NUMBER(2) NOT NULL, player player_type); INSERT INTO lineup VALUES (1,player_type (15, 'Dustin Pedroia', ball_stats_type (0.317, 50)));

   1 2	CREATE TABLE lineup (position NUMBER(2) NOT NULL, player player_type); INSERT INTO lineup VALUES (1,player_type (15, 'Dustin Pedroia', ball_stats_type (0.317, 50)));

3. Display the lineup table in the Database or DataWindow
   painter.

   PowerBuilder uses the following structure->member notation to
   display the table:

   PowerBuilder

   lineup ====== position player->player_no player->player_name player->ball_stats->bat_avg player->ball_stats->rbi

   1 2 3 4 5 6 7

   lineup ====== position player->player_no player->player_name player->ball_stats->bat_avg player->ball_stats->rbi

4. To access the get_avg method of the object ball_stats contained
   in the object column player, use the following structure->member
   notation when defining a computed column for the DataWindow object.
   For example, when working in the DataWindow painter, you could use
   this notation on the Compute tab in the SQL Toolbox:

   PowerBuilder

   player->ball_stats->get_avg()

   1	player->ball_stats->get_avg()