Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Michigan/Pennsylvania
    Posts
    4

    Unanswered: insert to/select from queries with VARCHARs and CLOBs

    Good evening..

    Does anyone know if you can do INSERT INTO x (clob_column) SELECT varchar_column FROM y? And vice versa?

    Thanks much.

    -Julie

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Julie,

    I have my thoughts on it, but why not try it to find out? It may sound unhelphull but I don't mean it to be.

    Regards
    Bill

  3. #3
    Join Date
    May 2003
    Location
    Michigan/Pennsylvania
    Posts
    4

    ah, the simple solutions are always best

    Hi Bill...

    Life would be easy if I could try it out myself, but unfortunately I don't have the database software. I've been charged with finding the answer for some developers (for several versions of Oracle, DB2, and SQL Server), and I don't get the impression they're keen on experimenting for themselves, lol. I've been reading documentation all day without any luck; the post is sort of a last resort. But thanks anyway... I appreciate the reply.

    -Julie

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Julie,

    In that case, in the lifelong aim to be the fountain of all wisdom (as opposed to being the fountain of all manner of sh*te which some people claim I am).. I offer the following on Oracle 8.1.7 ....

    create table t_clobs (c_clob clob);
    table created

    create table t_varchar2 (c_varchar2 varchar2(100));
    table created

    insert into t_clobs (c_clob) values ('smallstring');
    1 rows inserted

    insert into t_varchar2 (c_varchar2) values ('smallstring')
    1 rows inserted

    insert into t_clobs (c_clob) (select c_varchar2 from t_varchar2)
    1 rows inserted

    insert into t_varchar2 (c_varchar2) (select c_clob from t_clobs)
    ORA-00932: inconsistent datatypes

    insert into t_varchar2 (c_varchar2) (select dbms_lob.substr(c_clob,1,100) from t_clobs)
    2 rows inserted

    It appears that a straight insert of varchar to clob is no problem (presumably because the possible width of a clob will always be able to accomodate any varchar2). Howver the reverse isn't possible without giving Oracle some idea that you already know that truncation may have to take place.

    This may also differ depending on whether the result set is going to pass through the client or not, some clients (and connection types of Net8 and earlier version of SqlNet have their own limitations on passing clobs as straight datatypes to/from a client, as opposed to Pl/Sql). This explains the existence of Oracle's DBMS_LOB package.

    However, disregarding all of the above, I think your developers are trying to be clever, it's a bum question. Certainly how/why/whatever a dbms handles clobs has never been an issue for any of the developers I've known, and if your developers were worth their salt they could have ran the above in the same time it took me.

    I'm intrigued, please tell me why they need to know :-)

    I would expect that SqlServer has it's own lob implementation, as has DB2 etc.

    HTH
    Bill

  5. #5
    Join Date
    May 2003
    Location
    Michigan/Pennsylvania
    Posts
    4

    oh, ye font of wisdom!

    Hi Bill,

    My impression is that it has something to do with giving 8i the appearance of column renaming/retyping, but I could be mistaken.

    Thanks so much for your help :-)

    Best,
    Julie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •