PL/SQL PLS-00208:标识符"TYPE"不是合法的游标属性 [英] PL/SQL PLS-00208: identifier 'TYPE' is not a legal cursor attribute

问题描述

我要做什么

我正在尝试编写一个使用动态SQL .

I am trying to write a procedure that creates a table using Dynamic SQL.

出了什么问题

在编译过程时出现以下错误:

I get the following error while compiling the procedure:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: Statement ignored
4/80     PLS-00208: identifier 'TYPE' is not a legal cursor attribute

过程:

create or replace procedure createTab is

    begin
        EXECUTE IMMEDIATE 'create table thisYearCustomerNames (id ' || customer.id%type || ', name ' ||
                                                               customer.name%type || ', points ' ||
                                                               customer.points%type || ')';
    end;
    /

客户表

Name               Null?      Type
 -----------------------------------------
 ID                NOT NULL   NUMBER(5)
 NAME                         VARCHAR2(30)
 ADDRESS                      VARCHAR2(40)
 POINTS            NOT NULL   NUMBER(10)
 DATE_OF_JOINING              DATE
 GENDER                       CHAR(1)

推荐答案

您正在尝试创建具有%type属性的表，这是不允许的.

You are trying to create a table with %type attributes , which is not permitted.

%TYPE属性仅允许用作PL/SQL变量，而不能在创建表时使用.

%TYPE attributes are only allowed as PL/SQL variables and cannot be used while creating a table.

根据Oracle文档，％TYPE :

As per Oracle documentation, %TYPE:

％TYPE属性使您可以声明常量，变量集合 元素，记录字段或子程序参数具有相同的数据 类型作为先前声明的变量或列

The %TYPE attribute lets you declare a constant, variable, collection element, record field, or subprogram parameter to be of the same data type as a previously declared variable or column

因此，如果您需要创建一个与Customer相同定义的表，并且没有数据，请使用具有0行的Create table as select *.

So, if you need to create a table with the same definition as that of Customer, without data, use a Create table as select * with 0 rows.

即

create table thisYearCustomerNames AS SELECT id,name,points FROM customer where ROWNUM < 1;

并且您正在尝试将列名放置在之外，例如...id ' || customer.id%type-这也行不通.这是适当的方法.

And you are trying to place column names outside EXECUTE IMMEDIATE such as ...id ' || customer.id%type - which too would not have worked. This is the appropriate way.

BEGIN
    EXECUTE IMMEDIATE 'create table thisYearCustomerNames AS SELECT id,name,points
    FROM customer where ROWNUM < 1';
END;

这篇关于PL/SQL PLS-00208:标识符"TYPE"不是合法的游标属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！