[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Evaluating PL/SQL Functions in sql loader control file

Hi,

I have a csv data file and it has four columns. its of following format:

product desc,product type description,product type,product code.

I have a table which has four columns:
product id - integer NOT NULL
product type id - integer NOT NULL
product type - varchar2 NOT NULL
number - field for sorting NULL

I have to write a sql ldr script which has to:
1.Insert records in first three columns
2.Takes free format of the csv file in consideration
3. product desc in the csv file should be evaluated against pl/sql function func_product_id(return a number)
4. product type desc in the csv file should be evaluated against pl/sql function func_product_type(returns a number)
5. product type should be inserted as it is.
6. Ignore the fourth column in csv file

I have come up with following ctl file but its not working

LOAD DATA
INFILE data.csv
APPEND INTO TABLE XXX
FIELDS TERMINATED BY ','
( PRODUCT_ID    EXPRESSION "func_product_id(:PRODUCT_ID)"
   PRODUCT_TYPE_ID   EXPRESSION "func_product_type(:PRODUCT_TYPE_ID)")
   TEXT_VALUE)

If I dont use EXPRESSION then it says that the insert failed.
if I use EXPRESSION then it says that it cant bind the variables.

Can you guys please suggest whats wrong here?

How can i evaluate fields using pl/sql functions so that the evaluated value is stored in the table?


Thanks
0
narveer
Asked:
narveer
  • 5
  • 5
1 Solution
 
jrb1Commented:
Does this help?

LOAD DATA
INFILE data.csv
APPEND INTO TABLE XXX
FIELDS TERMINATED BY ','
( PRODUCT_ID  "func_product_id(:PRODUCT_ID)",
   PRODUCT_TYPE_ID  "func_product_type(:PRODUCT_TYPE_ID)",
   TEXT_VALUE)
0
 
sujit_kumarCommented:
I would say you do it in two parts, first create a temp table which inserts PRODUCT_ID  , and PRODUCT_TYPE_ID. Then use "insert into XXX select func_product_id(PRODUCT_ID), func_product_type(PRODUCT_TYPE_ID) FROM TEMP_XXX"  to  insert modified(transformed) records to the base table. It will be a faster process also rather than using the functions in the loading. You can use DIRECT PATH Loading (DIRECT=Y) to load records in the temp table. It will be much faster.
0
 
narveerAuthor Commented:
Hi jrb1,

I tried your solution and it gives me following error:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRODUCT_ID                          FIRST     *   ,       CHARACTER
    SQL string for column : "FUNC_PRODUCT_ID(:PRODUCT_ID)"
PROPERTY_ID                          NEXT     *   ,       CHARACTER
    SQL string for column : "FUNC_PROPERTY_ID(:PROPERTY_ID)"
TEXT_VALUE                           NEXT     *   ,       CHARACTER

Record 1: Rejected - Error on table APP_PRODUCT_PROPERTY_VALUE.
ORA-01400: cannot insert NULL into ("PRS"."APP_PRODUCT_PROPERTY_VALUE"."PRODUCT_ID")

Record 2: Rejected - Error on table APP_PRODUCT_PROPERTY_VALUE.
ORA-01400: cannot insert NULL into ("PRS"."APP_PRODUCT_PROPERTY_VALUE"."PRODUCT_ID")

Any Suggestions?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
narveerAuthor Commented:
hi jrb1,

The Above solution works if csv file has only three columns. My csv file has four columns and I have to ingnore the fourth column. If i execute sql loader with csv file having four columns then it goes on forever and i have to interrupt the process by control C. Any suggestions how I can ignore the fourth column in csv file.
0
 
jrb1Commented:
Sure, I forgot about that column in the data file...

LOAD DATA
INFILE data.csv
APPEND INTO TABLE XXX
FIELDS TERMINATED BY ','
( PRODUCT_ID  "func_product_id(:PRODUCT_ID)",
   PRODUCT_TYPE_ID  "func_product_type(:PRODUCT_TYPE_ID)",
   TEXT_VALUE,
    COLUMN4 FILLER)
0
 
narveerAuthor Commented:
hi jrb1,

I have tried this option before but its not working. The Above comments were posted after using filler for the fourth column.
0
 
narveerAuthor Commented:
Hi Sujit,

Thanks for the solution.

I have tried your option. The difficulty I am facing is that insert fails if the functions doesnt return any matching product id(primary key) for the desciptions. My csv file may and may not have descriptions which matches with the product id in the products table. Any other ideas?
0
 
jrb1Commented:
Well, here's what I did.  I created table:

TAB1
product_id number not null
product_type_id number not null
product_type varchar2(50) not null
num number

Then I created your functions to accept strings and return numbers (also did a version accepting numbers, returning numbers).

Finally, I coded this CSV file:

A101,G101,TEXT,TEXT
B201,G201,TEXT,TEXT
C301,F301,TEXT,TEXT
D401,E401,TEXT,TEXT

and this CTL file:

load data
infile tmpdata.csv
append
into table tab1
fields terminated by ','
(product_id "func_product_id(:product_id)",
 product_type_id "func_product_type(:product_type_id)",
 product_type,
 column4 filler)

When I run SQLLDR, I get

Commit point reached - logical record count 4

When I look at my table, the rows are added.  If I run a second time, additional rows are added.  How is this different than what you are doing?

(The column names listed in the CTL file need to match the fields on your table...except for the FILLER which doesn't need to match anything.)
0
 
jrb1Commented:
When I change the function to return a null sometimes and a value other times, the records with a null are rejected, but the process still completes successfully.
0
 
jrb1Commented:
What should be inserted if nulls are returned?  I'm not sure why SQL Loader's giving you problems, but if you use the temp table solution and don't need rows inserted where the key is null...

insert
into XXX
select func_product_id(PRODUCT_ID), func_product_type(PRODUCT_TYPE_ID), text
FROM TEMP_XXX
where func_product_id(PRODUCT_ID) is not null
and func_product_type(PRODUCT_TYPE_ID) is not null
0
 
narveerAuthor Commented:
Hi jrb1,

Thanks for the solution. I was doing the same thing what you have mentioned earlier.Now I created the file from the scratch and its working fine now. Many Many Thanks for the solution.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now