newbie
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: newbie

  1. #1
    Join Date
    Feb 2001
    Posts
    107
    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

  2. #2
    Join Date
    Aug 2000
    Posts
    29
    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
    J.Scott Adams

  3. #3
    Join Date
    Mar 2001
    Location
    Cologne, Germany
    Posts
    24
    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width