HomeAppeon Web | Appeon Mobile | Appeon PowerBuilderAppeon Online Help Center

Using Oracle user-defined types

PowerBuilder supports SQL CREATE TYPE and CREATE TABLE statements for Oracle user-defined types (objects) in the ISQL view of the Database painter. It correctly handles SQL SELECT, INSERT, UPDATE, and DELETE statements for user-defined types in the Database and DataWindow painters.

This means that using the Oracle native database interfaces in PowerBuilder, you can:

Do this

In

Use Oracle syntax to create user-defined types

Database painter

Use Oracle syntax to create tables with columns that reference user-defined types

Database painter

View columns in Oracle tables that reference user-defined types

Database painter

Manipulate data in Oracle tables that have user-defined types

Database painter

DataWindow painter

DataWindow objects

Export Oracle table syntax containing user-defined types to a log file

Database painter

Invoke methods

DataWindow object painter (Compute tab in SQL Toolbox)


Example

Here is a simple example that shows how you might create and use Oracle user-defined types in PowerBuilder.

For more information about Oracle user-defined types, see your Oracle documentation.

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. 

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

    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. 

    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: 

    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: 

    player->ball_stats->get_avg()
Prev  Up  Next
Using Oracle stored procedures as a data source  Home  Support for HA event notification