Funny, I just did this today for a developer,
it does not go to a table, it uses Oracle 8i's new feature to use clob's
without a table to point to, thus memory only.
create or replace procedure
DisplayClob
is
myclob1 CLOB;
myclob2 CLOB;
buffer VARCHAR2(32000);
output VARCHAR2(32000);
amount NUMBER := 50;
offset NUMBER := 1;
clob_length NUMBER;
read_counter NUMBER;
begin
--
Create text for the buffer
buffer := '1234567890abcdefghijklmnopqrstuvwxyz';
DBMS_OUTPUT.PUT_LINE('Buffer: ' || buffer);
--
Determine the length of the buffer
amount := length(buffer);
DBMS_OUTPUT.PUT_LINE('Buffer Length: ' || TO_CHAR(amount));
--
Create lob locator (Temporary)
DBMS_LOB.CREATETEMPORARY(myclob1,FALSE,DBMS_LOB.CALL);
DBMS_LOB.CREATETEMPORARY(myclob2,FALSE,DBMS_LOB.CALL);
--
Write data into the clob initially.
DBMS_LOB.WRITE(myclob2, amount, 1, buffer);
FOR i IN 1 .. 1000
LOOP
DBMS_LOB.APPEND(myclob1, myclob2);
END LOOP;
--
Display size of clob
clob_length := DBMS_LOB.GETLENGTH(myclob1);
DBMS_OUTPUT.PUT_LINE('CLOB Size: ' || clob_length);
--
Display data from the temporary clob
read_counter := 0;
WHILE read_counter < clob_length
LOOP
DBMS_LOB.READ(myclob1, amount, offset, output);
DBMS_OUTPUT.PUT_LINE('Clob contents: ' || output);
read_counter := read_counter +
amount;
END LOOP;
DBMS_LOB.FREETEMPORARY(myclob1);
DBMS_LOB.FREETEMPORARY(myclob2);
end DisplayClob;
"Do not criticize someone until you walked a
mile in their shoes, that way when you criticize them, you are a mile a way and
have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
From: Yuval Arnon
[mailto:[EMAIL PROTECTED]]
Sent: Monday, September 17, 2001
2:40 PM
To: Multiple recipients of list ORACLE-L
Subject: Using CLOB
Hi,
How
can you assign a text to a CLOB column defined in the DECLARE section of
PL/SQL.I am getting an error on wrong type.
i.e..
SQL> l
1 declare
2 c clob ;
3 BEGIN
4 c := 'This is a test';
5* END;
SQL>
/
c :=
'This is a test';
*
ERROR
at line 4:
ORA-06550:
line 4, column 6:
PLS-00382:
expression is of wrong type
ORA-06550:
line 4, column 1:
PL/SQL:
Statement ignored
Yuval Arnon
