Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: September 18, 2017 - 3:56 pm UTC

Last updated: July 26, 2019 - 5:31 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

I have an MS ACCESS application that will be developed with an Oracle table (back-end)
Weekly, a user will get Spreadsheets that are imported (via VBA scripting) into Access.
In the temporary Access tables, other data is added.

The plan is then to Export the temp tables from MSAccess to delimited Text Files and then initiate SQL*Loader to load the data from the textfiles into Oracle Tables.

The table size is not that large by SQL*Loader standards (all tables less than 400,000 records); but the speed gain is hours to seconds. So, the loader is essential.

I do not have SQL*Loader yet, and thus my planning is all theoretical until I get the tool.
I am working in TOAD v972 to build the control file.

The User will download data from sources into 6 spreadsheets, Import, Update temp tables, and Export via Access to Text files; when in place, the user Click a "Load Button" from Access which will run will find SQL * Loader in a Windows Folder that will contain the SQL*Loader executable, Control File, and Text files for loading.

In Toad, there is a option for SQL*Loader and the interface helps you create the Control File.
But the issue here is running SQL*Loader via a sort of "Batch file" that finds the loader, control file, and text files in a Windows Folder.
I have seen several examples of Code to run the Loader via a Batch from Access Code.

In MSAccess, the user can connect manually or via code to oracle tables via an ODBC connection.

So my question is:

How does SQL*Loader know to connect this user to the Oracle Tables.
What kind of Connection is it. Is it like an ODBC connection string with Database, UserID, Password, etc. ?'



-------------------

Thanks so much

Joseph Giallombardo

and Connor said...

OK, I'm assuming that with TOAD you are already connecting to the Oracle database.

So when you did that, you either used a tnsnames, eg

user/password@some_string

or a fully qualified connection, eg

user
password
hostname
port number
service name

In either case, SQL Loader will connect to the database in the same way on the command line. So it would be something like:

sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl

or

sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name control=my_control.ctl

That should get you going. WHen you get SQL Loader, if you're having problems still, get back to us via a Review.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joseph Giallombardo, September 19, 2017 - 3:31 pm UTC

Thanks...That seems clear. I will follow up once they give me the executable.

Joseph Giallombardo, October 12, 2017 - 10:31 pm UTC

I am still getting
704 connection error
12514 Listener does not currently know of service requested in connect descriptor.

I used:

sqlldr.exe userid=PAllegretti/pal234@//takt01.scan.unix.tpna.com:1521/tedwd.takedapharm.com control=ThirdPartyData.ctl

Does the DBA have to set up a "Listener"..I saw some information on starting a listener in the Oracle Error issues provided by one of the Techs.

Thanks for Any help
Connor McDonald
October 13, 2017 - 3:32 am UTC

Ask the DBA to do a "lsnrctl status" on the machine

takt01.scan.unix.tpna.com

That will tell you what services are running and what port they are listening on

Follow Up - One issue solved, on to the next

Joseph Giallombardo, November 10, 2017 - 5:48 pm UTC

I had submitted a new question concerning the control file. I discovered my dates were mm/dd/yyyy 0:00:00 in the input file. Bottomline: I have cleanup up the input and control files and have SQL*Loader now working in TOAD.

You may be responding to that question about the date...but after further investigation, that is issue is resolved.

The next step is to get SQL*Loader to RUN from Shell(path\sqlldr parmstring) from MS - ACCESS.

Here is the execution parmstring

sqlldr.exe userid=UsersName/UserPW@//takt01-scan.unix.tpna.com:1521/tedwd.takedapharm.com control=ThirdPartyData.ctl log=ThirdPartyData.log

Here is TNSNames.ora

TEDWD.TAKEDAPHARM.COM=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=takt01-scan.unix.tpna.com)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=tedwd.takedapharm.com)

Execute from TOAD
value used for ROWS parameter changed from 64 to 43
Record 126566: Discarded - all columns null.

Table "DOBS"."PR_GROUP":
126565 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
1 Row not loaded because all fields were null.


Space allocated for bind array: 255162 bytes(43 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 126566
Total logical records rejected: 0
Total logical records discarded: 1

Run began on Fri Nov 10 11:06:46 2017
Run ended on Fri Nov 10 11:07:07 2017

Elapsed time was: 00:00:20.83
CPU time was: 00:00:04.82

Sqlldr executed from a Batch file, it errors out after 7550 records. I use the same directory with control and input files for both methods.

@ echo off
cd Documents\RADS42DevelopmentArea\DataLoadSLQLoader
REM sqlldr userid=PAllegretti/pal234 control=GroupDataLoad.ctl
sqlldr.exe userid=PAllegretti/pal234@//takt01-scan.unix.tpna.com:1521/tedwd.takedapharm.com control=ThirdPartyData.ctl log=ThirdpartyData.log

Also it doesn't generate a Log file?

Thanks for anyhelp...
Connor McDonald
November 11, 2017 - 8:36 am UTC

Couple of suggestions

1) use fully qualified paths so you can be sure you know their destinations
2) make sure those paths are writable by the username your program is running as

