U0100086: GaussDB does not support the ANYDATA data type

Description

GaussDB does not support the ANYDATA data type and creation methods, including common type and its creation method (Convert*,Access*,Get*) and composite type and its creation method (ConvertObject,GetObject).

Database Type and Version

Source database type and version: Oracle versions supported by UGO
Target database type and version: GaussDB versions supported by UGO

Syntax Example

Example 1: Common type and its creation method
```
CREATE TABLE t1( c1 sys.anydata);
```

Example 2: Composite type and its creation method

-- Create a user-defined object type.
CREATE TYPE person_obj AS OBJECT (
   id NUMBER,
   name VARCHAR2(100),
   birthdate DATE
);
--Create a table.
drop table anydata_table;
create table anydata_table(name anydata);
-- Use ConvertObject.
DECLARE
   v_person person_obj := person_obj(1, 'John Doe', TO_DATE('1980-01-15', 'YYYY-MM-DD'));
   v_anydata ANYDATA;
BEGIN
   v_anydata := ANYDATA.ConvertObject(v_person);
   INSERT INTO anydata_table VALUES (v_anydata);
   COMMIT;
END;
/
-- Query GetObject.
DECLARE
   v_anydata ANYDATA;
   v_person person_obj;
   v_status NUMBER;
BEGIN
  -- Obtain ANYDATA data from the table.
   SELECT name INTO v_anydata FROM anydata_table;
   
  -- Convert objects.
   v_status := v_anydata.GetObject(v_person);
   
   IF v_status = DBMS_TYPES.SUCCESS THEN
  DBMS_OUTPUT.PUT_LINE('ID: ' || v_person.id);
      DBMS_OUTPUT.PUT_LINE('Name: ' || v_person.name);
      DBMS_OUTPUT.PUT_LINE('Birthdate: ' || TO_CHAR(v_person.birthdate, 'YYYY-MM-DD'));
   ELSE
      DBMS_OUTPUT.PUT_LINE('Error converting object: ' || v_status);
   END IF;
END;
/

Suggestion

Example 1: suggestion on modifying the creation method of the common type

1. Change ANYDATA to TYPE with the same name. Set the data type in typename_value to VARCHAR2. Execute the gettypename, Convert*, Access*, and Get* functions for the package and gettypename, Convert*, Access*, and Get* for the package body. Create data in public.

2. Delete .sys file after conversion.

3. Statements for creating ANYDATA data are not listed below because GaussDB does not support this type. PL/SQL may do not match the data type converted by UGO. Add PL/SQL statements as needed.

---Convert created data.

--FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA; ---GaussDB does not support this data type.

--FUNCTION ConvertTimestampLTZ(ts IN timestamp with local time zone) return AnyData;---GaussDB does not support this data type.

--FUNCTION ConvertBfile(b IN BFILE) return AnyData;---GaussDB does not support this data type.

--FUNCTION ConvertNClob(nc IN NCLOB) return AnyData;---GaussDB does not support this data type.

--FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData;---GaussDB does not support this data type.

--FUNCTION ConvertObject(obj IN "<object_type>") RETURN ANYDATA; ---The object type cannot be determined and cannot be directly modified.

--FUNCTION ConvertRef(rf IN REF "<object_type>") RETURN ANYDATA; ---The object type cannot be determined and cannot be directly modified.

--FUNCTION ConvertCollection(col IN "collection_type") RETURN ANYDATA ---The object type cannot be determined and cannot be directly modified.

---Access data.

--FUNCTION AccessBFloat(self IN AnyData) return BINARY_FLOAT ; ---GaussDB does not support this data type.

--FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC; ---GaussDB does not support this data type.

--FUNCTION AccessTimestampLTZ(self IN AnyData) return timestamp with local time zone ; ---GaussDB does not support this data type.

--FUNCTION AccessBfile(self IN AnyData) return BFILE ; ---GaussDB does not support this data type.

--FUNCTION AccessNClob(self IN AnyData) return NCLOB ; ---GaussDB does not support this data type.

----Get the current data.

--FUNCTION GetBfile(self IN ANYDATA, b OUT BFILE) RETURN INTEGER; ---GaussDB does not support this data type.

