how to Loading data file (SQL LOADER)
Hi all, Just think some how i have foll. file(I have a data file coming from VAX/VMS machine,on unix machine ,but i currently want soln for WIN2000) HDR 280120022044 SELL031128012002ESP051712311ESP28A SELL055728012002EUR0020011534557EUR28A SELL015728012002USD0005003289157USD28A SELL005328012002USD0010006579053USD28A SELL046428012002EUR0060034602464EUR28A TRL 000137 Record structure Record Size:54 Bytes -- Record Contents -- Item TypeSize Occ Offset IMX-HEADER-RECORDCHARACTER 540 .IMX-HEADER-TYPE CHARACTER 40 .PROCESS-DATECHARACTER 124 _IMX-DETAIL-RECORDCHARACTER 540 .IMX-DETAIL-TYPE CHARACTER 40 .SHOP-NUMBER CHARACTER 44 .TRANSACTION-DATECHARACTER 88 .CURRENCY-CODE CHARACTER 3 16 Item TypeSize Occ Offset .IMX-CURRENCY-VALUE ZONED UNSIGNED13 19 .IMX-STERLING-EQUIVALENT ZONED UNSIGNED13 32 .REFERENCE-NUMBERCHARACTER 9 45 _IMX-TRAILER-RECORD CHARACTER 540 .IMX-TRAILER-TYPECHARACTER 40 .IMX-RECORD-COUNTZONED UNSIGNED 64 How do I load this using SQLoader (is it possible) ya u all must be thinking ,i m loading flat file in a table and not applying RDBMS (normalization) concepts. but that the way i have to do,(now don't ask why? we'll discuss off the list) so my table struct is as foll. NameNull?Type --- IMX_REC_TYPENOT NULL VARCHAR2(4) SHOP_NO VARCHAR2(4) TRANSACTION_DATEDATE CURRENCY_CODE VARCHAR2(3) IMX_CURRENCY_VALUE NUMBER(13) IMX_STERLING_EQUIVALENT NUMBER(13) REFERENCE_NUMBERVARCHAR2(9) PROCESS_DATE DATE IMX_RECORD_COUNT NUMBER(6) IMX_REC_NO NUMBER(6) any help is appreciated using oracle 8.1.6 on win2000 Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FW: how to Loading data file (SQL LOADER)
i forgot to mention that i did look in to manual -Original Message- From: Ghadge,Sameer Sent: Tuesday, February 05, 2002 1:41 PM To: 'Multiple recipients of list ORACLE-L' Subject: how to Loading data file (SQL LOADER) Hi all, Just think some how i have foll. file(I have a data file coming from VAX/VMS machine,on unix machine ,but i currently want soln for WIN2000) HDR 280120022044 SELL031128012002ESP051712311ESP28A SELL055728012002EUR0020011534557EUR28A SELL015728012002USD0005003289157USD28A SELL005328012002USD0010006579053USD28A SELL046428012002EUR0060034602464EUR28A TRL 000137 Record structure Record Size:54 Bytes -- Record Contents -- Item TypeSize Occ Offset IMX-HEADER-RECORDCHARACTER 540 .IMX-HEADER-TYPE CHARACTER 40 .PROCESS-DATECHARACTER 124 _IMX-DETAIL-RECORDCHARACTER 540 .IMX-DETAIL-TYPE CHARACTER 40 .SHOP-NUMBER CHARACTER 44 .TRANSACTION-DATECHARACTER 88 .CURRENCY-CODE CHARACTER 3 16 Item TypeSize Occ Offset .IMX-CURRENCY-VALUE ZONED UNSIGNED13 19 .IMX-STERLING-EQUIVALENT ZONED UNSIGNED13 32 .REFERENCE-NUMBERCHARACTER 9 45 _IMX-TRAILER-RECORD CHARACTER 540 .IMX-TRAILER-TYPECHARACTER 40 .IMX-RECORD-COUNTZONED UNSIGNED 64 How do I load this using SQLoader (is it possible) ya u all must be thinking ,i m loading flat file in a table and not applying RDBMS (normalization) concepts. but that the way i have to do,(now don't ask why? we'll discuss off the list) so my table struct is as foll. NameNull?Type --- IMX_REC_TYPE NOT NULL VARCHAR2(4) SHOP_NO VARCHAR2(4) TRANSACTION_DATE DATE CURRENCY_CODE VARCHAR2(3) IMX_CURRENCY_VALUE NUMBER(13) IMX_STERLING_EQUIVALENT NUMBER(13) REFERENCE_NUMBERVARCHAR2(9) PROCESS_DATE DATE IMX_RECORD_COUNT NUMBER(6) IMX_REC_NO NUMBER(6) any help is appreciated using oracle 8.1.6 on win2000 Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to Loading data file (SQL LOADER)
Hello Sameer The manual in this case is : Oracle8i Utilities Release 2 (8.1.6) See case study number 5: loading data into multiply tables. In your case do three different loads into the same table. I just hope that you can load into one table in this case. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Ghadge,Sameer [SMTP:[EMAIL PROTECTED]] Sent: Tue, February 05, 2002 10:05 AM To: Multiple recipients of list ORACLE-L Subject: how to Loading data file (SQL LOADER) Hi all, Just think some how i have foll. file(I have a data file coming from VAX/VMS machine,on unix machine ,but i currently want soln for WIN2000) HDR 280120022044 SELL031128012002ESP051712311ESP28A SELL055728012002EUR0020011534557EUR28A SELL015728012002USD0005003289157USD28A SELL005328012002USD0010006579053USD28A SELL046428012002EUR0060034602464EUR28A TRL 000137 Record structure Record Size:54 Bytes -- Record Contents -- Item TypeSize Occ Offset IMX-HEADER-RECORDCHARACTER 540 .IMX-HEADER-TYPE CHARACTER 40 .PROCESS-DATECHARACTER 124 _IMX-DETAIL-RECORDCHARACTER 540 .IMX-DETAIL-TYPE CHARACTER 40 .SHOP-NUMBER CHARACTER 44 .TRANSACTION-DATECHARACTER 88 .CURRENCY-CODE CHARACTER 3 16 Item TypeSize Occ Offset .IMX-CURRENCY-VALUE ZONED UNSIGNED13 19 .IMX-STERLING-EQUIVALENT ZONED UNSIGNED13 32 .REFERENCE-NUMBERCHARACTER 9 45 _IMX-TRAILER-RECORD CHARACTER 540 .IMX-TRAILER-TYPECHARACTER 40 .IMX-RECORD-COUNTZONED UNSIGNED 64 How do I load this using SQLoader (is it possible) ya u all must be thinking ,i m loading flat file in a table and not applying RDBMS (normalization) concepts. but that the way i have to do,(now don't ask why? we'll discuss off the list) so my table struct is as foll. NameNull?Type --- IMX_REC_TYPE NOT NULL VARCHAR2(4) SHOP_NO VARCHAR2(4) TRANSACTION_DATE DATE CURRENCY_CODE VARCHAR2(3) IMX_CURRENCY_VALUE NUMBER(13) IMX_STERLING_EQUIVALENT NUMBER(13) REFERENCE_NUMBERVARCHAR2(9) PROCESS_DATE DATE IMX_RECORD_COUNT NUMBER(6) IMX_REC_NO NUMBER(6) any help is appreciated using oracle 8.1.6 on win2000 Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This e-mail was scanned by the eSafe Mail Gateway -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FW: how to Loading data file (SQL LOADER)
i forgot to mention that i did look in to manual This soon will be the right style write it :- I really not expert in sqlloader but I can say what I would do in such a situation I would create a temporary table and load all data (of course remebering the order) there without any modifications at all or maybe only simple ones. Then create a PL/SQL procedure/package to insert data from temporary table into destination table that handles all these complicated things. This is just because I'm feeling much bettter in PL/SQL than loader scripts, and in the same time I'm sure one can do much more with PL/SQL than loader control files. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Ghadge,Sameer Sameer_Ghadge@Synt To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] elinc.comcc: Sent by: Subject: FW: how to Loading data file (SQL LOADER) [EMAIL PROTECTED] 2002.02.05 10:05 Please respond to ORACLE-L i forgot to mention that i did look in to manual -Original Message- From: Ghadge,Sameer Sent: Tuesday, February 05, 2002 1:41 PM To: 'Multiple recipients of list ORACLE-L' Subject: how to Loading data file (SQL LOADER) Hi all, Just think some how i have foll. file(I have a data file coming from VAX/VMS machine,on unix machine ,but i currently want soln for WIN2000) HDR 280120022044 SELL031128012002ESP051712311ESP28A SELL055728012002EUR0020011534557EUR28A SELL015728012002USD0005003289157USD28A SELL005328012002USD0010006579053USD28A SELL046428012002EUR0060034602464EUR28A TRL 000137 Record structure Record Size:54 Bytes -- Record Contents -- Item TypeSize Occ Offset IMX-HEADER-RECORDCHARACTER 54 0 .IMX-HEADER-TYPE CHARACTER 4 0 .PROCESS-DATECHARACTER 12 4 _IMX-DETAIL-RECORDCHARACTER 54 0 .IMX-DETAIL-TYPE CHARACTER 4 0 .SHOP-NUMBER CHARACTER 4 4 .TRANSACTION-DATECHARACTER 8 8 .CURRENCY-CODE CHARACTER 3 16 Item TypeSize Occ Offset .IMX-CURRENCY-VALUE ZONED UNSIGNED13 19 .IMX-STERLING-EQUIVALENT ZONED UNSIGNED13 32 .REFERENCE-NUMBERCHARACTER 9 45 _IMX-TRAILER-RECORD CHARACTER 54 0 .IMX-TRAILER-TYPECHARACTER 4 0 .IMX-RECORD-COUNTZONED UNSIGNED 6 4 How do I load this using SQLoader (is it possible) ya u all must be thinking ,i m loading flat file in a table and not applying RDBMS (normalization) concepts. but that the way i have to do,(now don't ask why? we'll discuss off the list) so my table struct is as foll. NameNull?Type --- IMX_REC_TYPENOT NULL VARCHAR2(4) SHOP_NO VARCHAR2(4) TRANSACTION_DATEDATE CURRENCY_CODE VARCHAR2(3) IMX_CURRENCY_VALUE NUMBER(13) IMX_STERLING_EQUIVALENT NUMBER(13) REFERENCE_NUMBERVARCHAR2(9) PROCESS_DATE DATE IMX_RECORD_COUNT NUMBER(6) IMX_REC_NO
Re: FW: how to Loading data file (SQL LOADER)
IMX-HEADER-RECORD CHARACTER 54 0 .IMX-HEADER-TYPE CHARACTER 4 0 .PROCESS-DATECHARACTER 12 4 _IMX-DETAIL-RECORDCHARACTER 54 0 .IMX-DETAIL-TYPE CHARACTER 4 0 .SHOP-NUMBER CHARACTER 4 4 .TRANSACTION-DATECHARACTER 8 8 .CURRENCY-CODE CHARACTER 3 16 Item TypeSize Occ Offset .IMX-CURRENCY-VALUE ZONED UNSIGNED13 19 .IMX-STERLING-EQUIVALENT ZONED UNSIGNED13 32 .REFERENCE-NUMBERCHARACTER 9 45 _IMX-TRAILER-RECORD CReceived: from CONNECT-MTA by galotterHARACTER 54 0 .IMX-TRAILER-TYPECHARACTER 4 0 .IMX-RECORD-COUNTZONED UNSIGNED 6 4 How do I load this using SQLoader (is it possible) ya u all must be thinking ,i m loading flat file in a table and not applying RDBMS (normalization) concepts. but that the way i have to do,(now don't ask why? we'll discuss off the list) so my table struct is as foll. NameNull?Type --- IMX_REC_TYPENOT NULL VARCHAR2(4) SHOP_NO VARCHAR2(4) TRANSACTION_DATEDATE CURRENCY_CODE VARCHAR2(3) IMX_CURRENCY_VALUE NUMBER(13) IMX_STERLING_EQUIVALENT NUMBER(13) REFERENCE_NUMBERVARCHAR2(9) PROCESS_DATE DATE IMX_RECORD_COUNT NUMBER(6) IMX_REC_NO NUMBER(6) any help is appreciated using oracle 8.1.6 on win2000 Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FW: how to Loading data file (SQL LOADER)
IMX-HEADER-RECORD CHARACTER 54 0 .IMX-HEADER-TYPE CHARACTER 4 0 .PROCESS-DATECHARACTER 12 4 _IMX-DETAIL-RECORDCHARACTER 54 0 .IMX-DETAIL-TYPE CHARACTER 4 0 .SHOP-NUMBER CHARACTER 4 4 .TRANSACTION-DATECHARACTER 8 8 .CURRENCY-CODE CHARACTER 3 16 Item TypeSize Occ Offset .IMX-CURRENCY-VALUE ZONED UNSIGNED13 19 .IMX-STERLING-EQUIVALENT ZONED UNSIGNED13 32 .REFERENCE-NUMBERCHARACTER 9 45 _IMX-TRAILER-RECReceived: from CONNECT-MTA by galotterORD CHARACTER 54 0 .IMX-TRAILER-TYPECHARACTER 4 0 .IMX-RECORD-COUNTZONED UNSIGNED 6 4 How do I load this using SQLoader (is it possible) ya u all must be thinking ,i m loading flat file in a table and not applying RDBMS (normalization) concepts. but that the way i have to do,(now don't ask why? we'll discuss off the list) so my table struct is as foll. NameNull?Type --- IMX_REC_TYPENOT NULL VARCHAR2(4) SHOP_NO VARCHAR2(4) TRANSACTION_DATEDATE CURRENCY_CODE VARCHAR2(3) IMX_CURRENCY_VALUE NUMBER(13) IMX_STERLING_EQUIVALENT NUMBER(13) REFERENCE_NUMBERVARCHAR2(9) PROCESS_DATE DATE IMX_RECORD_COUNT NUMBER(6) IMX_REC_NO NUMBER(6) any help is appreciated using oracle 8.1.6 on win2000 Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).