Avatar of Nuno

asked on 

What is Sybase BCP utility equivalent in Oracle ?

I'm migrating from Sybase database to Oracle database and I need to change some korn shell scripts that access database. Inside these scripts is used BCP utility to generate flat files from tables in Sybase.
I need to know if there is an equivalent in Oracle besides spool command.

Thanks in advance  
Oracle DatabaseSybase DatabaseDatabases

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Oracle doesn't provide a tool similar to bcp.  sqlplus and spool is the best method using Oracle tools.

It's pretty easy to get it in the exact format you want with some set commands for things like pagesize, etc...

My guess is you want the output in CSV format.  Look at:  set markup csv on:
Avatar of Nuno


 Slightwv, I want the output in .dat format, without separator between fields and limiting (truncating) the length of each line. Is it possible ?
I don't know what ".dat" format is.

As long as the total length of the output isn't larger than 4,000 characters, you can use string concatenation of all the columns to do whatever you want with the data.

I'm also not overly familiar with BCP but have been through the docs a few times.  What does it do that you are trying to emulate in Oracle?
Avatar of Nuno


In my examples BCP is creating an output .dat file from a database table based on format file .fmt. I'm trying to create the same output file with spool according to the Sybase format (field fixed length, without delimiter and with a maximum length in each line - if the record that comes from the database table is bigger than for instance 600, it will truncate the to 600)  
If you can provide some sample data and expected results, I'll try to provide you a working sqlplus script.

A test with a sample create table and insert statements would save both of us some time.
Avatar of Nuno


Ok, for example :

Imagine I have the following table

create table spool_tst
(field1 char(8),
field2 char(5),
field3 char(20)

insert into spool_tst values ('value1','v1','aaaaaaaaaa');
insert into spool_tst values ('value2','v2','bbbbbbbbbb');
insert into spool_tst values ('value3','v3','ccccccccccc');  

The expected output of running the sqlplus script would be a file with this content (without any kind of delimiter between fields) :

value1  v1   aaaaaaa
value2  v2   bbbbbbb
value3  v3   cccccccc

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Nuno


I was looking for an option like "set colsep ..." to not have separator between the columns but maybe it doesn't exist 
If you keep reading around you'll find that 'colsep' is the wrong way to do this.  There are a few reasons why it is a idea.

If you want a guaranteed fixed format, best to take 100% of the control.
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo