Home » RDBMS Server » Server Utilities » Functions in SQL LOADER (oracle 9i)
Functions in SQL LOADER [message #333037] Thu, 10 July 2008 06:18 Go to next message
manoj12
Messages: 185
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 #333051 is a reply to message #333037] Thu, 10 July 2008 06:52 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, if a function is owned by "manoj_test" and you are loading data into "intcdr", I'm afraid that "intcdr" doesn't know that this procedure exists in "manoj_test" schema.

You might try to GRANT EXECUTE on the function from "manoj_test" to "intcdr" and create a SYNONYM in "intcdr" schema. Unfortunately, I can't promise it will work and I can't test it at the moment so - consider this a pure theory.
Re: Functions in SQL LOADER [message #333163 is a reply to message #333051] Thu, 10 July 2008 11:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Or, instead of creating a synonym, you could preface the function with the schema in the SQL*Loader control file:

"manoj_test.FN_MSTGL_CODE(:GL_CODE)"
icon10.gif  Re: Functions in SQL LOADER [message #333238 is a reply to message #333051] Fri, 11 July 2008 00:06 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I have tested this but it is not getting resolved.Now If I am creating these functions in intcdr user then the functions are returning a value.But as all the privileges are there on manoj_test user as well as intcdr user then iT's not working.But When I did this testing with granting these privileges then its not working.But when tested with manoj_test.FN_MSTGL_CODE its not working.So please help to resolve this issue.

Regards
Re: Functions in SQL LOADER [message #333240 is a reply to message #333037] Fri, 11 July 2008 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>iT's not working.
>its not working.
>its not working.
Exceptionally worthless statement!

My car is not working.
Tell me how to make my car go, please.

Re: Functions in SQL LOADER [message #333258 is a reply to message #333238] Fri, 11 July 2008 01:02 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If
Quote:
It's not working
you 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.
Previous Topic: EXPORT and IMPORT
Next Topic: How to overwrite Functions/ Procedures using expdp
Goto Forum:
  


Current Time: Wed Dec 07 16:38:18 CST 2016

Total time taken to generate the page: 0.12386 seconds