[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2749
  • Last Modified:

call user define function inside sqlldr

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
basirana
Asked:
basirana
2 Solutions
 
MilleniumaireCommented:
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
 
MikeOM_DBACommented:
Try:

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

Open in new window

0
 
basiranaAuthor Commented:
How can I add TRIALING NULLCOLS inside control file? Can you give me some example to change above code
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DrSQLCommented:
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
 
MilleniumaireCommented:
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
 
basiranaAuthor Commented:
Thanks
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now