Calling stored Procedure from SQL*LOADER [message #269340] |
Fri, 21 September 2007 14:21 |
vshalpatel
Messages: 5 Registered: September 2007
|
Junior Member |
|
|
I have a source file login.csv that I am loading in oracle database TABLE using SQL*LOADER. while loading the target table I have to perform some look up on other table to retrieve the values to populate in target table. I am trying to explain the scenario below.
The source file login.csv layout is:
**************************************************************************
Profile ID Secret Question Secret Answer
1001 Whats your mother’s maiden name? XXXXXXXXX
1002 Whats name of your first school? YYYYYYYYY
1003 Whats name of your birth place? ZZZZZZZZZZ
****************************************************************************
The ORACLE target table ddl is:
(2) user_security_ans
( PROFILE ID VARCHAR2(40) NOT NULL ENABLE,
QUESTION_ID VARCHAR2(10) NOT NULL ENABLE,
SECRET_ANSWER VARCHAR2(254) NOT NULL ENABLE
)
Lookup tabe is :
(1) user_security_question
( QUESTION_ID VARCHAR2(40) NOT NULL ENABLE,
SECRET_QUESTION VARCHAR2(255)
)
When I populate data in table (2) using SQL*LOADER, I have to perform look up on table (1).
For each record of source file, I have to compare ‘Secret Question’ from source file to ‘SECRET_QUESTION’ in look up table (1) and have to retrieve the corresponding QUESTION_ID from lookup table (1) to load in target table (2).
I created a store procedure named ‘look’ which takes SECURITY QUESTIONS as IN parameter and which returns the QUSTION_ID as OUT parameter. I tested this procedure it works well but when I call this procedure from SQL*LOADER control file, I am getting the following error.
ORA-00904: "LOOK": invalid identifier.
here is my store procedure:
***********************************************************************************************
create or replace procedure look(que_desc user_security_question.question_desc%type,
que_id OUT user_security_question.question_id%type)
as
begin
select question_id into que_id from user_security_question
where question_desc = que_desc;
exception
when no_data_found then
raise_application_error(-20001, 'question not found');
when too_many_rows then
/* this would not happen generally */
raise_application_error(-20002, 'More questions exist with the same number');
when others then
raise_application_error(-20003, SQLERRM);
end;
***********************************************************************************
here is my control file ‘securityans.ctl’
**************************************************************************
load data
INFILE 'c:\oracle\ora92\bin\LOGIN.CSV'
REPLACE
INTO table USER_SECURITY_ANS
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
(ID,
SECURITYQUESTION BOUNDFILLER,
SECRET_ANSWER,
Question_Id EXPRESSION "look(':SECURITYQUESTION',Question_id)"
)
****************************************************************
Could someone please give me direction how to make sql*loader recognize the store procedure? or some alternate way to achieve the same.
I will appreciate any help
Thanks
Vishal
|
|
|
|
Re: Calling stored Procedure from SQL*LOADER [message #269376 is a reply to message #269341] |
Fri, 21 September 2007 20:16 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
External tables are great if you can get your file onto the DB Server and get the DBA to create a directory object for you. Some environments restrict this type of access.
If yours is one, you could call the stored proc from a BEFORE INSERT trigger on the load table. It will be pretty slow though, and you cannot use direct-path load.
Ross Leishman
|
|
|
|
Re: Calling stored Procedure from SQL*LOADER [message #269810 is a reply to message #269469] |
Mon, 24 September 2007 13:39 |
vshalpatel
Messages: 5 Registered: September 2007
|
Junior Member |
|
|
Thanks barbara.
problem is been solved by using function instead of stored procedure .Function works fine. i successfully called the function from sql*loader and got the return value back to sql*loader to populate that value in my target table.
any idea why stored procedure didnt work?
any feedback will be appreciated.
Thanks
|
|
|
|
|