Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: July 25, 2017 - 12:36 pm UTC

Last updated: July 27, 2017 - 1:15 am UTC

Version: 12C

Viewed 10K+ times! This question is

You Asked

Hi,

We load data by calling sql loader from shell script. here we are getting below issues in loading data
1. not all Bind variable bound
2. data Exceeds maximum length
Here is the Sample Control File
-----
LOAD DATA
INFILE '$FILE'
APPEND
INTO TABLE 'SCOTT.EMP'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
"NAME" "TRIM(:NAME)",
"Description" "TRIM(:Description)"
)

with above control file, we got error "not all Bind variable bound". but when we run sql loder from command prompt(using sqlldr) by providing Data File Name, data loaded successfully.

So we removed " in control file and again ran the shell script, then data loaded successfully.
---
LOAD DATA
INFILE '$FILE'
APPEND
INTO TABLE 'SCOTT.EMP'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
NAME "TRIM(:NAME)",
Description "TRIM(:Description)"
)
similarly sometimes we get "data Exceeds maximum length" error but when we run sql loader through command prompt(using sqlldr command and same control file) we dont get this issues.
So my question is,
Does Sql Loader changes its functionality when we call it from shell script?

and Connor said...

Putting quotes around column names means you are preserving the case exactly, and hence you need to preserve that in your queries etc

SQL> create table t1 ( col int );

Table created.

SQL> create table t2 ( "col" int );

Table created.

SQL> desc t1
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------
 COL                                                                              NUMBER(38)

SQL> desc t2
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------
 col                                                                              NUMBER(38)

SQL> select col from t1;

no rows selected

SQL> select COL from t1;

no rows selected

SQL> select col from t2;
select col from t2
       *
ERROR at line 1:
ORA-00904: "COL": invalid identifier


SQL> select "col" from t2;

no rows selected




Rating

  (1 rating)

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

Comments

santhoshreddy, July 26, 2017 - 5:55 am UTC

But when i run sql loader from windows command prompt,the data loaded, but not when i call from shell script.
if that issue due to case preserve then how data loaded from Command prompt (i used same control file 2 times, and i did not make any changes)?
Connor McDonald
July 27, 2017 - 1:15 am UTC

Here's a tip...

Show us the entire control file, the shell script you're using AND the log file AND the output...

We're flying blind here

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library