Oracle Package / Stored Procedure to generate the distinct scrambled combination of given elements

Due to my 'lazy' mind fidgeting around, I came up with the following :-)

1. the following article gives the complete code (yes, it's redistributable!) to generate the distinct alphabets that are possible, if the list of alphabets to be used are known [in the table - elements_for_scramble] (through Oracle PL/SQL - pkg_scrambler )


2. as a part of solving the business problem, you will also learn the basics of SQL and  PL/SQL in oracle).

So, without further ado, here it goes:

 

 

--elements_for_scramble FOR 3!!!

--=: LOADER :=

/

drop table elements_for_scramble;

/

create table elements_for_scramble

(

      sno     number(5,0),

      avlbl_alphabet  varchar2(5)

);

/

truncate table elements_for_scramble;

insert into elements_for_scramble values (0,NULL);

insert into elements_for_scramble values (1,'O');

insert into elements_for_scramble values (2,'N');

insert into elements_for_scramble values (3,'E');

insert into elements_for_scramble values (4,'K');

commit;

/*

=: FRAMEWORK :=

select

      AVLBL_ALPHABET as combinations

from

      elements_for_scramble

where

      sno   != 0

union all

select distinct

      t1.AVLBL_ALPHABET|| t2.AVLBL_ALPHABET || t3.AVLBL_ALPHABET as combinations

from

      elements_for_scramble t1,

      elements_for_scramble t2,

      elements_for_scramble t3

where

(     t1.sno != t2.sno

AND   t1.sno != t3.sno

AND   t2.sno != t3.sno);

*/

 

--=: GENERIC :=

create or replace package pkg_scrambler is

      procedure load_elements;

      procedure scramble_elements;

      function get_sql_stmt return varchar2;

end pkg_scrambler;

/

 

create or replace package body pkg_scrambler is

      procedure load_elements is

            v_no_elements     number(2,0);

      begin      

            dbms_output.put_line('Hello!');          

      end load_elements;

     

      procedure scramble_elements is

            v_sql_stmt        varchar2(4000);

      begin

            v_sql_stmt := get_sql_stmt();

            execute immediate v_sql_stmt;

      end scramble_elements;

     

      function get_sql_stmt return varchar2 is

            v_sql_stmt        varchar2(4000);

            v_sql_col1        varchar2(1000);

            v_sql_col2        varchar2(1000);

            v_sql_tbl         varchar2(500);

            v_sql_where       varchar2(1500);

            v_no_of_elements  number(2,0);

            v_index                 number(2,0);

            v_inner_index           number(2,0);

            v_bool                  number(1,0);

            v_pipe                  varchar2(4);

            v_comma                 varchar2(3);

            v_and             varchar2(5);

      begin

            v_bool := 0;

            select count(*) - 1 into v_no_of_elements from elements_for_scramble;

            for v_index in 1..v_no_of_elements

            loop

                  --if v_index = v_no_of_elements

                  --then

                  --    v_index_X := 1;

                  --else

                  --    v_index_X := v_index + 1;

                  --end if;

                 

                  v_sql_col1 := v_sql_col1 || v_pipe || 'nvl(t' || v_index || '.AVLBL_ALPHABET,'''')';

                  v_sql_col2 := v_sql_col2 || v_pipe || 'to_char(t' || v_index || '.SNO)';

                  v_sql_tbl := v_sql_tbl || v_comma || 'elements_for_scramble t' || v_index;

 

                  v_inner_index := v_index + 1;            

                  for v_inner_index in v_index + 1 .. v_no_of_elements

                  loop

                        if v_bool = 1 then

                              v_pipe := ' || ';

                              v_comma := ' , ';

                              v_and := ' AND ';

                        else

                              v_pipe := '';

                              v_comma := '';

                              v_and := '';

                        end if;

                        v_sql_where :=  v_sql_where || v_and || ' (t'|| v_index ||'.SNO != t'|| v_inner_index ||'.SNO OR t'|| v_index ||'.SNO = 0)';

                        v_bool := 1;

                  end loop;

            end loop;

           

            --v_sql_stmt := 'select AVLBL_ALPHABET as combinations from elements_for_scramble where sno != 0 union all ';

            v_sql_stmt :=  v_sql_stmt ||' select distinct ' || v_sql_col1 || ' as combination_AVLBL_ALPHABET,' || v_sql_col2 ||' as SNO FROM ' || v_sql_tbl || ' WHERE ' || v_sql_where ;

            v_sql_stmt := 'select distinct combination_AVLBL_ALPHABET from (' || v_sql_stmt || ') where combination_AVLBL_ALPHABET IS NOT NULL ORDER BY length(combination_AVLBL_ALPHABET), combination_AVLBL_ALPHABET;';

            return(v_sql_stmt);

      end get_sql_stmt;

end pkg_scrambler;

/