Home » SQL & PL/SQL » SQL & PL/SQL » Unable to use sqlplus to unload CHAR(1) column with data = chr(0)
Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218814] Sat, 10 February 2007 12:45 Go to next message
Averell
Messages: 17
Registered: November 2006
Junior Member
Hi all,

I'm having problem to use sqlplus to unload CHAR(1) column with data = chr(0).
I have a table named T: (f1 number, f2 char(1), f3 number)

I want to unload data from this table using sqlplus. My unload script is as follow:
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set trimspool on
set termout off
spool ytmpz.sql
select f1 || ',' || f2 || ',' || f3 from T;
spool off
exit

Then on OS command line:
sqlplus -s un/pw @dump.sql


I got a problem with this: for all records with f2 = chr(0), the corresponding data in spooled file is replaced a space character (32).
I couldn't know how to solve this problem. Could you please help?
I'm using Oracle 7.3.3.5.0 on Unix.

Thank you very much!
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218815 is a reply to message #218814] Sat, 10 February 2007 14:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I don't have a 7.3 database or documentation for that matter to confirm if NVL is available, but if so, you can use:

SQL> desc test_null
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               CHAR(1)
 COL3                                               NUMBER

SQL> insert into test_null (select 1, null, 1 from dual union all
  2  select 2, ' ', 2 from dual union all
  3  select 3, 'A', 3 from dual);

3 rows created.

SQL> set null *
SQL> select * from test_null;

      COL1 C       COL3
---------- - ----------
         1 *          1
         2            2
         3 A          3


SQL> select col1||','||nvl(col2, chr(32))||','||col3 from test_null;

COL1||','||NVL(COL2,CHR(32))||','||COL3
--------------------------------------------------------------------------------
1, ,1
2, ,2
3,A,3
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218829 is a reply to message #218814] Sat, 10 February 2007 22:50 Go to previous messageGo to next message
Averell
Messages: 17
Registered: November 2006
Junior Member
Thank ebrian! But that's not my problem. I don't think my problem related to null issue.
SQL> desc test_null
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               CHAR(1)
 COL3                                               NUMBER

SQL> insert into test_null (select 1, ' ', 1 from dual union all
  2  select 2, chr(0), 2 from dual);

2 rows created.

SQL> select * from test_null;

      COL1 C       COL3
---------- - ----------
         1            1
         2            2


SQL> select col1||','||col2||','||col3 from test_null;

COL1||','||COL2||','||COL3
--------------------------------------------------------------------------------
1, ,1
2, ,2

My problem is: in the spooled file, 1st row and 2nd row are same, despite of the fact that in database, they are different.
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218832 is a reply to message #218829] Sun, 11 February 2007 00:44 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
CHR(0) is a strange beast and has seemed to morph between versions. You may want to review this link.
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218838 is a reply to message #218832] Sun, 11 February 2007 03:02 Go to previous messageGo to next message
Averell
Messages: 17
Registered: November 2006
Junior Member
Thank ebrian.

chr(0) is a beast.
I dont know what I could do now. I wanted to export data from that table to flat file, then use sqlldr to load to another table on another database. Whatever I do, in my new table, I get a space (32) or a zero (48).
Do you have any suggestion?
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218839 is a reply to message #218838] Sun, 11 February 2007 04:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot use spool to unload binary data. chr(0) should be seen as binary data (non-printable).
Can't you do a decode to another character that is not used in this column?

[Updated on: Sun, 11 February 2007 04:05]

Report message to a moderator

Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218848 is a reply to message #218839] Sun, 11 February 2007 09:36 Go to previous messageGo to next message
Averell
Messages: 17
Registered: November 2006
Junior Member
I want to load to another db (oracle 8.1) using sdlloader - direct mode. If I use conventional mode, I can use ascii(col2) to spool, then use chr() function to load. But this mode is much slower.
I don't know how to decode data while loading with sqlldr - direct mode.
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218851 is a reply to message #218814] Sun, 11 February 2007 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
why are you going out to flat file rather than just using DBLINK directly between Oracle databases?
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218860 is a reply to message #218814] Sun, 11 February 2007 11:45 Go to previous messageGo to next message
Averell
Messages: 17
Registered: November 2006
Junior Member
I have some problems with the network/firewall.

Beside that, while using a single insert/select command via DBLINK, I dont know how to monitor the process.

I have to copy more than 10 million records, each with a size of about 100 bytes. It took about 30 mins to spool the data to flat file. I can't estimate how long will it take to run the insert command via DBLINK.

I also couldn't know how big the rollback segment on the source db should be.

If I can solve those problems above, DBLINK could be the best choice. Could you please help me on that.
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218861 is a reply to message #218814] Sun, 11 February 2007 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I also couldn't know how big the rollback segment on the source db should be.
SELECT does not generate ROLLBACK. What is to be "rolled back" when only reading rows?
Re: Unable to use sqlplus to unload CHAR(1) column with data = chr(0) [message #218884 is a reply to message #218861] Sun, 11 February 2007 21:37 Go to previous message
Averell
Messages: 17
Registered: November 2006
Junior Member
Select command does need rollback segment. As I know, that is not for the session that runs the select command itself, but for other sessions updating the database while select is running.

I have many times received oracle error: "Snapshot too old..." while running select.
Previous Topic: I get an error when i try to set autorace on ..any help
Next Topic: Count rows before inserting data.
Goto Forum:
  


Current Time: Thu Dec 08 14:23:12 CST 2016

Total time taken to generate the page: 0.20651 seconds