CODEWITHSUNDEEP
oracle-databaseplsql
Ahtisham
Ahtisham

Reputation: 10116

PL/SQL PLS-00208: identifier 'TYPE' is not a legal cursor attribute

What I am trying to do

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

What is the problem

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

Procedure:

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;
    /

Customer Table

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)

Upvotes: 1

Views: 3887

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

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

As per Oracle documentation, %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

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.

I.e.,

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

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;

Upvotes: 4

Related Questions