目前 dbms_metadata.get_ddl 还不支持导出TYPE对象

orcl=> select version();version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------(MogDB 5.0.6 build 8b0a6ca8) compiled at 2024-03-27 11:05:29 commit 0 last mr 1804  on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)orcl=> select dbms_metadata.get_ddl('TYPE','TYPE_EMP_TABLE','SCOTT') from dual;
ERROR:  GET_DDL for type TYPE does not supported yet
CONTEXT:  referenced column: get_ddl

虽然 dbms_metadata.get_ddl不支持，但是还是有其他方法

在MogDB/openGauss中，可以使用gs_dump导出数据库中所有对象

可以先使用gs_dump导出所有对象DDL，再使用shell过滤CREATE TYPTE

[omm@mogdb506 ~]$ gs_dump orcl -U scott -Wtiger@123 --schema-only --schema=scott -f get_type_ddl.sql > /dev/null
[omm@mogdb506 ~]$ sed -i -n '/CREATE TYPE/{:start /;[[:space:]]*$/!{N;b start};p}' get_type_ddl.sql
[omm@mogdb506 ~]$ cat get_type_ddl.sql 
CREATE TYPE str_split AS TABLE OF "varchar";
CREATE TYPE type_emp_table AS (empno numeric(4,0),ename character varying(10),job character varying(9),mgr numeric(4,0),hiredate timestamp(0) without time zone,sal numeric(7,2),comm numeric(7,2),deptno numeric(2,0)
);
CREATE TYPE type_number AS TABLE OF "numeric";
CREATE TYPE type_number2 AS TABLE OF "numeric";

PG系数据库的pg_dump，openGauss系数据库的gs_dump还是挺实用的，点个赞

如需了解更多详情如白皮书、异构数据迁移方案等请移步www.mogdb.io

如果您是软件开发商申请数据库适配，请联系市场营销marketing@enmotech.com