Re: Adding a column in a procedure

Thank you for your help.

I now that it is not really recommended to play with a table definition in a procedure.
But in this case it is a helpful tool on an insignificant (temporary) table, so don't be afraid about that.

Anyway : Thank's.

Steffen

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> schrieb im Newsbeitrag news:3d0491b9$0$8510$ed9e5944_at_reading.news.pipex.net...
> Almost certainly something does happen, namely an error which you throw
> away.
>
> Having said that modifying a table definition in a stored procedure is a
> truly dreadful idea. If you possibly can avoid it do so. If you can't
think
> again I bet you can.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Steffen Pelzetter" <Steffen.Pelzetter_at_nexans.com> wrote in message
> news:1023701467.819951_at_slbhw0...
> > Hello,
> >
> > I want dynamicly add a column to a table in a stored procedur.
> > The code I created id accepted by the compiler, but if I call this
nothing
> > happens.
> >
> > here is my code:
> > AS
> >
> > cursor_name INTEGER;
> >
> > newcol varchar2(10);
> >
> > rows_processed INTEGER;
> >
> > BEGIN
> >
> > newcol:= 'TEST1';
> >
> > --to_char(sysdate,'DDMMYY');
> >
> > cursor_name := dbms_sql.open_cursor;
> >
> > dbms_sql.parse(cursor_name, 'alter table tablegrown add :X number(10)',
> >
> > dbms_sql.v7);
> >
> > dbms_output.put_line(newcol);
> >
> > dbms_sql.bind_variable(cursor_name, ':X',newcol,10);
> >
> > dbms_output.put_line('alter table tablegrown add :X number(10)');
> >
> >
> >
> > rows_processed := dbms_sql.execute(cursor_name);
> >
> > dbms_sql.close_cursor(cursor_name);
> >
> > EXCEPTION
> >
> > WHEN OTHERS THEN
> >
> > dbms_sql.close_cursor(cursor_name);
> >
> > END;
> >
> >
> > What's wrong with that program?
> > Please help.
> >
> > Steffen
> >
> >
> >
> >
>
>
Received on Mon Jun 10 2002 - 08:00:57 CDT