在oracle中创建类型时出现PLS-00201错误 [英] Getting PLS-00201 error while creating a type in oracle

问题描述

我有一个这样的表:

CREATE TABLE T_C_EVO_GAME_CONFIG_CHANGE_LOG(
F_TABLE_MODIFIED        VARCHAR2(40),
F_OPERATION_PERFORMED   VARCHAR2(30),
F_ROWS_ALTERED          INTEGER, 
F_LAST_UPDATED_BY       VARCHAR2(200),
F_LAST_UPDATED_DATE     TIMESTAMP);

我正在尝试构建具有相同结构的类型:

I am trying to build a type with the same structure:

create or replace type TYPE_EVOL_CONFIG_CHANGE_LOG as object
(
F_TABLE_MODIFIED        T_C_EVO_GAME_CONFIG_CHANGE_LOG.TABLE_MODIFIED%TYPE ,
F_OPERATION_PERFORMED   T_C_EVO_GAME_CONFIG_CHANGE_LOG.OPERATION_PERFORMED%TYPE,
F_ROWS_ALTERED          T_C_EVO_GAME_CONFIG_CHANGE_LOG.ROWS_ALTERED%TYPE , 
F_LAST_UPDATED_BY       T_C_EVO_GAME_CONFIG_CHANGE_LOG.LAST_UPDATED_BY%TYPE ,
F_LAST_UPDATED_DATE     T_C_EVO_GAME_CONFIG_CHANGE_LOG.LAST_UPDATED_DATE%TYPE
);

在创建Type时获得以下错误消息:

Getting the below error message in creating the Type :

错误(3,25):PLS-00201:必须声明标识符"T_C_EVO_GAME_CONFIG_CHANGE_LOG.TABLE_MODIFIED".

Error(3,25): PLS-00201: identifier 'T_C_EVO_GAME_CONFIG_CHANGE_LOG.TABLE_MODIFIED' must be declared.

以前，我尝试不使用%TYPE来创建Type，而只是简单地复制参数定义即可. 但是当我在Table中进行任何更改时，我都不想在Type中进行任何更改.

Previously I tried to create the Type without using %TYPE and just simply copying the parameter definition and it worked. But I don't want to make any changes in Type when I make any changes in Table.

推荐答案

%TYPE语法用于PL/SQL声明.不幸的是，在创建SQL对象时我们不能使用它. %rowtype也是如此.

The %TYPE syntax is for use in PL/SQL declarations. Unfortunately we cannot use it when creating SQL objects. Same goes for %rowtype.

如果可以的话，这将是非常整洁的，因为create or replace type的一种常见用法是按需构建表API.但是，在数据字典中管理引用构造会太复杂.请记住，类型"可用于定义其他对象，包括表"列.

It would be highly neat if we could, because one common use of create or replace type would be to build table APIs as you want to do. However, it would be too complicated to manage referencing constructs in the data dictionary; bear in mind that Types can be used to define other objects including Table columns.

So，您需要使用其属性的显式数据类型声明Type:

So alas, you need to declare the Type with explicit datatypes for its attributes:

create or replace type TYPE_EVOL_CONFIG_CHANGE_LOG as object
    (
    F_TABLE_MODIFIED        VARCHAR2(40) ,
    F_OPERATION_PERFORMED   VARCHAR2(30),
    F_ROWS_ALTERED          INTEGER , 
    F_LAST_UPDATED_BY       VARCHAR2(20) ,
    F_LAST_UPDATED_DATE     DATE
);

很明显，每当T_C_EVO_GAME_CONFIG_CHANGE_LOG列的结构更改时，您还需要手动同步它.但是，如果您添加或删除了列，则还是必须这样做.

Obviously you also need to sync it manually whenever the structure of any T_C_EVO_GAME_CONFIG_CHANGE_LOG column changes. But you would have to do this anyway if you added or dropped a column.

或者，您可以在程序包中将类型定义为PL/SQL记录.这样您就可以使用引用语法.

Alternatively you can define the type as a PL/SQL record in a package. That would allow you to use the referencing syntax.

create or replace package game_config as    

    TYPE_EVOL_CONFIG_CHANGE_LOG is record
    (
        F_TABLE_MODIFIED        T_C_EVO_GAME_CONFIG_CHANGE_LOG.F_TABLE_MODIFIED%TYPE ,
        F_OPERATION_PERFORMED   T_C_EVO_GAME_CONFIG_CHANGE_LOG.F_OPERATION_PERFORMED%TYPE,
        F_ROWS_ALTERED          T_C_EVO_GAME_CONFIG_CHANGE_LOG.F_ROWS_ALTERED%TYPE , 
        F_LAST_UPDATED_BY       T_C_EVO_GAME_CONFIG_CHANGE_LOG.F_LAST_UPDATED_BY%TYPE ,
        F_LAST_UPDATED_DATE     T_C_EVO_GAME_CONFIG_CHANGE_LOG.F_LAST_UPDATED_DATE%TYPE
    );

    -- or even
    TYPE TAB_EVOL_CONFIG_CHANGE_LOG is table of T_C_EVO_GAME_CONFIG_CHANGE_LOG%rowtype;
end;

这取决于您要如何在更广泛的应用程序中使用Type.

It depends how you want to use the Type in your broader application.

这篇关于在oracle中创建类型时出现PLS-00201错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！