Home » SQL & PL/SQL » SQL & PL/SQL » running sql loader from pl/sql
running sql loader from pl/sql [message #247939] Wed, 27 June 2007 10:39 Go to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
I am trying to run sql loader from pl/sql procedure, here i am calling a C-shell script which executes the unix commands.
i can run all the other commands but when i try to run sql loader i am getting the error , the error is
SQL*Loader-523: error -2 writing to file (STDERR)

please help me
Re: running sql loader from pl/sql [message #247951 is a reply to message #247939] Wed, 27 June 2007 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table.

Regards
Michel
Re: running sql loader from pl/sql [message #247967 is a reply to message #247939] Wed, 27 June 2007 11:53 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
external table creates virtual table, but for this i have to run the sql loader
Re: running sql loader from pl/sql [message #247968 is a reply to message #247939] Wed, 27 June 2007 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>here i am calling a C-shell script which executes the unix commands.
I suspect that the environment (env | sort -o /tmp/myenv.lis) of your script/process above has many errors of ommission
Re: running sql loader from pl/sql [message #247972 is a reply to message #247967] Wed, 27 June 2007 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for this i have to run the sql loader

Why?
Btw, what is "this"?

Regards
Michel
Re: running sql loader from pl/sql [message #247974 is a reply to message #247939] Wed, 27 June 2007 12:45 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
what i am doing is converting the Kshell scripts in to pl/sql procedure, in those Korn shells they have used the sql loader which loads when ever tht shell script is called. I need to code that in procedure which runs the sql loader
Re: running sql loader from pl/sql [message #247977 is a reply to message #247974] Wed, 27 June 2007 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you don't need it, use an external table.

What do you do with SQL*Loader? You load a table.
What do you do with an external? "insert select" to load a table.

Regards
Michel
Re: running sql loader from pl/sql [message #247979 is a reply to message #247939] Wed, 27 June 2007 13:15 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
when this procedure is called, what id does is it deletes the contents of the table first and loads the table with new records from a flat file.

i am sending the code
CREATE OR REPLACE PROCEDURE BZK.create_extract_file_table IS
BEGIN
system.shell('ls -1 /users/ftp/pub > /home/extracts/extract_files.dat');
system.shell(' ls -1 /home/mframe >> /home/extracts/extract_files.dat');
system.shell('sqlldr userid=loader/abacus control=/home/extracts/extract_files.ctl >& sqlldr_output.txt');
END;

here shell is Cshell code which recognises unix command
Re: running sql loader from pl/sql [message #247985 is a reply to message #247979] Wed, 27 June 2007 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you really want to use your shell statements, use scheduler to execute your script.

Regards
Michel

[Updated on: Thu, 28 June 2007 09:20]

Report message to a moderator

Re: running sql loader from pl/sql [message #248272 is a reply to message #247939] Thu, 28 June 2007 09:16 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
HI michael,

can you tell me clearly how i can use the DBMS_SCHEDULER in this case.

regards
bharat
Re: running sql loader from pl/sql [message #248274 is a reply to message #248272] Thu, 28 June 2007 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Administrator's Guide:
Chapter 26 Scheduler Concepts
Chapter 27 Using the Scheduler

Regards
Michel
Re: running sql loader from pl/sql [message #248275 is a reply to message #247939] Thu, 28 June 2007 09:22 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I'm just curious, why are you rejecting the idea of an external table ?

It is the simplest and most logical solution.

Do you even know what an external table is ?

At least explore this option before you put a load of work on your DBA.

Just my two cents.

Marc


Re: running sql loader from pl/sql [message #248287 is a reply to message #247939] Thu, 28 June 2007 09:49 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
Hi marc,

i know what external table is, but the DB version is 8.1 so i couldnt use the external table,

regards
bharat
Re: running sql loader from pl/sql [message #248311 is a reply to message #248287] Thu, 28 June 2007 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
F...
You're down for scheduler.

Next time, post your version FIRST.

Now, for me, You're On Your Own (YOYO), I stop wasting my time with you.

Regards
Michel
Re: running sql loader from pl/sql [message #248346 is a reply to message #247939] Thu, 28 June 2007 13:00 Go to previous message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
sorry michel,

i am going with your idea, i have asked them to move to 10g, they said yes, so i should be using external tables

thanks for answering all the questions
regards
bharat
Previous Topic: Update statement generates error - SQL command not ended properly
Next Topic: help with clob output
Goto Forum:
  


Current Time: Thu Dec 08 08:34:11 CST 2016

Total time taken to generate the page: 0.29694 seconds