speeding up conventional path sqlldr
I am using sqlldr conventional path to load some data. My understanding is that I cannot use direct path since my users require access to the table at the same time as the load is in progress. Is this a correct assumption? The speed of the upload is slow, or I think so10,000 rows is taking 20-30 seconds. The first record in the file(rec id 0) is loaded into one table, the rest of the rows into another table. The data records have a fixed length of 1100 bytes. I am running Oracle 9.2 on a Red Hat Linux 9 box with 384 MB RAM. Below is my control file. Any hints on speeding up a conventional path load? load data into table mailpieces_header append when recid = 0 (recid FILLER POSITION(1:1) INTEGER EXTERNAL, JOB_ID CONSTANT 002744, PRINT_STREAM_NAME POSITION(2:9) CHAR, INSERT_NAME_01 POSITION(10:29) CHAR, INSERT_SCAN_CODE_01POSITION(30:41) INTEGER EXTERNAL, INSERT_NAME_02 POSITION(42:61) CHAR, INSERT_SCAN_CODE_02POSITION(62:73) INTEGER EXTERNAL, INSERT_NAME_03 POSITION(74:93) CHAR, INSERT_SCAN_CODE_03POSITION(94:105) INTEGER EXTERNAL, INSERT_NAME_04 POSITION(106:125) CHAR, INSERT_SCAN_CODE_04POSITION(126:137) INTEGER EXTERNAL, INSERT_NAME_05 POSITION(138:157) CHAR, INSERT_SCAN_CODE_05POSITION(158:169) INTEGER EXTERNAL, INSERT_NAME_06 POSITION(170:189) CHAR, INSERT_SCAN_CODE_06POSITION(190:201) INTEGER EXTERNAL, INSERT_NAME_07 POSITION(202:221) CHAR, INSERT_SCAN_CODE_07POSITION(222:233) INTEGER EXTERNAL, INSERT_NAME_08 POSITION(234:253) CHAR, INSERT_SCAN_CODE_08POSITION(254:265) INTEGER EXTERNAL, INSERT_NAME_09 POSITION(266:285) CHAR, INSERT_SCAN_CODE_09POSITION(286:297) INTEGER EXTERNAL, INSERT_NAME_10 POSITION(298:317) CHAR, INSERT_SCAN_CODE_10POSITION(318:329) INTEGER EXTERNAL, INSERT_NAME_11 POSITION(330:349) CHAR, INSERT_SCAN_CODE_11POSITION(350:361) INTEGER EXTERNAL, INSERT_NAME_12 POSITION(362:381) CHAR, INSERT_SCAN_CODE_12POSITION(382:393) INTEGER EXTERNAL, INSERT_NAME_13 POSITION(394:413) CHAR, INSERT_SCAN_CODE_13POSITION(414:425) INTEGER EXTERNAL, INSERT_NAME_14 POSITION(426:445) CHAR, INSERT_SCAN_CODE_14POSITION(446:457) INTEGER EXTERNAL, INSERT_NAME_15 POSITION(458:477) CHAR, INSERT_SCAN_CODE_15POSITION(478:489) INTEGER EXTERNAL, INSERT_NAME_16 POSITION(490:509) CHAR, INSERT_SCAN_CODE_16POSITION(510:521) INTEGER EXTERNAL, INSERT_NAME_17 POSITION(522:541) CHAR, INSERT_SCAN_CODE_17POSITION(542:553) INTEGER EXTERNAL, INSERT_NAME_18 POSITION(554:573) CHAR, INSERT_SCAN_CODE_18POSITION(574:585) INTEGER EXTERNAL, INSERT_NAME_19 POSITION(586:605) CHAR, INSERT_SCAN_CODE_19POSITION(606:617) INTEGER EXTERNAL, INSERT_NAME_20 POSITION(618:637) CHAR, INSERT_SCAN_CODE_20POSITION(638:649) INTEGER EXTERNAL, INSERT_NAME_21 POSITION(650:669) CHAR, INSERT_SCAN_CODE_21POSITION(670:681) INTEGER EXTERNAL, ACCOUNT_NUMBER POSITION(682:689) CHAR, SUB_ACCOUNT_NUMBER POSITION(690:697) CHAR, PRINT_KEYLINE POSITION(698:698) CHAR, PRINT_RECIPIENT_ADDRESSPOSITION(699:699) CHAR, PRINT_RETURN_ADDRESS POSITION(700:700) CHAR, PRINT_MARKETING_MESSAGEPOSITION(701:701) CHAR, PRINT_CERTIFIED_MAIL_BARCODE POSITION(702:702) CHAR, PRINT_ENDORSEMENT_LINE POSITION(703:703) CHAR, PRINT_ENCLOSURE_MESSAGEPOSITION(704:704) CHAR, MANIFEST_JOB POSITION(705:705) CHAR, REPRINTS_ORG_MANIFEST POSITION(706:706) CHAR, REPRINTS_ORG_TRAY POSITION(707:707) CHAR, REPRINTS_REASSIGN_SEQUENCESPOSITION(708:708) CHAR, METER_RATE_1 POSITION(709:714) CHAR, METER_RATE_2 POSITION(715:720) CHAR, TRAY_SIZE POSITION(721:723) CHAR, JOB_DISPOSITIONPOSITION(724:727) CHAR, SITE_IDPOSITION(728:728) CHAR, FUTURE_USE_1 POSITION(729:900) CHAR, INITIAL_RUN_DATETIME POSITION(901:914) DATE MMDDhh24miss, REPRINT_RUN_NUMBER POSITION(915:916) CHAR, FINAL_RUN_DATETIME POSITION(917:930) DATE MMDDhh24miss, TOTAL_PIECES
Re: speeding up conventional path sqlldr
ive found through repeated use that its faster to direct path load the data to a staging table then do an insert /*+ append */ to move the data over to the master table. speed of inserts will depend on how many indexes you have. however, since you are on 9i you dont need to use either. use an external table and a merge. go to asktom. he has an example. From: John Dunn [EMAIL PROTECTED] Date: 2003/08/15 Fri AM 07:09:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: speeding up conventional path sqlldr I am using sqlldr conventional path to load some data. My understanding is that I cannot use direct path since my users require access to the table at the same time as the load is in progress. Is this a correct assumption? The speed of the upload is slow, or I think so10,000 rows is taking 20-30 seconds. The first record in the file(rec id 0) is loaded into one table, the rest of the rows into another table. The data records have a fixed length of 1100 bytes. I am running Oracle 9.2 on a Red Hat Linux 9 box with 384 MB RAM. Below is my control file. Any hints on speeding up a conventional path load? load data into table mailpieces_header append when recid = 0 (recid FILLER POSITION(1:1) INTEGER EXTERNAL, JOB_ID CONSTANT 002744, PRINT_STREAM_NAME POSITION(2:9) CHAR, INSERT_NAME_01 POSITION(10:29) CHAR, INSERT_SCAN_CODE_01POSITION(30:41) INTEGER EXTERNAL, INSERT_NAME_02 POSITION(42:61) CHAR, INSERT_SCAN_CODE_02POSITION(62:73) INTEGER EXTERNAL, INSERT_NAME_03 POSITION(74:93) CHAR, INSERT_SCAN_CODE_03POSITION(94:105) INTEGER EXTERNAL, INSERT_NAME_04 POSITION(106:125) CHAR, INSERT_SCAN_CODE_04POSITION(126:137) INTEGER EXTERNAL, INSERT_NAME_05 POSITION(138:157) CHAR, INSERT_SCAN_CODE_05POSITION(158:169) INTEGER EXTERNAL, INSERT_NAME_06 POSITION(170:189) CHAR, INSERT_SCAN_CODE_06POSITION(190:201) INTEGER EXTERNAL, INSERT_NAME_07 POSITION(202:221) CHAR, INSERT_SCAN_CODE_07POSITION(222:233) INTEGER EXTERNAL, INSERT_NAME_08 POSITION(234:253) CHAR, INSERT_SCAN_CODE_08POSITION(254:265) INTEGER EXTERNAL, INSERT_NAME_09 POSITION(266:285) CHAR, INSERT_SCAN_CODE_09POSITION(286:297) INTEGER EXTERNAL, INSERT_NAME_10 POSITION(298:317) CHAR, INSERT_SCAN_CODE_10POSITION(318:329) INTEGER EXTERNAL, INSERT_NAME_11 POSITION(330:349) CHAR, INSERT_SCAN_CODE_11POSITION(350:361) INTEGER EXTERNAL, INSERT_NAME_12 POSITION(362:381) CHAR, INSERT_SCAN_CODE_12POSITION(382:393) INTEGER EXTERNAL, INSERT_NAME_13 POSITION(394:413) CHAR, INSERT_SCAN_CODE_13POSITION(414:425) INTEGER EXTERNAL, INSERT_NAME_14 POSITION(426:445) CHAR, INSERT_SCAN_CODE_14POSITION(446:457) INTEGER EXTERNAL, INSERT_NAME_15 POSITION(458:477) CHAR, INSERT_SCAN_CODE_15POSITION(478:489) INTEGER EXTERNAL, INSERT_NAME_16 POSITION(490:509) CHAR, INSERT_SCAN_CODE_16POSITION(510:521) INTEGER EXTERNAL, INSERT_NAME_17 POSITION(522:541) CHAR, INSERT_SCAN_CODE_17POSITION(542:553) INTEGER EXTERNAL, INSERT_NAME_18 POSITION(554:573) CHAR, INSERT_SCAN_CODE_18POSITION(574:585) INTEGER EXTERNAL, INSERT_NAME_19 POSITION(586:605) CHAR, INSERT_SCAN_CODE_19POSITION(606:617) INTEGER EXTERNAL, INSERT_NAME_20 POSITION(618:637) CHAR, INSERT_SCAN_CODE_20POSITION(638:649) INTEGER EXTERNAL, INSERT_NAME_21 POSITION(650:669) CHAR, INSERT_SCAN_CODE_21POSITION(670:681) INTEGER EXTERNAL, ACCOUNT_NUMBER POSITION(682:689) CHAR, SUB_ACCOUNT_NUMBER POSITION(690:697) CHAR, PRINT_KEYLINE POSITION(698:698) CHAR, PRINT_RECIPIENT_ADDRESSPOSITION(699:699) CHAR, PRINT_RETURN_ADDRESS POSITION(700:700) CHAR, PRINT_MARKETING_MESSAGEPOSITION(701:701) CHAR, PRINT_CERTIFIED_MAIL_BARCODE POSITION(702:702) CHAR, PRINT_ENDORSEMENT_LINE POSITION(703:703) CHAR, PRINT_ENCLOSURE_MESSAGEPOSITION(704:704) CHAR, MANIFEST_JOB POSITION(705:705) CHAR, REPRINTS_ORG_MANIFEST POSITION(706:706) CHAR, REPRINTS_ORG_TRAY POSITION(707:707) CHAR, REPRINTS_REASSIGN_SEQUENCESPOSITION
RE: speeding up conventional path sqlldr
Try external tables or checkout a good article written by Stephen Andert from this list which was a review for Jonathan Gennick's sqlloader book The link is http://www.oreillynet.com/pub/a/oreilly/oracle/news/oraclesqlload_0401.html Key factors are bindsize and rows settings John -Original Message- Sent: 15 August 2003 12:39 To: Multiple recipients of list ORACLE-L ive found through repeated use that its faster to direct path load the data to a staging table then do an insert /*+ append */ to move the data over to the master table. speed of inserts will depend on how many indexes you have. however, since you are on 9i you dont need to use either. use an external table and a merge. go to asktom. he has an example. From: John Dunn [EMAIL PROTECTED] Date: 2003/08/15 Fri AM 07:09:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: speeding up conventional path sqlldr I am using sqlldr conventional path to load some data. My understanding is that I cannot use direct path since my users require access to the table at the same time as the load is in progress. Is this a correct assumption? The speed of the upload is slow, or I think so10,000 rows is taking 20-30 seconds. The first record in the file(rec id 0) is loaded into one table, the rest of the rows into another table. The data records have a fixed length of 1100 bytes. I am running Oracle 9.2 on a Red Hat Linux 9 box with 384 MB RAM. Below is my control file. Any hints on speeding up a conventional path load? load data into table mailpieces_header append when recid = 0 (recid FILLER POSITION(1:1) INTEGER EXTERNAL, JOB_ID CONSTANT 002744, PRINT_STREAM_NAME POSITION(2:9) CHAR, INSERT_NAME_01 POSITION(10:29) CHAR, INSERT_SCAN_CODE_01POSITION(30:41) INTEGER EXTERNAL, INSERT_NAME_02 POSITION(42:61) CHAR, INSERT_SCAN_CODE_02POSITION(62:73) INTEGER EXTERNAL, INSERT_NAME_03 POSITION(74:93) CHAR, INSERT_SCAN_CODE_03POSITION(94:105) INTEGER EXTERNAL, INSERT_NAME_04 POSITION(106:125) CHAR, INSERT_SCAN_CODE_04POSITION(126:137) INTEGER EXTERNAL, INSERT_NAME_05 POSITION(138:157) CHAR, INSERT_SCAN_CODE_05POSITION(158:169) INTEGER EXTERNAL, INSERT_NAME_06 POSITION(170:189) CHAR, INSERT_SCAN_CODE_06POSITION(190:201) INTEGER EXTERNAL, INSERT_NAME_07 POSITION(202:221) CHAR, INSERT_SCAN_CODE_07POSITION(222:233) INTEGER EXTERNAL, INSERT_NAME_08 POSITION(234:253) CHAR, INSERT_SCAN_CODE_08POSITION(254:265) INTEGER EXTERNAL, INSERT_NAME_09 POSITION(266:285) CHAR, INSERT_SCAN_CODE_09POSITION(286:297) INTEGER EXTERNAL, INSERT_NAME_10 POSITION(298:317) CHAR, INSERT_SCAN_CODE_10POSITION(318:329) INTEGER EXTERNAL, INSERT_NAME_11 POSITION(330:349) CHAR, INSERT_SCAN_CODE_11POSITION(350:361) INTEGER EXTERNAL, INSERT_NAME_12 POSITION(362:381) CHAR, INSERT_SCAN_CODE_12POSITION(382:393) INTEGER EXTERNAL, INSERT_NAME_13 POSITION(394:413) CHAR, INSERT_SCAN_CODE_13POSITION(414:425) INTEGER EXTERNAL, INSERT_NAME_14 POSITION(426:445) CHAR, INSERT_SCAN_CODE_14POSITION(446:457) INTEGER EXTERNAL, INSERT_NAME_15 POSITION(458:477) CHAR, INSERT_SCAN_CODE_15POSITION(478:489) INTEGER EXTERNAL, INSERT_NAME_16 POSITION(490:509) CHAR, INSERT_SCAN_CODE_16POSITION(510:521) INTEGER EXTERNAL, INSERT_NAME_17 POSITION(522:541) CHAR, INSERT_SCAN_CODE_17POSITION(542:553) INTEGER EXTERNAL, INSERT_NAME_18 POSITION(554:573) CHAR, INSERT_SCAN_CODE_18POSITION(574:585) INTEGER EXTERNAL, INSERT_NAME_19 POSITION(586:605) CHAR, INSERT_SCAN_CODE_19POSITION(606:617) INTEGER EXTERNAL, INSERT_NAME_20 POSITION(618:637) CHAR, INSERT_SCAN_CODE_20POSITION(638:649) INTEGER EXTERNAL, INSERT_NAME_21 POSITION(650:669) CHAR, INSERT_SCAN_CODE_21POSITION(670:681) INTEGER EXTERNAL, ACCOUNT_NUMBER POSITION(682:689) CHAR, SUB_ACCOUNT_NUMBER POSITION(690:697) CHAR, PRINT_KEYLINE POSITION(698:698) CHAR, PRINT_RECIPIENT_ADDRESSPOSITION(699:699) CHAR, PRINT_RETURN_ADDRESS POSITION(700:700) CHAR, PRINT_MARKETING_MESSAGEPOSITION(701:701) CHAR
sqlldr questions
I'm a sqlldr newbie. Platform is Oracle 9.2. My question is...can I prevent sqlldr loading any data if there is even a single bad record, even if it is, for example, the last record? I am using conventional path load. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: sqlldr questions
An excellent book for SQL Loader new comers, and experienced users as well, is one by Jonathan Gennick and Sanjay Mishra : Oracle SQL Loader, The definitive Guide, by O'Reilly. Stenphen Andert will agree with me ;) - Kirti --- John Dunn [EMAIL PROTECTED] wrote: I'm a sqlldr newbie. Platform is Oracle 9.2. My question is...can I prevent sqlldr loading any data if there is even a single bad record, even if it is, for example, the last record? I am using conventional path load. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: sqlldr questions
i read this last year. correct me if im wrong, but it didnt seem to have much more than was on otn? just explained slightly differently with a slightly more depth. From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2003/07/17 Thu AM 09:54:29 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: sqlldr questions An excellent book for SQL Loader new comers, and experienced users as well, is one by Jonathan Gennick and Sanjay Mishra : Oracle SQL Loader, The definitive Guide, by O'Reilly. Stenphen Andert will agree with me ;) - Kirti --- John Dunn [EMAIL PROTECTED] wrote: I'm a sqlldr newbie. Platform is Oracle 9.2. My question is...can I prevent sqlldr loading any data if there is even a single bad record, even if it is, for example, the last record? I am using conventional path load. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: sqlldr questions
Please look the following text, which works fine everyday for loading my data into oracle 9.2 database. $ORACLE_HOME/bin/sqlldr USERID=your_username/[EMAIL PROTECTED] control=link-summary.ctl log=link-summary.log $ more link-summary.ctl options (rows=9297500, errors=2, bindsize=100, readsize=100) LOAD DATA INFILE '/tmp/log_file_parsing_output.dat' truncate INTO TABLE access_summary FIELDS TERMINATED BY ' ' (CLIENT_IP char(100), ACCESS_DATE char(9), URL char(400)) This is my application. You need to change file name, file path, and table name and its column name as well. Hope it can help you. Don Kirtikumar Deshpande wrote: An excellent book for SQL Loader new comers, and experienced users as well, is one by Jonathan Gennick and Sanjay Mishra : Oracle SQL Loader, The definitive Guide, by O'Reilly. Stenphen Andert will agree with me ;) - Kirti --- John Dunn [EMAIL PROTECTED] wrote: I'm a sqlldr newbie. Platform is Oracle 9.2. My question is...can I prevent sqlldr loading any data if there is even a single bad record, even if it is, for example, the last record? I am using conventional path load. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Don Yu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
ORA-01722 while using sqlldr
I'm hoping for expert assistance. I'm using SQL*Loader to load some 2200+ records (comma delimited) into a table and running into this problem for the modcnt field: The control file reads: modcnt DECIMAL EXTERNAL -- I'm sure this is where my problem is. I have tried almost every datatype that the control file will accept - some bleed into the next column which is a date field, messing up that column, etc with ORA-01841 (full) year must be between -4713 and +, and not be 0. The datatype in the table is: MODCNT NUMBER -- no precision defined The value in the file for this field is always 0. And when I start to load the file, I receive this error (I'm testing with a few random records from the 2200+ and each have the same error): Record 1: Rejected - Error on table LC_F, column MODCNT. ORA-01722: invalid number Any help is greatly appreciated. Thanks, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Datafile with no extension using sqlldr
Krishna, Have you tried putting a . at the end of your file name when calling sqlldr? Or can you rename the file to be emp.dat at the os level? Stephen [EMAIL PROTECTED] 12/28/02 09:13PM Hi, Oracle 8.1.7 on SUN Solaris 5.8 I want to load a datafile with no extension into Oracle using SQL*Loader. But it looks for a file with a .dat extension. This is the exact error message I got: SQL*Loader-500: Unable to open file (emp.dat) SQL*Loader-553: file not found SQL*Loader-509: System error: The system cannot find the file specified. I have tried a number of ways but in vain. I understand I can rename the file. But, is there any other way to load a datafile with no extension? TIA, Krishna -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Datafile with no extension using sqlldr
infile 'abcd.' Yechiel AdarMehish - Original Message - From: Krishna Kakatur To: Multiple recipients of list ORACLE-L Sent: Sunday, December 29, 2002 6:13 AM Subject: Datafile with no extension using sqlldr Hi, Oracle 8.1.7 on SUN Solaris 5.8 I want to load a datafile with no extension into Oracle using SQL*Loader. But it looks for a file with a .dat extension. This is the exact error message I got: SQL*Loader-500: Unable to open file (emp.dat)SQL*Loader-553: file not foundSQL*Loader-509: System error: The system cannot find the file specified. I have tried a number of ways but in vain. I understand I can rename the file. But, is there any other way to load a datafile with no extension? TIA,Krishna
Datafile with no extension using sqlldr
Hi, Oracle 8.1.7 on SUN Solaris 5.8 I want to load a datafile with no extension into Oracle using SQL*Loader. But it looks for a file with a .dat extension. This is the exact error message I got: SQL*Loader-500: Unable to open file (emp.dat)SQL*Loader-553: file not foundSQL*Loader-509: System error: The system cannot find the file specified. I have tried a number of ways but in vain. I understand I can rename the file. But, is there any other way to load a datafile with no extension? TIA,Krishna
Giving back sqlldr (was: Giving back Perl)
Great idea, Lisa! I may not have much to give back to the List for all that It's done for me, but this little ditty I just threw together might help someone: I've been struggling with grep, awk, and sed on the listener.log file to help me get an idea of how we're hitting our production DBs. Without much luck (not very good w/awk), a dim 10-watt bulb switched on when I thought of dumping the pertinent parts of the log into a DB so we can SQL some reports off of them. Since I rotate our listener.log daily to prevent them from growing too large (~10K attaches/day), a simple table and accompanying SQL*Load script ought to do the job. First, the simple table, indexes, and a trigger: CREATE TABLE LISTENER_LOG ( TIMESTAMP DATE, SIDNAMEVARCHAR2(10), PROGRAMVARCHAR2(80), HOSTNAME VARCHAR2(32), OSUSER VARCHAR2(32), IPADDR VARCHAR2(15) ); CREATE INDEX LISTENER_LOG_HOSTNAME ON LISTENER_LOG (HOSTNAME); CREATE INDEX LISTENER_LOG_OSUSER ON LISTENER_LOG (OSUSER); CREATE INDEX LISTENER_LOG_PROGRAM ON LISTENER_LOG (PROGRAM); REM Trigger used to remove Windohs directory specs from program name. CREATE OR REPLACE TRIGGER LISTENER_LOG_I BEFORE INSERT ON LISTENER_LOG FOR EACH ROW DECLARE BEGIN :NEW.program := LOWER(SUBSTR(:NEW.program,INSTR(:NEW.program,'\',-1)+1)); END listener_log_i; / SHOW ERRORS; Obviously, the above can be modified for size, tablespace, etc. Next, we need a SQL*Loader control file: load data infile '$ORACLE_HOME/network/log/listener.log' badfile 'listener.err' append into table listener_log when (43) = 'P' trailing nullcols (timestamp position(1:20) date DD-MON- HH24:MI:SS, skip1 FILLER char terminated by '=', sidname enclosed by (SID= and ), skip2 FILLER char terminated by =, program enclosed by (PROGRAM= and ), hostname enclosed by (HOST= and ), osuser enclosed by (USER= and ))), skip3 FILLER char terminated by '=', skip4 FILLER char terminated by '=', skip5 FILLER char terminated by ')', ipaddr enclosed by (HOST= and ) ) I used the WHEN clause to only pickup those rows in the listener.log file that actually log a connection to our production DB. For this example, our production DB might be PROD. The placement of the (SID=PROD) section will vary depending on how the service is defined in Oracle Networking (e.g. TNSNAMES.ORA, ONAMES, LDAP). Ours happens to start at column 43. Modify the WHEN clause according to your own listener.log. Additionally, with 8i (I think) there could be a (SRVR=DEDICATED) or (SERVER=DEDICATED) block -- unless you're using MTS (we're not). For this, I needed to change skip2 to read: skip2 FILLER char enclosed by (SRVR= and =, Finally, we need to call SQL*Loader (for me this is on 8.1.7 on HP/UX): sqlldr MYSCHEMANAME control=listener.sqlload log=listener_sqlload.log direct=false rows=2000 bindsize=1000 readsize=1000 ...where MYSCHEMANAME is the same user that owns the LISTENER_LOG table. Expect many many Discarded - failed all WHEN clauses in the listener_sqlload.log file. That's it! Afterwards, you can have all sorts of Holiday fun whilst querying your newly poplulated table. When we get to 9i, I'm thinking that an external table and a CTAS or other load script might do the trick, too. Ho-Ho-Hope this can help someone! Merry Christmas! Happy New Year! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, December 19, 2002 11:30 AM To: Multiple recipients of list ORACLE-L Hello everyone - I've been trying to write more Perl lately and after showing my Perl mentor some of the stuff I had written over the last couple of weeks the first thing he said was why don't you give back to the community. So I figure if I offer up my latest creation I could 1. give back and maybe help a person or two and 2. receive comments on my coding. My coding style is rather simplistic and I would appreciate any comments about what I'm doing wrong/how something could be done differently (TMTOWTDI), etc. My environment is W2K sp2/8.1.7.2. I slapped together a script that fires backup controlfile to trace (including the stored proc) and ftp's it off to another server. If anyone is interested in seeing it please email me directly. Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
SQLLdr help needed
Hi All, Oracle 8.1.6 WinNT I have been trying to use sqlldr and I am getting following error Record 1: Rejected - Error on table INTERVIEW, column COMMENTS. end of logical record found when reading length of varying length field Here is sample record and control file. Any ideas why I get this error. MetaLink says TRAILING NULLCOLS should correct. The field it is complaining is second to last field in input file. 37901|01/01/1970 04:59:10||LDMARCHI||| LOAD DATA INFILE 'd:\oracle\admin\tmwktst3\scripts\BDUMPI2.txt' BADFILE 'd:\oracle\admin\tmwktst3\scripts\INTERVIEW.bad' insert INTO TABLE INTERVIEW FIELDS TERMINATED BY '|' TRAILING NULLCOLS (PROFILE_NO, INTERVIEW_DATE DATE 'MM/DD/ HH24:MI:SS', FU_DATE DATE 'MM/DD/', OPERATOR CHAR, SOURCE CHAR, APPEARANCE CHAR, FAMILY CHAR, LANGUAGE_SKILLS CHAR, CAREER_PLANS CHAR, MALPRACTICE_HIS CHAR, ECONOMICS1 CHAR, ECONOMICS2 CHAR, MET_WITH CHAR, COMMENTS varchar(4000) SUBSTR(:COMMENTS,1,2000), ROW_PROCESSED) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQLLdr help needed
I am not sure if it helps, but I would use: - FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '' - COMMENTS position(n:n+2000) where n=the offset of the first character of the field COMMENTS including delimiters - ROW_POCESSED SEQUENCE(MAX,1) inka -Original Message- Sent: Wednesday, October 02, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Hi All, Oracle 8.1.6 WinNT I have been trying to use sqlldr and I am getting following error Record 1: Rejected - Error on table INTERVIEW, column COMMENTS. end of logical record found when reading length of varying length field Here is sample record and control file. Any ideas why I get this error. MetaLink says TRAILING NULLCOLS should correct. The field it is complaining is second to last field in input file. 37901|01/01/1970 04:59:10||LDMARCHI||| LOAD DATA INFILE 'd:\oracle\admin\tmwktst3\scripts\BDUMPI2.txt' BADFILE 'd:\oracle\admin\tmwktst3\scripts\INTERVIEW.bad' insert INTO TABLE INTERVIEW FIELDS TERMINATED BY '|' TRAILING NULLCOLS (PROFILE_NO, INTERVIEW_DATE DATE 'MM/DD/ HH24:MI:SS', FU_DATE DATE 'MM/DD/', OPERATOR CHAR, SOURCE CHAR, APPEARANCE CHAR, FAMILY CHAR, LANGUAGE_SKILLS CHAR, CAREER_PLANS CHAR, MALPRACTICE_HIS CHAR, ECONOMICS1 CHAR, ECONOMICS2 CHAR, MET_WITH CHAR, COMMENTS varchar(4000) SUBSTR(:COMMENTS,1,2000), ROW_PROCESSED) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Inka Bezdziecka INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQLLDR question SOLVED
Thanks for all the help. It turns out there was a problem with the data and the field seperator was part of the data in one of the fields. -Original Message- Sent: Friday, July 26, 2002 10:33 AM To: Multiple recipients of list ORACLE-L I am trying to load data via sqlldr (8.1.6.3 on Solaris 8). A couple of records keep kicking out. Because on the commonality between the records that are kicked out, I have a question. Does sqlldr treat an the same way that sqlplus does. In other words, if one of the fields in a records has an embedded in it, will sqlldr reject the record? If so, is there a way around this? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ball, Terry 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: SQLLDR question
Terry, I tried the following in 817 and it worked fine: LOAD DATA INFILE * INTO TABLE test REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( col1 char , col_seq test_seq.nextval ) BEGINDATA 'one', 'two', (sorry about the first email - fingers slipped!) Do you get an error in the sqlldr log? - what does it say? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, July 26, 2002 11:33 AM To: Multiple recipients of list ORACLE-L I am trying to load data via sqlldr (8.1.6.3 on Solaris 8). A couple of records keep kicking out. Because on the commonality between the records that are kicked out, I have a question. Does sqlldr treat an the same way that sqlplus does. In other words, if one of the fields in a records has an embedded in it, will sqlldr reject the record? If so, is there a way around this? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Mercadante, Thomas F 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: SQLLDR question
I'm not a sqlloader expert, so take with a large grain of salt. If you are using conventional path, a series of 'insert' statements are executed. This may cause the to be interpreted as the variable indicator. If you are using direct path, it may create a block of rows and then directly place them in the proper extent without using the SQL layer. So, try direct path and see if it works, but no promises. Another thought is to do a search and replace of the data and add the escape character (\) before each . Again, try it, but no promises. -Original Message- Sent: Friday, July 26, 2002 9:33 AM To: Multiple recipients of list ORACLE-L I am trying to load data via sqlldr (8.1.6.3 on Solaris 8). A couple of records keep kicking out. Because on the commonality between the records that are kicked out, I have a question. Does sqlldr treat an the same way that sqlplus does. In other words, if one of the fields in a records has an embedded in it, will sqlldr reject the record? If so, is there a way around this? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Fink, Dan 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).
How to make SQLLDR to run faster?
I try to insert 14,000 rows into oracle database using SQLLDR and it takes too long to finish (around an hour). Is there a way to improve SQLLDR to make it run faster? For example, if modify parameter file will help? Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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 make SQLLDR to run faster?
David, start shameless self-plug I wrote an article last year about tuning SQL*Loader. It is at http://oracle.oreilly.com/news/oraclesqlload_0401.htmlI experienced a huge improvement in performance and others have told me that they were able to achieve similar improvements. end shameless self-plug Stephen [EMAIL PROTECTED] 06/20/02 04:20PM I try to insert 14,000 rows into oracle database using SQLLDR and it takestoo long to finish (around an hour). Is there a way to improve SQLLDR tomake it run faster? For example, if modify parameter file will help?Thanks,David-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Nguyen, David M INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: How to make SQLLDR to run faster?
Please check this out : http://oracle.oreilly.com/news/oraclesqlload_0401.html And then the book by Jonathan Gennick and Sanjay Sharma. HTH, - Kirti -Original Message- Sent: Thursday, June 20, 2002 6:20 PM To: Multiple recipients of list ORACLE-L I try to insert 14,000 rows into oracle database using SQLLDR and it takes too long to finish (around an hour). Is there a way to improve SQLLDR to make it run faster? For example, if modify parameter file will help? Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: Deshpande, Kirti 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: sqlldr
DIR=directory where the files live ls $DIR | while read FILE do sqlldr ... file=$DIR/$FILE ... done --- Ravindra [EMAIL PROTECTED] wrote: We have a process that generates log file continously with dateandtime stamp on the filename like log_041502_113545.log These log files should be loade to the table using sql loader.I have the sqlldr script to load to the table.But this sqlldr will be part of the shell script that is will be executed as cron job on the unix server. How can I read the varying file name in the sqlldr command and automate the sqlldr job. thanks Ravi __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass 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).
sqlldr
We have a process that generates log file continously with dateandtime stamp on the filename like log_041502_113545.log These log files should be loade to the table using sql loader.I have the sqlldr script to load to the table.But this sqlldr will be part of the shell script that is will be executed as cron job on the unix server. How can I read the varying file name in the sqlldr command and automate the sqlldr job. thanks Ravi
How to insert new lines through SQLLDR
Hi all, I need to insert special charecter into the database through sql loader. i.e., I have a table with LONG datatype field and data should read: [Cancellation] IF ServiceDate = sysdate THEN Chargepercent(100) ELSEIF ServiceDate sydate THEN chargepercent(50) ELSE chargepercent(25) ENDIF My flat file: '[Cancellation' ||chr(13)||chr(10)|| 'IF ServiceDate = sydate THEN ' ||chr(13)||chr(10)|| 'Chargeperce(100)' ||chr(13)||chr(10)|| 'ELSEIF ServiceDate sysdate THEN ' ||chr(13)||chr(10)|| 'chargepercent(50)' ||chr(13)||chr(10)|| 'ELSE ' ||chr(13)||chr(10)|| 'chargepercent(25)' ||chr(13)||chr(10)|| 'ENDIF' When I use the above flat file to load the table with sqlldr the table is not getting populated the way I expected. How can I insert the new lines? Thanks, Suhasini. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kanchanakuntla, Suhasini 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 insert new lines through SQLLDR
Several things. One, you can indicate (RTFM, don't remember the details) that your physical record is terminated by something else than a carriage return. Second, if what you want to store is PL/SQL code, then carriage returns are useless - they are 'for your eyes only' (which is why you have a hardly readable but Oracle-usable V$SQLTEXT and a V$SQLTEXT_WITH_NEW_LINES when having an indiscreet peek into the SGA). Third, if I were you I would use either CLOBs or even VARCHAR2 rather than LONGs. Fourth, you look on your way to build the application of death and reinventing the stored procedure. There are times when flexibility is not THAT desirable. I am not sure that EXECUTE IMMEDIATE of dynamically built PL/SQL blocks (if I read correctly between the lines) is a good solution. - Original Message - From: Kanchanakuntla, Suhasini [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 30 Apr 2002 07:08:51 Hi all, I need to insert special charecter into the database through sql loader. i.e., I have a table with LONG datatype field and data should read: [Cancellation] IF ServiceDate = sysdate THEN Chargepercent(100) ELSEIF ServiceDate sydate THEN chargepercent(50) ELSE chargepercent(25) ENDIF My flat file: '[Cancellation' ||chr(13)||chr(10)|| 'IF ServiceDate = sydate THEN ' ||chr(13)||chr(10)|| 'Chargeperce(100)' ||chr(13)||chr(10)|| 'ELSEIF ServiceDate sysdate THEN ' ||chr(13)||chr(10)|| 'chargepercent(50)' ||chr(13)||chr(10)|| 'ELSE ' ||chr(13)||chr(10)|| 'chargepercent(25)' ||chr(13)||chr(10)|| 'ENDIF' When I use the above flat file to load the table with sqlldr the table is not getting populated the way I expected. How can I insert the new lines? Thanks, Suhasini. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kanchanakuntla, Suhasini 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: sqlldr
Hello I do not have experience with 9i and 8i. One paper that I downloaded from OTN (I think) is: Install and Configure the Oracle 9i Platform. This paper starts with preinstall task: Remove installed oracle databases and removed all installed oracle software from the machine. I believe this is just to simplify the new install. We have a NT server with oracle 7.3.4 and 8.1.6 installed in two homes and there are no problems. Yechiel Adar Mehish - Original Message - From: Eriovaldo Andrietta To: Multiple recipients of list ORACLE-L Sent: Sunday, April 28, 2002 2:13 AM Subject: Re: sqlldr HI Yechiel : How can i to install two versions of Oracle in the same server , like : Oracle 8i and Oracle 9i, in oracle_home different. Is it possible ? Regards Eriovaldo - Original Message - From: Yechiel Adar To: Multiple recipients of list ORACLE-L Sent: Saturday, April 27, 2002 8:18 PM Subject: Re: sqlldr Hello Stephen Thanks for the link. The article is very illuminating. As we have some loader tuning to do I thank you for sharing the knowledge. Yechiel Adar Mehish - Original Message - From: Stephen Andert To: Multiple recipients of list ORACLE-L Sent: Friday, April 26, 2002 6:43 PM Subject: Re: sqlldr Ravi, To the best of my knowledge, there is no limitation on the number of records that can be loaded at a time other than the size of your rollback segments. I have loaded 10's of millions of rows in a single sqlldr job by setting some parameters. There are defaults that work, but don't perform very well. To determine appropriate settings, you may want to read an article I wrote for O'Reilly Associates (located athttp://oracle.oreilly.com/news/oraclesqlload_0401.html)which gives the step-by-step directions on setting2 of the key parameters (BINDSIZE and ROWS). It's a pretty easy thing to do, but without knowing you data, the only advice I could give is that DIRECT is the best quick tuning fix if you can. If you are going to be doing any serious data loading with SQL*Loader, Jonathan's book Oracle SQL*Loader: The Definitive Guidewould be a good investment. HTH Stephen [EMAIL PROTECTED] 04/25/02 10:13PM i belive there is a limitation on the number of records that can be loadedfrom sqlldr at a time based on the bind array and rows default parameter.if i have a data file with more than 5000records(don't know the exactnumber) that needs to be imported using sqlldrwhat is the value i need to set for the "rows" and/or "bind array" for me toload the records to the table at one attempt.thanksravi-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ravindra B INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: sqlldr
ok Yechiel. Thanks for the answer. Regards Eriovaldo - Original Message - From: Yechiel Adar To: Multiple recipients of list ORACLE-L Sent: Sunday, April 28, 2002 6:43 AM Subject: Re: sqlldr Hello I do not have experience with 9i and 8i. One paper that I downloaded from OTN (I think) is: Install and Configure the Oracle 9i Platform. This paper starts with preinstall task: Remove installed oracle databases and removed all installed oracle software from the machine. I believe this is just to simplify the new install. We have a NT server with oracle 7.3.4 and 8.1.6 installed in two homes and there are no problems. Yechiel Adar Mehish - Original Message - From: Eriovaldo Andrietta To: Multiple recipients of list ORACLE-L Sent: Sunday, April 28, 2002 2:13 AM Subject: Re: sqlldr HI Yechiel : How can i to install two versions of Oracle in the same server , like : Oracle 8i and Oracle 9i, in oracle_home different. Is it possible ? Regards Eriovaldo - Original Message - From: Yechiel Adar To: Multiple recipients of list ORACLE-L Sent: Saturday, April 27, 2002 8:18 PM Subject: Re: sqlldr Hello Stephen Thanks for the link. The article is very illuminating. As we have some loader tuning to do I thank you for sharing the knowledge. Yechiel Adar Mehish - Original Message - From: Stephen Andert To: Multiple recipients of list ORACLE-L Sent: Friday, April 26, 2002 6:43 PM Subject: Re: sqlldr Ravi, To the best of my knowledge, there is no limitation on the number of records that can be loaded at a time other than the size of your rollback segments. I have loaded 10's of millions of rows in a single sqlldr job by setting some parameters. There are defaults that work, but don't perform very well. To determine appropriate settings, you may want to read an article I wrote for O'Reilly Associates (located athttp://oracle.oreilly.com/news/oraclesqlload_0401.html)which gives the step-by-step directions on setting2 of the key parameters (BINDSIZE and ROWS). It's a pretty easy thing to do, but without knowing you data, the only advice I could give is that DIRECT is the best quick tuning fix if you can. If you are going to be doing any serious data loading with SQL*Loader, Jonathan's book Oracle SQL*Loader: The Definitive Guidewould be a good investment. HTH Stephen [EMAIL PROTECTED] 04/25/02 10:13PM i belive there is a limitation on the number of records that can be loadedfrom sqlldr at a time based on the bind array and rows default parameter.if i have a data file with more than 5000records(don't know the exactnumber) that needs to be imported using sqlldrwhat is the value i need to set for the "rows" and/or "bind array" for me toload the records to the table at one attempt.thanksravi-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ravindra B INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: sqlldr
Hello Stephen Thanks for the link. The article is very illuminating. As we have some loader tuning to do I thank you for sharing the knowledge. Yechiel Adar Mehish - Original Message - From: Stephen Andert To: Multiple recipients of list ORACLE-L Sent: Friday, April 26, 2002 6:43 PM Subject: Re: sqlldr Ravi, To the best of my knowledge, there is no limitation on the number of records that can be loaded at a time other than the size of your rollback segments. I have loaded 10's of millions of rows in a single sqlldr job by setting some parameters. There are defaults that work, but don't perform very well. To determine appropriate settings, you may want to read an article I wrote for O'Reilly Associates (located athttp://oracle.oreilly.com/news/oraclesqlload_0401.html)which gives the step-by-step directions on setting2 of the key parameters (BINDSIZE and ROWS). It's a pretty easy thing to do, but without knowing you data, the only advice I could give is that DIRECT is the best quick tuning fix if you can. If you are going to be doing any serious data loading with SQL*Loader, Jonathan's book Oracle SQL*Loader: The Definitive Guidewould be a good investment. HTH Stephen [EMAIL PROTECTED] 04/25/02 10:13PM i belive there is a limitation on the number of records that can be loadedfrom sqlldr at a time based on the bind array and rows default parameter.if i have a data file with more than 5000records(don't know the exactnumber) that needs to be imported using sqlldrwhat is the value i need to set for the "rows" and/or "bind array" for me toload the records to the table at one attempt.thanksravi-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ravindra B INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: sqlldr
HI Yechiel : How can i to install two versions of Oracle in the same server , like : Oracle 8i and Oracle 9i, in oracle_home different. Is it possible ? Regards Eriovaldo - Original Message - From: Yechiel Adar To: Multiple recipients of list ORACLE-L Sent: Saturday, April 27, 2002 8:18 PM Subject: Re: sqlldr Hello Stephen Thanks for the link. The article is very illuminating. As we have some loader tuning to do I thank you for sharing the knowledge. Yechiel Adar Mehish - Original Message - From: Stephen Andert To: Multiple recipients of list ORACLE-L Sent: Friday, April 26, 2002 6:43 PM Subject: Re: sqlldr Ravi, To the best of my knowledge, there is no limitation on the number of records that can be loaded at a time other than the size of your rollback segments. I have loaded 10's of millions of rows in a single sqlldr job by setting some parameters. There are defaults that work, but don't perform very well. To determine appropriate settings, you may want to read an article I wrote for O'Reilly Associates (located athttp://oracle.oreilly.com/news/oraclesqlload_0401.html)which gives the step-by-step directions on setting2 of the key parameters (BINDSIZE and ROWS). It's a pretty easy thing to do, but without knowing you data, the only advice I could give is that DIRECT is the best quick tuning fix if you can. If you are going to be doing any serious data loading with SQL*Loader, Jonathan's book Oracle SQL*Loader: The Definitive Guidewould be a good investment. HTH Stephen [EMAIL PROTECTED] 04/25/02 10:13PM i belive there is a limitation on the number of records that can be loadedfrom sqlldr at a time based on the bind array and rows default parameter.if i have a data file with more than 5000records(don't know the exactnumber) that needs to be imported using sqlldrwhat is the value i need to set for the "rows" and/or "bind array" for me toload the records to the table at one attempt.thanksravi-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ravindra B INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: sqlldr
Ravi, To the best of my knowledge, there is no limitation on the number of records that can be loaded at a time other than the size of your rollback segments. I have loaded 10's of millions of rows in a single sqlldr job by setting some parameters. There are defaults that work, but don't perform very well. To determine appropriate settings, you may want to read an article I wrote for O'Reilly Associates (located athttp://oracle.oreilly.com/news/oraclesqlload_0401.html)which gives the step-by-step directions on setting2 of the key parameters (BINDSIZE and ROWS). It's a pretty easy thing to do, but without knowing you data, the only advice I could give is that DIRECT is the best quick tuning fix if you can. If you are going to be doing any serious data loading with SQL*Loader, Jonathan's book Oracle SQL*Loader: The Definitive Guidewould be a good investment. HTH Stephen [EMAIL PROTECTED] 04/25/02 10:13PM i belive there is a limitation on the number of records that can be loadedfrom sqlldr at a time based on the bind array and rows default parameter.if i have a data file with more than 5000records(don't know the exactnumber) that needs to be imported using sqlldrwhat is the value i need to set for the "rows" and/or "bind array" for me toload the records to the table at one attempt.thanksravi-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ravindra B INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
sqlldr
i belive there is a limitation on the number of records that can be loaded from sqlldr at a time based on the bind array and rows default parameter. if i have a data file with more than 5000records(don't know the exact number) that needs to be imported using sqlldr what is the value i need to set for the rows and/or bind array for me to load the records to the table at one attempt. thanks ravi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra B 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).
SQLLDR core dumping
Hi I installed 9iAS 1.0.2.2.2 on Solaris 8. All went well except the Portal component failed. When I tried to re-run the opca (portal configuration assistant) it failed on a segmentation fault in SQLLDR. Oracle support seems to be stumped by this. Has anyone else run into this? Thanks, Ben Poels Queen's University -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Poels 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: SQLLDR core dumping
I had the same problem. We're on Solaris 8, Oracle 8.1.7.2, 9iAS. I worked with Oracle support for a long time on this issue and they could never solve the problem. It has something to do with the environment variables. I ended up creating a user with only the most necessary environment variables (for Oracle 8i only, none of the ones for 9iAS) and sqlldr works OK. If you ever find a solution for this problem I'll appreciate it if you tell me what you did. Thanks Ana E. Choto Systems Programmer American University e-Operations - Information Technology Phone (202) 885-2275 Fax (202) 885-2224 Ben Poels [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ueensu.ca cc: Sent by: Subject: SQLLDR core dumping [EMAIL PROTECTED] om 04/18/2002 10:23 AM Please respond to ORACLE-L Hi I installed 9iAS 1.0.2.2.2 on Solaris 8. All went well except the Portal component failed. When I tried to re-run the opca (portal configuration assistant) it failed on a segmentation fault in SQLLDR. Oracle support seems to be stumped by this. Has anyone else run into this? Thanks, Ben Poels Queen's University -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Poels 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: Ana Choto 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).
nls_date_format problem in sqlldr
Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: nls_date_format problem in sqlldr
Ruth Gramolini wrote: Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth You can specify in the SQL*LOADER control file the date mask with date columns. I know that syntactic schemas are not extremely explicit, but have a harder look at field descriptions in the manual. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: nls_date_format problem in sqlldr
Ruth: Are you using a delimited flat file via SQL Loader? Would the to_date function do the trick? My 0.03 Euro's worth, Ken -Original Message- Sent: Thursday, January 10, 2002 7:25 AM To: Multiple recipients of list ORACLE-L Subject:nls_date_format problem in sqlldr Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Ken Janusz 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: nls_date_format problem in sqlldr
Hello Ruth The answer is not to change the nls_date_format but to tell the loader the format you use for your date. We use the following in our ctl files: xx_xx_TAR_NECHONUT date(8) 'MMDD', maybe you can use: my_date date(8) 'MMMDD' . (I did not check if it works). Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [SMTP:[EMAIL PROTECTED]] Sent: Thu, January 10, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Subject: nls_date_format problem in sqlldr Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: nls_date_format problem in sqlldr
Ruth, If the source file is being loaded with SQL LOADER that you can format the date load in the controlfile. We get our dates in the MMDD format and load in to the database default by defining the source data: SaleDate TERMINATED BY "," "TO_DATE(:SALEDATE,'MMDD')", ...Good Luck. Ron ROR mª¿ªm [EMAIL PROTECTED] 01/10/02 08:25AM Good morning,We are having a problem load a file containing dates in MON DD formatinto a database having and nls_date_format=MMDD. Is there a way to doan alter session command in the control file or the parameter file to thatwe can load this data?We have had the production database down for 4 days with various problemsand this is the last straw.Thanks in advance,Ruth-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ruth Gramolini INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: nls_date_format problem in sqlldr
We got around this issue but if anyone knows whether this is possible I would like to know for future reference. Thanks anyway, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 10, 2002 8:25 AM Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Ruth Gramolini 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: nls_date_format problem in sqlldr
Just set the environment variable before running sqlldr: export NLS_DATE_FORMAT='MON DD ' Ruth Gramolini [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ate.vt.us cc: Sent by: Subject: nls_date_format problem in sqlldr [EMAIL PROTECTED] 01/10/02 05:25 AM Please respond to ORACLE-L Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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).
URGENT!! SQLLDR: How do I load a constant date with Direct Path?
I need to load a constant date into a column (01/01/2002) and I need to use
direct path loading. I tried:
creation_date to_date('01/01/2002','dd/mm/'),
SQL*Loader-417: SQL string (on column CREATION_DATE) not allowed in direct
path.
This works with conventional path load. I have tried using constant and it
gives me an error.
How do I get around this?
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
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).
SQLLDR and Direct Path Load
I want to put a number of ctl scripts together into one so I don't have to run each script by itself. Is it possible to have scripts for direct path load and conventional path load in the same sqlldr script? I don't think so, but would like the opinion of others. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: URGENT!! SQLLDR: How do I load a constant date with Direct Path?
HI Ken,
Two ideas
1.Have you tried using a default value on the column?
2.Load the data and then go back and do a mass update.
[EMAIL PROTECTED] wrote:
I need to load a constant date into a column (01/01/2002) and I need to use
direct path loading. I tried:
creation_date to_date('01/01/2002','dd/mm/'),
SQL*Loader-417: SQL string (on column CREATION_DATE) not allowed in direct
path.
This works with conventional path load. I have tried using constant and it
gives me an error.
How do I get around this?
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: orantdba
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: Generating a SQLLDR Sequence Number
try replacing document_number sequence (max,80,1), with document_number sequence (max,1), sqlloader is taking 80 as your increment... docs show only 2 arguments for sequence function... hth -Original Message- Sent: Tuesday, December 04, 2001 11:01 AM To: Multiple recipients of list ORACLE-L Tom: Ran into another problem. I need to generate a sequence number for one of the DB columns. Here is the CTL code: document_number sequence (max,80,1), It runs but I keep getting the 80 that doubles for each record (80, 160, 320, etc.). The book only shows the syntax but doesn't give an example of how it works. Any ideas? Thanks, Ken -Original Message- Sent: Tuesday, December 04, 2001 7:35 AM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Commit Point? Ken Did you look at the Sql*Loader log file? Maybe all your records were rejected and placed into the .bad file? Does a .bad file exist? Sql*Loader does not really consider rejected records an error - it just moves them there. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, December 03, 2001 4:33 PM To: Multiple recipients of list ORACLE-L I am working on a control script that I have restricted to only loading 5 records as a test. Basically I am taking data from the load file and putting it into a table SQL Loader runs and gives me this. - SQL*Loader: Release 8.1.7.0.0 - Production on Mon Dec 3 15:22:26 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. Commit point reached - logical record count 5 -- When I do a select on this table I get no rows selected. I don't get an error message from SQL Loader. So any ideas as to what the problem is? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Mercadante, Thomas F 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: Ken Janusz 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). FONT SIZE = 1 This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and may be illegal. Unless otherwise stated, opinions expressed in this e-mail are those of the author and are not endorsed by the author's employer./FONT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumar, Dharminder 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
RE: Generating a SQLLDR Sequence Number
Ken, This is what I did once to do a similar exercise. I defined a seq dk_seq as in my case in the database and used the following control file. The next value of sequence should depend on how you define the sequence. LOAD DATA INFILE 'sysx.csv' TRUNCATE INTO TABLE dk_tmp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' trailing nullcols ( CUSIP char, ISIN char, SMFchar, STATUS char, REASON char, ELG_DATE char, CURRENCY char, MATURITY char, seqno dk_seq.nextval ) -Original Message- Sent: Tuesday, December 04, 2001 11:01 AM To: Multiple recipients of list ORACLE-L Tom: Ran into another problem. I need to generate a sequence number for one of the DB columns. Here is the CTL code: document_number sequence (max,80,1), It runs but I keep getting the 80 that doubles for each record (80, 160, 320, etc.). The book only shows the syntax but doesn't give an example of how it works. Any ideas? Thanks, Ken -Original Message- Sent: Tuesday, December 04, 2001 7:35 AM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Commit Point? Ken Did you look at the Sql*Loader log file? Maybe all your records were rejected and placed into the .bad file? Does a .bad file exist? Sql*Loader does not really consider rejected records an error - it just moves them there. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, December 03, 2001 4:33 PM To: Multiple recipients of list ORACLE-L I am working on a control script that I have restricted to only loading 5 records as a test. Basically I am taking data from the load file and putting it into a table SQL Loader runs and gives me this. - SQL*Loader: Release 8.1.7.0.0 - Production on Mon Dec 3 15:22:26 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. Commit point reached - logical record count 5 -- When I do a select on this table I get no rows selected. I don't get an error message from SQL Loader. So any ideas as to what the problem is? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Mercadante, Thomas F 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: Ken Janusz 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). FONT SIZE = 1 This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and
Load listener.log via sqlldr
Friends, Has anyone developed a sqlldr control file to load listener.log? I have a requirement to trap the IP addresses of failed connection attempts; this info is not trapped as an IP address in DBA_AUDIT_TRAIL or AUD$. If you have already accomplished this, let me know so I don't reinvent the wheel. Many thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Scott 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).
Sqlldr-help
Hallo, Can anyone give me an example on a control.file for th esql ldr that does the following: Truncate table X inthe database Loads data into table X Makes an insert from that table into another table in the database. And of course commit statement afterwards Thanks in advance Roland S -- 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).
Re: Sqlldr-help
look in the sqlldr docs for example control files, 1. truncate is a keyword for control file 2. load data 3. create trigger to do the insert into another table. [EMAIL PROTECTED] wrote: Hallo, Can anyone give me an example on a control.file for th esql ldr that does the following: Truncate table X inthe database Loads data into table X Makes an insert from that table into another table in the database. And of course commit statement afterwards Thanks in advance Roland S -- Joe Testa My EMAILS to oracle-l, not giving you the answer but helping you to find it yourself. IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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).
sqlldr prob
hi all i am facing very strange problem with sql loader when import the data(total lines=147876) into table(containing just 2 columns) at the 87754 line i am getting error 2 errors SQL*Loader-500: unable to open file name SQL*Loader-522: lfiopn failed for file (name) i am getting errors for the same file... i have tried other files with even more data but they all worked. the rows parameter in sqlldr is 64 (default). i have tried to load same file with rows =2..it worked well without giving errors.. can u plz point out what exactly is the probs... thanks and regards ! Shirish Khapre, SE Rolta India Ltd.Off Ph No. (+91) (022) 832,826,8300568Ext'n 2730Minds are like parachutes. They only function when they are open
$ORACLE_HOME/bin/sqlload or $ORACLE_HOME/bin/sqlldr
List, I am currently working on migrating an application from Oracle 7.3 to Oracle 8.1.6.2 (This is on Solaris 2.6). One of the shell scripts makes use of sqlload ($ORACLE_HOME/bin/sqlload) which exists on the 7.3 server but not on the 8.1.6.2 server. On the 7.3 server it looks like there are two directory entries, one for sqlload and one for sqlldr... orapps1 $ ls -al | grep sql -rwxr-x--x 2 oracle dba 3612900 Oct 3 1997 sqlldr -rwxr-x--x 2 oracle dba 3612900 Oct 3 1997 sqlload On the 8.1.6.2 server there is just sqlldr. I am wondering if the sqlload link is something that has been dropped from newer versions? or has someone manually applied the link to the 7.3 server? Thanks, Ken _ Clinical and Regulatory Informatics - Groton/New London Coordinator, Business and Technical Services Tel: (860) 732-0026 Fax: (860) 715-8346 Email: mailto:[EMAIL PROTECTED] LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fowler, Kenneth R 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: $ORACLE_HOME/bin/sqlload or $ORACLE_HOME/bin/sqlldr
Ken, Earlier versions had sqlldr and sqlload as copies of the same code. I think sqlload was a link in 8.0.x. Now with 8i it's gone. You can create the link if you do not wish to change the existing code. We did just that since Developers did not want to change their scripts. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Fowler, Kenneth R [SMTP:[EMAIL PROTECTED]] Sent: Monday, July 09, 2001 11:45 AM To: Multiple recipients of list ORACLE-L Subject: $ORACLE_HOME/bin/sqlload or $ORACLE_HOME/bin/sqlldr List, I am currently working on migrating an application from Oracle 7.3 to Oracle 8.1.6.2 (This is on Solaris 2.6). One of the shell scripts makes use of sqlload ($ORACLE_HOME/bin/sqlload) which exists on the 7.3 server but not on the 8.1.6.2 server. On the 7.3 server it looks like there are two directory entries, one for sqlload and one for sqlldr... orapps1 $ ls -al | grep sql -rwxr-x--x 2 oracle dba 3612900 Oct 3 1997 sqlldr -rwxr-x--x 2 oracle dba 3612900 Oct 3 1997 sqlload On the 8.1.6.2 server there is just sqlldr. I am wondering if the sqlload link is something that has been dropped from newer versions? or has someone manually applied the link to the 7.3 server? Thanks, Ken _ Clinical and Regulatory Informatics - Groton/New London Coordinator, Business and Technical Services Tel: (860) 732-0026 Fax: (860) 715-8346 Email: mailto:[EMAIL PROTECTED] LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fowler, Kenneth R 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: Deshpande, Kirti 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).
sqlldr ver 8 can't load v7 database
I have checkout the solution offer by Oracle , as they said use the sqlldr come together with Oracle 7 , but if we upgrade our system to 8i already , where can I still found the v7 sqlldr ?? IT also mention about OCI , what is OCI mean ? a interface program for sqlldr with database ??? Raymond fall asleep in waiting for the Q. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond Lee Meng Hong 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: SQLLDR Question
Can you remove the comma between date and time to make it 1 field? Simon Fox Room 205, CRH 01270 533997 -Original Message- Sent: 23 May 2001 22:41 To: Multiple recipients of list ORACLE-L I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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 email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of Sema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the Sema UK Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: FOX, Simon 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: SQLLDR Question
Thank you to everyone who responded to my request. I guess it's just a limitation of sqlldr that it can't handle this without modifying the data. Unfortunately, I can't have the source change their download, so I have to deal with the data as I get it. I did come up with a way to get around the problem. I created a text field for the time and created an on-insert trigger that appends the time to the date field and puts it back into the date field. That works at the minimal cost of a few extra bytes per record and a small amount of extra processing time. Here is the trigger that I came up with: TRIGGER rit_cyber_hist_i before insert on rit_cyber_hist for each row begin :new.trans_date := to_date(to_char(:new.trans_date,'mm/dd/yy ') || :new.time_char,'mm/dd/yy hh24:mi'); end; -Original Message- Sent: 23 May 2001 22:41 To: Multiple recipients of list ORACLE-L I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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 email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of Sema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the Sema UK Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: FOX, Simon 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). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: SQLLDR Question
Since those are the last two fields in the data file, I think you can use TERMINATED BY WHITESPACE on the field definition. Something like: trans_date date(14) mm/dd/yy,hh:mi terminated by whitespace -Original Message- From: srcdco [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 23, 2001 4:41 PM To: ORACLE-L Cc: srcdco Subject: SQLLDR Question I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: SQLLDR Question
You can combine two data fields into one column with sqlldr, but I think both
fields have to be in the table.
Example:
SQL desc x
Name Type
-
TRANS_DATEDATE
F1CHAR(8)
LOAD DATA
INFILE *
APPEND
INTO TABLE x
FIELDS TERMINATED BY ','
(f1,
trans_date to_date(:f1||' '||:trans_date,'mm/dd/yy hh24:mi')
)
BEGINDATA
05/01/01,14:21
05/24/01,9:57
(After the load, you can ALTER TABLE table DROP COLUMN column to get rid of the
f1 column,)
Jared Still [EMAIL PROTECTED] on 05/23/2001 11:16:00 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Chaim Katz/Completions/Bombardier)
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
Re: SQLLDR Question
In 8.1.6, you don't have to have the one column in the database. Use
'FILLER' as the data type.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
[EMAIL PROTECTED]
ardier.com To: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED] cc:
Fax to:
Subject: Re: SQLLDR
Question
05/24/2001 11:31 AM
Please respond to ORACLE-L
You can combine two data fields into one column with sqlldr, but I think
both
fields have to be in the table.
Example:
SQL desc x
Name Type
-
TRANS_DATEDATE
F1CHAR(8)
LOAD DATA
INFILE *
APPEND
INTO TABLE x
FIELDS TERMINATED BY ','
(f1,
trans_date to_date(:f1||' '||:trans_date,'mm/dd/yy hh24:mi')
)
BEGINDATA
05/01/01,14:21
05/24/01,9:57
(After the load, you can ALTER TABLE table DROP COLUMN column to get rid of
the
f1 column,)
Jared Still [EMAIL PROTECTED] on 05/23/2001 11:16:00 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Chaim Katz/Completions/Bombardier)
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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
SQLLDR Question
I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: SQLLDR Question
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
sqlldr to_number
Hallo you DBA's: How can I write a script in ctl-file if I want to import a textfile. I want to use the command decode because I want to replace the character '?' with a 0 (zero), which is a number field inthe database. How can I do this. I want to do it while the sqlldrscript is running. Sincerely Roland Sköldblom -- 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).
Re: sqlldr to_number
Example of a SQL*Load control file to load a file, skipping the first line, replacing the data in the table, with comma-separated, double-quote enclosed data, using the decode function on one of the columns. OPTIONS (SKIP=1) LOAD DATA INFILE data.dat INTO TABLE mytable REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ( REC_TYPEchar, VCIDchar, SHIP_TO_ID char decode(:SHIP_TO_ID, '?', '0') ) Maybe this is what you're looking for? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Roland.Skoldb [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Fax to: com Subject: sqlldr to_number 05/22/2001 03:00 AM Please respond to ORACLE-L Hallo you DBA's: How can I write a script in ctl-file if I want to import a textfile. I want to use the command decode because I want to replace the character '?' with a 0 (zero), which is a number field inthe database. How can I do this. I want to do it while the sqlldrscript is running. Sincerely Roland Sköldblom -- 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: 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).
sqlldr help
I have a data file that looks like this; 100Memo line 1 information. 100more information for record 100. 100extra information again. 200Memo for 200. 200more info for 200 300info for 300 300next memo 300and more 300yet more again. I want to load to a table (col1, infodesc) to look like this; col1infodesc -- 100Memo line 1 information. more information for record 100. extra information again 200extra information again. Memo for 200. more info for 200 300info for 300 next memo and more yet more again. Can this be done with sqlldr or do I need to load the data as is and use PL/SQL (and cursor loops) to concatentate the data? thanks for any help! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis 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: sqlldr help
Sounds like a job for Perl.
Here's an example of cleaning up this data before you
send it to the database.
Really, this is much easier than doing it with PL/SQL or Java.
Jared
--
#!/usr/bin/perl
our %hashData;
while(DATA) {
my($key, @data) = split(/\s/);
if (defined($hashData{$key} ) ) {
$hashData{$key} .= ' ' . join(' ',@data);
} else {
$hashData{$key} = join(' ',@data);
}
}
foreach my $key ( sort keys %hashData ) {
print Key: $key - |$hashData{$key}|\n;
}
__DATA__
100 Memo line 1 information.
100 more information for record 100.
100 extra information again.
200 Memo for 200.
200 more info for 200
300 info for 300
300 next memo
300 and more
300 yet more again.
--
On Thursday 17 May 2001 10:45, Glenn Travis wrote:
I have a data file that looks like this;
100Memo line 1 information.
100more information for record 100.
100extra information again.
200Memo for 200.
200more info for 200
300info for 300
300next memo
300and more
300yet more again.
I want to load to a table (col1, infodesc) to look like this;
col1infodesc
--
100Memo line 1 information. more information for record 100. extra
information again
200extra information again. Memo for 200. more info for 200
300info for 300 next memo and more yet more again.
Can this be done with sqlldr or do I need to load the data as is and use
PL/SQL (and cursor loops) to concatentate the data?
thanks for any help!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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: SQLLDR - whitespace (tab space) problem
Try something like the lines below, as 9 is the ASCII code for a TAB : load data infile * INTO TABLE experiment_with_tab_delimiter FIELDS TERMINATED BY X'9' (textcol1, textcol2) BEGINDATA Saj -Original Message- From: Helen Zhung [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 27, 2001 7:40 PM To: Multiple recipients of list ORACLE-L Subject:SQLLDR - whitespace (tab space) problem Hello: I'm new to SQL Loader. Is there a way to code in control file that can separate 'a tab' and 'a blank space' ? I use FIELDS TERMINATED BY WHITESPACE, but it will TERMINATE a field when there is a space or a tab. However, the data fields were separated only by the tab, not 'a space'. Is it possible to identify the two? Thanks _ Do You Yahoo!? Yahoo! Auctions http://auctions.yahoo.com/ - buy the things you want at great prices ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Saj Raza 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: SQLLDR - whitespace (tab space) problem
Great! That works too. Now I can do it (modify the data) from either Shell script or control file. Thanks for all the input. Saj Raza [EMAIL PROTECTED] wrote: Try something like the lines below, as 9 is the ASCII code for a TAB :load datainfile *INTO TABLE experiment_with_tab_delimiterFIELDS TERMINATED BY X'9'(textcol1, textcol2)BEGINDATASaj-Original Message-From: Helen Zhung [SMTP:[EMAIL PROTECTED]]Sent: Friday, April 27, 2001 7:40 PMTo: Multiple recipients of list ORACLE-LSubject: SQLLDR - whitespace (tab space) problemHello: I'm new to SQL Loader. Is there a way to code in control file thatcan separate 'a tab' and 'a blank space' ? I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE afield when there is a space or a tab. However, the data fields wereseparated only by the "tab", not 'a space'. Is it possible to identify thetwo? Thanks _ Do You Yahoo!?Yahoo! Auctions ! ! - buy the things youwant at great prices**This email and any files transmitted with it are confidential andintended solely for the use of the individual or entity to whom theyare addressed. If you have received this email in error please notifythe system manager.This footnote also confirms that this email message has been swept byMIMEsweeper for the presence of computer viruses.www.mimesweeper.com**-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Saj RazaINET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mai! ! ling list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices
SQLLDR - whitespace (tab space) problem
Hello: I'm new to SQL Loader. Is there a way to code incontrol filethatcan separate 'a tab' and 'a blank space' ? I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE a field when there isa space or a tab. However, the data fields were separated only by the "tab", not 'a space'. Is it possible to identify the two? Thanks Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices
Re: SQLLDR - whitespace (tab space) problem
Helen, You can use the optionally parameter to specify multiple enclosed by/terminated by parameters. NULLIF is also available to assist in loading blanks. Regards, David A. Barbour Oracle DBA, OCP Helen Zhung wrote: Hello: I'm new to SQL Loader. Is there a way to code in control file that can separate 'a tab' and 'a blank space' ? I use FIELDS TERMINATED BY WHITESPACE, but it will TERMINATE a field when there is a space or a tab. However, the data fields were separated only by the tab, not 'a space'. Is it possible to identify the two? Thanks -- Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour 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: SQLLDR - whitespace (tab space) problem
You can no doubt do this with Sql*loader. Personally, I like to clean up the data first instead. I find it easier to work with that way. These task are easy on unix, and not too bad on windoze if you get some decent text tools. The following line for instance will change all tab characters to a space, then compress all consecutive spaces to a single space. tr \011 dirty.txt | tr -s | clean.txt You can even send the output to a pipe, and read the pipe directly from sqlloader mkfifo loader_pipe tr \011 test.txt | tr -sloader_pipe sqlloader ... data=loader_pipe ... Jared On Friday 27 April 2001 11:40, Helen Zhung wrote: Hello: I'm new to SQL Loader. Is there a way to code in control file that can separate 'a tab' and 'a blank space' ? I use FIELDS TERMINATED BY WHITESPACE, but it will TERMINATE a field when there is a space or a tab. However, the data fields were separated only by the tab, not 'a space'. Is it possible to identify the two? Thanks - Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices Content-Type: text/html; charset=us-ascii; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Awfully annoying SQLLDR problem
Tom, Just cogitating and ruminating... You mentioned that you're executing the load as part of a larger procedure. Couldn't you execute a shell script that would ftp the file to the target server and run sqlldr via rsh -c? Another thought would be to have the target file on an NFS-mounted drive that could be accessed by the target and have sqlldr kicked off on the target via a trigger on a simple insert by the source into a DATA_LOAD_REQUIRED table on the target? Regards, David A. Barbour Oracle DBA [EMAIL PROTECTED] wrote: Thank you ever-so-much to Tim, Kirk, Peter, and especially Kirti for your intelligent responses. I'm going to open a TAR on Metalink. I'll probably get the quick and easy response We don't support it anymore. I'm no fan of Oracle but would not blame them for giving me that response. We will have upgraded our source database to 8.1.6.2 by mid July. Perhaps my project will have to wait till then. thanks again, ..tom -Original Message- From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 3:08 PM To: Multiple recipients of list ORACLE-L Subject: RE: Awfully annoying SQLLDR problem The missing verb is think, as in I don't think you can avoid [EMAIL PROTECTED] 04/23/01 02:46PM I don't you *can* avoid the larger issue. Oracle 8 has undergone some radical changes underneath the covers. I would expect SQLLDR to have changed just as radically... [EMAIL PROTECTED] 04/23/01 02:06PM No you are reading correctly. I have the data on a server with a 7.3.2.3 database and am using that databases' binary sqlldr to load into the 8.1.5 database. The problem is a timing issue. I need to execute the load from the 7.3.2.3 server as part of a larger script. I cannot ensure that the load will be executed i a timely manner if it is run on the distant server. Whether or not you agree with the timing issue I describe, I'd really appreciate an answer to the Oracle problem. I don't want to get into the larger issue of why I have to run it from one server vs. another. From an Oracle POV it should work either way. thanks, ..tom -Original Message- From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Subject:RE: Awfully annoying SQLLDR problem Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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
RE: Awfully annoying SQLLDR problem
It is not my purpose here to discuss issues not related to Oracle. The 7.3.2.3 database is inside a firewall - not so the 8.1.5 database. Our corporate standard for server file exchange is MQSeries though I could probably get an exemption to use rsh. In my TAR I wrote :One Oracle workaround I could try is running the sqlldr against the 7.3.2.3 database into a view of a table in the 8.1.5 database through a DB link. I will try that if there is no quick easy solution to my problem. Oracle's reply is this. (their syntax - not mine) I think you may try the workaround you mentioned above. At this time, there is no quick around solution. It will take longer process to test this case and if it's not work we have to file a new bug. This process will take awhile. Let me know if your workaround work. So I guess I'll try my workaround. thanks all, ..tom -Original Message- From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, April 24, 2001 2:57 PM To: Multiple recipients of list ORACLE-L Subject: RE: Awfully annoying SQLLDR problem This would be clean, but recall he did not want to get into the larger issue of why I have to run it from one server vs. another. Pay no attention to the man behind the curtain! G || -Original Message- || From: David A. Barbour [mailto:[EMAIL PROTECTED]] || Sent: Tuesday, April 24, 2001 12:28 PM || To: Multiple recipients of list ORACLE-L || Subject: Re: Awfully annoying SQLLDR problem || || || Tom, || || Just cogitating and ruminating... || || You mentioned that you're executing the load as part of a larger || procedure. Couldn't you execute a shell script that would || ftp the file || to the target server and run sqlldr via rsh -c? Another || thought would || be to have the target file on an NFS-mounted drive that could be || accessed by the target and have sqlldr kicked off on the target via a || trigger on a simple insert by the source into a || DATA_LOAD_REQUIRED table || on the target? || || Regards, || || David A. Barbour || Oracle DBA || || [EMAIL PROTECTED] wrote: || || Thank you ever-so-much to Tim, Kirk, Peter, and especially || Kirti for your || intelligent responses. I'm going to open a TAR on || Metalink. I'll probably get || the quick and easy response We don't support it anymore. || I'm no fan of Oracle || but would not blame them for giving me that response. || || We will have upgraded our source database to 8.1.6.2 by || mid July. Perhaps my || project will have to wait till then. || || thanks again, || || ..tom || || -Original Message- || From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] || Sent: Monday, April 23, 2001 3:08 PM || To: Multiple recipients of list ORACLE-L || Subject: RE: Awfully annoying SQLLDR problem || || The missing verb is think, as in I don't think you || can avoid || ||[EMAIL PROTECTED] 04/23/01 02:46PM || I don't you *can* avoid the larger issue. Oracle 8 has || undergone some radical || changes underneath the covers. I would expect SQLLDR to || have changed just as || radically... || ||[EMAIL PROTECTED] 04/23/01 02:06PM || No you are reading correctly. I have the data on a || server with a 7.3.2.3 || database and am using that databases' binary sqlldr to || load into the 8.1.5 || database. The problem is a timing issue. I need to || execute the load from the || 7.3.2.3 server as part of a larger script. I cannot || ensure that the load will || be executed i a timely manner if it is run on the distant server. || || Whether or not you agree with the timing issue I || describe, I'd really || appreciate || an answer to the Oracle problem. I don't want to get || into the larger issue of || why I have to run it from one server vs. another. From || an Oracle POV it || should || work either way. || || thanks, || || ..tom || || || ||-Original Message- ||From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] ||Sent: Monday, April 23, 2001 12:27 PM ||To: Multiple recipients of list ORACLE-L ||Subject:RE: Awfully annoying SQLLDR problem || ||Ok,maybe i am lazy or a poor reader, but are you ||using 732 sqlldr binaries to load an 815 db? || ||Why not the 815 sqlldr binaries? || ||I mean, it sounds like you have to ship data over the ||wire no matter what you do, so...why not use the latest ||version of the sqlldr bits? || || || ||== -Original Message- ||== From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] ||== Sent: Monday, April 23, 2001 11:10 AM ||== To: Multiple recipients of list ORACLE-L ||== Subject: Awfully annoying SQLLDR problem ||== ||== ||== I am executing a SQLLDR shell file from a server
Awfully annoying SQLLDR problem
I am executing a SQLLDR shell file from a server :: sourceserv; RDBMS 7.3.2.3 AIX 4.3.2 into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 using a SQL*Net connect string destserv_destSID. This connect string is defined in the local tnsnames.ora and works. I confirmed this by doing sqlplus user/pass@destserv_destSID and verifying that I am connected to the correct instance. The target table exists. I am connecting as the owner of the target table. That owner has DBA. but when I run sqlldr userid=tabowner/pass@destserv_destSID CONTROL=localcontrolfile.ctl (controlfile is below) I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) ORA-00942: table or view does not exist Help! Does anyone have any ideas? tia, ..tom here's the controlfile LOAD DATA INFILE data1.dat BADFILE load.bad DISCARDFILE load.dsc INSERT INTO TABLE OWNER.TEST_LOAD FIELDS TERMINATED BY ',' TRAILING NULLCOLS (COL1,COL2,COL3) -- 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).
RE: Awfully annoying SQLLDR problem
Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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: Mohan, Ross 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: Awfully annoying SQLLDR problem
No you are reading correctly. I have the data on a server with a 7.3.2.3 database and am using that databases' binary sqlldr to load into the 8.1.5 database. The problem is a timing issue. I need to execute the load from the 7.3.2.3 server as part of a larger script. I cannot ensure that the load will be executed i a timely manner if it is run on the distant server. Whether or not you agree with the timing issue I describe, I'd really appreciate an answer to the Oracle problem. I don't want to get into the larger issue of why I have to run it from one server vs. another. From an Oracle POV it should work either way. thanks, ..tom -Original Message- From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Subject: RE: Awfully annoying SQLLDR problem Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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: Mohan, Ross 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).
RE: Awfully annoying SQLLDR problem
I don't you *can* avoid the larger issue. Oracle 8 has undergone some radical changes underneath the covers. I would expect SQLLDR to have changed just as radically... [EMAIL PROTECTED] 04/23/01 02:06PM No you are reading correctly. I have the data on a server with a 7.3.2.3 database and am using that databases' binary sqlldr to load into the 8.1.5 database. The problem is a timing issue. I need to execute the load from the 7.3.2.3 server as part of a larger script. I cannot ensure that the load will be executed i a timely manner if it is run on the distant server. Whether or not you agree with the timing issue I describe, I'd really appreciate an answer to the Oracle problem. I don't want to get into the larger issue of why I have to run it from one server vs. another. From an Oracle POV it should work either way. thanks, ..tom -Original Message- From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Subject: RE: Awfully annoying SQLLDR problem Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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: Mohan, Ross 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: Tim Sawmiller INET: [EMAIL PROTECTED
RE: Awfully annoying SQLLDR problem
Have you run catexp7 on the 8.1.5 instance? Pete Barnett Oracle Database Administrator Regence BlueCross BlueShield [EMAIL PROTECTED] On Mon, 23 Apr 2001, Mohan, Ross wrote: Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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: Mohan, Ross 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: Peter Barnett 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: Awfully annoying SQLLDR problem
The missing verb is think, as in I don't think you can avoid [EMAIL PROTECTED] 04/23/01 02:46PM I don't you *can* avoid the larger issue. Oracle 8 has undergone some radical changes underneath the covers. I would expect SQLLDR to have changed just as radically... [EMAIL PROTECTED] 04/23/01 02:06PM No you are reading correctly. I have the data on a server with a 7.3.2.3 database and am using that databases' binary sqlldr to load into the 8.1.5 database. The problem is a timing issue. I need to execute the load from the 7.3.2.3 server as part of a larger script. I cannot ensure that the load will be executed i a timely manner if it is run on the distant server. Whether or not you agree with the timing issue I describe, I'd really appreciate an answer to the Oracle problem. I don't want to get into the larger issue of why I have to run it from one server vs. another. From an Oracle POV it should work either way. thanks, ..tom -Original Message- From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Subject: RE: Awfully annoying SQLLDR problem Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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: Mohan, Ross 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
RE: Awfully annoying SQLLDR problem
Hi Tom, Have you tried setting TWO_TASK variable instead of using the connect string? I vaguely remember one of my co-workers mentioning it to me when they ran into performance problems when loading from the appl server to the database server. The performance issue was related to bindsize and rows parameter and not to the TWO_TASK variable. Is it possible to try TWO_TASK ? HTH.. - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 1:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: Awfully annoying SQLLDR problem No you are reading correctly. I have the data on a server with a 7.3.2.3 database and am using that databases' binary sqlldr to load into the 8.1.5 database. The problem is a timing issue. I need to execute the load from the 7.3.2.3 server as part of a larger script. I cannot ensure that the load will be executed i a timely manner if it is run on the distant server. Whether or not you agree with the timing issue I describe, I'd really appreciate an answer to the Oracle problem. I don't want to get into the larger issue of why I have to run it from one server vs. another. From an Oracle POV it should work either way. thanks, ..tom -Original Message- From: Mohan, Ross [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Subject:RE: Awfully annoying SQLLDR problem Ok,maybe i am lazy or a poor reader, but are you using 732 sqlldr binaries to load an 815 db? Why not the 815 sqlldr binaries? I mean, it sounds like you have to ship data over the wire no matter what you do, so...why not use the latest version of the sqlldr bits? == -Original Message- == From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] == Sent: Monday, April 23, 2001 11:10 AM == To: Multiple recipients of list ORACLE-L == Subject: Awfully annoying SQLLDR problem == == == I am executing a SQLLDR shell file from a server :: == sourceserv; RDBMS 7.3.2.3 == AIX 4.3.2 == into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3 == using a SQL*Net connect string destserv_destSID. == == This connect string is defined in the local tnsnames.ora == and works. I confirmed == this by doing sqlplus user/pass@destserv_destSID and == verifying that I am == connected to the correct instance. == == The target table exists. I am connecting as the owner == of the target table. That == owner has DBA. == == but when I run == == sqlldr userid=tabowner/pass@destserv_destSID == CONTROL=localcontrolfile.ctl == (controlfile is below) == == I get SQL*Loader-925: Error while parsing a cursor (via ocisq3) == ORA-00942: table or view does not exist == == Help! Does anyone have any ideas? == == tia, == == ..tom == == here's the controlfile == == LOAD DATA == INFILE data1.dat BADFILE load.bad == DISCARDFILE load.dsc == INSERT == INTO TABLE OWNER.TEST_LOAD == FIELDS TERMINATED BY ',' == TRAILING NULLCOLS == (COL1,COL2,COL3) == == == -- == 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: Mohan, Ross 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
