Tags: company, concat, database, dept, description, mysql, ora-01489, oracle, sql, string, table, tbla, varchar2
ORA-01489 Result of string concat is too large
On Oracle 9i R2, I have a table tblA(id varchar2(14), title varchar2(500), company varchar2(500), description varchar2(4000), dept varchar2(250), unit varchar2(250)...)
I need to create a flat [demilited] file. The script I created works for about 10-15 rows before failing with ORA-01489 error.
Leave a comment...
- 14 Comments
- A concatenated string value cannot exceed 4000 characters. When you create a column by concatenating the columns, for some rows the length exceeds 4000 characters.#1; Sat, 23 Feb 2008 12:57:00 GMT
Then how come my script is working for the first 10-15 rows. I have checked the actual size and for these 10-15 rows, it does exceed 4000 chars for each row.
I wilI research the link you provided, Thanks.#2; Sat, 23 Feb 2008 12:58:00 GMT
- select lpad('a',4000,'*')||'b' from dual
ERROR at line 1:
ORA-01489: result of string concatenation is too long
As Kamal said, concatenated string cannot exceed 4000 chars.#3; Sat, 23 Feb 2008 12:59:00 GMT
- If you use the UTL_FILE package to create your flat delimeted file, you can retrieve your columns as individual columns and then output each one using the UTL_FILE.PUTF procedure.
Remember to use the final parameter in the UTL_FILE.FOPEN to specify the maximum line size to the maximum it can be (or 32767 which is the max it can take).
This way you don't have to select all the data concatenated together which is causing your problem.#4; Sat, 23 Feb 2008 13:00:00 GMT
- Have you tried spooling the table to a file?
sql> set heading off
sql> set html markup off
sql> set verify off
sql> set linesize <sum of col widths plus any literals>
sql> spool d:\myflatfile.txt;
sql> select * from mytable;
sql> spool off;
if you need to include a delimiter, such as a comma you can include literals in a column list to be spooled
select col_1, ',' , col_2, ',' , col_9 from mytable
Peter#5; Sat, 23 Feb 2008 13:01:00 GMT
the dump_csv script on the asktom site is giving me following errors:
1. ORA-29280 Invalid directory
I know the directory exists. I have spooled to this directory several times successsfully.
I created the dump_cvs funtion using SQL PLUS and am writing a simple PL/SQL script to execute it.
Any suggestions?#6; Sat, 23 Feb 2008 13:02:00 GMT
- >> I know the directory exists. I have spooled to this directory several times successsfully.
The directory must be a valid directory on the server where the database is running, not a directory on your local hard drive.#7; Sat, 23 Feb 2008 13:03:00 GMT
- Please read "demo setup" of this website http://www.psoug.org/reference/utl_file.html#8; Sat, 23 Feb 2008 13:04:00 GMT
SQL> create table t(v1 varchar2(4000), v2 varchar2(4000));
Table created. SQL> insert into t values (lpad('x',4000,'x'), lpad('y',4000,'y'));
1 row created. SQL> select length(v1||v2) from t;
select length(v1||v2) from t *ERROR at line 1:ORA-01489: result of string concatenation is too long SQL> select length(to_clob(v1)||to_clob(v2)) from t;
so you just have to convert to clob before concatenation#9; Sat, 23 Feb 2008 13:05:00 GMT
- Thanks to most of the links and suggestions. I have got the script working to 90%.
Here is the 10% question:
p_query = 'select id, '', '1234', name from test_table where testdate < '01-jan-2006' ';
How do I handle the embedded quotes
Thanks#10; Sat, 23 Feb 2008 13:06:00 GMT
- Just double each single quote within the string
p_query = 'select id, null, ''1234'', name from test_table where testdate <
to_date(''01-jan-2006'', ''DD-MON-YYYY'') ';#11; Sat, 23 Feb 2008 13:07:00 GMT
- Never mind with the previous question... I guess its end of week. My mind is not working any more...#12; Sat, 23 Feb 2008 13:08:00 GMT
- Okay I give up... my deadlines due...
when I run the following I get the result that I want
chkCnt := dump_csv('
', '|', '/ora/utl/dir/, 'myfile.txt');
However a more complex query gives me following error
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 375
ORA-06512: at "SYS.UTL_FILE", line 696
ORA-06512: at "schema.DUMP_csv", line 47
ORA-06512: at line 4
I see the file is created, but no values in them?
Please advice#14; Sat, 23 Feb 2008 13:10:00 GMT