RE: Using CLOB

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Mon, 17 Sep 2001 12:24:58 -0700
Message-ID: <F001.00390BEB.20010917114526@fatcity.com>

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.

<span

style='font-size:10.0pt;font-family:"Courier New";color:black;font-weight:bold'>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

<span

style='font-size:10.0pt;font-family:"Courier New";color:red;font-style:italic'>-- Create text for the buffer
buffer := '1234567890abcdefghijklmnopqrstuvwxyz';
DBMS_OUTPUT.PUT_LINE('Buffer: ' || buffer);

<span

style='font-size:10.0pt;font-family:"Courier New";color:red;font-style:italic'>-- Determine the length of the buffer
amount := length(buffer);
DBMS_OUTPUT.PUT_LINE(<font size=2 color="#0000f0"

face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:#0000F0'>'Buffer Length: '<font size=2 color=black
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";

color:black'> || TO_CHAR(amount));

<span

style='font-size:10.0pt;font-family:"Courier New";color:red;font-style:italic'>-- Create lob locator (Temporary)
DBMS_LOB.CREATETEMPORARY(myclob1,FALSE,DBMS_LOB.CALL);
DBMS_LOB.CREATETEMPORARY(myclob2,FALSE,DBMS_LOB.CALL);

<span

style='font-size:10.0pt;font-family:"Courier New";color:red;font-style:italic'>-- 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;

<span

style='font-size:10.0pt;font-family:"Courier New";color:red;font-style:italic'>-- Display size of clob
clob_length := DBMS_LOB.GETLENGTH(myclob1); DBMS_OUTPUT.PUT_LINE('CLOB Size: ' || clob_length);

<span

style='font-size:10.0pt;font-family:"Courier New";color:red;font-style:italic'>-- 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
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Oracle DBA
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Phone: (978) 322-5744
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Fax: (707) 885-2275

Fuelspot
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>73 Princeton Street
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>North, Chelmsford 01863

<span

style='font-size:10.0pt;font-family:Tahoma'>-----Original Message----- From: Yuval Arnon
[mailto:Yuval.Arnon_at_wwfent.com]
Sent: Monday, September 17, 2001
2:40 PM
To: Multiple recipients of list ORACLE-L
Subject: Using CLOB

<span

style='font-size:12.0pt'>

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

<span

style='font-size:10.0pt;color:blue;font-style:italic'>Yuval Arnon Received on Mon Sep 17 2001 - 14:24:58 CDT