Table data ==> CLOB
Kirtan Desai, April 01, 2007 - 6:11 pm UTC
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
April 01, 2007 - 8:08 pm UTC
nope
duplicate file check
Kirtan Desai, April 01, 2007 - 10:28 pm UTC
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
file/dataset compare
Kirtan Desai, April 03, 2007 - 11:27 pm UTC
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
updates
Kirtan Desai, April 04, 2007 - 3:32 pm UTC
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
jitender sing negi, April 19, 2010 - 1:47 am UTC
very simple