'[Error] Execution (8: 3): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 5' in the following plsql

1
votes

thanks for all,i had modified from varchar2(10) to varchar2(20) and removing the column keyword in execute immediate statement then program is executing

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

if i want to take the values dynamically i had used the following code

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

then i am getting errors

 [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
2
oracleplsqloracle11g
'varchar2(10)' is 12 characters, you're reserving space for only 10. - Joachim Isaksson
thanks for all,i had modified from varchar2(10) to varchar2(20) but i am getting another error in the program that is ORA-00904: : invalid identifier ORA-06512: at line 7 - Smart003
See my answer below instead, added the details there. - Joachim Isaksson
thanks, i had modified the code but the still the query executed with errors - Smart003
You may want to add all details when you're creating the question, not add complexity as you go. Deleting my answer since it was an answer to your original question, not the updated one. - Joachim Isaksson

2 Answers

0
votes
    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;
0
votes

The below will solve it:

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