Re: DOUBT - Oracle command to REPLACE data

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Sat, 2 Jul 2011 21:13:43 -0300
Message-ID: <CAJdDhaNnFNQ-mHjwUJzmC9HdnhnzKWqhRnj-Xxy4CSb+=cBHkw_at_mail.gmail.com>

Hello Stephane and Robert,
Many thanks for attention and suggestions.

The bit function sys_connect_by_path suggested worked fine as expected. Great .. Great ... Great

Look at the test evidences I did in the 11g. On next monday I will test the function running in 10.2.0.5 Oracle version.

I created a function to receive the map from and map to variables,

create or replace function F_CONCATE_BIT (MAPAFROM in VARCHAR2,
  MAPATO IN VARCHAR2)
return varchar2 is
  Result Varchar2(45);
begin
select val
  INTO RESULT
  from (select rn, replace(sys_connect_by_path(bit, '/'), '/', '') val

          from (select rn,
                       greatest(to_number(substr(val, rn, 1)),
                                to_number(substr(val2, rn, 1))) bit
                from (select    MAPAFROM val from dual)
                      cross join (select   MAPATO val2 from dual)
                      cross join (select rownum rn
                                    from dual
                                 connect by level <= 45))
          connect by rn = prior rn + 1
          start with rn = 1)

where rn = 45;

return(Result);
end F_CONCATE_BIT;
/

then , just run it :)
each 1 value will be replaced in the mapato variable, as expected.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

SQL> SQL> DECLARE 2 v_mapa_initial VARCHAR2(45);

3 v_mapa_result VARCHAR2(45);

4 TYPE t_mapa_replace IS TABLE OF VARCHAR2(45) INDEX BY BINARY_INTEGER;

5 v_mapa_replace t_mapa_replace;

6 v_i BINARY_INTEGER := 0;

8 BEGIN 9 DBMS_OUTPUT.ENABLE(1000000);

10 v_mapa_initial := '000000000000000000000000000000000000000000000';

11 v_mapa_replace(1) := '100000000000000000000000000000000000000000000';

12 v_mapa_replace(2) := '010000000000000000000000000000000000000000000';

13 v_mapa_replace(3) := '001000000000000000000000000000000000000000000';

14 v_mapa_replace(4) := '000100000000000000000000000000000000000000000';

15 v_mapa_replace(5) := '000010000000000000000000000000000000000000000';

16 v_mapa_replace(6) := '000001000000000000000000000000000000000000000';

17 v_mapa_replace(7) := '000000100000000000000000000000000000000000000';

18 v_mapa_replace(8) := '000000010000000000000000000000000000000000000';

19 v_mapa_replace(9) := '000000001000000000000000000000000000000000000';

20 v_mapa_replace(10) := '000000000100000000000000000000000000000000000';

21 v_mapa_replace(11) := '000000000010000000000000000000000000000000000';

22 v_mapa_replace(12) := '000000000001000000000000000000000000000000000';

23 v_mapa_replace(13) := '000000000000100000000000000000000000000000000';

24 v_mapa_replace(14) := '000000000000010000000000000000000000000000000';

25 v_mapa_replace(15) := '000000000000001000000000000000000000000000000';

26 v_mapa_replace(16) := '000000000000000100000000000000000000000000000';

27 v_mapa_replace(17) := '000000000000000010000000000000000000000000000';

28 v_mapa_replace(18) := '000000000000000001000000000000000000000000000';

29 v_mapa_replace(19) := '000000000000000000100000000000000000000000000';

30 v_mapa_replace(20) := '000000000000000000010000000000000000000000000';

31 v_mapa_replace(21) := '000000000000000000001000000000000000000000000';

32 v_mapa_replace(22) := '000000000000000000000100000000000000000000000';

33 v_mapa_replace(23) := '000000000000000000000010000000000000000000000';

34 v_mapa_replace(24) := '000000000000000000000001000000000000000000000';

35 v_mapa_replace(25) := '000000000000000000000000100000000000000000000';

36 v_mapa_replace(26) := '000000000000000000000000010000000000000000000';

37 v_mapa_replace(27) := '000000000000000000000000001000000000000000000';

38 v_mapa_replace(28) := '000000000000000000000000000100000000000000000';

39 v_mapa_replace(29) := '000000000000000000000000000010000000000000000';

40 v_mapa_replace(30) := '000000000000000000000000000001000000000000000';

41 v_mapa_replace(31) := '000000000000000000000000000000100000000000000';

42 v_mapa_replace(32) := '000000000000000000000000000000010000000000000';

43 v_mapa_replace(33) := '000000000000000000000000000000001000000000000';

44 v_mapa_replace(34) := '000000000000000000000000000000000100000000000';

45 v_mapa_replace(35) := '000000000000000000000000000000000010000000000';

46 v_mapa_replace(36) := '000000000000000000000000000000000001000000000';

47 v_mapa_replace(37) := '000000000000000000000000000000000000100000000';

48 v_mapa_replace(38) := '000000000000000000000000000000000000010000000';

49 v_mapa_replace(39) := '000000000000000000000000000000000000001000000';

50 v_mapa_replace(40) := '000000000000000000000000000000000000000100000';

51 v_mapa_replace(41) := '000000000000000000000000000000000000000010000';

52 v_mapa_replace(42) := '000000000000000000000000000000000000000001000';

53 v_mapa_replace(43) := '000000000000000000000000000000000000000000100';

54 v_mapa_replace(44) := '000000000000000000000000000000000000000000010';

55 v_mapa_replace(45) := '000000000000000000000000000000000000000000001';

57 v_i := 0;

58 WHILE v_i < 45

59 LOOP 60 v_i := v_i + 1;