--FUNCTION GetBFloat(self IN ANYDATA, fl OUT BINARY_FLOAT) RETURN INTEGER;---GaussDB does not support this data type.

--FUNCTION GetCollection(self IN ANYDATA,col OUT "<collection_type>")RETURN INTEGER;---GaussDB does not support the data type.

--FUNCTION GetObject(self IN ANYDATA,obj OUT "<object_type>")RETURN INTEGER; The object type cannot be determined and cannot be directly modified.

--FUNCTION GetRef(self IN ANYDATA,rf OUT REF "<object_type>")RETURN INTEGER; The object type cannot be determined and cannot be directly modified.

--FUNCTION GetTimestampLTZ(self IN ANYDATA,ts OUT TIMESTAMP WITH LOCAL TIME ZONE) RETURN INTEGER; ---GaussDB does not support the data type.

--FUNCTION GetNClob(self IN ANYDATA, nc OUT NCLOB) RETURN INTEGER; ---GaussDB does not support the data type.

Syntax compatible with GaussDB:

-- Create ANYDATA data in public and define the creation method.
--type: ANYDATA contains the common type.
drop type public.anydata cascade;
CREATE  TYPE public.anydata AS  (
 typename VARCHAR2,  
 typename_value      VARCHAR2
);
--package: ANYDATA data creation method: Only compatible common data is processed. Incompatible data or objects are not processed.
CREATE OR REPLACE PACKAGE public.anydata AS
-Check the GetTypeName type.
 FUNCTION gettypename(self IN ANYDATA) RETURN       VARCHAR2;
---Convert created data.
 FUNCTION ConvertNumber(num IN NUMBER) return AnyData;
 FUNCTION ConvertDate(dat IN DATE) return AnyData;
 FUNCTION ConvertChar(c IN CHAR) return AnyData;
 FUNCTION ConvertVarchar(c IN VARCHAR) return AnyData;
 FUNCTION ConvertVarchar2(c IN VARCHAR2) return AnyData;
 FUNCTION ConvertBlob(b IN BLOB) return AnyData;
 FUNCTION ConvertClob(c IN CLOB) return AnyData;
 FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return AnyData;
 FUNCTION ConvertTimestampTZ(ts IN timestamp with time zone) return AnyData;
 FUNCTION ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH) return AnyData;
 FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return AnyData;
 FUNCTION ConvertNchar(nc IN NCHAR) return AnyData;
 FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return AnyData;
 FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA;
 FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData;
--FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA; ---GaussDB does not support this data type.
--FUNCTION ConvertTimestampLTZ(ts IN timestamp with local time zone) return AnyData;---GaussDB does not support this data type.
--FUNCTION ConvertBfile(b IN BFILE) return AnyData;---GaussDB does not support this data type.
--FUNCTION ConvertNClob(nc IN NCLOB) return AnyData;---GaussDB does not support this data type.
--FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData;---GaussDB does not support this data type.
--FUNCTION ConvertObject(obj IN "<object_type>") RETURN ANYDATA; ---The object type cannot be determined and cannot be directly modified.
--FUNCTION ConvertRef(rf IN REF "<object_type>") RETURN ANYDATA; ---The object type cannot be determined and cannot be directly modified.
--FUNCTION ConvertCollection(col IN "collection_type") RETURN ANYDATA ---The object type cannot be determined and cannot be directly modified.
---Access data.
 FUNCTION AccessNumber(self IN AnyData) return NUMBER ;
 FUNCTION AccessDate(self IN AnyData) return DATE ;
 FUNCTION AccessChar(self IN AnyData) return CHAR;
 FUNCTION AccessVarchar(self IN AnyData) return VARCHAR ;
 FUNCTION AccessVarchar2(self IN AnyData) return VARCHAR2 ;
 FUNCTION AccessBlob(self IN AnyData) return BLOB ;
 FUNCTION AccessClob(self IN AnyData) return CLOB ;
 FUNCTION AccessTimestamp(self IN AnyData) return TIMESTAMP ;
 FUNCTION AccessTimestampTZ(self IN AnyData) return timestamp with time zone ;
 FUNCTION AccessIntervalYM(self IN AnyData) return INTERVAL YEAR TO MONTH ;
 FUNCTION AccessIntervalDS(self IN AnyData) return INTERVAL DAY TO SECOND ;
 FUNCTION AccessNchar(self IN AnyData) return Nchar ;
 FUNCTION AccessNVarchar2(self IN AnyData) return NVARCHAR2 ;
 FUNCTION AccessRaw(self IN ANYDATA) return RAW;
 FUNCTION AccessBDouble(self IN AnyData) return BINARY_DOUBLE ;
