Re: Whats the bcp (SQL Server) substitute in Oracle??

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Wed, 2 Aug 2006 14:37:56 GMT
Message-ID: <J3DKn9.929@igsrsparc2.er.usgs.gov>

riteshjain82_at_gmail.com wrote:

> Robert Klemme wrote:

>> riteshjain82_at_gmail.com wrote:
>>> Hi friends,
>>>
>>> In my project right now we are creating flat files from tables on one
>>> secured UNIX server using the bcp utility of SQL Server.
>>>
>>> But now this database will be migrated to Oracle 10g. Can i achieve the
>>> same functionality of bcp in Oracle?? I already have one procedure
>>> which can create one flat file on a fixed server location which will be
>>> specified in init.ora file.
>>>
>>> But I want to create the three files that are created using the bcp
>>> i.e. Error File, Flat File and Out File. Also, I am not very clear
>>> about the difference between the Flat file and Out file generated by
>>> bcp. I also want to pass the secured server location as a parameter to
>>> this procedure.
>>>
>>> Can anyone help me on this?? Please provide the code snippet as i am
>>> very new to Oracle.
>>> This is really urgent friends, please help me.
>> There are several tools, the most similar probably being SQL*Loader.
>> You can find it in the "Utilities" book. It's unlikely though that
>> you'll achieve the exact same functionality you have with bcp.
>>
>> Then there's exp and imp and Oracle Data Pump for moving of data.
>>
>> Kind regards
>>
>> robert

> 
> 
> Yeah but the thing is that we are not allowed to use any of these tools
> or utilities. We have to write a procedure for this.
> Also, i think that SQL Loader can only be used to load data in tables
> from flat files, while i need to create flat files from tables. Please
> correct me if i am wrong??
> 

Aside from the fact that the utilities do not do the job you are looking to get accomplished, why can you not use utilities? Why must this be done in a procedure? It seems to me you were able to use vendor-supplied utilities like bcp with SQL Server. Why the limitation with Oracle?

Anyway, the link below shows how to use a stored procedure to perform this task:

http://asktom.oracle.com/pls/ask/f?p=4950:8:11665125328971332646::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:235814350980

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Aug 02 2006 - 09:37:56 CDT