Re: SQL*Loader calling stored functions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 05 Jan 1999 14:12:45 GMT
Message-ID: <36a11d10.89026343@192.86.155.100>

A copy of this was sent to "Joseph Sunderland" <tpcjsunderland.NO_at_SPAMgci.com> (if that email address didn't require changing) On 4 Jan 1999 13:49:59 -0900, you wrote:

>Does anyone know when specifying the control file for SQL*Loader on Oracle
>8.0 if I can also specify stored database functions?
>
>Thanks.
>
>Joseph S

sure, lets say you had a function:

Table created.

SQL> create or replace function my_function( x in varchar2, y in number )   2 return varchar2
  3 as
  4 begin
  5 return x || '_' || y;
  6 end;
  7 /

Function created.

and a ctl file like:

--
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO,
DNAME "my_function( :dname, :deptno )", LOC)
BEGINDATA

12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"

Notice how on the dname column, we call 'my_function' and pass it the dept name and dept number for the currect record. It concatenates and returns that value.

After running that, we would have:

SQL> select * from dept
2 ;

DEPTNO DNAME LOC
---------- -------------- -------------

        12 RESEARCH_12    SARATOGA
        10 ACCOUNTING_10  CLEVELAND
        11 ART_11         SALEM
        13 FINANCE_13     BOSTON
        21 SALES_21       PHILA.
        22 SALES_22       ROCHESTER
        42 INT'L_42       SAN FRAN

7 rows selected.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jan 05 1999 - 08:12:45 CST