重庆思庄Oracle、Redhat认证学习论坛

标题: 获取ORACLE包体的定义，报:ORA-31600:invalid input value PACKAGE BODY [打印本页]

---

作者: 郑全    时间: 2023-7-25 10:05
标题: 获取ORACLE包体的定义，报:ORA-31600:invalid input value PACKAGE BODY


现象：
     今天一个包体失效了，想看看包体的定义：
      select dbms_metadata.get_ddl('PACKAGE BODY','YLTJ','YLTJ') FROM DUAL;结果报错，提示给的包体类型不对，如下：
      SQL> select dbms_metadata.get_ddl('PACKAGE BODY','YLTJ','YLTJ') FROM DUAL;
ERROR:
ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1

分析：
     难到给的类型不对，检查一下：
     SQL> select distinct object_type from dba_objects where owner='YLTJ';

OBJECT_TYPE
-------------------
PACKAGE BODY
PACKAGE
LOB
INDEX
TABLE
VIEW
JOB
    看到有package body呀 ，
    再看具体的这个名字对应的类型：
    SQL> SELECT owner,object_type,object_name from dba_objects where owner='YLTJ' AND object_type like 'PACKAGE%' AND object_name='YLTJ';

OWNER                          OBJECT_TYPE         OBJECT_NAME
------------------------------ ------------------- ------------------------------
YLTJ                           PACKAGE             YLTJ
YLTJ                           PACKAGE BODY        YLTJ

SQL>

   不是也是这个名字吗，PACKAGE BODY ，网上搜了一下，也是直接使用的 package body,但不行，看来网上也是乱劈材哈。

   后来想到，在dbms_metadata.get_ddl中，有两个单词的，一般要加下划线，于是加一个下划线试一下：
    select dbms_metadata.get_ddl('PACKAGE_BODY','YLTJ','YLTJ') FROM DUAL;
。。。

    DBMS_METADATA.GET_DDL('PACKAGE_BODY','YLTJ','YLTJ')
--------------------------------------------------------------------------------
   when others then
    resultCode := -1;
    return;
  end;
。。。

   结果出来了。
--结论，看来，这个多单词的数据类型，需要加下划线哈。

附：
    具体的对象类型

   Table 109-12 DBMS_METADATA: Object Types[td]

