Re: TRUNCATE from a stored procedure?

From: Jim Kennedy <Jim_kennedy_at_medicalogic.com>
Date: 1997/05/30
Message-ID: <3391f5af.1792936406@207.230.236.9>#1/1

Do it all the time. Create a stored procedure and use the dbms_sql package. Here is an example that truncates all the tables in the foo schema

CREATE OR REPLACE PROCEDURE clean_foo AS

	CURSOR C1 IS
	SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE OWNER =
'FOO' and
		tablespace_name ='FOO_DATA';
	TABLE_REC  C1%ROWTYPE;
	type tabNames is table of varchar2(80) index by
BINARY_INTEGER;
	tab_list tabNames;
	curr_row binary_integer:=0;
	rows binary_integer;
	SQL_COMMAND CHAR(200);
	cursorid integer;
	BEGIN
	OPEN C1; -- LOOP THRU LIST OF TABLES
	LOOP
		FETCH C1 INTO TABLE_REC  ;
		EXIT WHEN (C1%NOTFOUND);
		curr_row:=curr_row+1;
		tab_list(curr_row):=table_rec.table_name;
		rows := curr_row;
	end loop;
	close c1;
	for curr_row in 1 .. rows loop
		-- now truncate the tables
	SQL_COMMAND:='Truncate table foo.'||tab_list(curr_row) ;
		CURSORID:=DBMS_SQL.OPEN_CURSOR;

dbms_output.put_line('Truncating
table:'||tab_list(curr_row));

		DBMS_SQL.PARSE(CURSORID,SQL_COMMAND, DBMS_SQL.V7);
		DBMS_SQL.CLOSE_CURSOR(CURSORID);

	END LOOP;
	dbms_output.put_line('Everything went okay');
	END;

/

show errors

You might want to modify parts of it, but I think you get the idea.
Jim Kennedy

On Sun, 25 May 1997 15:15:12 -0400, "Panther" <rollman_at_one.net> wrote:

>I have users who need to reload several large tables on a weekly basis.
>Currently, they truncate
>each
> table and then run SQL* LOAD to load the new data. Apparently, doing a
>delete in a loop
>
>takes too
> long. I'm trying to find a way to take the schema owner's password away
>from them,
>without slowing
> down
> their reloads too much.
>
>I know that I can't directly put a TRUNCATE command in a stored procedure,
>since it's a DDL
>command,
> but several people have told me that there is an indirect way of calling
>TRUNCATE
>from a stored
> procedure. However none of them can remember the details of how it is
>done.
>If anyone can tell me
> how this might be accomplished, I'd appreciate it.
>
>
>If it makes a difference, the databases are versions 7.2 and 7.3.
>
>Please post the answer to the newsgroup, or Email me at: alan.kilivry_at_msx.ae
>.ge.com
>
>Thanks!
>
>

Jim Kennedy X7055 Received on Fri May 30 1997 - 00:00:00 CDT