Nuno
asked on
What is Sybase BCP utility equivalent in Oracle ?
Hi,
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
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
Last Comment
ASKER
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?
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?
ASKER
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.
A test with a sample create table and insert statements would save both of us some time.
ASKER
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
If you want a guaranteed fixed format, best to take 100% of the control.
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.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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:
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/starting-SQL-Plus.html#GUID-B3B93959-2119-447D-A9D3-2349DDD70435