Home » RDBMS Server » Server Utilities » Calling stored Procedure from SQL*LOADER
Calling stored Procedure from SQL*LOADER [message #269340] Fri, 21 September 2007 14:21 Go to next message
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 #269341 is a reply to message #269340] Fri, 21 September 2007 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table, it will be more efficient and easier to manage.

Regards
Michel

Re: Calling stored Procedure from SQL*LOADER [message #269376 is a reply to message #269341] Fri, 21 September 2007 20:16 Go to previous messageGo to next message
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 #269469 is a reply to message #269340] Sat, 22 September 2007 10:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Use a function instead of a procedure:


create or replace function look
  (que_desc user_security_question.question_desc%type)
  return varchar2
as
  que_id user_security_question.question_id%type;
begin
  select question_id 
  into   que_id 
  from   user_security_question
  where  question_desc = que_desc;
  --
  return que_id;
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 look;
/


Then use the function in your SQL*Loader control file:

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 "look(:SECURITYQUESTION)"
)



Re: Calling stored Procedure from SQL*LOADER [message #269810 is a reply to message #269469] Mon, 24 September 2007 13:39 Go to previous messageGo to next message
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


Re: Calling stored Procedure from SQL*LOADER [message #269850 is a reply to message #269810] Mon, 24 September 2007 19:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
According to the section on "Applying SQL Operators to Fields" of the "Field List Reference" of the part of the "Database Utilities Guide" regarding SQL*Loader:

"A wide variety of SQL operators can be applied to field data with the SQL string. This string can contain any combination of SQL expressions that are recognized by the Oracle database as valid for the VALUES clause of an INSERT statement. In general, any SQL function that returns a single value that is compatible with the target column's datatype can be used. SQL strings can be applied to simple scalar column types as well as to user-defined complex types such as column object and collections."

The excerpt above can be found using the link below:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#i1008150
Re: Calling stored Procedure from SQL*LOADER [message #269852 is a reply to message #269850] Mon, 24 September 2007 20:03 Go to previous message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
If you use the link above and scroll down a bit, you will see that using "expresion" also requires a sql string that meets the same criteria.
Previous Topic: How to dump table into XML format?
Next Topic: sqlloader error
Goto Forum:
  


Current Time: Mon Jun 24 07:32:35 CDT 2024