CLOB insertion
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: CLOB insertion

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    135
    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

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    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]

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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
  •  


Click Here to Expand Forum to Full Width