-
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]
Good Judgement comes from Experience.
Experience comes from Bad Judgement
-
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
-
Not working ??
I'm unable to get this...???
Code:
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]
-
Code:
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
Ales The whole difference between a little boy and an adult man is the price of toys
-
Thanks. That sorted it out!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|