Type Name	Meaning	Attributes	Notes
AQ_QUEUE	queues	SND	Dependent on table
AQ_QUEUE_TABLE	additional metadata for queue tables	ND	Dependent on table
AQ_TRANSFORM	transforms	SN	None
ASSOCIATION	associate statistics	D	None
AUDIT	audits of SQL statements	DG	Modeled as dependent, granted object. The base object name is the statement audit option name (for example, ALTER SYSTEM). There is no base object schema. The grantee is the user or proxy whose statements are audited.
AUDIT_OBJ	audits of schema objects	D	None
CLUSTER	clusters	SN	None
COMMENT	comments	D	None
CONSTRAINT	constraints	SND	Does not include: primary key constraint for IOT column NOT NULL constraints certain REF SCOPE and WITH ROWID constraints for tables with REF columns
CONTEXT	application contexts	N	None
DATABASE_EXPORT	all metadata objects in a database	H	Corresponds to a full database export
DB_LINK	database links	SN	Modeled as schema objects because they have owners. For public links, the owner is PUBLIC. For private links, the creator is the owner.
DEFAULT_ROLE	default roles	G	Granted to a user by ALTER USER
DIMENSION	dimensions	SN	None
DIRECTORY	directories	N	None
FGA_POLICY	fine-grained audit policies	D	Not modeled as named object because policy names are not unique.
FUNCTION	stored functions	SN	None
INDEX_STATISTICS	precomputed statistics on indexes	D	The base object is the index's table.
INDEX	indexes	SND	None
INDEXTYPE	indextypes	SN	None
JAVA_SOURCE	Java sources	SN	None
JOB	jobs	S	None
LIBRARY	external procedure libraries	SN	None
MATERIALIZED_VIEW	materialized views	SN	None
MATERIALIZED_VIEW_LOG	materialized view logs	D	None
OBJECT_GRANT	object grants	DG	None
ON_USER_GRANT	Grants	G	Modeled as user grants. Grants the privileges of one user to other user in the form GRANT ... ON USER .... The grantee is the user. Example: GRANT INHERIT PRIVILEGES ON USER "USER1" TO "USER2".
OPERATOR	operators	SN	None
PACKAGE	stored packages	SN	By default, both package specification and package body are retrieved. See "[color=var(--oj-link-text-color)]SET_FILTER Procedure".
PACKAGE_SPEC	package specifications	SN	None
PACKAGE_BODY	package bodies	SN	None
PROCEDURE	stored procedures	SN	None
PROFILE	profiles	N	None
PROXY	proxy authentications	G	Granted to a user by ALTER USER
REF_CONSTRAINT	referential constraint	SND	None
REFRESH_GROUP	refresh groups	SN	None
RESOURCE_COST	resource cost info	H	None
RLS_CONTEXT	driving contexts for enforcement of fine-grained access-control policies	D	Corresponds to the DBMS_RLS.ADD_POLICY_CONTENT procedure
RLS_GROUP	fine-grained access-control policy groups	D	Corresponds to the DBMS_RLS.CREATE_GROUP procedure
RLS_POLICY	fine-grained access-control policies	D	Corresponds to DBMS_RLS.ADD_GROUPED_POLICY. Not modeled as named objects because policy names are not unique.
RMGR_CONSUMER_GROUP	resource consumer groups	SN	Data Pump does not use these object types. Instead, it exports resource manager objects as procedural objects.
RMGR_INTITIAL_CONSUMER_GROUP	assign initial consumer groups to users	G	None
RMGR_PLAN	resource plans	SN	None
RMGR_PLAN_DIRECTIVE	resource plan directives	D	Dependent on resource plan
ROLE	roles	N	None
ROLE_GRANT	role grants	G	None
ROLLBACK_SEGMENT	rollback segments	N	None
SCHEMA_EXPORT	all metadata objects in a schema	H	Corresponds to user-mode export.
SEQUENCE	sequences	SN	None
SYNONYM	synonyms	See notes	Private synonyms are schema objects. Public synonyms are not, but for the purposes of this API, their schema name is PUBLIC. The name of a synonym is considered to be the synonym itself. For example, in CREATE PUBLIC SYNONYM FOO FOR BAR, the resultant object is considered to have name FOO and schema PUBLIC.
SYSTEM_GRANT	system privilege grants	G	None
TABLE	tables	SN	None
TABLE_DATA	metadata describing row data for a table, nested table, or partition	SND	For partitions, the object name is the partition name. For nested tables, the object name is the storage table name. The base object is the top-level table to which the table data belongs. For nested tables and partitioning, this is the top-level table (not the parent table or partition). For nonpartitioned tables and non-nested tables this is the table itself.
TABLE_EXPORT	metadata for a table and its associated objects	H	Corresponds to table-mode export
TABLE_STATISTICS	precomputed statistics on tables	D	None
TABLESPACE	tablespaces	N	None
TABLESPACE_QUOTA	tablespace quotas	G	Granted with ALTER USER
TRANSPORTABLE_EXPORT	metadata for objects in a transportable tablespace set	H	Corresponds to transportable tablespace export
TRIGGER	triggers	SND	None
TRUSTED_DB_LINK	trusted links	N	None
TYPE	user-defined types	SN	By default, both type and type body are retrieved. See "SET_FILTER Procedure". .
TYPE_SPEC	type specifications	SN	None
TYPE_BODY	type bodies	SN	None
USER	users	N	None
VIEW	views	SN	None
XMLSCHEMA	XML schema	SN	The object's name is its URL (which may be longer than 30 characters). Its schema is the user who registered it.
XS_USER	Real Application Security (RAS) user	N	Corresponds to RAS users
XS_ROLE	Real Application Security (RAS) role	N	Corresponds to RAS roles
XS_ROLESET	Real Application Security (RAS) rolesets	N	Corresponds to RAS rolesets
XS_ROLE_GRANT	Real Application Security (RAS) role grants	N	Corresponds to RAS role grants
XS_SECURITY_CLASS	Real Application Security (RAS) security class	SN	Corresponds to RAS security classes
XS_DATA_SECURITY	Real Application Security (RAS) data security policy	SN	Corresponds to RAS data security policies
XS_ACL	Real Application Security (RAS) ACL	SN	Corresponds to RAS access control lists (ACLs) and associated access control entries (ACEs)
XS_ACL_PARAM	Real Application Security (RAS) ACL parameter	N	Corresponds to RAS access control lists (ACL) parameters
XS_NAMESPACE	Real Application Security (RAS) namespace	N	Corresponds to RAS namespa

---

欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)

Powered by Discuz! X3.2