Home » RDBMS Server » Server Utilities » SQL Loader's SQL string issue
SQL Loader's SQL string issue [message #155234] Thu, 12 January 2006 21:09 Go to next message
k08489
Messages: 6
Registered: January 2006
Junior Member
I have written the below control file for my data import. I have gotten an error from the log file.

LOAD DATA
INFILE 'C:\Jie\PSA\SQLLoader\JobPlanTask01.csv'
Append
INTO TABLE jobtask
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(JPNUM POSITION(*),
JPTASK,
TASKSEQUENCE,
DESCRIPTION,
TASKDURATION,
METERNAME,
JOBPLANID EXPRESSION "SELECT jobplan.JOBPLANID FROM jobplan WHERE jobplan.JPNUM = :JPNUM",
LANGCODE CONSTANT 'EN',
ORGID CONSTANT 'EAGLENA',
HASLD CONSTANT '0',
JOBTASKID "jobtaskseq.nextval"
)


Record 1: Rejected - Error on table JOBTASK, column JOBPLANID.
ORA-00936: missing expression

Record 2: Rejected - Error on table JOBTASK, column JOBPLANID.
ORA-00936: missing expression

Can anyone tell me what mistake I had in my sql string? Thanks for your help.
Re: SQL Loader's SQL string issue [message #155237 is a reply to message #155234] Thu, 12 January 2006 22:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7997
Registered: November 2002
Location: California, USA
Senior Member
You could use a function:

CREATE OR REPLACE FUNCTION get_jobplanid
  (p_jpnum IN jobplan.jpnum%TYPE)
  RETURN      jobplan.jobplanid%TYPE
AS
  v_jobplanid jobplan.jobplanid%TYPE;
BEGIN
  SELECT jobplan.JOBPLANID 
  INTO   v_jobplanid
  FROM   jobplan 
  WHERE  jobplan.jpnum = p_jpnum;
  RETURN v_jobplanid;
END get_jobplanid;
/


