Home » SQL & PL/SQL » SQL & PL/SQL » alter table with dynamic sql
alter table with dynamic sql [message #253943] Wed, 25 July 2007 06:16 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
create table testcol(col1 number,col2 number);




i have prepared a piece of code that adds a new column to th is



DECLARE
l_sqlstmt VARCHAR2(32767);
l_COLUMN_NAME VARCHAR2(32767);
BEGIN


SELECT COLUMN_NAME into l_COLUMN_NAME from user_tab_cols
where COLUMN_NAME='newcol' and table_name='testcol';

exception 
    when no_data_found then

        l_sqlstmt := 'ALTER TABLE testcol ADD(newcol DATE)';
        dbms_output.put_line(l_sqlstmt); 
        EXECUTE IMMEDIATE l_sqlstmt;

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE(SQLERRM);

end;    




i run it for first time, fine, but when i run it again

it says



SQL> desc testcol;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 COL1                                               NUMBER
 COL2                                               NUMBER

SQL> @test;
 22  /
ALTER TABLE testcol ADD(newcol DATE)

PL/SQL procedure successfully completed.

SQL> desc testcol;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 COL1                                               NUMBER
 COL2                                               NUMBER
 NEWCOL                                             DATE

SQL> @test;
 22  /
ALTER TABLE testcol ADD(newcol DATE)
DECLARE
*
ERROR at line 1:
ORA-01430: column being added already exists in table
ORA-06512: at line 15
ORA-01403: no data found


Re: alter table with dynamic sql [message #253944 is a reply to message #253943] Wed, 25 July 2007 06:17 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
what is your question?
Re: alter table with dynamic sql [message #253947 is a reply to message #253943] Wed, 25 July 2007 06:19 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
when i run it for the second time, i am not able to
handle the exception properly

or do u think i am? this script looks fine?
Re: alter table with dynamic sql [message #253950 is a reply to message #253947] Wed, 25 July 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You put "testcol" in lower case in the query, its name is in UPPER case.
Fix your query.

Regards
Michel
Re: alter table with dynamic sql [message #253951 is a reply to message #253947] Wed, 25 July 2007 06:27 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well, for a start, column names and table names are stored in the data dictionary in uppercase (unless defined using double quotes)
Apart from that, you are performing the task in an extremely awkward way.

Excecute the sql statement and trap the error

air code:

declare
associate error -0143 with an error name
begin

execute immediate 'alter table statement'

exception 
when that error then
error logging or error message
end;
Previous Topic: suggest to user sql%found or sql%rowcount
Next Topic: Interesting Query
Goto Forum:
  


Current Time: Wed Dec 07 06:44:52 CST 2016

Total time taken to generate the page: 0.12394 seconds