newbie

[itwizzkid]

We have 7 test databases which we load with data from production. Regularly we have to trucate the tables and bulk load data from a data feed application. Is there anyway of using sql or pl/sql to write a program that will truncate all the tables or individually when needed? This will be done by operation staff at night. Also need one for export/import

Thanks in advance

[scotta]

You can setup a batch file that can run a schedule to run @ night

NOTE: this is using Windows

first run an sql file that will truncate the tables you want to delete data out of.
the run the sqlldr to import the data into Oracle.

Example batchfile

@Echo off
REM truncate tables
sqlplus scott/tiger @c:\temp\truncate.sql
REM bulkload data into Oracle
sqlplus userid=scott/tiger contol=d:\temp\test.ctl log=d:\temp\log\test.log

Within the body of the "truncate.sql"

spool c:\temp\log.out
truncate table temp1
truncate table temp2
truncate table temp3
quit

hope this helps
scotta

[6502]

Hi there,

in a PL/SQL-Procedure or script, you can use the following command:
EXECUTE IMMEDIATE 'truncate table dingsda';

Assuming that you do not want to clear all tables of the database, but just of one schema in an instance, use a Cursor-Loop feeded by 'select table_name from user_tables' and concat the table_name to your truncate-command, then execute it:

DECLARE
CURSOR c_tables IS
SELECT table_name FROM user_tables;
BEGIN
FOR r_tables IN c_tables LOOP
EXECUTE IMMEDIATE 'truncate table '||r_tables.table_name;
END LOOP
END;


To schedule, you could (on Unix) use the cron to start the scripts via an sql-plus call, or use the DBMS_JOB-package.

cu
6502