Hello Experts,
My requirement is simple. I need to convert tables data into equivalent binary value and insert it into corresponding table(The tables whose data needs to be converted are quite high may be in hundreds).
Function (below) to convert from decimal to binary
FUNCTION dec2bin (InputData IN NUMBER,NumberOfBits In Number) RETURN Varchar2 RESULT_CACHE IS
binval VARCHAR2(64);
N2 NUMBER := InputData;
BEGIN
IF InputData is not null then
WHILE ( N2 > 0 ) LOOP
binval := MOD(N2, 2) || binval;
N2 := TRUNC( N2 / 2 );
END LOOP;
IF NumberOfBits IS NOT NULL THEN
binval := LPad(binval,NumberOfBits,'0');
END IF;
END IF;
RETURN binval;
END dec2bin;
now when I call the function above on my table (size : 10k rows) it is taking 1Min 40Secs.
Since there are no build in function available to convert string/number value to binary I have to use the function above.
creating a functional index on table columns and changing function as deterministic may solve the problem but doing this activity on hundreds of tables look difficult.
SELECT dec2bin( DATA_COUNT, 16 ) DATA_COUNT, dec2bin( LOCDATA, 32 ) LOCDATA, dec2bin( HEIGHTDATA, 32 ) HEIGHTDATA
FROM MATERIAL_DATA
WHERE id = 'MTR1';
Above is the sample query that converts 3 columns to binary.
Please let me know how to go ahead with fixing this performance issue.
DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Thanks,
Imran.