61 v_mapa_result := f_concate_bit (v_mapa_initial, v_mapa_replace(v_i));

62 DBMS_OUTPUT.put_line (v_mapa_result);

63 v_mapa_initial := v_mapa_result;

64 END LOOP; 65 END; 66 /

 100000000000000000000000000000000000000000000

110000000000000000000000000000000000000000000

111000000000000000000000000000000000000000000

111100000000000000000000000000000000000000000

111110000000000000000000000000000000000000000

111111000000000000000000000000000000000000000

111111100000000000000000000000000000000000000

111111110000000000000000000000000000000000000

111111111000000000000000000000000000000000000

111111111100000000000000000000000000000000000

111111111110000000000000000000000000000000000

111111111111000000000000000000000000000000000

111111111111100000000000000000000000000000000

111111111111110000000000000000000000000000000

111111111111111000000000000000000000000000000

111111111111111100000000000000000000000000000

111111111111111110000000000000000000000000000

111111111111111111000000000000000000000000000

111111111111111111100000000000000000000000000

111111111111111111110000000000000000000000000

111111111111111111111000000000000000000000000

111111111111111111111100000000000000000000000

111111111111111111111110000000000000000000000

111111111111111111111111000000000000000000000

111111111111111111111111100000000000000000000

111111111111111111111111110000000000000000000

111111111111111111111111111000000000000000000

111111111111111111111111111100000000000000000

111111111111111111111111111110000000000000000

111111111111111111111111111111000000000000000

111111111111111111111111111111100000000000000

111111111111111111111111111111110000000000000

111111111111111111111111111111111000000000000

111111111111111111111111111111111100000000000

111111111111111111111111111111111110000000000

111111111111111111111111111111111111000000000

111111111111111111111111111111111111100000000

111111111111111111111111111111111111110000000

111111111111111111111111111111111111111000000

111111111111111111111111111111111111111100000

111111111111111111111111111111111111111110000

111111111111111111111111111111111111111111000

111111111111111111111111111111111111111111100

111111111111111111111111111111111111111111110

111111111111111111111111111111111111111111111

PL/SQL procedure successfully completed

SQL> Best Regards
Eriovaldo

On Sat, Jul 2, 2011 at 6:41 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> **
> Eriovaldo,
>
> I'd second Robert on this, this seems to me a very weak design - with 45
> bits you are far from what Oracle can store:
>
> SQL> set num 40
> SQL> select power(2, 46) - 1 from dual;
>
> POWER(2,46)-1
> ----------------------------------------
> 70368744177663
>
> SQL>
>
> Handling integers would allow you to use the bit functions and it's always
> possible to "translate" them to a binary-looking string.
>
> Alternatively, something like this could give you some ideas if you are
> powerless on the design:
>
> select val
> from (select rn, replace(sys_connect_by_path(bit, '/'), '/', '') val
> from (select rn,
> greatest(to_number(substr(val, rn, 1)),
> to_number(substr(val2, rn, 1))) bit
> from (select '111110000000000000000000000000000000000000000'
> val from dual)
> cross join (select
> '000000000000000000000000000000101010101010100' val2 from dual)
> cross join (select rownum rn
> from dual
> connect by level <= 45))
> connect by rn = prior rn + 1
> start with rn = 1)
> where rn = 45
> /
>
>
> --
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
>
> On 07/02/2011 11:20 PM, Robert Freeman wrote:
>
> Are you trying to do the equivalent of bitwise operations here? If so, I'd
> change your approach altogether and use actually bit manipulations using the
> functions that Oracle provides. I've used them for security/role/grouping
> designs quite successfully in the past and it's much faster than what you
> may be trying to do.
>
> Robert
>
>
> Robert G. Freeman
> Master Principal Consultant, Oracle Corporation, Oracle ACE
> Author of various books on RMAN, New Features and this shorter signature
> line.
> Blog: http://robertgfreeman.blogspot.com
>
> Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is
> just the opinion of one Oracle employee. I can be wrong, have been wrong in
> the past and will be wrong in the future. If your problem is a critical
> production problem, you should always contact Oracle support for assistance.
> Statements in this email in no way represent Oracle Corporation or any
> subsidiaries and reflect only the opinion of the author of this email.
>
>
> ------------------------------
> *From:* Eriovaldo Andrietta <ecandrietta_at_gmail.com><ecandrietta_at_gmail.com>
> *To:* ORACLE-L <oracle-l_at_freelists.org> <oracle-l_at_freelists.org>
> *Sent:* Sat, July 2, 2011 12:46:21 PM
> *Subject:* DOUBT - Oracle command to REPLACE data
>
> Hi Friends,
>
> I have the following issue:
>
> I need to store a column VARCHAR2(45).
>
> If I am inserting data for first time I can , for example, save
> something like this:
>
> 111110000000000000000000000000000000000000000
>
> When the line exists recorded in the table with the content above, and
> during my process I must updated the following, considering only character
> 1:
>
> 000000000000000000000000000000101010101010100
>
> The final result must be as below, replacing only the character 1:
>
> 111110000000000000000000000000101010101010100
>
> I know that I can do it:
> WHILE X < Y
> LOOP
> FINAL COLUMN := SUBSTR(N) || 1 || ....
> END LOOP;
>
> But is there any other way to do it, like regular expression or another
> command / instruction ?
>
> Regards
> Eriovaldo
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jul 02 2011 - 19:13:43 CDT

This message: [ Message body ]
Next message: Andre van Winssen: "Re: Patch 12311357 - 11.2.0.2.2 GI Patch Set Update"
Previous message: Stephane Faroult: "Re: DOUBT - Oracle command to REPLACE data"
In reply to: Stephane Faroult: "Re: DOUBT - Oracle command to REPLACE data"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message