SQL*Loader from Concurrent Manager
Date: Wed, 23 Jun 1999 16:22:08 -0700
From: "Donna M. Armstrong" donnabsc@gte.net
Subject: GEN: SQL*Loader from Concurrent Manager
Hi All,
I was trying to set up a SQL*Loader process to be run from the
Concurrent Manager and ran into a (hopefully) simple problem. I would
like to have the data file placed in a custom directory, for example
$CUST_TOP/data_in, but when I coded the INFILE statement as:
INFILE $CUST_TOP/data_in/datafile.dat
the process completed in error with a SQL*Loader-350 Syntax error
on the $ character.
I am wondering if there is a way to accomplish this, and if not,
what directory does it expect the file to be found in? I know the .ctl
file goes in the bin sub-directory, but where does the data file go?
Thanks in advance,
Donna
Date: Wed, 23 Jun 1999 20:57:16 -0700 (PDT)
From: Kevin Gillins kgillins@yahoo.com
Subject: Re: GEN: SQL*Loader from Concurrent Manager
You can format the infile on the command line instead of in the control
file. Then use UNIX shell (assuming you are using UNIX) to reference
the $CUST_TOP variable.
SQl Loader - Data exceeds length
Date: Thu, 17 Jun 1999 11:53:21 -0400
From: Jalpa jalpa@triad-com.com
Subject: SQL Loader
Hi,
I am trying to load data into a table. The table has a varchar2(4000)
column, but the data for that column in the data file is more then 4000
characters. I only care to load the first 4000 characters.
So in my control file I have
LN char(40),
OMENTIONED char(4000) "SUBSTR(:OMENTIONED, 1, 4000)" ,
BATCH CHAR(10) etc.
Oracle still gives me an error
Record 1: Rejected - Error on table L_DOC_TB, column OMENTIONED.
Field in data file exceeds maximum length.
I have also tried "SUBSTR(:OMENTIONED, 1, 3999)"
The fields are comma delimited and quote enclosed.
TIA
Jalpa
Buffer size during Import
Date: Fri, 25 Jun 1999 09:11:42 -0500
From: Sistla.Sambasivarao@indsys.ge.com
To: oraapps-dba@cpa.qc.ca
Subject: Import Utility
Hi all,
When I'm trying to import a file , I'm getting this error:
"IMP-00020: long column too large for column buffer size(30)"
I've increased the buffersize parameter which it asks to 50000 and still
getting this error(maybe it has nothing to do with the error).
Any help would be really appreciated.
Thanks,
Shiva Sistla
Date: Fri, 25 Jun 1999 10:08:05 -0500
From: Diana Boules dboules@csac.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Import Utility
I have always used a minimum buffer size of 65000 and recently started
using a buffer size of 1024000. Either of these will resolve your problem.
A long column will not fit in the standard buffer size.
Diana
Date: Fri, 25 Jun 1999 10:18:46 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: RE: Import Utility
In export we can user directly to bypass buffer. while during import I
didn't see any thing like this or am I missing something?
How can I use direct import without using buffer?
thanks.
Bharat Patel
Running SQL*Loader from Windows NT
Date: Wed, 28 Jul 1999 14:17:17 -0700 (PDT)
From: Mohan muthan mmuthan@yahoo.com
Subject: Running SQL*Loader from Windows NT
Hi All,
How do I run SQL*Loader from Windows NT. Is there any additional
Software that has to be run inorder for the SQL*Loader to be run from
it. Or is that I need to use a different command other than sqlldr. I
tried it from C:\orawin\bin and all other Oracle related directories.
Any immediate help would be appreciated..
Thanks
Mohan
Date: Wed, 28 Jul 1999 15:22:19 -0700
From: "Bullard, James" James.Bullard@fluke.com
Subject: RE: Using SQL*Loader in Windows Nt
Have you tried sqlldr73 or sqlldr80 from a DOS prompt? I would also suggest
that you check the for a directory called ORANT\bin. I am not sure of the
version of your server nor the version of NT.
Hope this helps.
Thanks,
James
James Bullard, Oracle DBA
Fluke Corporation
6920 Seaway Blvd M/S 221B
Everett, WA. 98203
Phone 425-356-5328, Fax 425-446-6033
Date: Thu, 29 Jul 1999 21:54:55 +0500
From: Naqi Mirza mirzas@isb.apollo.net.pk
Subject: Re: Using SQL*Loader in Windows Nt
dear mohan,
Check if your autoexec.bat has the entry c:\orawin\bin
rgds
Naqi Mirza
x-------------x
Date: Mon, 2 Aug 1999 14:49:41 -0700 (PDT)SQL Loader - passing parameters to
Date: Mon, 30 Aug 1999 15:26:06 EDT
From: "Oracle Apps" oapps@hotmail.com
To: OraApps-L@cpa.qc.ca
Subject: Can we pass parameters to SQL-Loader Script
All,
Can we pass parameters to SQL-Loader Script.
Date: Mon, 30 Aug 1999 15:39:44 -0400
From: "Satya, Kumar (GEAE, Foreign National)" Kumar.Satya@ae.ge.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Can we pass parameters to SQL-Loader Script
Yes.
Satya
Satya Kumar.G
Tata Consultancy Services
work : (513) 679-5074
dial comm : 8*623-5074
Date: Tue, 31 Aug 1999 10:10:14 EDT
From: "Ragu N" oapps@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: RE: Can we pass parameters to SQL-Loader Script
Sathya,
Can i know how we can pass parameters to SQL-Loader script.
Can u reply back with an example.
Thanks
Ragu
SQL Loader - updating multiple tables from BAI Format
Date: Wed, 01 Sep 1999 12:19:06 -0400
From: "Michael Porter" MPorter@pgahq.com
To: ORAapps-dba@cpa.qc.ca, oraapps-l@cpa.qc.ca
Subject: Cash Management Load to Interface Tables (I.E. BAI File)
I need some help with a SQL*loader CTL file. I need to update multiple
tables ce_statement_headers and lines tables from a file in BAI format. It
has multiple header, footer, summary and transaction physical records in
the same file. I am having a hard time figuring out how to make the
logical records and load them into the proper table. I have the loader
Oracle book and it has some examples, but not of the detail/multiple type
to get me started.... I assume I need to use update multiple tables with
multiple record types and the fields are comma delimited. All these at
once is breaking my head. Any examples would be wonderful!!!
Any help is appreciated,
Mike
Date: Wed, 1 Sep 1999 09:28:26 -0700
From: "Orth, Tom (torth)" torth@sequent.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Cash Management Load to Interface Tables (I.E. BAI File)
Assuming each record has a record type column in, say, the first
character, and each record type is going into only one table,
you simply need to use the WHEN clause. For each record
in the file, the when clause will handle how that record should be
handled. The other option is to use UTL_FILE package in pl/sql
and read the file in directly from the operating system from within
a stored procedure. This requires you be on database version 7.3
or later I believe. Plus your DBA must enable this feature and
set up a directory on the server whey you can put your files.
A utl_file directory for you.
Date: Sat, 4 Sep 1999 10:42:50 +0400
From: rajiv@lucky.co.ae
To: oraapps-l@cpa.qc.ca
Subject: Re: Cash Management Load to Interface Tables (I.E. BAI File)
Mike
My understanding of your problem is that you are trying to upload a file
which is not strucuctured [standard delimted file] and it is mixed up with
header information (typical report format) if this is the case then
normally one would have to convert it into a standard delimited file. The
only tool which I have seen which helps in uploading a non-structured file
is ACL (Audit Command Language). If you need more info on this let me know.
Rajeev Lalwani
KPMG
From: Mohammad Fouwaaz Mfouwaaz@savola.com
Reply-To: oraapps-l@cpa.qc.ca
To: Multiple recipients of list oraapps-l@cpa.qc.ca
Subject: RE: Cash Management Load to Interface Tables (I.E. BAI File)
Date: Fri, 3 Sep 1999 05:14:22 -0400 (EDT)
What is BAI format?
Regards
Date: Sun, 05 Sep 1999 07:17:36 PDT
From: "Satish Hakim" satish_hakim@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: RE: Cash Management Load to Interface Tables (I.E. BAI File)
BAI stands for (Bank Administration Institute). BAI is the standard
transmission format used by most banks.
Thanks,
Satish Hakim
Uploading files thru Oracle Application Server 4
Date: Tue, 7 Sep 1999 00:54:30 +0800
From: "Jason Ekamper" Jasek@bigpond.com
To: "Oracle Developer 2000 Mailing List (E-mail)" ODTUG-DEV2K-L@cpa.qc.ca,
Subject: Uploading files thru Oracle Application Server 4
Hiya. We are running OAS 4.07.1 and Oracle 8.05 on NT. I have to upload
files selected by the user thru a HTML page (or plsql generated page) to the
oracle database. I have heard the best way to do this is thru BLOBS. Anyone
out there got this working, and willing to help me get it up and running.
Code examples would be great. Any help is appreciated.
Cheers
Jason Ekamper
Jasek@bigpond.com
FTP file from Unix to PC
From: Erik Ritters [SMTP:eritters@prg.com]
Sent: 30 September, 1999 10:33 AM
To: Multiple recipients of list
Subject: FTPing file from Unix to PC
Has anyone ever wrote a unix shell script or something similar that FTPs a
file from unix to a PC on an NT network?
If so, any details would be greatly appreciated!
Thanks
Erik Ritters
Date: Thu, 30 Sep 1999 12:57:22 -0400
From: "Phillips, Brant" phillib@mps.bellhowell.com
To: "'OAUG Distributions'" oraapps-l@cpa.qc.ca
Subject: FW: FTPing file from Unix to PC
Erik,
Use Network File Services(NFS) for your UNIX and NT servers. We use HP NFS
and Maestro NFS for NT. It works great. Simply mount the drives from your
UNIX box. All you need to do is copy or move your files to the physical
mounted directory. The file will show up on NT to the directory that you
mapped it to. The only problem that we are running into is security between
UNIX and NT. We have yet to figure out a way to write the files to these
directories as applmgr and specify specific users and/or groups on the NT
that have access to them. Right now we have to open it up to the world on
NT in order for multiple users to access these files.
Hope this helps. If anyone has any information on NFS security between UNIX
and NT please let me know.
Thanks,
Brant
Brant Phillips
Systems Consultant
Bell & Howell Mail & Messaging Technologies
Numbers formatted with commas - load thru SQL*Loader
Date: Fri, 15 Oct 1999 16:12:34 -0400
From: Melanie Jubinville Melanie.Jubinville@future.ca
Subject: SQL*LOADER question
Hi,
I am trying to load in a custom table a field formatted like this
1,235.35 in a NUMBER column name.
If I specified this field as decimal external it does not work. How
can I upload numbers formatted with comas in a NUMBER column ? (It only
works if I upload in a VARCHAR2 column)
Thanks
Melanie
Date: Sat, 16 Oct 1999 03:31:39 -0700 (PDT)
From: FMT firas70@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: Re: SQL*LOADER question
Hi,
You can use the following example (in the control file)
as a road show :
salary POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')"
Hope this helps.
Firas Al Tamimi
Date: Sat, 16 Oct 1999 05:08:39 -0700 (PDT)
From: Harikrishna Babu geethari99@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: Re: SQL*LOADER question
Hi,
Following sample script might solve your problem.
bonus.ctl
LOAD DATA
INFILE 'c:\temp\bonus.dat'
into table BONUS append
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( ENAME CHAR,
JOB CHAR,
SAL CHAR "to_number(:sal,'999,999,999.99')",
COMM DECIMAL EXTERNAL)
bonus.dat
HARI,ENGINEER,"1,235.35",100
Cheers,