CLOB insertion

[sona]

hai

i have a tabe like this
Table name :test with fields
A number(20)
B clob


When i give
SQL> insert into test values(1,'sdfsdf');
it gets inserted successfully.

but how to do in an anonymous block by assigning to a temportary clob variable

declare
d clob;
begin
d:='sdf' - this statement is throwing error
insert into test values(1,d);
commit;
end;
/

so when i want to assign a clob type to a clob variable what should i do

also when i want the input variable (clob type) to be passed to a procedure what should i do.

[Edited by sona on 09-04-2002 at 06:59 AM]

[TimHall]

Hi.

In Oracle8i you need to insert a record with an empty CLOB then update the CLOB. This can be done in one of two ways:

1) You do it the long way:

DECLARE
d CLOB;
BEGIN
INSERT INTO TEST (a, b)
VALUES (1,EMPTY_CLOB());

SELECT b
INTO d
FROM test
WHERE a = 1
FOR UPDATE;

d := 'sdf';

UPDATE test
SET b = d
WHERE a = 1;
COMMIT;
END;
/

2) You return the LOB locator in the insert then simply set the CLOB value:

DECLARE
d CLOB;
BEGIN
INSERT INTO TEST (a, b)
VALUES (1,EMPTY_CLOB())
RETURNING b INTO d;

d := 'sdf';

COMMIT;
END;
/

Obviously the second is preferable since it's alot neater and removed the need for several database hits.

In Oracle9i the datatype conversions are alot neater when dealing with CLOBS, VARCHAR2 and LONG varibales. THis means you can do things like this:

DECLARE
d CLOB;
BEGIN
d := 'sdf';
INSERT INTO TEST (a, b)
VALUES (1,d);
COMMIT;
END;
/

DECLARE
d CLOB := EMPTY_CLOB();
BEGIN
d := 'sdf';
INSERT INTO TEST (a, b)
VALUES (1,d);
COMMIT;
END;
/

Cheers

[ggnanaraj]

I'm unable to get this...???

SQL> create table test(a number(20), b clob);

Table created.

SQL> insert into test values(1, 'sdsdf');

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2  d clob;
  3  begin
  4  insert into test(a,b)
  5  values(2,EMPTY_CLOB());
  6  select b into d from test where a = 2 for update;
  7  d := 'sdf';
  8  update test set b = d where a = 2;
  9  commit;
 10  end;
 11  /
d := 'sdf';
     *
ERROR at line 7:
ORA-06550: line 7, column 6:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored


SQL> declare 
  2  d CLOB;
  3  BEGIN
  4  INSERT INTO TEST (a, b)
  5  VALUES(2, EMPTY_CLOB()) 
  6  RETURNING b INTO d;
  7  
  8  d := 'sdf';
  9  
 10  COMMIT;
 11  END;
 12  /
d := 'sdf';
     *
ERROR at line 8:
ORA-06550: line 8, column 6:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored

SQL>

What am I doing wrong ?? I'm using Oracle 8.1.5 & 8.1.7

Please help.

Thanks.


[Edited by ggnanaraj on 10-08-2002 at 07:57 AM]

[ales]

declare
    d clob;
    text varchar2(32767);
    len integer;
begin
    text:=rpad('-',32767,'-');
    len:=length(text);
    insert into test(a,b)
      values(2,EMPTY_CLOB()) returning b INTO d;
    dbms_lob.write(d,len,1,text);
    commit;
end;
/

The d variable is of the CLOB type, it's a LOB locator and the direct update with a char value is not possible.

See also http://otn.oracle.com/docs/products/...a76940/toc.htm

[ggnanaraj]

Thanks. That sorted it out!!