LOAD DATA
INFILE 'C:\Jie\PSA\SQLLoader\JobPlanTask01.csv'
Append
INTO TABLE jobtask
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(JPNUM POSITION(*),
JPTASK,
TASKSEQUENCE,
DESCRIPTION,
TASKDURATION,
METERNAME,
JOBPLANID "get_jobplanid (:jpnum)",
LANGCODE CONSTANT 'EN',
ORGID CONSTANT 'EAGLENA',
HASLD CONSTANT '0',
JOBTASKID "jobtaskseq.nextval"
)
Re: SQL Loader's SQL string issue [message #155250 is a reply to message #155234] Fri, 13 January 2006 00:26 Go to previous messageGo to next message
k08489
Messages: 6
Registered: January 2006
Junior Member
Thanks for your brilliant solution. Laughing
Re: SQL Loader's SQL string issue [message #500974 is a reply to message #155250] Thu, 24 March 2011 14:41 Go to previous messageGo to next message
speeler
Messages: 3
Registered: June 2008
Location: USA
Junior Member
Thank you. The function works. But, can I use "Select ..."
Re: SQL Loader's SQL string issue [message #500983 is a reply to message #500974] Thu, 24 March 2011 16:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: SQL Loader's SQL string issue [message #500984 is a reply to message #500974] Thu, 24 March 2011 16:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7997
Registered: November 2002
Location: California, USA
Senior Member
speeler wrote on Thu, 24 March 2011 12:41
Thank you. The function works. But, can I use "Select ..."


No, the expression must be a sql string, not a sql select statement.
Re: SQL Loader's SQL string issue [message #508836 is a reply to message #500984] Tue, 24 May 2011 10:32 Go to previous messageGo to next message
ric90
Messages: 40
Registered: May 2011
Location: Belfort
Member
Barbara Boehmer wrote on Thu, 24 March 2011 22:10
speeler wrote on Thu, 24 March 2011 12:41
Thank you. The function works. But, can I use "Select ..."


No, the expression must be a sql string, not a sql select statement.



Hi,
I'm sorry but you are wrong.
You can put a SELECT statement in control file.

(
        LINE_NUMBER			SEQUENCE(COUNT),
		ROW_1        POSITION(4:15) "SUBSTR(:ROW_1,1,8)",
        CODE_INFORMATION    POSITION(16:19),
        INFORMATION_DATA    POSITION(20) CHAR(4000),
        DATE_TRAITEMENT		EXPRESSION	"(SELECT TITI FROM TOTO WHERE DATE_CREATION = (SELECT MAX(DATE_CREATION) FROM TOTO))"

[Updated on: Tue, 24 May 2011 10:33]

Report message to a moderator

Re: SQL Loader's SQL string issue [message #508869 is a reply to message #508836] Tue, 24 May 2011 13:37 Go to previous messageGo to next message
joy_division
Messages: 4532
Registered: February 2005
Location: East Coast USA
Senior Member
ric90 wrote on Tue, 24 May 2011 11:32

I'm sorry but you are wrong.
You can put a SELECT statement in control file.

(
        LINE_NUMBER			SEQUENCE(COUNT),
		ROW_1        POSITION(4:15) "SUBSTR(:ROW_1,1,8)",
        CODE_INFORMATION    POSITION(16:19),
        INFORMATION_DATA    POSITION(20) CHAR(4000),
        DATE_TRAITEMENT		EXPRESSION	"(SELECT TITI FROM TOTO WHERE DATE_CREATION = (SELECT MAX(DATE_CREATION) FROM TOTO))"


Doesn't work for me in
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table x1 (x1 VARCHAR2(5),
  2  x2 VARCHAR2(55),
  3  x3 VARCHAR2(55));

Table created.

SQL> desc tm
 Name                     Null?    Type
 ------------------------ -------- ------------------------------------
 XNAME                    NOT NULL VARCHAR2(8)

SQL> select * from tm where rownum <= 1;

XNAME
--------
*****

SQL> !cat x.ctl
LOAD DATA
INFILE 'a.csv'
replace
INTO table x1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(x1,
 x2 expression "select xname from tm where rownum <= 1",
 x3)

SQL> !cat a.csv
A,ABC,NONE
B,DEF,NONE
C,EFG,NONE

SQL> !sqlldr control=x.ctl
Username:scott/tiger@testdb
SQL*Loader: Release 10.1.0.4.2 - Production on Tue May 24 13:32:06 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3

SQL> !cat x.log

SQL*Loader: Release 10.1.0.4.2 - Production on Tue May 24 13:32:06 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   x.ctl
Data File:      a.csv
  Bad File:     a.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table X1, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X1                                  FIRST     *   ,  O(") CHARACTER
X2                                                        EXPRESSION
    SQL string for column : "select xname from tm where rownum <= 1"
X3                                   NEXT     *   ,  O(") CHARACTER

Record 1: Rejected - Error on table X1, column X2.
ORA-00936: missing expression

Record 2: Rejected - Error on table X1, column X2.
ORA-00936: missing expression

Record 3: Rejected - Error on table X1, column X2.
ORA-00936: missing expression


Table X1:
  0 Rows successfully loaded.
  3 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.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         3
Total logical records discarded:        0

Run began on Tue May 24 13:32:06 2011
Run ended on Tue May 24 13:32:10 2011

Elapsed time was:     00:00:04.03
CPU time was:         00:00:00.05
Re: SQL Loader's SQL string issue [message #508874 is a reply to message #508869] Tue, 24 May 2011 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The parenthesis around the query are mandatory:
SQL> create table x1 (x1 VARCHAR2(5), x2 VARCHAR2(55), x3 VARCHAR2(55));

Table created.

SQL>  create table tm (XNAME                    VARCHAR2(8) );

Table created.

SQL> insert into tm values ('Michel');

1 row created.

SQL> commit;

Commit complete.

SQL> host sqlldr michel/michel control=x.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Mar. Mai 24 21:28:01 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 3

SQL> select * from x1;
X1    X2                                                      X3
----- ------------------------------------------------------- ----------------
A     Michel                                                  ABC
B     Michel                                                  DEF
C     Michel                                                  EFG

3 rows selected.

SQL> host type x.ctl
LOAD DATA
INFILE 'a.csv'
replace
INTO table x1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(x1,
 x2 expression "(select xname from tm where rownum <= 1)",
 x3)


Regards
Michel
Re: SQL Loader's SQL string issue [message #508881 is a reply to message #508836] Tue, 24 May 2011 15:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7997
Registered: November 2002
Location: California, USA
Senior Member
ric90,

Thanks for the correction. I tested without parentheses around the select statement, so I thought it couldn't be done, but I see now that it works with the parentheses.
Re: SQL Loader's SQL string issue [message #508981 is a reply to message #508874] Wed, 25 May 2011 08:08 Go to previous messageGo to next message
joy_division
Messages: 4532
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Tue, 24 May 2011 15:28
The parenthesis around the query are mandatory:


Thanks, I did not know this either.
Re: SQL Loader's SQL string issue [message #509002 is a reply to message #508881] Wed, 25 May 2011 09:19 Go to previous messageGo to next message
ric90
Messages: 40
Registered: May 2011
Location: Belfort
Member
Barbara Boehmer wrote on Tue, 24 May 2011 22:29
ric90,

Thanks for the correction. I tested without parentheses around the select statement, so I thought it couldn't be done, but I see now that it works with the parentheses.



My pleasure.
Don't forget the parentheses for select statement, and it'll work fine.
Re: SQL Loader's SQL string issue [message #509006 is a reply to message #509002] Wed, 25 May 2011 10:12 Go to previous message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for this trick.

Regards
Michel
Previous Topic: validation after expdp and impdp
Next Topic: Concatenating dashes in a column using sqlldr
Goto Forum:
  


Current Time: Fri Oct 31 14:36:26 CDT 2014

Total time taken to generate the page: 0.16779 seconds