newbie Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: newbie

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

    hope this helps
    J.Scott Adams

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

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

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


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