|
|
|
|
|
|
| Re: how to build long data ie >32k in pl/sql? [message #117094 is a reply to message #116326] |
Mon, 25 April 2005 01:10   |
dhamayanthi
Messages: 17
Registered: April 2005
Location: India
|
Junior Member |
|
|
Thanks for ur reply...But I can't change it into clob datatype since i have to do some modifications in existing appl. and the table is referred by many procedures and functions.
and u said that long can hold 65535 chars. but in some sites i have seen that long pl/sql variable can have only 32000 or 32767. but same time in oracle database, long (as database column) can have upto 2gb of data. as if having 2gb of size, there should be some way to insert that much data. that s why i tried that. and i want to know is there any other options to store more than 32k by a pl/sql variable.
Thanks,
Dhamayanthi K.
and Steve Answered........
From that description, I would say the easiest solution would be to change the datatype on the table from LONG to CLOB or change the datatype defined in the application from CLOB to LONG or LONG VARCHAR (the easiest way to do this would be in your declaration declare the variable that holds the CLOB like this:
BEGIN
DECLARE
l_some_variable table_with_long_datatype.long_datatype_field%TYPE;
BEGIN
...rest of your code
Is there any reason why you won't be able to do this? Now that I am remembering about the LONG data type, it can hold 65,535 characters. The issue with casting is that it can cause data integrity problems. Remember, when you are storing values in variables it ALWAYS should be the same datatype as the field in the table.
Good luck,
Steve
|
|
|
|
| Re: how to build long data ie >32k in pl/sql? [message #117096 is a reply to message #117094] |
Mon, 25 April 2005 01:16 |
dhamayanthi
Messages: 17
Registered: April 2005
Location: India
|
Junior Member |
|
|
Hi,
Yes ....i tried that also. i just assigned data to the column type variable and i tried to insert. it is working fine when the size is <=32k but not more than that.
as for as i know and i thought that column type variable have same datatype as in table. but now only i came to know that the size is fixed in pl/sql(ie 32K) and even cannot be changed if we use Column type also.
and i have one more doubt. i tried many of alternatives. the foll. is one of them.
can we append data to long column with update command?
is it possible to use update or insert statement like:
insert into dha (id,f1) values (1,p_array(1) ||p_array(2));
or
of1 is varchar2(32000) or it can be long.
select f1 into of1 from dha where id=1;
update dha set f1 = of1 || p_array(2) where id=1;
The Table struct. is
id number
f1 long.
i just created a varry to hold >32k of data(32000 bytes of chunk)
create type varray(10) of varchar2(32000);
and i stored 32000 bytes of data in every subscript and tried to insert or update.
but my need is to build data >32k and have to insert into long column.
Expecting Reply.....from u.
Thanks,
Dhamayanthi K
Thanks Steve.....i feel that u r taking much pain regd this.
But i told u in the prev. mail about inserting or updating data with concate operation. is it possible to do? if i try that it is giving error like,
"ORA-01461: can bind a LONG value only for insert into a LONG column"
if i use 2 constants and i perform same concate operation, it is performing well.i don't know if i did any mistake there or long fiel will not allow to these operations.
and pls. give ur procedure to perform chunk operation(piecewise). finally i try that also.....
Thanks Once again....
Dhamayanthi K
and Steve Answered...
Its not a problem to share info with other developers.
Here is a procedure that will read a column of type BLOB and output it 250 characters at a time (limitation of DBMS_OUTPUT.PUT_LINE). I think in your procedure you shouldn't have to cast from RAW to VARCHAR2 because you are dealing with LONG. This just shows how to output the value piece-by-piece or what is known as piece-wise construction:
CREATE OR REPLACE PROCEDURE read_attBLOB
(p_id IN NUMBER)
IS
l_BLOB BLOB;
BEGIN
SELECT attBLOB
INTO l_BLOB
FROM sc_lob_data
WHERE doc_id = p_id;
--
-- DBMS_OUTPUT.ENABLE (1000000); ignore this line
DBMS_OUTPUT.PUT_LINE ('length of attBLOB: ' || DBMS_LOB.GETLENGTH (l_BLOB));
FOR i IN 0 .. FLOOR (DBMS_LOB.GETLENGTH (l_BLOB) / 250) LOOP
DBMS_OUTPUT.PUT_LINE
(UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (l_BLOB, 250, i * 250 + 1)));
END LOOP;
END;
/
Actually, you might have to cast from CLOB to LONG although I'm not sure the UTL packages make use for this. You may still have to do some reading because your datatypes are different (you never know, it may be easier using CLOB in this procedure than BLOB or it may be the same, i am not sure)
The key here is the For i IN 0..FLOOR line because that loop is doing the heavy lifting in this procedure. That is, essentially, the piece-wise construction.
Good luck,
Steve
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
