Oracle PL/SQL help

Hello all,

I'm just wondering if you could help me with some Oracle SQL syntax script I'm trying to write. Basically, I have 3 .dbf table files that I need copied into an oracle database with some additional stuff done to them.

1) I need to first copy these databases as is into the Oracle DB
2) Then I need to copy certain values from the tables into newly created tables with different column names and some extra fields added for use later
3) I also need to fix some data in the tables (ie year 2004 is recorded as A4)

I just need the basic syntax how to do these commands, any help appreciated!

1. There are several ways to accomplish this step. Perhaps the most straight-
forward is to copy your .dbf into a text file and then use SQLLDR to import it.

If memory serves correct, something like
USE MY_FILE.DBF
COPY TO NEW_FILE SDF

Then you need to create a SQLLDR script to load this newly created text file.

This is a bit involved, your best bet is to check any Oracle book for the specifics.

2. This is easy:

CREATE NEW_TABLE AS
SELECT COL1 COL_NAME1,
COL2 COL_NAME2
FROM OLD_TABLE;

You can add columns to the new table:

ALTER TABLE NEW_TABLE ADD COL3 VARCHAR2(20);

3. UPDATE NEW_TABLE
SET COL5 = '2004'
WHERE COL5 = 'A4'

These are just generalizations. Your best bet is to print out the Oracle PL/SQL documentation and do some reading.

thank you for your reply... is there a way to make the code reusable? This script will be run over and over again, the only thing changing will be the table name prefixes

Do you have perl on your system? You could set up a cron job using a perl dbi to do the updates to the tables for you.

no, unfortunately no perl installed