Re: truncate problems in PL/SQL

I figured it was something to do with the DDL, I was just unsure how to get around it :) Interestingly I have not had this problem when I write TSQL code for MS SQL 7, I have similar routines for some of the support systems and do this all the time.

Thanks for the help.

-Paul-

In article <969340632.18233.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> truncate is a ddl statement.
> To perform a truncate you either need to use dbms_sql
> (v7 and 8.0) or execute immediate (8i).
> In 8i this would be
> execute immediate 'truncate table edge_numbers';
>
> in v7
> sql_str := 'truncate table edge_numbers';
> cur_handle := dbms_sql.open_cursor;
> dbms_sql.parse(cur_handle, sql_str, dbms_sql.native);
> res := dbms_sql.execute(cur_handle);
> dbms_sql.close_cursor(cur_handle);
>
> where res and cur_handle are integer variables.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> <toadsprocket_at_my-deja.com> wrote in message
> news:8q6loi$73r$1_at_nnrp1.deja.com...
> > I am writing a PL/SQL routine that needs to truncate a summary table
> > before starting, the code goes something like this:
> >
> > DECLARE
> > CampaignVar varchar2(80);
> > BEGIN
> > truncate table edge_numbers;
> > select Distinct(Campaign_ID)
> > into CampaignVar
> > etc etc etc.....
> >
> > With the truncate I get error PLS-00103 from the parser, I want to
> > trunc it on the way in and on the way out of the routine. As soon as
 I
> > pull out the truncate everything works like a charm, can someone say
> > ARRGGHH!! :)
> >
> > Any ideas??
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 19 2000 - 09:35:45 CDT