CODEWITHSUNDEEP
postgresqluser-defined-typesora2pgpostgresql-15
andrewtindle
andrewtindle

Reputation: 61

PostgreSQL User Defined Type Conversion from Oracle - CREATE TYPE <name> AS TABLE OF <type>

I've used ora2Pg to convert a PL/SQL procedure which uses a user-defined Oracle Object Types and corresponding Table Object Type.

The Object Types and the Stored Procedure convert successfully and both install/compile successfully in PostgreSQL version 15.1

However, I'm not seeing the expected behaviour when trying to assign a Object Type instance to its corresponding Table Object Type.

Using a stripped down test case to demonstrate this:

1: Oracle Object Type and corresponding Table Object Type:

CREATE TYPE t_myobj AS OBJECT (rid         NUMBER,
                               description VARCHAR2(100))
/

CREATE TYPE tab_myobjs AS TABLE OF t_myobj;
/

converts to the following PostgreSQL Object Types:

CREATE TYPE t_myobj AS (rid          integer,
                        description  varchar(100));

CREATE TYPE tab_myobjs AS (tab_myobjs t_myobj[]);

2: The converted PostgreSQL Stored Procedure is as follows:

CREATE OR REPLACE PROCEDURE test_prc() LANGUAGE PLPGSQL AS $$
DECLARE
  v_myobj        t_myobj;
  v_myojbs_tab   tab_myobjs;
  v_error        VARCHAR(100);

BEGIN

  RAISE NOTICE '1: START: Assign v_myobj object type attributes';
  v_myobj.rid := 1;
  v_myobj.description := 'test';

  RAISE NOTICE '2: Assign v_myobj object type to v_myojbs_tab';
  v_myojbs_tab := tab_myobjs(v_myobj);

  RAISE NOTICE '3: END';

  EXCEPTION

    WHEN OTHERS THEN
      ROLLBACK;

      v_error := SUBSTR(SQLERRM,1,512);
      RAISE EXCEPTION '%', 'Unhandled Exception: '||v_error USING ERRCODE = '45000';

END; $$

3: When I call TEST_PRC, the following is returned:

NOTICE:  1: START: Assign v_myobj object type attributes
NOTICE:  2: Assign v_myobj object type to v_myojbs_tab
ERROR:  Unhandled Exception: function tab_myobjs(t_myobj) does not exist
CONTEXT:  PL/pgSQL function test_prc() line 24 at RAISE

i.e. this line v_myojbs_tab := tab_myobjs(v_myobj); throws the error.

My question is whether PostgreSQL actually supports usage/assignment of Table Object Types, e.g. tab_myobjs, and, if so, what is the syntax to assign a standard Object Type, e.g. an instance of t_myobj to the table type.

Thank you Andrew

Upvotes: 2

Views: 398

Answers (2)

michael hansen079
michael hansen079

Reputation: 41

Simple answer is that ora2pg does not support automatic conversion of plsql using custom types and /or collection types such as nested tables.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246483

It's not easy to write this in such a complicated fashion in PostgreSQL. A simple version would be:

CREATE TYPE t_myobj AS (rid          integer,
                        description  varchar(100));

CREATE PROCEDURE test_prc() LANGUAGE PLPGSQL AS
$$DECLARE
  v_myojbs_tab   t_myobj[];
BEGIN
  v_myojbs_tab[1] := (1, 'test');
END;$$;

Upvotes: 0

Related Questions