| Functions in SQL LOADER [message #333037] |
Thu, 10 July 2008 06:18  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear sir
I have two schemas name manoj_test and the other schema as intcdr.In manoj_test schema there are functions written which will be returning the values as per master setup which is done in the tables.Now I wanted to load the data into INTCDR database.For every column I have written a function.Suppose I have one table by the name INF_BANK
AND THE COLUMNS AS
CREATE TABLE INF_BANK
(
GL_CODE VARCHAR2(10),
AMOUNT NUMBER(10),
DATAFEEDDATE DATE
);
But I have made the control file like this since the data is there in bar delimetted format
LOAD DATA
REPLACE INTO TABLE INF_MANOJ
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
GL_CODE CHAR "FN_MSTGL_CODE(:GL_CODE)",
AMOUNT DECIMAL EXTERNAL,
DATAFEEDDATE CHAR
)
Since this is a function of FN_MSTGL_CODE of schema as manoj_test.I wanted to load the data into intcdr_obc database.
I am getting this error as Record 1: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904: "FN_MSTGL_CODE": invalid identifier
Record 2: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904: "FN_MSTGL_CODE": invalid identifier
Record 3: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904: "FN_MSTGL_CODE": invalid identifier
Record 4: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904: "FN_MSTGL_CODE": invalid identifier
Record 5: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904: "FN_MSTGL_CODE": invalid identifier
Record 6: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904: "FN_MSTGL_CODE": invalid identifier
Record 7: Rejected - Error on table INF_BANK_GL, column GL_CODE.
ORA-00904:"FN_MSTGL_CODE": invalid identifier
This function will return a value from the master setup table i.e MSTGL_CODE.Now my question is I wanted SQLLDR to read this function and on the basis of this column so that will help me to load the data into the table INF_BANK which is there in manoj_test database.
Any help would help to resolve this issue
Regards
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Functions in SQL LOADER [message #333258 is a reply to message #333238] |
Fri, 11 July 2008 01:02  |
 |
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Ifyou must have done something wrong.
Here's a test case: connected as Scott, I'm creating a table which will be target of loading session. As user Mike, I'm creating a function which will be used by Scott:SQL> create table test (id number, datum date);
Table created.
SQL> connect mike/lion@ora10
Connected.
SQL> create function fun_now return date as
2 begin
3 return sysdate;
4 end;
5 /
Function created.
SQL> grant execute on fun_now to scott;
Grant succeeded.
SQL> connect scott/tiger@ora10
Connected.
SQL> select * from test;
no rows selected
Here's the "test.ctl" control file:SQL> $type test.ctl
load data
infile *
replace
into table test
fields terminated by ','
trailing nullcols
(id,
datum "mike.fun_now"
)
begindata
1
2
3
Finally, testing:SQL> $sqlldr scott/tiger@ora10 control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pet Srp 11 07:56:46 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
Commit point reached - logical record count 3
SQL> select * from test;
ID DATUM
---------- -------------------
1 11.07.2008 07:56:47
2 11.07.2008 07:56:47
3 11.07.2008 07:56:47
SQL>
It appears that "datum" column has its value.
Let's try the same with a synonym:SQL> truncate table test;
Table truncated.
SQL> create synonym fun_sad for mike.fun_now;
Synonym created.
SQL> $type test.ctl
load data
infile *
replace
into table test
fields terminated by ','
trailing nullcols
(id,
datum "fun_sad"
)
begindata
1
2
3
SQL> $sqlldr scott/tiger@ora10 control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pet Srp 11 07:58:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
Commit point reached - logical record count 3
SQL> select * from test;
ID DATUM
---------- -------------------
1 11.07.2008 07:58:15
2 11.07.2008 07:58:15
3 11.07.2008 07:58:15
SQL>
Works again!
Now, which step did you miss? What did you do wrong? Could you follow these examples and see whether you can fix it? If not (as already being said), "my car is not working" trouble description is useless. Provide YOUR test case, copy and paste SQL*Plus session and someone will take a look.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
