| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Passing Long RAW
Hi
You cannot assign a long col. to a variable in pl/sql.
it is good to know:(copied from Oracle Doc)
LONG columns store variable length character strings
containing up to 2 gigabytes.
The use of LONG values are subject to some restrictions:
o A table cannot contain more than one LONG column.
o LONG columns cannot appear in integrity constraints
(except for NULL and NOT NULL constraints).
o LONG columns cannot be indexed.
o A stored function cannot return a LONG value.
o Within a single SQL statement, all LONG columns,
updated tables, and locked tables must be located
on the same database.
set operators (UNION, UNION ALL, INTERSECT, and MINUS). o select lists of CREATE TABLE...AS SELECT statements;
LONG columns cannot be referenced when you create a
table with rows returned by a subquery .
o select lists of subqueries in INSERT statements;
LONG columns cannot be referenced when you insert
rows returned by a subquery into a table or view
(INSERT INTO...SELECT...)
into a LONG column.
o If data from a LONG column can be converted to a
constrained datatype (such as CHAR or VARCHAR2),
a LONG column can be referenced in a SQL statement
within a trigger. Note that the maximum length
for these datatypes is 32 Kbytes.
o Variables or arguments in triggers or PL/SQL program
units cannot be declared using the LONG datatype.
o :NEW and :OLD cannot be used with LONG columns.
However there are some ways like using dbms_sql package, pro*c or oci.
Anyway let's discuss the issue more. As we understand We can insert a long into a long database column. On the other end we cannot select a value longer than 32760 bytes from a long column into a long variable in pl/sql. (this is true for long raw as well). Right? If we need to exceed 32K we may use dynamic sql. (one method) the following is a sample for manipulating long data in pl/sql:
SQLWKS> drop table long_temp
2> /
Statement processed.
SQLWKS> create table long_temp
2> (rec_id varchar2(10),
3> long_text LONG)
4> /
2> values('1', lpad('fjdfsjsdjlk', 32000,
'lsd[]234]234234]\423900@#$)(@#$*$@@&($@#'))
3> /
1 row processed.
...........that was 32000K because lpad function. don't expect me to
type,copy/paste.
...........this is just a sample.
SQLWKS> commit
Statement processed.
SQLWKS> /
SQLWKS> CREATE OR REPLACE PROCEDURE readlongdata(rec_id IN NUMBER) IS
2> data_chunk VARCHAR2(254); -- these can go higher
3> chunk_size NUMBER:=254; -- up to 32k
4> chunk_size_returned NUMBER;
5> mycursor NUMBER;
6> sql_stmt VARCHAR2(1024);
7> cur_pos NUMBER:=0;
8> rows NUMBER;
9> dummy NUMBER;
10> BEGIN
11> sql_stmt:= 'SELECT long_text FROM long_temp WHERE rec_id =
:doctoget';
12> mycursor:= dbms_sql.open_cursor;
13> dbms_sql.parse(mycursor, sql_stmt, dbms_sql.v7);
14> dbms_sql.bind_variable(mycursor, ':doctoget', rec_id);
15> dbms_sql.define_column_long(mycursor, 1);
16> dummy := dbms_sql.execute(mycursor);
17> rows:=dbms_sql.fetch_rows(mycursor);
18> LOOP
19> dbms_sql.column_value_long(mycursor, 1, chunk_size, cur_pos,
data_chunk, chunk_size_returned);
20> dbms_output.put_line(data_chunk);
21> cur_pos:=cur_pos + chunk_size;
22> EXIT WHEN chunk_size_returned = 0;
23> END LOOP;
24> dbms_sql.close_cursor(mycursor);
25> END readlongdata;
Server Output ON
SQLWKS> begin
2> readlongdata('1');
3> end;
4> /
SQLWKS>
Also You can manipulate 2G data in a precompiler program
with regular embedded SQL by using datatype equivalencing to declare
a host variable of type long varchar.
Also there should be an example under in pro*c folder.
I wrote an app a long time ago. I cannot locate it though.
But it is same approach. IMHO Pro*c would be ideal solution.
one min, let me look at my oracle documentation. Yes it is sample4.pc. Analyze it please.
Another note would be there is a change in processing of those columns
in Oracle 8. There are other datatypes (clob, blob etc.) with increased
limits.
Or Oracle context option...
I assumed you have Oracle7.
Good Luck
Hakan Eren
Prakash wrote:
> > Can anyone tell me how to assign Long raw to a variable? Here is my > problem: > > When I assign long raw data from my database to a variable, in my Stored > Procedure, it accepts if the value is < 32 K. Otherwise throws the > exception of Numeric or Value Error.But Database can hold upto 2 GB of > data on the same column. > > Please help me. Thanks > > Sent via Deja.com http://www.deja.com/ > Before you buy.
clean text for testing pl/sql code:
drop table long_temp
/
create table long_temp
(rec_id varchar2(10),
long_text LONG)
/
insert into long_temp
values('1', lpad('fjdfsjsdjlk', 32000,
'lsd[]234]234234]\423900@#$)(@#$*$@@&($@#'))
/
commit
/
CREATE OR REPLACE PROCEDURE readlongdata(rec_id IN NUMBER) IS
data_chunk VARCHAR2(254); -- these can go higher
chunk_size NUMBER:=254; -- up to 32k
chunk_size_returned NUMBER;
mycursor NUMBER;
sql_stmt VARCHAR2(1024);
cur_pos NUMBER:=0;
rows NUMBER;
dummy NUMBER;
BEGIN
sql_stmt:= 'SELECT long_text FROM long_temp WHERE rec_id = :doctoget';
mycursor:= dbms_sql.open_cursor;
dbms_sql.parse(mycursor, sql_stmt, dbms_sql.v7); dbms_sql.bind_variable(mycursor, ':doctoget', rec_id); dbms_sql.define_column_long(mycursor, 1);dummy := dbms_sql.execute(mycursor);
dbms_output.put_line(data_chunk);
cur_pos:=cur_pos + chunk_size;
EXIT WHEN chunk_size_returned = 0;
END LOOP;
dbms_sql.close_cursor(mycursor);
END readlongdata;
/
set serveroutput on
begin
readlongdata('1');
end;
/
Received on Thu Mar 16 2000 - 14:40:07 CST
 |
 |