SQL*Loader Express

Rajeshwaran Jeyabal, July 25, 2019 - 7:28 am UTC

Team,

Was reading about SQL*Loader Express from documentation

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-sql-loader-express-mode.html#GUID-3C000956-3730-4D27-9A46-F7D12BC81540

D:\>sqlldr demo/demo@pdb1 table=EMP date_format='DD-MON-YYYY hh12:mi:ss am' optionally_enclosed_by='"' log=emp_log.txt data=emp.csv terminated_by='|'

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Jul 25 12:45:00 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file emp.csv
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMP
Path used:      Direct

Load completed - logical record count 14.

Table EMP:
  0 Rows successfully loaded.

Check the log file:
  emp_log.txt
for more information about the load.

D:\>type emp.csv
7369|"SMITH"|"CLERK"|7902|17-DEC-1980 12:00:00 am|800||20
7499|"ALLEN"|"SALESMAN"|7698|20-FEB-1981 12:00:00 am|1600|300|30
7521|"WARD"|"SALESMAN"|7698|22-FEB-1981 12:00:00 am|1250|500|30
7566|"JONES"|"MANAGER"|7839|02-APR-1981 12:00:00 am|2975||20
7654|"MARTIN"|"SALESMAN"|7698|28-SEP-1981 12:00:00 am|1250|1400|30
7698|"BLAKE"|"MANAGER"|7839|01-MAY-1981 12:00:00 am|2850||30
7782|"CLARK"|"MANAGER"|7839|09-JUN-1981 12:00:00 am|2450||10
7788|"SCOTT"|"ANALYST"|7566|19-APR-0087 12:00:00 am|3000||20
7839|"KING"|"PRESIDENT"||17-NOV-1981 12:00:00 am|5000||10
7844|"TURNER"|"SALESMAN"|7698|08-SEP-1981 12:00:00 am|1500|0|30
7876|"ADAMS"|"CLERK"|7788|23-MAY-0087 12:00:00 am|1100||20
7900|"JAMES"|"CLERK"|7698|03-DEC-1981 12:00:00 am|950||30
7902|"FORD"|"ANALYST"|7566|03-DEC-1981 12:00:00 am|3000||20
7934|"MILLER"|"CLERK"|7782|23-JAN-1982 12:00:00 am|1300||10

none of the records got loaded, looking into the log file it shows this.
Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'emp.csv'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY ""
DATE FORMAT "DD-MON-YYYY hh12:mi:ss am"
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE DATE,
  SAL,
  COMM,
  DEPTNO
)
End of generated control file for possible reuse.

Record 1: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 2: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 3: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 4: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 5: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 6: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 7: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 8: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 9: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 10: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 11: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 12: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 13: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 14: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present

Table EMP:
  0 Rows successfully loaded.
  14 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Questions:

a) though we have said Optionally_enclosed_by='"' - why does it looks for that enclose character to every field in the datafile, rather than just where it is mentioned?

b) how to specify "optionally_enclosed_by" clause if my data is enclosed by something like this {...} ( say for example, KING is represented as {KING} in the datafile ) ?
Connor McDonald
July 26, 2019 - 5:31 am UTC

It will be how the environment understands the command line, in particular, quotes and spaces etc. For example, on Unix I had to do this:

[oracle]$ sqlldr xxx/xxx@pdb1 table=EMP date_format='DD-MON-YYYY\ hh12:mi:ss\ am' optionally_enclosed_by='\"' log=emp_log.txt data=emp.csv terminated_by='|'

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jul 25 22:33:17 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMP:
  14 Rows successfully loaded.

Check the log files:
  emp_log.txt
  emp_log.txt_xt
for more information about the load.


SQL*Loader Express

Rajeshwaran Jeyabal, July 25, 2019 - 7:44 am UTC

when i put them in a parfile it worked correctly.
D:\>sqlldr demo/demo@pdb1 parfile=emp.par

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Jul 25 13:02:18 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file emp.csv
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMP
Path used:      Direct

Load completed - logical record count 14.

Table EMP:
  14 Rows successfully loaded.

Check the log file:
  emp_log.txt
for more information about the load.

D:\>type emp.par
table=EMP
date_format='DD-MON-YYYY hh12:mi:ss am'
optionally_enclosed_by='"'
log=emp_log.txt
data=emp.csv
terminated_by='|'

D:\>


the control file used for this load from log file has correct reference to OPTIONALLY_ENCLOSED_BY clause ( OPTIONALLY ENCLOSED BY '"' )

OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'emp.csv'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
DATE FORMAT "DD-MON-YYYY hh12:mi:ss am"
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE DATE,
  SAL,
  COMM,
  DEPTNO
)
End of generated control file for possible reuse.

previously (when parfile is not used) the OPTIONALLY_ENCLOSED_BY clause has something like this( OPTIONALLY ENCLOSED BY "" )

so from the above post, ignore the first question (a) and help us to answer the second question (b)

b) how to specify "optionally_enclosed_by" clause if my data is enclosed by something like this {...} ( say for example, KING is represented as {KING} in the datafile ) ?

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.