'[Error] Execution (8: 3): ORA-06502: PL/SQL: numeric or value error: character string buffer too

Question

感谢大家，我已经从varchar2(10)修改为varchar2(20)，并删除了execute immediate语句中的column关键字，然后程序正在执行

declare
coldate varchar2(20);
colname varchar2(20);
begin
    coldate :='varchar2(10)';
    colname :='smaple';
    execute immediate 'alter table smap1 add column '||colname ||' '||coldate ;
end;

如果我想动态获取值，我使用了以下代码

declare
coldate varchar2(20):=&coldate;
colname varchar2(20):=&colname;
begin
   execute immediate 'alter table smap1 add '||colname ||' '||coldate ;
end;

那么我得到错误

 [Error] Execution (11: 23): ORA-06550: line 2, column 23:
 PLS-00330: invalid use of type name or subtype name
 ORA-06550: line 2, column 9:
 PL/SQL: Item ignored
 ORA-06550: line 3, column 23:
 PLS-00201: identifier 'SMAPLE' must be declared
 ORA-06550: line 3, column 9:
 PL/SQL: Item ignored
 ORA-06550: line 6, column 45:
 PLS-00320: the declaration of the type of this expression is incomplete or malformed
 ORA-06550: line 6, column 1:
 PL/SQL: Statement ignored

Answer 1

    declare
    coldate varchar2(20);
    colname varchar2(20);
    begin
        coldate :='varchar2(10)';
        colname :='smaple';
        execute immediate 'alter table smap1 add column '||colname ||' '||coldate ;
    end;
    This query wont work as syntactically its not correct.
    Please try this as it will help you definitely
    declare
    coldate varchar2(20):='varchar2(20)';
    colname varchar2(20):='Newadd';
    begin
       execute immediate 'alter table <tablename> add '||colname ||' '||coldate ;
    end;
-- And to use dynamically input values Please try this
SET DEFINE ON;
declare
coldate varchar2(20):='&DATATYPE';
colname varchar2(20):='&COL_NAME';
begin
   execute immediate 'alter table emp add '||colname ||' '||coldate ;
end;

Answer 2

解决方法如下:

coldate varchar2(12);
colname varchar2(20);
begin
    coldate :='varchar2(10)';