Re: TRUNCATE in PL/SQL

This should work for you...

/*
This code can execute any special SQL, such as DDL example:
EXECUTE special_sql('truncate table test_table'); */

CREATE OR REPLACE PROCEDURE special_sql (p_sql VARCHAR2) as

   ch INTEGER;

   v_ErrorCode NUMBER;
   v_ErrorText VARCHAR2(200);
   v_NumRows NUMBER;

begin

   ch := DBMS_SQL.OPEN_CURSOR;
   DBMS_OUTPUT.ENABLE(1000);
   DBMS_SQL.PARSE(ch,p_sql,DBMS_SQL.NATIVE);    v_NumRows := DBMS_SQL.EXECUTE(ch);
   DBMS_SQL.CLOSE_CURSOR(ch);
   commit;
   DBMS_OUTPUT.PUT_LINE('Number of Rows '|| v_NumRows);

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR (ch);
v_ErrorCode := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);

/*
Note: to make use of the error handling, the v_Error_* values need to be entered into
a table, so this table needs to be created and then insert statement placed here.
*/

end special_sql;

Jim Bromhal wrote in message ...
>Hello,
>
>I want to clear out a temp table at the end of a package. With small
>amounts of data to delete, "DELETE FROM temp_table" works, but with large
>amounts, I get rollback space errors. "TRUNCATE table temp_table;" works
>from SQL*Plus, but I can't compile in pl/sql.
>
>Why can't I compile the TRUNCATE statement in pl/sql code? Is there a
>better way? Thanks,
>
>jim
>
>
Received on Fri Apr 07 2000 - 00:00:00 CDT