Oracle-L: Re: Solved - RE: UTL

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Mon, 27 Oct 2003 15:59:26 -0800
Message-ID: <F001.005D4B56.20031027155926@fatcity.com>

Raj,

Jamadagni, Rajendra wrote:
> Read if you are interested ...
>
> Finally I got some time and luckily the largest message to use with dbms_profiler.
> And the results shocked me .... dbms_profiler showed me that instead of utl_raw, > substr() was the culprit. Remember my operation is character by character.

Could you please show dbms_profiler output data? I'd also suggest to remove everything related to TCP/IP out from the code -- to get the clear picture.

Some questions/suggestions, if you do not mind

. I do not think that you need utl_raw to do byte by byte xor operation -- you could do it using BITAND -- it should be faster.

. What's the point to do it char by char in general? Do you modify encryption key making it dependent on each given char in the string? If not why not to use something like the code below (see r1), hope I did not make any mistake:

VAR r1 VARCHAR2(256);
VAR r2 VARCHAR2(256);
VAR r3 VARCHAR2(256);
DECLARE
  r_key                             RAW(1) := '41'; -- hex
  r_key_n                           BINARY_INTEGER := 65; -- dec

  l_n                               BINARY_INTEGER; -- ASCII of current char

  -- string to be "encrypted"
  l_string                          VARCHAR2(128) := 'AZBYCXDWEVFUGT';
  -- its length
  l_string_len                      BINARY_INTEGER := NVL(LENGTH(l_string), 0);

BEGIN

string
one can define utl_raw.copies(r_key, 128) as a constant, if it's possible. :r1 := utl_raw.substr(utl_raw.bit_xor(utl_raw.cast_to_raw(l_string), utl_raw.copies(r_key, 128)), 1, l_string_len);
char by char :r2 := ''; FOR i IN 1..l_string_len LOOP :r2 := :r2 || utl_raw.bit_xor(utl_raw.cast_to_raw(SUBSTR(l_string, i, 1)), r_key); END LOOP;
bitand :r3 := ''; FOR i IN 1..l_string_len LOOP l_n := ASCII(SUBSTR(l_string, i, 1)); :r3 := :r3 || TO_CHAR(BITAND(-BITAND(-l_n - 1, -r_key_n - 1) - 1, -BITAND(l_n, r_key_n) - 1), 'FM0X'); END LOOP;

END;
/
PRINT r1
PRINT r2
PRINT r3

BTW, you have double conversion to ASCII then back to CHR (lines 6 and 7) -- it's not dramatic but it can be eliminated.

HTH.   1 msglen := LENGTH (msg_text);
  2 nCharsSent := 0;
  3 p('Encrypting data...');
  4 FOR i IN 1 .. msglen
  5 LOOP
  6 ntcpchar := ASCII (SUBSTR (msg_text, i, 1));   7 r_chr := utl_raw.cast_to_raw(CHR(ntcpchar));   8 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'xxxx');   9 tcpmsglen := UTL_TCP.write_text (gv_tcp_conn, CHR(nenctcpchar), NULL); 10 nCharsSent := nCharssent + 1;
11 IF MOD(ncharssent,128) = 0 THEN
12 p('Before Flush ...');
13 UTL_TCP.FLUSH (gv_tcp_conn);
14 p('Connection Flushed at ' || ncharssent); 15 END IF;
16 --
17 END LOOP; -- FOR i IN 1 .. msglen

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Oct 27 2003 - 17:59:26 CST

Re: Solved - RE: UTL_RAW and slowness