Skip to Main Content
  • Questions
  • Stored procedure to hash a text column in a table with one of the hashing methods

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Seham.

Asked: November 30, 2020 - 8:54 am UTC

Last updated: December 01, 2020 - 9:32 am UTC

Version: 1.0

Viewed 100+ times

You Asked

Hello Experts,

I have written the function that hashed a string but I donot know how to use this function to take in a column from another table to hash that column and then insert the hashed value into another hastable.

any help will be appreciated,

here is my hashing function

CREATE OR REPLACE Function Hash_msg_clob
       (msg IN clob)
        RETURN varchar2
        AS
        hashed_value CLOB;
   
   --added
   l_timestamp_begin   varchar2(100);
   l_timestamp_end     varchar2(100);
   l_interval          varchar2(100);
   
         
  BEGIN
   
   IF msg IS NULL THEN
     dbms_output.put_line('No clob was passed');
   ELSE
      l_timestamp_begin := dbms_utility.get_time;
      
      DBMS_OUTPUT.PUT_LINE('The started time is: '||   l_timestamp_begin);
      
      SELECT STANDARD_HASH(dbms_lob.substr(msg, 4000, 1))INTO hashed_value FROM dual; --default algorithm SHA-1
      
      l_timestamp_end  := dbms_utility.get_time;
      
      DBMS_OUTPUT.PUT_LINE('The finished time is: '|| l_timestamp_end);
      
      
     
      DBMS_OUTPUT.PUT_LINE(hashed_value);
     
      l_interval       := l_timestamp_end - l_timestamp_begin;
      DBMS_OUTPUT.PUT_LINE('The interval time is: '|| l_interval/100);
      RETURN hashed_value;
      
   END IF; 
   EXCEPTION 
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
     DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
     RAISE; 
     RETURN msg;
 END;

and we said...

If you want to hash a column and insert it in another table, just do an insert ... select, calling the function in the query:

create table src (
  c1 varchar2(100)
);
create table dest (
  c1 varchar2(100)
);

insert into src values ( 'Source string' );

insert into dest
  select hash_msg_clob ( c1 ) from src;
  
select * from dest;

C1                                         
0F02B0225FCC9E7AC77B548CC3938E0F134322AF    

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.