--FUNCTION AccessBFloat(self IN AnyData) return BINARY_FLOAT ; ---GaussDB does not support this data type.
--FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC; ---GaussDB does not support this data type.
--FUNCTION AccessTimestampLTZ(self IN AnyData) return timestamp with local time zone ; ---GaussDB does not support this data type.
--FUNCTION AccessBfile(self IN AnyData) return BFILE ; ---GaussDB does not support this data type.
--FUNCTION AccessNClob(self IN AnyData) return NCLOB ; ---GaussDB does not support this data type.
----Get the current data.
FUNCTION GetBDouble(self IN ANYDATA,dbl OUT  BINARY_DOUBLE) RETURN INTEGER;
FUNCTION GetBlob(self IN ANYDATA,b OUT  BLOB) RETURN INTEGER;
FUNCTION GetChar(self IN ANYDATA,c   OUT  CHAR) RETURN INTEGER;
FUNCTION GetClob(self IN ANYDATA,c  OUT  CLOB)RETURN INTEGER;
FUNCTION GetDate(self IN ANYDATA,dat OUT  DATE)RETURN INTEGER;
FUNCTION GetIntervalDS(self IN ANYDATA,inv OUT  INTERVAL DAY TO SECOND) RETURN   INTEGER;
FUNCTION GetIntervalYM(self IN ANYDATA,inv OUT  INTERVAL YEAR TO MONTH) RETURN   INTEGER;
FUNCTION GetNchar(self IN ANYDATA, nc  OUT  NCHAR) RETURN   INTEGER;
FUNCTION GetNumber(self IN ANYDATA,num OUT  NUMBER)RETURN INTEGER;
FUNCTION GetNVarchar2(self IN ANYDATA, nc  OUT  NVARCHAR2) RETURN   INTEGER;
FUNCTION GetRaw(self IN ANYDATA,r  OUT  RAW)RETURN INTEGER;
FUNCTION GetTimestamp(self IN ANYDATA, ts  OUT  TIMESTAMP)RETURN INTEGER;
FUNCTION GetTimestampTZ(self IN ANYDATA, ts  OUT  TIMESTAMP WITH TIME ZONE) RETURN INTEGER;
FUNCTION GetVarchar(self IN ANYDATA,c  OUT  VARCHAR)RETURN INTEGER;
FUNCTION GetVarchar2(self IN ANYDATA,c  OUT  VARCHAR2)RETURN INTEGER;
--FUNCTION GetBfile( self IN ANYDATA, b OUT BFILE) RETURN INTEGER; ---GaussDB does not support this data type.
--FUNCTION GetBFloat( self IN ANYDATA, fl OUT BINARY_FLOAT) RETURN INTEGER;---GaussDB does not support this data type.
--FUNCTION GetCollection(self IN ANYDATA,col OUT "<collection_type>")RETURN INTEGER;---GaussDB does not support the data type.
--FUNCTION GetObject(self IN ANYDATA,obj OUT "<object_type>")RETURN INTEGER; The object type cannot be determined and cannot be directly modified.
--FUNCTION GetRef(self IN ANYDATA,rf OUT REF "<object_type>")RETURN INTEGER; The object type cannot be determined and cannot be directly modified.
--FUNCTION GetTimestampLTZ(self IN ANYDATA,ts OUT TIMESTAMP WITH LOCAL TIME ZONE) RETURN INTEGER; ---GaussDB does not support the data type.
--FUNCTION GetNClob(self IN ANYDATA, nc OUT NCLOB) RETURN INTEGER; ---GaussDB does not support the data type.
END anydata;
/
--package body: ANYDATA data creation method: Only compatible common data is processed. Incompatible data or objects are not processed.
CREATE OR REPLACE PACKAGE BODY public.anydata AS
--Check the GetTypeName type.
function gettypename(self IN ANYDATA) RETURN VARCHAR2 as
v_data VARCHAR2;
    begin
        v_data:=self.typename;
        return v_data;
    end ;
