Home » SQL & PL/SQL » SQL & PL/SQL » How to extract text from a long datatype
icon7.gif  How to extract text from a long datatype [message #202587] Fri, 10 November 2006 08:08 Go to next message
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 #202600 is a reply to message #202587] Fri, 10 November 2006 08:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The error you're getting comes from dbms_output, not from the other code. You can raise the buffersize to 1000000 by using:
SQL> SET SERVEROUT ON SIZE 1000000


MHE
Re: How to extract text from a long datatype [message #202602 is a reply to message #202600] Fri, 10 November 2006 09:07 Go to previous messageGo to next message
albert21
Messages: 7
Registered: November 2006
Location: France
Junior Member
thank you,

i have the same error but later
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: à "GENIO5.SHOWLONG", ligne 29
ORA-06512: à ligne 2

I will try a bigger limit !

Re: How to extract text from a long datatype [message #202604 is a reply to message #202602] Fri, 10 November 2006 09:12 Go to previous messageGo to next message
albert21
Messages: 7
Registered: November 2006
Location: France
Junior Member
I have the same error
and if i try a bigger value i have a out of rang error.

SP2-0547: size option 1000000000 out of range (2000 through 1000000)
Re: How to extract text from a long datatype [message #202605 is a reply to message #202600] Fri, 10 November 2006 09:16 Go to previous messageGo to next message
albert21
Messages: 7
Registered: November 2006
Location: France
Junior Member
I have the same error
and if i try a bigger value i have a out of rang error.

SP2-0547: size option 1000000000 out of range (2000 through 1000000)
Re: How to extract text from a long datatype [message #202608 is a reply to message #202604] Fri, 10 November 2006 09:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is a limitation of the dbms_output package. Look here for a way out.

But why don't you use utl_file to write to the file?

MHE
Re: How to extract text from a long datatype [message #202609 is a reply to message #202605] Fri, 10 November 2006 09:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the data is that large, then you won't be able to use DBMS_OUTPUT to print the contents.
You could use UTL_FILE instead.

What exactly are you trying to achieve - possibly we can think of an alternative way of achieving it.
icon7.gif  Re: How to extract text from a long datatype [message #202611 is a reply to message #202609] Fri, 10 November 2006 09:27 Go to previous messageGo to next message
albert21
Messages: 7
Registered: November 2006
Location: France
Junior Member
Hi,
The field is logging XML text;
I just want to extract the text from the long data field to an ASCII file and then view the XML file.
Re: How to extract text from a long datatype [message #202621 is a reply to message #202611] Fri, 10 November 2006 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use a CLOB instead of a LONG.
Better, use an XMLTYPE.

Failing that, I think you'll have to use UTL_FILE to write the file out to the server.
Re: How to extract text from a long datatype [message #202882 is a reply to message #202621] Mon, 13 November 2006 02:58 Go to previous messageGo to next message
albert21
Messages: 7
Registered: November 2006
Location: France
Junior Member
I cannot use a Clob because it is in a package; the program is like that, and if I want that it works I will better not modify this table.
Re: How to extract text from a long datatype [message #202888 is a reply to message #202882] Mon, 13 November 2006 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, it looks like you'll be using UTL_FILE to write the details out to a file on the server then.
Re: How to extract text from a long datatype [message #202894 is a reply to message #202888] Mon, 13 November 2006 04:06 Go to previous messageGo to next message
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
Re: How to extract text from a long datatype [message #280793 is a reply to message #202605] Wed, 14 November 2007 14:21 Go to previous message
whippsa
Messages: 2
Registered: November 2007
Location: USA
Junior Member
We can't use UTL_FILE here.
Shops are going to separate servers for data and applications. The directory has to be on the same server to create a database definition in Oracle.
as in:
CREATE DIRECTORY log_dir AS '/appl/gl/log';

That (to my understanding) can't point 'off server.'

Big problem for me. I like the define external for sql loader as well but can't use it without a defined directory.

Confused Sad
Previous Topic: How to store jpeg file in database
Next Topic: Help With Strings and Quotes
Goto Forum:
  


Current Time: Fri Dec 09 03:59:50 CST 2016

Total time taken to generate the page: 0.16292 seconds