Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Adding a column in a procedure
have you tried global temporary tables?
-- Niall Litchfield Oracle DBA Audit Commission UK "Steffen Pelzetter" <Steffen.Pelzetter_at_nexans.com> wrote in message news:1023710457.281937_at_slbhw0...Received on Mon Jun 10 2002 - 16:41:13 CDT
> 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
> > >
> > >
> > >
> > >
> >
> >
>
>