--Create data using the Convert function.
FUNCTION ConvertNumber(num IN NUMBER) return AnyData
as
 v_data anydata;
begin
  v_data.typename := 'NUMBER';
  v_data.typename_value :=num;
  return v_data;
end;
 FUNCTION ConvertDate(dat IN DATE) return AnyData
 as
 v_data anydata;
begin
  v_data.typename := 'DATE';
  v_data.typename_value :=dat;
  return v_data;
end;
 FUNCTION ConvertChar(c IN CHAR) return AnyData
 as
 v_data anydata;
begin
  v_data.typename := 'CHAR';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertVarchar(c IN VARCHAR) return AnyData
  as
 v_data anydata;
begin
  v_data.typename := 'VARCHAR';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertVarchar2(c IN VARCHAR2) return AnyData
   as
 v_data anydata;
begin
  v_data.typename := 'VARCHAR2';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertBlob(b IN BLOB) return AnyData
    as
 v_data anydata;
begin
  v_data.typename := 'BLOB';
  v_data.typename_value :=b;
  return v_data;
end;
 FUNCTION ConvertClob(c IN CLOB) return AnyData
     as
 v_data anydata;
begin
  v_data.typename := 'CLOB';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return AnyData
   as
 v_data anydata;
begin
  v_data.typename := 'TIMESTAMP';
  v_data.typename_value :=ts;
  return v_data;
end;
 FUNCTION ConvertTimestampTZ(ts IN timestamp with time zone) return AnyData
    as
 v_data anydata;
begin
  v_data.typename := 'timestamp with time zone';
  v_data.typename_value :=ts;
  return v_data;
end;
 FUNCTION ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH) return AnyData
     as
 v_data anydata;
begin
  v_data.typename := 'INTERVAL YEAR TO MONTH';
  v_data.typename_value :=inv;
  return v_data;
end;
 FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return AnyData
      as
 v_data anydata;
begin
  v_data.typename := 'INTERVAL DAY TO SECOND';
  v_data.typename_value :=inv;
  return v_data;
end;
 FUNCTION ConvertNchar(nc IN NCHAR) return AnyData
       as
 v_data anydata;
begin
  v_data.typename := 'NCHAR';
  v_data.typename_value :=nc;
  return v_data;
end;
 FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return AnyData
      as
 v_data anydata;
begin
  v_data.typename := 'NVARCHAR2';
  v_data.typename_value :=nc;
  return v_data;
end;
 FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA
       as
 v_data anydata;
begin
  v_data.typename := 'RAW';
  v_data.typename_value :=r;
  return v_data;
end;
 FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData
       as
 v_data anydata;
begin
  v_data.typename := 'BINARY_DOUBLE';
  v_data.typename_value :=dbl;
  return v_data;
