Solved

call user define function inside sqlldr

Posted on 2008-06-26
6
2,298 Views
Last Modified: 2013-12-18
Hi

I am trying to call user define function inside sqlldr but I am getting error

below are the details

Thanks
Control file
============
 
LOAD DATA
INTO TABLE TEMP
APPEND
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
(
TIME_RECIEVED FILLER,
FILE_LOCATION FILLER,
LOAD_DATE SYSDATE,
RUN_NUMBER "schema_name.get_run_number('process_data')"
)
 
ERROR
=====
Record 1: Rejected - Error on table TEMP, column RUN_NUMBER.
Column not found before end of logical record (use TRAILING NULLCOLS)

Open in new window

0
Comment
Question by:basirana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21875769
You don't appear to actually be loading any data from the file!  Have you tried adding the TRIALING NULLCOLS option to your LOAD DATA section?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21875785
Try:

RUN_NUMBER Expression "schema_name.get_run_number('process_data')"

Open in new window

0
 

Author Comment

by:basirana
ID: 21875789
How can I add TRIALING NULLCOLS inside control file? Can you give me some example to change above code
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 22

Assisted Solution

by:DrSQL
DrSQL earned 60 total points
ID: 21875805
You need to add the "expression" clause:

LOAD DATA
INTO TABLE TEMP
APPEND
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
(
TIME_RECIEVED FILLER,
FILE_LOCATION FILLER,
LOAD_DATE SYSDATE,
RUN_NUMBER EXPRESSION "schema_name.get_run_number('process_data')"
)
 
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 65 total points
ID: 21875848
Try the following:

LOAD DATA
INTO TABLE TEMP
APPEND
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TIME_RECIEVED FILLER,
FILE_LOCATION FILLER,
LOAD_DATE SYSDATE,
RUN_NUMBER "schema_name.get_run_number('process_data')"
)
0
 

Author Closing Comment

by:basirana
ID: 31471017
Thanks
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question