Tom,

I have a little different question related to clob.

Other than PL/SQL code is there a direct way to "select" contents of a table in clob? For example, is it possible to select contents of EMP table in clob format. I guess I am talking about something like XMLTYPE.getClobVal() but without all the XML tags.

thanks

Tom,
Thanks for yout reply. Here is a little background on the question above. This may take the discussion into a different direction.

The idea is to stop users from sending duplicate filenames. I want to generate a hash value for a set of data in a table. And than I want to compare that hash value against previously generated hash values. I do understand that with a minor change in the file I would end up getting a different hash value. But that's ok. We are just trying to catch human errors (someone sending same file again without knowledge, for example).

I was going to generate a clob out of the contents of the table and create a hash value for it.

Could you please give me some suggestions as to how to go about this? Is my approach wrong? Is there a better way to do this?
thanks

I wrote some trial code to implement this feature. I don't know how good or bad it is. I tried it with 30,000 rows, It performed pretty well.

I would really appreciate it if you would review the code at http://kirtandesai.com/write/2007/04/03/duplicate-file-check/

Below is the stored procedure that does it.

CREATE OR REPLACE PROCEDURE clob_to_hash IS
  i number(5);
  mychar VARCHAR2(4000);
  myclob CLOB;
  myraw raw(2000);
  rec_cnt NUMBER(2);
  CURSOR mycur1 IS
  SELECT column_name AS field_name FROM sys.all_tab_cols WHERE owner = 'KSDESAI' AND TABLE_NAME = 'EMP' ORDER BY column_id;
  mycur mycur1%ROWTYPE;
  col_names VARCHAR2(4000);
BEGIN
  i:=0;
  OPEN mycur1;
  LOOP
    FETCH mycur1 INTO mycur;
    EXIT WHEN mycur1%NOTFOUND;
    IF i = 0 THEN
      col_names := mycur.field_name;
    ELSE
      col_names := col_names || ',' || mycur.field_name;
    end if;
    i:=i+1;
  END LOOP;
  close mycur1;
  SELECT xmlagg(xmlelement("event",col_names)).getclobval() AS abc INTO myclob FROM scott.emp;
  myraw := dbms_crypto.hash(src => myclob, typ => dbms_crypto.hash_md5);
  mychar := rawtohex(myraw);
  SELECT COUNT(*) INTO rec_cnt FROM hashtest WHERE mchar = mychar;
  IF rec_cnt = 0 THEN
    INSERT INTO hashtest VALUES(mychar);
  ELSE
    INSERT INTO hashtest VALUES('duplicate data set');
  END IF;
  COMMIT;
END;

thanks

I made a change to the stored procedure. First time I didn't check the value in the clob. It was creating hash values for the string created by concatenation of column names and not the values in those columns.
I fixed it with dynamic sql. Code is not so elegant looking but it's good enough for my test.

create or replace PROCEDURE clob_to_hash IS 
i number(5);
mychar VARCHAR2(4000);
myclob CLOB;
myraw raw(2000);
rec_cnt NUMBER(2);
CURSOR mycur1 IS
              SELECT column_name AS
              field_name
              FROM sys.all_tab_cols
              WHERE owner = 'SCOTT'
               AND TABLE_NAME = 'EMP'
              ORDER BY column_id;
mycur mycur1%ROWTYPE;
col_names VARCHAR2(4000);
mysqlstr  varchar2(4000);
BEGIN
i:=0;
  OPEN mycur1;
    LOOP
  
  FETCH mycur1 INTO mycur;
    EXIT WHEN mycur1%NOTFOUND;
  IF i = 0 THEN
    col_names := mycur.field_name;
  ELSE
    col_names := col_names || ',' || mycur.field_name;
  end if;
  i:=i+1;
  END LOOP;
close mycur1;
  mysqlstr := 'SELECT xmlagg(xmlelement("emp",' || col_names || ')).getclobval() FROM scott.emp';
  execute immediate mysqlstr INTO myclob;
  myraw := dbms_crypto.hash(src => myclob,   typ => dbms_crypto.hash_md5);
  mychar := rawtohex(myraw);
  SELECT COUNT(*)
  INTO rec_cnt
  FROM hashtest
  WHERE mchar = mychar;
  IF rec_cnt = 0 THEN
    INSERT
    INTO hashtest
    VALUES(mychar);
  ELSE
    INSERT
    INTO hashtest
    VALUES('duplicate data set');
  END IF;

  COMMIT;
END;

very simple