| Read from long field more then 4000 bytes to varchar (merged) [message #389194] |
Fri, 27 February 2009 10:10  |
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
|
|
|
|
|
|
|
|
|
|
| Read from long field more then 4000 bytes to varchar [message #389217 is a reply to message #389194] |
Fri, 27 February 2009 11:40  |
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 #389223 is a reply to message #389221] |
Fri, 27 February 2009 12:08 |
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 |
 |
BlackSwan
Messages: 26766
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 |
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 #389339 is a reply to message #389194] |
Sat, 28 February 2009 19:14 |
 |
Barbara Boehmer
Messages: 9106
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
