Home » SQL & PL/SQL » SQL & PL/SQL » Record Generation (Oracle 10g)
Record Generation [message #397086] Wed, 08 April 2009 17:45 Go to next message
Krishna_dev
Messages: 32
Registered: May 2007
Member
This is my scenario.

I am importing records from SQL Server to an Oracle staging table for a journal import.
For each record imported I need to generate 4 entries only.
The columns being imported from SQL Server are as follows:

COMPANY := 0052 (Constant)
ACCOUNT := 6220700 (Constant)
AMOUNT := Always changes.
INVOICE := Always changes.

So a typical incoming record would be like 0052||6220700||$7000||INVOICE#01.So given the rules

above I would need to generate 3 other records as follows from the orignal record imported as

show below.So once this record is processed I would have the following 4 rows in the oracle

table.

COMPANY||ACCOUNT||AMOUNT||INVOICE

0052 ||6220700||$7000 ||INVOICE#01
0052 ||1664000||$7000 ||INVOICE#01
0002 ||6220700||$7000 ||INVOICE#01
0002 ||1664000||$7000 ||INVOICE#01

Could anyone tell me how I can create these records.
Re: Record Generation [message #397087 is a reply to message #397086] Wed, 08 April 2009 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Could anyone tell me how I can create these records.
Create the needed data in SQL Server before dumping to a flat file, access the flat file as an external table and INSERT into the real & final Oracle table.
Re: Record Generation [message #397091 is a reply to message #397087] Wed, 08 April 2009 21:20 Go to previous messageGo to next message
Krishna_dev
Messages: 32
Registered: May 2007
Member
>Create the needed data in SQL Server before dumping to a flat file, access the flat file as an external table and INSERT into the real & final Oracle table.

Access to the SQL Server Database is limited to "Select" queries only, so we cannot create and records within that database before importing to Oracle.
Re: Record Generation [message #397095 is a reply to message #397086] Wed, 08 April 2009 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Then you either need to write a script or PL/SQL to do the desired extraction & transformation.

Enjoy!
Re: Record Generation [message #397112 is a reply to message #397091] Thu, 09 April 2009 01:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Krishna_dev wrote on Thu, 09 April 2009 04:20
>Create the needed data in SQL Server before dumping to a flat file, access the flat file as an external table and INSERT into the real & final Oracle table.

Access to the SQL Server Database is limited to "Select" queries only, so we cannot create and records within that database before importing to Oracle.

If you can select, you can output anything in any form.
Previous Topic: Effective spooling method
Next Topic: Raise_Application_Error
Goto Forum:
  


Current Time: Thu Dec 08 20:17:52 CST 2016

Total time taken to generate the page: 0.36056 seconds