Home » RDBMS Server » Server Utilities » Can you call an Oracle function from a Control file?
Can you call an Oracle function from a Control file? [message #68962] Thu, 04 October 2001 08:25 Go to next message
Tom
Messages: 67
Registered: June 1998
Member
The situation is that a data file is being sent in from an external system with details for year, month, and day format e.g. 2001-10-06, however, the load table that I am trying to write to has the format year, period, week and day. I can convert the format by calling a new Oracle function, however I would like to call the function within the Control file.
Is this possible?
Any help would be very much appreciated.

Regards,

Tom

----------------------------------------------------------------------
Re: Can you call an Oracle function from a Control file? [message #68963 is a reply to message #68962] Thu, 04 October 2001 09:58 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
yes,possible

----------------------------------------------------------------------
RE: UPDATE - Can you call an Oracle function within a Control file? [message #68964 is a reply to message #68963] Thu, 04 October 2001 11:23 Go to previous message
Tom
Messages: 67
Registered: June 1998
Member
Thanks for the update.
However how would I do the following if I wanted to take values from the function and load the result to the two load columns - period and week.
clndr.get_wk_prd is the Oracle function.
N.B. The data in the incoming file in positions 30 to 31 is a 2 digit month e.g. 10 represents October :
e.g. append into table when ......
(year position(26:29) integer external,
period position(30:31) integer external
"decode(:period,10,'OCT','11','NOV'....)"
"select period, week from clndr_table where clndr.get_wk_prd(:year,period,week,:day) < trunc(sysdate);",
week position(32:32) integer external)

Questions :
1) How could I concatenate the period and week columns so that both are populated by the Oracle function at the same time?
for example - period position(30:31) integer external "||" week position(32:32) integer external "decode statement as above...."
"Oracle function call as above....."

2) How do you separate the decode statement from the Oracle function call?

3) Do you use a semi colon to terminate the query as you would normally do in SQL?

Sorry to ask all these questions but I've not used SQL loader very much and would appreciate any help available.

Cheers,

Tom

----------------------------------------------------------------------
Previous Topic: SQL*Plus
Next Topic: Re: OCP Exam Papers
Goto Forum:
  


Current Time: Fri Mar 29 05:26:13 CDT 2024