end;
---Access data.
 FUNCTION AccessNumber(self IN AnyData) return NUMBER 
 as
 v_data number;
 begin
 if self.typename <> 'NUMBER' then
  return null;
  else 
  v_data:=self.typename_value;
  end if;
  return v_data;
 end;
 FUNCTION AccessDate(self IN AnyData) return DATE 
 as
 v_data DATE;
 begin
 if self.typename <> 'DATE' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessChar(self IN AnyData) return CHAR
 as
 v_data CHAR;
 begin
 if self.typename <> 'CHAR' then
   return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessVarchar(self IN AnyData) return VARCHAR 
  as
 v_data VARCHAR;
 begin
   if self.typename <> 'VARCHAR' then
   return null;
  else 
  v_data:=self.typename_value;
  end if;
  return v_data;
 end;
 FUNCTION AccessVarchar2(self IN AnyData) return VARCHAR2 
   as
 v_data VARCHAR2;
 begin
 if self.typename <> 'VARCHAR2' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessBlob(self IN AnyData) return BLOB 
    as
 v_data BLOB;
 begin
 if self.typename <> 'BLOB' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessClob(self IN AnyData) return CLOB 
    as
 v_data CLOB;
 begin
 if self.typename <> 'CLOB' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessTimestamp(self IN AnyData) return TIMESTAMP 
    as
 v_data TIMESTAMP;
 begin
 if self.typename <> 'TIMESTAMP' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessTimestampTZ(self IN AnyData) return timestamp with time zone 
    as
 v_data timestamp with time zone ;
 begin
 if self.typename <> 'TIMESTAMP WITH TIME ZONE' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessIntervalYM(self IN AnyData) return INTERVAL YEAR TO MONTH 
  as
 v_data INTERVAL YEAR TO MONTH ;
 begin
 if self.typename <> 'INTERVAL YEAR TO MONTH' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessIntervalDS(self IN AnyData) return INTERVAL DAY TO SECOND 
  as
 v_data INTERVAL DAY TO SECOND ;
 begin
 if self.typename <> 'INTERVAL DAY TO SECOND' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessNchar(self IN AnyData) return NCHAR 
  as
 v_data NCHAR;
 begin
 if self.typename <> 'NCHAR' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessNVarchar2(self IN AnyData) return NVARCHAR2 
  as
 v_data NVARCHAR2;
 begin
 if self.typename <> 'NVARCHAR2' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessRaw(self IN ANYDATA) return RAW
 as
 v_data RAW;
 begin
 if self.typename <> 'RAW' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessBDouble(self IN AnyData) return BINARY_DOUBLE 
 as
 v_data BINARY_DOUBLE;
 begin
 if self.typename <> 'BINARY_DOUBLE' then
  return null;
  else 
  v_data:=self.typename_value;
   end if;
  return v_data;
 end;
----Get the current data.
FUNCTION GetBDouble(self IN ANYDATA,dbl OUT  BINARY_DOUBLE) RETURN INTEGER
 as
 begin
 if self.typename <> 'BINARY_DOUBLE' then
  return null;
  else 
  dbl:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetBlob(self IN ANYDATA,b OUT  BLOB) RETURN INTEGER
as
 begin
 if self.typename <> 'BLOB' then
  return null;
  else 
  b:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetChar(self IN ANYDATA,c   OUT  CHAR) RETURN INTEGER
as
 begin
 if self.typename <> 'CHAR' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetClob(self IN ANYDATA,c  OUT  CLOB)RETURN INTEGER
as
 begin
 if self.typename <> 'CLOB' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetDate(self IN ANYDATA,dat OUT  DATE)RETURN INTEGER
as
 begin
 if self.typename <> 'DATE' then
  return null;
  else 
  dat:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetIntervalDS(self IN ANYDATA,inv OUT  INTERVAL DAY TO SECOND) RETURN   INTEGER
as
 begin
 if self.typename <> 'INTERVAL DAY TO SECOND' then
  return null;
  else 
  inv:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetIntervalYM(self IN ANYDATA,inv OUT  INTERVAL YEAR TO MONTH) RETURN   INTEGER
as
 begin
 if self.typename <> 'INTERVAL YEAR TO MONTH' then
  return null;
  else 
  inv:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetNchar(self IN ANYDATA, nc  OUT  NCHAR) RETURN   INTEGER
as
 begin
 if self.typename <> 'NCHAR' then
  return null;
  else 
  nc:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetNumber(self IN ANYDATA,num OUT  NUMBER)RETURN INTEGER
as
 begin
 if self.typename <> 'NUMBER' then
  return null;
  else 
  num:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetNVarchar2(self IN ANYDATA, nc  OUT  NVARCHAR2) RETURN   INTEGER
as
 begin
 if self.typename <> 'NVARCHAR2' then
  return null;
  else 
  nc:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetRaw(self IN ANYDATA,r  OUT  RAW)RETURN INTEGER
as
 begin
 if self.typename <> 'RAW' then
  return null;
  else 
  r:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetTimestamp(self IN ANYDATA, ts  OUT  TIMESTAMP)RETURN INTEGER
as
 begin
 if self.typename <> 'TIMESTAMP' then
  return null;
  else 
  ts:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetTimestampTZ(self IN ANYDATA, ts  OUT  TIMESTAMP WITH TIME ZONE) RETURN INTEGER
