Home » RDBMS Server » Server Utilities » SQL Loader Calling Stored Proc (Oracle 10g/Windows 2000)
SQL Loader Calling Stored Proc [message #624857] Fri, 26 September 2014 12:04 Go to next message
raskotha
Messages: 5
Registered: February 2007
Location: Pittsburgh
Junior Member
Hi,

As per requirment i need to load data from a flat file and at the end i need to call a stored proc to update a table with total number of rows successfully loaded.

For ex: i have a data file as below:
HEADER,123,01/01/2014
X,A0100,100.00,ABC 
X,B0200,233.12,XYZ
A,AAAAA,332.22,SHHH COMPANY
TRILER,2,3


So, My Control file will only look for records HEADER,X,TRAILER reocrds

Once i load i need to update a table (on the TRAILER record).

I wronte a stored function which calls a stored procedure to perform DML... but errored out
Record 2716: Rejected - Error on table TEMP_A, column TOT_RECORDS.
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 



Edited by Lalit : Added code tags wherever required

[Updated on: Fri, 26 September 2014 12:28] by Moderator

Report message to a moderator

Re: SQL Loader Calling Stored Proc [message #624858 is a reply to message #624857] Fri, 26 September 2014 12:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I wronte a stored function which calls a stored procedure to perform DML... but errored out
Issue is with the stored procedure. we cannot comment on what we cannot see.
>>update a table with total number of rows successfully loaded
Just do it. Separately.
Please post your table DDL and a valid sample datafile

[Updated on: Fri, 26 September 2014 12:32]

Report message to a moderator

Re: SQL Loader Calling Stored Proc [message #624860 is a reply to message #624857] Fri, 26 September 2014 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Re: SQL Loader Calling Stored Proc [message #624861 is a reply to message #624857] Fri, 26 September 2014 12:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
As you can see, you cannot do any DML inside of a function, but you don't show us the function, so we have no idea what you are doing.

I'd say the easiest thing to do is just an external table for your datafile and then you can count the records in there based on a condition or load them into another table and do to it what you would like.

I am having a hard time following what you really are trying to do and what happened.

[edit] fixed typo which changed context of response.

[Updated on: Fri, 26 September 2014 12:46]

Report message to a moderator

Re: SQL Loader Calling Stored Proc [message #624907 is a reply to message #624857] Sat, 27 September 2014 15:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following uses two SQL*Loader runs with two control files. The first run loads only records where the first column = 'X' into the target table. The second run uses the log file from the first run and extracts the number of rows successfully loaded and loads that number into the tot_records column of the temp_a table.


SCOTT@orcl12c> host type data_file.csv
HEADER,123,01/01/2014
X,A0100,100.00,ABC
X,B0200,233.12,XYZ
A,AAAAA,332.22,SHHH COMPANY
TRAILER,2,3

SCOTT@orcl12c> host type test.ctl
load data
into table target_table
when col1='X'
fields terminated by ','
trailing nullcols
(col1, col2, col3, col4)

SCOTT@orcl12c> create table target_table
  2    (col1  varchar2(4),
  3     col2  varchar2(5),
  4     col3  number,
  5     col4  varchar2(12))
  6  /

Table created.

SCOTT@orcl12c> create table temp_a
  2    (tot_records     number)
  3  /

Table created.

SCOTT@orcl12c> host sqlldr scott/tiger control=test1.ctl data=data_file.csv log=test1.log

SQL*Loader: Release 12.1.0.1.0 - Production on Sat Sep 27 13:11:35 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 5

Table TARGET_TABLE:
  2 Rows successfully loaded.

Check the log file:
  test1.log
for more information about the load.

SCOTT@orcl12c> host sqlldr scott/tiger control=test2.ctl data=test1.log log=test2.log

SQL*Loader: Release 12.1.0.1.0 - Production on Sat Sep 27 13:11:35 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 54

Table TEMP_A:
  1 Row successfully loaded.

Check the log file:
  test2.log
for more information about the load.

SCOTT@orcl12c> select * from target_table
  2  /

COL1 COL2        COL3 COL4
---- ----- ---------- ------------
X    A0100        100 ABC
X    B0200     233.12 XYZ

2 rows selected.

SCOTT@orcl12c> select * from temp_a
  2  /

TOT_RECORDS
-----------
          2

1 row selected.

Re: SQL Loader Calling Stored Proc [message #625046 is a reply to message #624907] Tue, 30 September 2014 10:24 Go to previous messageGo to next message
raskotha
Messages: 5
Registered: February 2007
Location: Pittsburgh
Junior Member
Thanks Barbara. That was awesome. So, can you show the test2.ctl file content please.
Re: SQL Loader Calling Stored Proc [message #625058 is a reply to message #625046] Tue, 30 September 2014 20:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Sorry, I didn't mean to leave that out. Here it is.

-- test2.ctl:
load data
into table temp_a
when col2='Rows successfully loaded.'
fields terminated by whitespace
( tot_records position(1) terminated by whitespace
, col2        filler      terminated by '\n')

icon14.gif  Re: SQL Loader Calling Stored Proc [message #626098 is a reply to message #625058] Mon, 20 October 2014 12:55 Go to previous messageGo to next message
raskotha
Messages: 5
Registered: February 2007
Location: Pittsburgh
Junior Member
Thank you so much.
Re: SQL Loader Calling Stored Proc [message #626119 is a reply to message #626098] Tue, 21 October 2014 01:13 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Received via PM:

Quote:

... I have another task, which is, to store the file name processed though SQL Loader. Environment details: Oracle 10g/Windows.

The Scenario,
Front End (: .Net ) will call control file by passing the file name (for example: a.ctl datafile=c:\x.dat'). Control file should accept that file name and store in table.


You could pass the file name to a windows batch file that would concatenate the filename to each row of the data in that file and output the result in another file. You could then use that resulting output file in your SQ*Loader control file. You could use wildcards in the file name, so you can specify multiple files or files with the same naming convention. Please see the example below.

SCOTT@orcl12c> -- data file:
SCOTT@orcl12c> host type x.dat
HEADER,123,01/01/2014
X,A0100,100.00,ABC
X,B0200,233.12,XYZ
A,AAAAA,332.22,SHHH COMPANY
TRAILER,2,3

SCOTT@orcl12c> -- control file:
SCOTT@orcl12c> host type test.ctl
load data
into table target_table
when col1='X'
fields terminated by ','
trailing nullcols
(col1, col2, col3, col4)

SCOTT@orcl12c> create table target_table
  2    (filename  varchar2(30),
  3     col1      varchar2(4),
  4     col2      varchar2(5),
  5     col3      number,
  6     col4      varchar2(12))
  7  /

Table created.

SCOTT@orcl12c> -- batch file to merge data with file names and load it with file names:
SCOTT@orcl12c> host type add_file_name.bat
findstr "^" x*.dat >output.txt
sqlldr scott/tiger control=test1.ctl data=output.txt log=test1.log

SCOTT@orcl12c> -- run of above batch file:
SCOTT@orcl12c> host add_file_name.bat

C:\my_oracle_files>findstr "^" x*.dat  1>output.txt

C:\my_oracle_files>sqlldr scott/tiger control=test1.ctl data=output.txt log=test1.log

SQL*Loader: Release 12.1.0.1.0 - Production on Mon Oct 20 23:05:02 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 5

Table TARGET_TABLE:
  2 Rows successfully loaded.

Check the log file:
  test1.log
for more information about the load.

SCOTT@orcl12c> -- resulting merged data:
SCOTT@orcl12c> host type output.txt
x.dat:HEADER,123,01/01/2014
x.dat:X,A0100,100.00,ABC
x.dat:X,B0200,233.12,XYZ
x.dat:A,AAAAA,332.22,SHHH COMPANY
x.dat:TRAILER,2,3

SCOTT@orcl12c> -- resulting data in table:
SCOTT@orcl12c> select * from target_table
  2  /

FILENAME                       COL1 COL2        COL3 COL4
------------------------------ ---- ----- ---------- ------------
x.dat                          X    A0100        100 ABC
x.dat                          X    B0200     233.12 XYZ

2 rows selected.




Previous Topic: could not open parameter file 'imp_woc.par'
Next Topic: sqlldr-how to generate log file for all files loaded
Goto Forum:
  


Current Time: Thu Mar 28 17:55:38 CDT 2024