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  |
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  |
 |
Mahesh Rajendran
Messages: 10708
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 #624861 is a reply to message #624857] |
Fri, 26 September 2014 12:45 |
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 |
 |
Barbara Boehmer
Messages: 9104
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 #626119 is a reply to message #626098] |
Tue, 21 October 2014 01:13 |
|
Barbara Boehmer
Messages: 9104
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.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 20:17:54 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
