Generating RATS Sequence

In this blog, my effort is to demonstrate the logic of generating the RATS Sequence.

All the numbers which are in the form of S(n)=( n+R(n)) is called RATS number. The sequence of RATS number is called RATS Sequence.

In the above formula : n denotes an Integer, R(n) denotes the Reversed Number, S(n) is an number , which is the sum of  the number n and its Reversal number R(n) , after sequencing the digits of the number in ascending order .

For further information on RATS Sequence, refer to   http://mathworld.wolfram.com/RATSSequence.html

Below code snippet demonstrates the basic logic for generating the RATS Sequence.

CREATE OR REPLACE PROCEDURE GENERATE_RATS_SEQUENCE(IN_FIRST_TERM IN NUMBER, IN_NTH_TERM IN NUMBER) IS
  lvRATSSequence VARCHAR2(32767):=NULL;
  lnPreviousTerm NUMBER:=0;
  lnRatsNumber NUMBER:=0;
  lnFirstTerm NUMBER:=0;
  FUNCTION GENERATE_RATS_NUMBER (IN_NUMBER IN NUMBER) RETURN NUMBER IS
    lnReverseNumber NUMBER:=0;
  BEGIN
    SELECT REVERSE(TO_CHAR(IN_NUMBER)) INTO lnReverseNumber FROM DUAL;
    RETURN SwapDigits((IN_NUMBER+ lnReverseNumber));
  END GENERATE_RATS_NUMBER;
BEGIN
  IF IN_FIRST_TERM IS NULL THEN
     lnFirstTerm:=1;
  ELSE
     lnFirstTerm:=IN_FIRST_TERM;
  END IF;
  lnPreviousTerm:=lnFirstTerm;
  lvRATSSequence:=lnFirstTerm;
  FOR I IN lnFirstTerm..IN_NTH_TERM LOOP   
    lnRatsNumber:= GENERATE_RATS_NUMBER(lnPreviousTerm);
    lnPreviousTerm:=lnRatsNumber;
    lvRATSSequence:=lvRATSSequence||','||lnPreviousTerm;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('RATS Sequence starting with '||IN_FIRST_TERM||' upto '||IN_NTH_TERM||' terms is : '||chr(10)||lvRATSSequence);
END GENERATE_RATS_SEQUENCE;

Sorting Collections in PL/SQL

In this blog, I made an attempt to demonstrate the usage of CAST and MULTISET pseudo-functions.  Pseudo functions are usually used with collections such as VARRAYS, NESTED TABLES and PL/SQL TABLES in Oracle. The below statement creates a user-defined Nested Table of primitive data type NUMBER.

CREATE OR REPLACE TYPE SwapTable IS TABLE OF NUMBER NOT NULL;
/

CAST pseudo function converts one collection type to another collection type.

MULTISET pseudo function returns a collection type records as a table.

The usage of CAST and MULTISET pseudo columns retrieves rows from a table as collection type.

The below function arranges the digits of a given number in ascending order.

CREATE OR REPLACE FUNCTION SwapDigits(IN_NUMBER IN NUMBER) RETURN NUMBER IS

ltSwappingTable SwapTable;

 ltSwappedTable SwapTable;

 lvSwappedNumber VARCHAR2(3000):=NULL;

BEGIN

 ltSwappingTable:= SwapTable ();

 ltSwappingTable.EXTEND(LENGTH(TO_CHAR(IN_NUMBER)));

 FOR I IN 1..LENGTH(TO_CHAR(IN_NUMBER)) LOOP

  ltSwappingTable(I):=SUBSTR(TO_CHAR(IN_NUMBER),I,1);

  DBMS_OUTPUT.PUT_LINE(ltSwappingTable(I));

 END LOOP;

 SELECT CAST(MULTISET(SELECT * FROM TABLE(ltSwappingTable) ORDER BY 1 ASC) AS SwapTable )

   INTO ltSwappedTable

   FROM DUAL;

 FOR J IN ltSwappedTable.FIRST..ltSwappedTable.LAST LOOP

  DBMS_OUTPUT.PUT_LINE(ltSwappedTable(J));

  lvSwappedNumber:=lvSwappedNumber||ltSwappedTable(J);

 END LOOP;

 RETURN TO_NUMBER(lvSwappedNumber);  

END SwapDigits;