as
 begin
 if self.typename <> 'TIMESTAMP WITH TIME ZONE' then
  return null;
  else 
  ts:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetVarchar(self IN ANYDATA,c  OUT  VARCHAR)RETURN INTEGER
as
 begin
 if self.typename <> 'VARCHAR' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetVarchar2(self IN ANYDATA,c  OUT  VARCHAR2)RETURN INTEGER
as
 begin
 if self.typename <> 'VARCHAR2' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
END anydata;
/
--table
CREATE TABLE t1( c1 anydata);

Example 2: suggestion on modifying the creation method of the composite type

1. Change ANYDATA to TYPE with the same name. Set the data type in typename_value to VARCHAR2. Execute the gettypename, ConvertObject, and GetObject functions for the package and package body. Create data in public.

2. Delete .sys file after conversion.

3. Change v_anydata.GetObject(v_person) to anydata.GetObject(v_anydata,v_person);

4. Change the value of DBMS_TYPES.SUCCESS to 0.

Syntax compatible with GaussDB:

-- Create a user-defined object type.
CREATE TYPE person_obj AS  (
   id NUMBER,
   name VARCHAR2(100),
   birthdate DATE
);
-- Create an ANYDATA data type.
drop type public.anydata;
CREATE  TYPE public.anydata AS  (
 typename varchar2,  
 typename_value      person_obj
);
-- Create a package. Define the creation method of ANYDATA and the object type.
CREATE OR REPLACE PACKAGE public.anydata AS
 ---gettypename
 FUNCTION gettypename(self IN ANYDATA) RETURN       VARCHAR2;
 ---ConvertObject
 FUNCTION ConvertObject(obj  IN person_obj) return AnyData;
 ---GetObject
 FUNCTION GetObject(self IN AnyData, obj OUT person_obj ) return INTEGER ;
 END anydata;
/
-- Create a package body. Define the creation method of ANYDATA and the object type.
CREATE OR REPLACE PACKAGE BODY public.anydata AS
 --gettypename
function gettypename(self IN ANYDATA) RETURN VARCHAR2 as
v_data VARCHAR2;
    begin
        v_data:=self.typename;
        return v_data;
    end ;
  --ConvertObject
FUNCTION ConvertObject(obj  IN person_obj) return AnyData
as
 v_data anydata;
begin
  v_data.typename := 'person_obj';
  v_data.typename_value :=obj;
  return v_data;
end;
--GetObject
 FUNCTION GetObject(self IN AnyData, obj OUT person_obj ) return INTEGER
 as
 begin
 if self.typename <> 'person_obj' then
  return null;
  else 
  obj:=self.typename_value;
   end if;
  return 0;
 end;
END anydata;
/
-- Create a table.
drop table anydata_table;
create table anydata_table(name anydata);
-- Use ConvertObject.
DECLARE
   v_person person_obj := person_obj(1, 'John Doe', TO_DATE('1980-01-15', 'YYYY-MM-DD'));
   v_anydata ANYDATA;
BEGIN
   v_anydata := ANYDATA.ConvertObject(v_person);
   INSERT INTO anydata_table VALUES (v_anydata);
   COMMIT;
END;
/
-- Query GetObject.
DECLARE
   v_anydata ANYDATA;
   v_person person_obj;
   v_status NUMBER;
BEGIN
  -- Obtain ANYDATA data from the table.
   SELECT name INTO v_anydata FROM anydata_table;
  -- Convert objects.
   v_status := anydata.GetObject(v_anydata,v_person);
   IF v_status = 0 THEN
  DBE_OUTPUT.PRINT_LINE('ID: ' || v_person.id);
      DBE_OUTPUT.PRINT_LINE('Name: ' || v_person.name);
      DBE_OUTPUT.PRINT_LINE('Birthdate: ' || TO_CHAR(v_person.birthdate, 'YYYY-MM-DD'));
   ELSE
      DBE_OUTPUT.PRINT_LINE('Error converting object: ' || v_status);
   END IF;
END;
/

Parent topic: Error Codes Generated During Conversion from Oracle to GaussDB

Previous topic: U0100085: GaussDB does not support subqueries in TABLE

Next topic: U0100087: The precision of the returned value of DBE_UTILITY.GET_TIME in GaussDB and Oracle is different