How can I use a select query to insert with a control file in Oracle?

Question

I have a control file and a data file that look like this:

CONTROLFILE1:

RECOVERABLE 
LOAD DATA 

INFILE "D:\KING\DATAFILE.txt"

APPEND 
CONCATENATE 1

INTO TABLE TABLE1 
FIELDS TERMINATED BY ','
TRAILING NULLCOLS

(
USERID CHAR(7),
COUNTRY CHAR(100),
FIRSTNAME EXPRESSION "SELECT USERNAME FROM USER_TABLE WHERE ID=3",
ACTIVE_FLAG CONSTANT 'Y',
CHANGE_DT SYSDATE ,
)

DATAFILE.TXT

1,INDIA
5,USA
19,RUSSIA

I'm not able to load the data into TABLE1. It's showing the error "FIRSTNAME" (Expression missing..)

I need to use the result of select query as a insert value. How can I do this?

Answer 1

I don't understand what exactly you plan to do.

Do you try to nest one function into the other?

Nevertheless I've got the feeling that you should cut your steps into single steps (re to: "I need to use the result of select query as a insert value.")

Maybe you can solve your problem with this function: GenID

With GenID you can generate the next ID before the following step.

Answer 2

Enclosing the query in parentheses fixed this error for me:

FIRSTNAME EXPRESSION "(SELECT USERNAME FROM USER_TABLE WHERE ID=3)"

I think this is because in order to be an expression, it has to be seen as a subquery.