 How to extract text from a long datatype [message #202587] |
Fri, 10 November 2006 08:08  |
albert21
Messages: 7
Registered: November 2006
Location: France
|
Junior Member |
|
|
Hello,
I have a problem to extract text from a long (very long) data type.
I have tried different PLSQL script but nothing is working.
Here is a script given on this site what does not work:
create or replace procedure showlong( p_query in varchar2 ) as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(250);
l_long_len number;
l_buflen number := 250;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0) then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos,l_long_val, l_long_len );
l_curpos := l_curpos + l_long_len;
dbms_output.put_line( l_long_val );
exit when l_long_len = 0;
end loop;
end if;
dbms_output.put_line( '====================' );
dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length' );
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end showlong;
/
When I call this procedure, I have some lines and the next error:
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: à "GENIO5.SHOWLONG", ligne 29
ORA-06512: à ligne 2
I have oracle 9i
I can’t change this in Blob,…
I just want to extract this Long datatype field in an ASCII file.
Does anyone know how to proceed ????????
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to extract text from a long datatype [message #202894 is a reply to message #202888] |
Mon, 13 November 2006 04:06  |
albert21
Messages: 7
Registered: November 2006
Location: France
|
Junior Member |
|
|
I want to use UTL_FILE but when I try i have errors.
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: invalid LOB
locator specified: ORA-22275
ORA-06512: à "SYS.DBMS_LOB", ligne 574
ORA-06512: à "GENIO5.WRITE_LONG", ligne 42
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à ligne 2
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
