Re: using the truncate in PL/SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 17 Nov 1998 12:52:34 GMT
Message-ID: <3651711d.840568@inet16.us.oracle.com>

On Tue, 17 Nov 1998 08:09:31 GMT, hjsoon_at_samsung.co.kr wrote:

>
>
> I am wordering if the truncate is used in PL/SQL.
>
> If the truncate is used in PL/SQL,
> how can I using the truncate in PL/SQL ?
>

You need to use dynamic SQL.

eg.

SQL> desc bar

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                                     VARCHAR2(20)
 ID                                       NUMBER

SQL> select * from bar;

NAME                                   ID
------------------------------ ----------
A                                       1
A                                       2
A                                       3
A                                       4
A                                       5
A                                       6
A                                       8
B                                       7
chris                                   9
df                                    199
df                                    198
df                                    197
df                                    196
df                                    195
df                                    194
df                                    193
dfsdfsdfsdf                           192
df                                    200

18 rows selected.

  1 declare
  2 c number;
  3 s number;
  4 begin
  5 c := dbms_sql.open_cursor;
  6 dbms_sql.parse( c, 'truncate table bar', dbms_sql.native );   7 s := dbms_sql.execute( c );
  8 dbms_sql.close_cursor( c );
  9 exception
10 when others then

 11      if dbms_sql.is_open( c ) then
 12        dbms_sql.close_cursor( c );
 13      end if;

14* end;
SQL> / PL/SQL procedure successfully completed.

SQL> select * from bar;

no rows selected

SQL> hope this helps.

chris.
--
Christopher Beck
Oracle Corp.
Reston, VA.

>
>
>
>
>
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Nov 17 1998 - 06:52:34 CST