Home » SQL & PL/SQL » SQL & PL/SQL » Read from long field more then 4000 bytes to varchar (merged)
Read from long field more then 4000 bytes to varchar (merged) [message #389194] Fri, 27 February 2009 10:10 Go to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
Hello,

I'm in a middle of a problem right now on our production environment.
I need to read and compare the information inside a long datatype column.
To do this I use the following function to read the long field:

create or replace function getlong_vw( p_tname in varchar2,
p_cname in varchar2,
p_cname2 in varchar2) return varchar2
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(32767);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
l_pos_start number := 1;
counter integer := 0;
l_long_txt varchar2(32767);
begin
dbms_sql.parse( l_cursor,
'select ' || p_cname || ' from ' || p_tname ||
' where view_name = ',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, '', p_cname2 );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getlong;
/

But with this, I can only read the first 4000 bytes inside the long field.
What I need, is read more then that, eventually until the 32Kb limit.
So I try to use a while loop before the column_value_long and put the data inside a 32Kb varchar2 variable and return in the end

Something like :

....

if (dbms_sql.fetch_rows(l_cursor)>0)
then
while l_pos_start <= 32767 loop
dbms_sql.column_value_long(l_cursor, l_pos_start, l_buflen, 0 ,
l_long_val, l_long_len );

l_pos_start := l_pos_start+l_buflen;
l_long_txt := l_long_txt||l_long_val;
end loop;
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_txt;
end getlong;

But I keep getting the error ORA-01007: variable not in select list

Can anyone help me, what I'm doing wrong?
How can I return the value inside a long field until the 32Kb and not only the first 4000 bytes?

Thank you very much in advance
Rui
Re: Help in production environment ASAP please [message #389201 is a reply to message #389194] Fri, 27 February 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

OraFAQ Forum Guide
Choose a topic title that makes sense. A good title might be "ORA-1555 during pl/sql loop"
A less fortunate choice might be "URGNT PLZ!!!!!!!!!!!!"

Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)


Regards
Michel

Re: Help in production environment ASAP please [message #389204 is a reply to message #389201] Fri, 27 February 2009 10:48 Go to previous messageGo to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
How can I edit my post and made the recommended changes?
How can I active the HTML code in forum. I don't see where is that option?

Sorry for any inconvenience.
Re: Help in production environment ASAP please [message #389206 is a reply to message #389194] Fri, 27 February 2009 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How can I edit my post and made the recommended changes?
This forum allows editing only for the last post in a thread.
You can add a new post (which is properly formatted).
>How can I active the HTML code in forum. I don't see where is that option?

What is "active the HTML"?
Read from long field more then 4000 bytes to varchar [message #389217 is a reply to message #389194] Fri, 27 February 2009 11:40 Go to previous messageGo to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
Hello,

I'm in a middle of a problem right now on our production environment.
I need to read and compare the information inside a long datatype column.
To do this I use the following function to read the long field:

CREATE OR REPLACE FUNCTION Getlong
(p_tname IN VARCHAR2,
p_cname IN VARCHAR2,
p_rowid IN ROWID)
RETURN VARCHAR2
AS
l_cursor INTEGER DEFAULT dbms_sql.open_cursor;
l_n NUMBER;
l_long_val VARCHAR2(32767);
l_long_len NUMBER;
l_buflen NUMBER := 4000;
l_curpos NUMBER := 0;
l_pos_start NUMBER := 1;
counter INTEGER := 0;
l_long_txt VARCHAR2(32767);
BEGIN
dbms_sql.Parse(l_cursor,'select '
||p_cname
||' from '
||p_tname
||' where rowid = :x',dbms_sql.native);

dbms_sql.Bind_variable(l_cursor,':x',p_rowid);

dbms_sql.Define_column_long(l_cursor,1);

l_n := dbms_sql.Execute(l_cursor);

IF (dbms_sql.Fetch_rows(l_cursor) > 0) THEN
WHILE l_pos_start <= 32767 LOOP
dbms_sql.Column_value_long(l_cursor,l_pos_start,l_buflen,0,l_long_val,
l_long_len);

l_pos_start := l_pos_start + l_buflen;

l_long_txt := l_long_txt
||l_long_val;
END LOOP;
END IF;

dbms_sql.Close_cursor(l_cursor);

RETURN l_long_txt;
END getlong;

If I comment the while loop function, the function works without problems return the first 4000 bytes in the input long field.
If I try to use the while to get more bytes I get the ORA-01007: variable not in select list
What I'm doing wrong?

Re: Read from long field more then 4000 bytes to varchar [message #389219 is a reply to message #389217] Fri, 27 February 2009 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
do NOT cross/multi-post

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Re: Read from long field more then 4000 bytes to varchar [message #389220 is a reply to message #389219] Fri, 27 February 2009 11:50 Go to previous messageGo to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
But you have told me to "add a new post (which is properly formatted)." !!

Probably I do something different that your recomendation. Sorry for that. The first post should be removed, Can I do it? or should I ask to the admin?
Re: Read from long field more then 4000 bytes to varchar [message #389221 is a reply to message #389217] Fri, 27 February 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
A THREAD or TOPIC is started by clicking on "NEW TOPIC" button.

A POST or REPLY is created by clicking on "REPLY" button.

besides your code is still not formatted!

Posting Guideline clearly state to use CUT & PASTE so we can see EXACTLY what you enter & how Oracle responds!
Re: Read from long field more then 4000 bytes to varchar [message #389223 is a reply to message #389221] Fri, 27 February 2009 12:08 Go to previous messageGo to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
Sorry about the new topic, can someone remove the old one for me?
I use the sqlformatter page to format my code.
On that page everything looks great, then I click on "Copy Formatted SQL To Clipboard" and use paste (right mouse click on the empty message). The final result looks it's on the post !!!
I agree that is not according with the formatted code in the sqlformatter page !!! Once again I'm doing something wrong for sure. This bad be a newbie !!!! Sorry for all.
Re: Read from long field more then 4000 bytes to varchar (merged) [message #389224 is a reply to message #389194] Fri, 27 February 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE FUNCTION Getlong_vw 
     (p_tname   IN VARCHAR2, 
      p_cname   IN VARCHAR2, 
      p_cname2  IN VARCHAR2) 
RETURN VARCHAR2 
AS 
  l_cursor     INTEGER DEFAULT dbms_sql.open_cursor; 
  l_n          NUMBER; 
  l_long_val   VARCHAR2(32767); 
  l_long_len   NUMBER; 
  l_buflen     NUMBER := 4000; 
  l_curpos     NUMBER := 0; 
  l_pos_start  NUMBER := 1; 
  counter      INTEGER := 0; 
  l_long_txt   VARCHAR2(32767); 
BEGIN 
  dbms_sql.Parse(l_cursor,'select ' 
                          ||p_cname 
                          ||' from ' 
                          ||p_tname 
                          ||' where view_name = ',dbms_sql.native); 
   
  dbms_sql.Bind_variable(l_cursor,'',p_cname2); 
   
  dbms_sql.Define_column_long(l_cursor,1); 
   
  l_n := dbms_sql.Execute(l_cursor); 
   
  IF (dbms_sql.Fetch_rows(l_cursor) > 0) THEN 
    dbms_sql.Column_value_long(l_cursor,1,l_buflen,l_curpos,l_long_val,l_long_len); 
  END IF; 
   
  dbms_sql.Close_cursor(l_cursor); 
   
  RETURN l_long_val; 
END getlong; 
/


Just use <code tags> as described in Posting Guidelines!
Re: Read from long field more then 4000 bytes to varchar (merged) [message #389225 is a reply to message #389224] Fri, 27 February 2009 12:21 Go to previous messageGo to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
Done, Thank You

I have send the code again because on your format the bind variable have dissapear.

CREATE OR REPLACE FUNCTION Getlong 
     (p_tname  IN VARCHAR2, 
      p_cname  IN VARCHAR2, 
      p_rowid  IN ROWID) 
RETURN VARCHAR2 
AS 
  l_cursor     INTEGER DEFAULT dbms_sql.open_cursor; 
  l_n          NUMBER; 
  l_long_val   VARCHAR2(32767); 
  l_long_len   NUMBER; 
  l_buflen     NUMBER := 4000; 
  l_curpos     NUMBER := 0; 
  l_pos_start  NUMBER := 1; 
  counter      INTEGER := 0; 
  l_long_txt   VARCHAR2(32767); 
BEGIN 
  dbms_sql.Parse(l_cursor,'select ' 
                          ||p_cname 
                          ||' from ' 
                          ||p_tname 
                          ||' where rowid = :x',dbms_sql.native); 
   
  dbms_sql.Bind_variable(l_cursor,':x',p_rowid); 
   
  dbms_sql.Define_column_long(l_cursor,1); 
   
  l_n := dbms_sql.Execute(l_cursor); 
   
  IF (dbms_sql.Fetch_rows(l_cursor) > 0) THEN 
    WHILE l_pos_start <= 32767 LOOP 
      dbms_sql.Column_value_long(l_cursor,l_pos_start,l_buflen,0,l_long_val, 
                                 l_long_len); 
       
      l_pos_start := l_pos_start + l_buflen; 
       
      l_long_txt := l_long_txt 
                    ||l_long_val; 
    END LOOP; 
  END IF; 
   
  dbms_sql.Close_cursor(l_cursor); 
   
  RETURN l_long_txt; 
END getlong; 


Resume:

If I remove the while loop function everything works well, but the function only read 4000 bytes.
IF I put the while loop function, I get the error ORA-01007: variable not in select list

Thank You
Re: Read from long field more then 4000 bytes to varchar (merged) [message #389319 is a reply to message #389225] Sat, 28 February 2009 08:25 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Copy and paste the entire error message
Re: Read from long field more then 4000 bytes to varchar (merged) [message #389339 is a reply to message #389194] Sat, 28 February 2009 19:14 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The problem is probably how you are calling the function. If you are trying to use the function in a sql select statement, then you are limited to 4000. If you are assigning the value using pl/sql, then you can get 32767. Notice the difference in the two methods below and the resulting lengths.

SCOTT@orcl_11g> declare
  2    v_var varchar2 (32767);
  3  begin
  4    select rpad ('*', 32767, '*') into v_var from dual;
  5    dbms_output.put_line (length (v_var));
  6  end;
  7  /
4000

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> declare
  2    v_var varchar2 (32767);
  3  begin
  4    v_var := rpad ('*', 32767, '*');
  5    dbms_output.put_line (length (v_var));
  6  end;
  7  /
32767

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Previous Topic: Regroup table rows by id
Next Topic: Multiple Recipients in SMTP
Goto Forum:
  


Current Time: Sat Dec 10 20:25:03 CST 2016

Total time taken to generate the page: 0.07348 seconds