Building a training set of tags for plsql

[ErikSchierboom]

Hello lovely maintainers :wave:

We've recently added "tags" to student's solutions. These express the constructs, paradigms and techniques that a solution uses. We are going to be using these tags for lots of things including filtering, pointing a student to alternative approaches, and much more.

In order to do this, we've built out a full AST-based tagger in C#, which has allowed us to do things like detect recursion or bit shifting. We've set things up so other tracks can do the same for their languages, but its a lot of work, and we've determined that actually it may be unnecessary. Instead we think that we can use machine learning to achieve tagging with good enough results. We've fine-tuned a model that can determine the correct tags for C# from the examples with a high success rate. It's also doing reasonably well in an untrained state for other languages. We think that with only a few examples per language, we can potentially get some quite good results, and that we can then refine things further as we go.

I released a new video on the Insiders page that talks through this in more detail.

We're going to be adding a fully-fledged UI in the coming weeks that allow maintainers and mentors to tag solutions and create training sets for the neural networks, but to start with, we're hoping you would be willing to manually tag 20 solutions for this track. In this post we'll add 20 comments, each with a student's solution, and the tags our model has generated. Your mission (should you choose to accept it) is to edit the tags on each issue, removing any incorrect ones, and add any that are missing. In order to build one model that performs well across languages, it's best if you stick as closely as possible to the C# tags as you can. Those are listed here. If you want to add extra tags, that's totally fine, but please don't arbitrarily reword existing tags, even if you don't like what Erik's chosen, as it'll just make it less likely that your language gets the correct tags assigned by the neural network.

---

To summarise - there are two paths forward for this issue:

1. You're up for helping: Add a comment saying you're up for helping. Update the tags some time in the next few days. Add a comment when you're done. We'll then add them to our training set and move forward.
2. You not up for helping: No problem! Just please add a comment letting us know :)

If you tell us you're not able/wanting to help or there's no comment added, we'll automatically crowd-source this in a week or so.

Finally, if you have questions or want to discuss things, it would be best done on the forum, so the knowledge can be shared across all maintainers in all tracks.

Thanks for your help! :blue_heart:

---

Note: Meta discussion on the forum

[ErikSchierboom]

Exercise: hello-world

Code

CREATE OR REPLACE PACKAGE hello_world# IS
   FUNCTION hello(
      i_name                                        varchar2 := ''
   ) 
      RETURN varchar2;

END hello_world#;
/

CREATE OR REPLACE PACKAGE BODY hello_world# IS
   FUNCTION hello(
      i_name                                        varchar2 := ''
   ) 
      RETURN varchar2
   AS
   BEGIN
      RETURN 'Hello, ' || nvl(i_name, 'World') ||'!';
   END hello;
END hello_world#;
/

Tags:

construct:assignment
construct:begin
construct:default-parameter-value
construct:function
construct:function-overloading
construct:invocation
construct:optional-parameter
construct:package
construct:parameter
construct:return
construct:string
construct:throw
construct:variable
construct:visibility-modifiers
paradigm:functional
paradigm:imperative
paradigm:object-oriented
technique:exceptions

[ErikSchierboom]

Exercise: hello-world

Code

CREATE OR REPLACE PACKAGE hello_world# IS
   FUNCTION hello(
      i_name                                        varchar2 := 'World'
   ) 
      RETURN varchar2;

END hello_world#;
/
CREATE OR REPLACE PACKAGE BODY hello_world# IS
   FUNCTION hello(
      i_name                                        varchar2 := 'World'
   ) 
      RETURN varchar2
   AS
   BEGIN
    return 'Hello, '|| i_name ||'!';
   END hello;
END hello_world#;
/

Tags:

construct:assignment
construct:begin
construct:default-parameter-value
construct:function
construct:input-parameter
construct:package
construct:package-body
construct:parameter
construct:return
construct:string
construct:variable
construct:visibility
paradigm:imperative
paradigm:functional
paradigm:object-oriented

[ErikSchierboom]

Exercise: hello-world

Code

CREATE OR REPLACE PACKAGE hello_world# IS
   FUNCTION hello(
      i_name                                        varchar2 := ''
   ) 
      RETURN varchar2;

END hello_world#;
/

CREATE OR REPLACE PACKAGE BODY hello_world# IS
   FUNCTION hello(
      i_name                                        varchar2 := ''
   ) 
      RETURN varchar2
   AS
   BEGIN
      CASE WHEN i_name =''
        THEN RETURN "Hello, World!";
      ELSE 
        RETURN "Hello, "+i_name+"!";
      END
   END hello;
END hello_world#;
/

Tags:

construct:add
construct:assignment
construct:case
construct:function
construct:implicit-conversion
construct:package
construct:parameter
construct:plsql
construct:return
construct:string
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:procedural

[ErikSchierboom]

Exercise: hamming

Code

create or replace package hamming#
is
  --+--------------------------------------------------------------------------+
  -- Computes the Hamming distance between two starnds.
  --
  -- @param i_first  sequence to compare
  -- @param i_second sequence to compare
  --
  -- @return         Hamming distance between i_first and i_second
  --+--------------------------------------------------------------------------+
  function distance (
    i_first                                       varchar2
   ,i_second                                      varchar2
  ) return pls_integer;

   ------------- Exceptions and Errorcodes ------------------------------------
   exc_length_not_equal                         exception;
   exc_input_is_null                            exception;
   RETURN_LENGTH_NOT_EQUAL                      constant pls_integer := -1;
   RETURN_INPUT_IS_NULL                         constant pls_integer := -2;

end hamming#;
/

create or replace package body hamming#
is
  function distance (
    i_first                                       varchar2
   ,i_second                                      varchar2
  ) return pls_integer
  as
     l_computed_distance                          pls_integer := 0;
     l_length_strand1                             pls_integer;
     l_length_strand2                             pls_integer;

  begin  
     -- make sure length of strands are equal and not null
     l_length_strand1 := nvl(length(i_first),0);
     l_length_strand2 := nvl(length(i_second),0);

     if l_length_strand1 * l_length_strand2 = 0
     then 
        raise exc_input_is_null;
     elsif l_length_strand1 != l_length_strand2
     then
        raise exc_length_not_equal;   
     end if;

     -- loop through the strand and count mistakes
     for i in 1 .. l_length_strand1
     loop
        if substr(i_first,i,1) != substr(i_second,i,1)
        then
           l_computed_distance := l_computed_distance + 1;
        end if;
     end loop;

     return l_computed_distance;

     exception 
     when exc_input_is_null
      then dbms_output.put_line('Input is null!'); 
      return RETURN_INPUT_IS_NULL;
     when exc_length_not_equal
       then dbms_output.put_line('Strands are not of equal length!'); 
       return  RETURN_LENGTH_NOT_EQUAL;
     when others 
       then raise;
  end distance;

end hamming#;
/

Tags:

construct:add
construct:assignment
construct:boolean
construct:comment
construct:constant
construct:create-package
construct:divide
construct:exception
construct:for-loop
construct:function
construct:if
construct:implicit-conversion
construct:integer
construct:invocation
construct:loop
construct:named-argument
construct:negative-number
construct:null
construct:nullability
construct:number
construct:package
construct:parameter
construct:plsql
construct:return
construct:string
construct:subtract
construct:then
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:procedural
technique:exceptions
technique:looping

[ErikSchierboom]

Exercise: gigasecond

Code

create or replace package gigasecond# 
is
  function since (
    i_random_date                 date
  ) return date;

end gigasecond#;
/

create or replace package body gigasecond#
is
  function since (
    i_random_date                 date
  ) return date
  is 
  begin 

    return i_random_date + power(10,9) / (24 * 60 * 60);
  end since;
end gigasecond#;
/

Tags:

construct:add
construct:date
construct:date-add
construct:divide
construct:divide-float
construct:floating-point-number
construct:function
construct:invocation
construct:multiply
construct:number
construct:package
construct:package-body
construct:parameter
construct:power
construct:return
construct:slash
construct:underscore
paradigm:functional
paradigm:imperative
paradigm:object-oriented
uses:date-arithmetic
uses:math:power

[ErikSchierboom]

Exercise: gigasecond

Code

create or replace package gigasecond#
is
  --+--------------------------------------------------------------------------+
  -- Calculate the date that someone turned or will celebrate their 1 Gs anniversary.  
  --
  -- @param dob      date - date of birth 
  --
  -- @return         the date that someone turned their 1 Gs anniversary
  --+--------------------------------------------------------------------------+

  -- A gigasecond is one billion (10**9) seconds.
  GIGASECOND                                      constant pls_integer := 1000000000;  

  function since (
    dob                                           date
  ) return date;

end gigasecond#;
/

create or replace package body gigasecond#
is
  function since (
    dob                                           date
  ) return date
  as
     l_gs_anniversary                             date;
     l_seconds_per_day                            pls_integer;                                     
     l_days_per_gs                                number;

  begin  
     l_seconds_per_day := 60*60*24;
     l_days_per_gs := GIGASECOND / l_seconds_per_day;

     l_gs_anniversary := dob + l_days_per_gs;

     -- calculate the celebration day, not the seconds one turns 1Gs, so truncate the date first
     l_gs_anniversary := trunc(l_gs_anniversary);

     return l_gs_anniversary;

  exception 
     when others 
       then raise;
  end since;

end gigasecond#;
/

Tags:

construct:add
construct:assignment
construct:comment
construct:constant
construct:date
construct:date-add
construct:divide
construct:exception
construct:expression
construct:function
construct:integer
construct:multiply
construct:number
construct:package
construct:package-body
construct:parameter
construct:return
construct:subtract
construct:variable
construct:visibility-modifiers
paradigm:functional
paradigm:imperative
paradigm:object-oriented
technique:exceptions
uses:Date
uses:DateTime
uses:TimeSpan

[ErikSchierboom]

Exercise: rna-transcription

Code

create or replace package complement# as

  type complement_t is table of varchar2(1)
  index by varchar2(1);

  function of_dna(dna_in varchar2)
  return varchar2;

  function of_rna(rna_in varchar2)
  return varchar2;

end complement#;
/

create or replace package body complement# as

  -- `G` -> `C`
  -- `C` -> `G`
  -- `T` -> `A`
  -- `A` -> `U`

  function of_dna(dna_in varchar2)
  return varchar2 is
    l_comp varchar2(4000);
    l_rule complement_t;
  begin
    if dna_in is null then return null; end if;
    l_rule('G') := 'C';
    l_rule('C') := 'G';
    l_rule('T') := 'A';
    l_rule('A') := 'U';
    for i in 1..length(dna_in) loop
      l_comp := l_comp || l_rule(substr(dna_in,i,1)); 
    end loop;
    return l_comp;
  end of_dna;

  function of_rna(rna_in varchar2)
  return varchar2 is
    l_comp varchar2(4000);
    l_rule complement_t;
  begin
    if rna_in is null then return null; end if;
    l_rule('C') := 'G';
    l_rule('G') := 'C';
    l_rule('A') := 'T';
    l_rule('U') := 'A';
    for i in 1..length(rna_in) loop
      l_comp := l_comp || l_rule(substr(rna_in,i,1)); 
    end loop;
    return l_comp;
  end of_rna;

end complement#;
/

Tags:

construct:assignment
construct:comment
construct:constructor
construct:for-loop
construct:function
construct:if
construct:index-by-table
construct:loop
construct:null
construct:nullability
construct:number
construct:package
construct:package-body
construct:parameter
construct:return
construct:string
construct:table-of
construct:throw
construct:type
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:object-oriented
technique:exceptions
technique:looping

[ErikSchierboom]

Exercise: rna-transcription

Code

CREATE OR REPLACE PACKAGE complement# AS
  ----------------------------------------
  -- Declaration:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- OF_DNA:
  --   Gets the RNA complements of the
  --   given DNA nucleoides.
  --
  --   p_dna_nucleoides: the DNA
  --                     nucleoides
  ----------------------------------------
  FUNCTION OF_DNA (
    p_dna_nucleoides IN VARCHAR2)
  RETURN VARCHAR2;

  ----------------------------------------
  -- OF_RNA:
  --   Gets the DNA complements of the
  --   given RNA nucleoides.
  --
  --   p_rna_nucleoides: the RNA
  --                     nucleoides
  ----------------------------------------
  FUNCTION OF_RNA (
    p_rna_nucleoides IN VARCHAR2)
  RETURN VARCHAR2;
END complement#;
/

CREATE OR REPLACE PACKAGE BODY complement# AS
  ----------------------------------------
  -- Declaration:
  --   Private Functions.
  ----------------------------------------
  ----------------------------------------
  -- GET_COMPLEMENT_OF:
  --   Gets the RNA complement of the
  --   given DNA nucleoide, or
  --   Gets the RNA complement of the
  --   given DNA nucleoide.
  --
  --   p_nucleoide: the nucleoide
  ----------------------------------------
  FUNCTION GET_COMPLEMENT_OF (
    p_nucleoide    IN VARCHAR2
    , p_identifier IN VARCHAR2)
  RETURN VARCHAR2;

  ----------------------------------------
  -- Implementation:
  --   Private Functions.
  ----------------------------------------
  ----------------------------------------
  -- GET_COMPLEMENT_OF:
  --   Gets the RNA complement of the
  --   given DNA nucleoide, or
  --   Gets the RNA complement of the
  --   given DNA nucleoide.
  --
  --   p_nucleoide: the nucleoide
  ----------------------------------------
  FUNCTION GET_COMPLEMENT_OF (
    p_nucleoide    IN VARCHAR2
    , p_identifier IN VARCHAR2)
  RETURN VARCHAR2 IS
    TYPE nucleoide_cursor_type IS REF CURSOR;  

    nucleoide_cursor nucleoide_cursor_type; 

    v_id             NUMBER(11, 0); 

    v_nucleoide      VARCHAR2(16 CHAR);
    v_dna_nucleoide  VARCHAR2(1 CHAR);  
    v_rna_nucleoide  VARCHAR2(1 CHAR);
  BEGIN
    CASE p_identifier
      WHEN 'DNA' THEN v_nucleoide := 'dna_nucleoide';
      WHEN 'RNA' THEN v_nucleoide := 'rna_nucleoide';
      ELSE RAISE_APPLICATION_ERROR (-20003, 'Invalid Identifier found.');
    END CASE;

    OPEN nucleoide_cursor FOR   
      'select id, dna_nucleoide, rna_nucleoide from (  
         select 1 as id, ''A'' as dna_nucleoide, ''U'' as rna_nucleoide from dual  
           union all  
         select 2 as id, ''C'' as dna_nucleoide, ''G'' as rna_nucleoide from dual  
           union all  
         select 3 as id, ''G'' as dna_nucleoide, ''C'' as rna_nucleoide from dual  
           union all  
         select 4 as id, ''T'' as dna_nucleoide, ''A'' as rna_nucleoide from dual) 
       where ' || v_nucleoide || ' = upper(:s)'
    USING p_nucleoide;  

    LOOP  
      FETCH nucleoide_cursor INTO v_id, v_dna_nucleoide, v_rna_nucleoide;  
      EXIT WHEN nucleoide_cursor%notfound;  
    END LOOP;  

    CLOSE nucleoide_cursor; 

    CASE p_identifier
      WHEN 'DNA' THEN RETURN (v_rna_nucleoide);
      WHEN 'RNA' THEN RETURN (v_dna_nucleoide);
      ELSE RETURN (NULL);
    END CASE;

    RETURN (v_rna_nucleoide);
  END GET_COMPLEMENT_OF;

  ----------------------------------------
  -- OF_NUCLEOIDE:
  --   Gets the complements of the
  --   given nucleoides.
  --
  --   p_nucleoides: the nucleoides
  ----------------------------------------
  FUNCTION OF_NUCLEOIDE (
    p_nucleoides   IN VARCHAR2
    , p_identifier IN VARCHAR2)
  RETURN VARCHAR2 IS
    v_nucleoide  VARCHAR2(1 CHAR);
    v_nucleoides VARCHAR2(512 CHAR);
    v_input      VARCHAR2(512 CHAR);
  BEGIN
    v_input := UPPER(TRIM(p_nucleoides));

    IF (p_nucleoides IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20001, 'Input Parameter is NULL.');
    ELSIF (v_input IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20002, 'Input Parameter is empty.');
    END IF;

    v_nucleoides := '';

    FOR i IN 1..LENGTH(v_input) LOOP
      v_nucleoide := GET_COMPLEMENT_OF(substr(v_input, i, 1),
                                       p_identifier);

      IF (v_nucleoide IS NOT NULL) THEN
        v_nucleoides := v_nucleoides || v_nucleoide;
      ELSE
        RAISE_APPLICATION_ERROR (-20003, 'Invalid ' || p_identifier || '-Nucleoide found.');
      END IF;
    END LOOP; 

    RETURN (v_nucleoides);
  END OF_NUCLEOIDE;

  ----------------------------------------
  -- Implementation:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- OF_DNA:
  --   Gets the RNA complements of the
  --   given DNA nucleoides.
  --
  --   p_dna_nucleoides: the DNA
  --                     nucleoides
  ----------------------------------------
  FUNCTION OF_DNA (
    p_dna_nucleoides IN VARCHAR2)
  RETURN VARCHAR2 IS
  BEGIN
    RETURN (OF_NUCLEOIDE(p_dna_nucleoides,
                         'DNA'));
  END OF_DNA;

  ----------------------------------------
  -- OF_RNA:
  --   Gets the DNA complements of the
  --   given RNA nucleoides.
  --
  --   p_rna_nucleoides: the RNA
  --                     nucleoides
  ----------------------------------------
  FUNCTION OF_RNA (
    p_rna_nucleoides IN VARCHAR2)
  RETURN VARCHAR2 IS
  BEGIN
    RETURN (OF_NUCLEOIDE(p_rna_nucleoides,
                         'RNA'));
  END OF_RNA;
END complement#;
/

Tags:

construct:boolean
construct:case
construct:close-cursor
construct:concatenation
construct:constructor
construct:cursor
construct:declare
construct:definition
construct:elseif
construct:for-loop
construct:function
construct:if
construct:invocation
construct:loop
construct:named-argument
construct:null
construct:nullability
construct:number
construct:open-cursor
construct:package
construct:parameter
construct:plsql
construct:procedure
construct:return
construct:string
construct:subtype
construct:throw
construct:union
construct:using
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:object-oriented
technique:exceptions
technique:looping

[ErikSchierboom]

Exercise: rna-transcription

Code

CREATE OR REPLACE PACKAGE complement#
AS
  --+--------------------------------------------------------------------------+
  -- Calculates the RNA of an DNA.
  --
  -- @param dna   given DNA strand
  --
  -- @return      calculated RNA strand
  --+--------------------------------------------------------------------------+
  FUNCTION of_dna (
    dna     varchar2
  ) RETURN varchar2;

  --+--------------------------------------------------------------------------+
  -- Calculates the DNA of an RNA.
  --
  -- @param dna   given RNA strand
  --
  -- @return      calculated DNA strand
  --+--------------------------------------------------------------------------+
  FUNCTION of_rna (
    rna     varchar2
  ) RETURN  varchar2;

  INVALID_NUCLEOTIDE EXCEPTION;

END complement#;
/

CREATE OR REPLACE PACKAGE BODY complement#
AS
  FUNCTION of_nucleotide(
    i_nucleotide  char,
    i_toRna       BOOLEAN := TRUE
  ) RETURN char
  AS
    nucleotide       char(1);
  BEGIN
    nucleotide :=  CASE
        WHEN i_nucleotide = 'G' THEN 'C'
        WHEN i_nucleotide = 'C' THEN 'G'
        WHEN i_nucleotide = 'T' AND i_toRna = TRUE THEN 'A'
        WHEN i_nucleotide = 'A' AND i_toRna = TRUE THEN 'U'
        WHEN i_nucleotide = 'A' AND i_toRna = FALSE THEN 'T'
        WHEN i_nucleotide = 'U' AND i_toRna = FALSE THEN 'A'
        ELSE NULL
      END;
    IF nucleotide IS NULL THEN
      RAISE INVALID_NUCLEOTIDE;
    END IF;
    RETURN nucleotide;
  END of_nucleotide;

  FUNCTION of_dna (
    dna     varchar2
  ) RETURN  varchar2
  AS
    rna       varchar2(10000);
    nucleotide       char(1);
  BEGIN
    IF dna IS NULL THEN
      RETURN NULL;
    END IF;
    FOR i IN 1..LENGTH(dna) LOOP
      rna := rna || of_nucleotide(SUBSTR(dna, i, 1), TRUE);
    END LOOP;
    RETURN rna;
  END of_dna;

  FUNCTION of_rna (
    rna     varchar2
  ) RETURN  varchar2
  AS
    dna       varchar2(10000);
    nucleotide       char(1);
  BEGIN
    IF rna IS NULL THEN
      RETURN NULL;
    END IF;
    FOR i IN 1..LENGTH(rna) LOOP
      dna := dna || of_nucleotide(SUBSTR(rna, i, 1), FALSE);
    END LOOP;
    RETURN dna;
  END of_rna;

end complement#;
/

Tags:

construct:boolean
construct:char
construct:comment
construct:constructor
construct:for-loop
construct:function
construct:function-overloading
construct:if
construct:invocation
construct:length
construct:null
construct:nullability
construct:number
construct:optional-parameter
construct:package
construct:parameter
construct:return
construct:string
construct:throw
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:object-oriented
technique:exceptions
technique:looping

[ErikSchierboom]

Exercise: raindrops

Code

create or replace package raindrops# as

  function convert(from_integer_in in integer)
  return varchar2;

end raindrops#;
/

create or replace package body raindrops# as

  function convert(from_integer_in in integer)
  return varchar2 is
    l_result varchar2(4000);
  begin
    if mod(from_integer_in,3) = 0 then
      l_result := l_result || 'Pling';
    end if;
    if mod(from_integer_in,5) = 0 then
      l_result := l_result || 'Plang';
    end if;
    if mod(from_integer_in,7) = 0 then
      l_result := l_result || 'Plong';
    end if;
    if l_result is null then
      l_result := from_integer_in;
    end if;
    return l_result;
  end convert;

end raindrops#;
/

Tags:

construct:assignment
construct:body
construct:comment
construct:create-or-replace-package
construct:function
construct:if
construct:invocation
construct:is-null
construct:package
construct:parameter
construct:return
construct:string
construct:then
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:procedural

[ErikSchierboom]

Exercise: raindrops

Code

REATE OR REPLACE PACKAGE raindrops# AS
  ----------------------------------------
  -- Declaration:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- CONVERT:
  --   Converts the given Number into
  --   a Raindrop-String.
  --
  --   p_number: the Number
  ----------------------------------------
  FUNCTION CONVERT (
    p_number IN NUMBER)
  RETURN VARCHAR2;
END raindrops#;
/

CREATE OR REPLACE PACKAGE BODY raindrops# AS
  ----------------------------------------
  -- Declaration:
  --   Private Functions.
  ----------------------------------------
  ----------------------------------------
  -- GET_RAINDROP:
  --   Gets the Raindrop.
  --
  --   p_index: the Index
  ----------------------------------------
  FUNCTION GET_RAINDROP (
    p_index IN NUMBER)
  RETURN VARCHAR2;

  ----------------------------------------
  -- GET_RAINDROPS:
  --   Converts the given Number into
  --   a Raindrop-String.
  --
  --   p_number: the Number
  ----------------------------------------
  FUNCTION GET_RAINDROPS (
    p_number IN NUMBER)
  RETURN VARCHAR2;

  ----------------------------------------
  -- Implementation:
  --   Private Functions.
  ----------------------------------------
  ----------------------------------------
  -- GET_RAINDROP:
  --   Gets the Raindrop.
  --
  --   p_index: the Index
  ----------------------------------------
  FUNCTION GET_RAINDROP (
    p_index IN NUMBER)
  RETURN VARCHAR2 IS
    TYPE raindrop_cursor_type IS REF CURSOR;  

    raindrop_cursor raindrop_cursor_type; 

    v_id            NUMBER(11, 0); 

    v_raindrop      VARCHAR2(8 CHAR);
  BEGIN
    OPEN raindrop_cursor FOR   
      'select id, raindrop from (  
         select 3 as id, ''Pling'' as raindrop from dual  
           union all  
         select 5 as id, ''Plang'' as raindrop from dual  
           union all  
         select 7 as id, ''Plong'' as raindrop from dual) 
       where id = :s'
    USING p_index;  

    LOOP  
      FETCH raindrop_cursor INTO v_id, v_raindrop;  
      EXIT WHEN raindrop_cursor%notfound;  
    END LOOP;  

    CLOSE raindrop_cursor; 

    RETURN (v_raindrop);
  END GET_RAINDROP;

  ----------------------------------------
  -- GET_RAINDROPS:
  --   Converts the given Number into
  --   a Raindrop-String.
  --
  --   p_number: the Number
  ----------------------------------------
  FUNCTION GET_RAINDROPS (
    p_number IN NUMBER)
  RETURN VARCHAR2 IS
    v_raindrop  VARCHAR2(8 CHAR);
    v_raindrops VARCHAR2(512 CHAR);

    i           NUMBER(11, 0);
  BEGIN
    IF (p_number IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20001, 'Input Parameter is NULL.');
    END IF;

    v_raindrops := '';

    i := 3;

    WHILE i <= 7
    LOOP
      IF (MOD(p_number, i) = 0) THEN
        v_raindrop := GET_RAINDROP(i);

        IF (v_raindrop IS NOT NULL) THEN
          v_raindrops := v_raindrops || v_raindrop;
        END IF;
      END IF;

      i := i + 2;
    END LOOP;

    IF (v_raindrops IS NULL) THEN
      RETURN (to_char(p_number));
    ELSE
      RETURN (v_raindrops);
    END IF;
  END GET_RAINDROPS;

  ----------------------------------------
  -- Implementation:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- CONVERT:
  --   Converts the given Number into
  --   a Raindrop-String.
  --
  --   p_number: the Number
  ----------------------------------------
  FUNCTION CONVERT (
    p_number IN NUMBER)
  RETURN VARCHAR2 IS
  BEGIN
    RETURN (GET_RAINDROPS(p_number));
  END CONVERT;
END raindrops#;
/

Tags:

construct:assignment
construct:char
construct:comment
construct:constructor
construct:curly-braces
construct:divide
construct:double
construct:floating-point-number
construct:if
construct:implicit-conversion
construct:int
construct:integral-number
construct:invocation
construct:lambda
construct:method
construct:number
construct:parameter
construct:return
construct:string
construct:throw
construct:using-directive
construct:variable
construct:visibility-modifiers
paradigm:functional
paradigm:imperative
paradigm:object-oriented
technique:exceptions
technique:higher-order-functions

[ErikSchierboom]

Exercise: raindrops

Code

CREATE OR REPLACE PACKAGE raindrops#
AS
  --+--------------------------------------------------------------------------+
  -- Convert a number to a raindrop-string, the contents of which depend on the number's factors.
  --
  -- @param number_to_convert
  --
  -- @return      raindrop-string
  --+--------------------------------------------------------------------------+
  FUNCTION convert (
    number_to_convert     INTEGER
  ) RETURN  varchar2;

END raindrops#;
/

CREATE OR REPLACE PACKAGE BODY raindrops#
AS

  TYPE raindrop_t IS TABLE OF varchar2(5)
  INDEX BY PLS_INTEGER;

  FUNCTION get_raindrops_constants RETURN raindrop_t
  AS
    lv_raindrops raindrop_t;
  BEGIN
    lv_raindrops(3) := 'Pling';
    lv_raindrops(5) := 'Plang';
    lv_raindrops(7) := 'Plong';
    RETURN lv_raindrops;
  END get_raindrops_constants;

  FUNCTION convert (
    number_to_convert     INTEGER
  ) RETURN  varchar2
  AS
    pc_raindrops CONSTANT raindrop_t := get_raindrops_constants();
    lv_raindrop           PLS_INTEGER := pc_raindrops.FIRST;
    converted             varchar2(10000);
  BEGIN
    IF number_to_convert IS NULL THEN
      RETURN NULL;
    END IF;
    WHILE lv_raindrop IS NOT NULL LOOP
      IF MOD(number_to_convert, lv_raindrop) = 0 THEN
        converted := converted || pc_raindrops(lv_raindrop);
      END IF;
      lv_raindrop := pc_raindrops.NEXT(lv_raindrop); 
    END LOOP;

    IF converted IS NULL OR converted = '' THEN
      converted := number_to_convert;
    END IF;
    RETURN converted;
  END convert;

end raindrops#;
/

Tags:

construct:assignment
construct:boolean
construct:comment
construct:constructor
construct:function
construct:if
construct:integer
construct:invocation
construct:logical-or
construct:method
construct:null
construct:nullability
construct:number
construct:overloading
construct:package
construct:parameter
construct:plsql
construct:return
construct:string
construct:table
construct:table-of
construct:then
construct:type
construct:variable
construct:visibility-modifiers
construct:while-loop
paradigm:imperative
paradigm:declarative
paradigm:functional
technique:boolean-logic
technique:looping
uses:Raindrops

[ErikSchierboom]

Exercise: difference-of-squares

Code

create or replace package series# as

  function square_of_sums(num_in in integer)
  return integer;

  function sum_of_squares(num_in in integer)
  return integer;

  function diff_of_squares(num_in in integer)
  return integer;

end series#;
/

create or replace package body series# as

  function square_of_sums(num_in in integer)
  return integer is
    l_result integer := 0;
  begin
    for i in 1..num_in loop
      l_result := l_result + i;
    end loop;
    return power(l_result,2);
  end square_of_sums;

  function sum_of_squares(num_in in integer)
  return integer is
    l_result integer := 0;
  begin
    for i in 1..num_in loop
      l_result := l_result + power(i,2);
    end loop;
    return l_result;
  end sum_of_squares;

  function diff_of_squares(num_in in integer)
  return integer is
  begin
    return square_of_sums(num_in) - sum_of_squares(num_in);
  end diff_of_squares;

end series#;
/

Tags:

construct:add
construct:assignment
construct:body
construct:create-package
construct:divide
construct:for-loop
construct:function
construct:function-overloading
construct:integer
construct:invocation
construct:loop
construct:package
construct:parameter
construct:return
construct:subtract
construct:variable
construct:visibility
paradigm:imperative
paradigm:declarative
technique:looping

[ErikSchierboom]

Exercise: roman-numerals

Code

create or replace package numeral#
is

  function to_roman (v_value    number) return varchar2;

end numeral#;
/

create or replace package body numeral#
is

    function to_roman (v_value  number) return varchar2 is
        v_return    number := 0;
    begin
        if v_value <= 0 or v_value > 3000 then
            raise invalid_number;
        else
            return trim(to_char(v_value,'RN'));
        end if;

        return power(v_return,2);

    end to_roman;

end numeral#;

/

Tags:

construct:boolean
construct:char
construct:create-or-replace
construct:function
construct:if
construct:implicit-conversion
construct:invocation
construct:number
construct:or
construct:package
construct:parameter
construct:raise
construct:return
construct:string
construct:then
construct:variable
construct:visibility-modifiers
paradigm:functional
paradigm:imperative
paradigm:object-oriented
technique:boolean-logic
technique:exceptions
technique:math

[ErikSchierboom]

Exercise: nth-prime

Code

REATE OR REPLACE PACKAGE prime# AS
  ----------------------------------------
  -- Declaration:
  --   Public Variables.
  ----------------------------------------
  INVALID_ARGUMENT_ERROR EXCEPTION;

  ----------------------------------------
  -- Declaration:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- nth:
  --   Given a number p_number, determine
  --   what the nth prime is.
  --
  --   p_number: determine what the nth
  --             prime is
  ----------------------------------------
  FUNCTION nth (
    p_number IN NUMBER)
  RETURN NUMBER;
END prime#;
/

CREATE OR REPLACE PACKAGE BODY prime# AS
  ----------------------------------------
  -- Declaration:
  --   Private Functions.
  ----------------------------------------
  ----------------------------------------
  -- is_prime:
  --   Checks if the number p_number is
  --   a prime.
  --
  --   p_number: the Number to check
  --   x:        factor
  --   y:        factor
  ----------------------------------------
  FUNCTION is_prime(
    p_number NUMBER
    , x      NUMBER DEFAULT 5
    , y      NUMBER DEFAULT 2)
  RETURN BOOLEAN;

  ----------------------------------------
  -- calculate:
  --   Calculates the result.
  --
  --   p_number:  the Number to check
  --   p_current: the calulated Number
  ----------------------------------------
  FUNCTION calculate(
    p_number    NUMBER
    , p_current NUMBER)
  RETURN NUMBER;

  ----------------------------------------
  -- Implementation:
  --   Private Functions.
  ----------------------------------------
  ----------------------------------------
  -- is_prime:
  --   Checks if the number p_number is
  --   a prime.
  --
  --   p_number: the Number to check
  --   x:        factor
  --   y:        factor
  ----------------------------------------
  FUNCTION is_prime(
    p_number NUMBER
    , x      NUMBER DEFAULT 5
    , y      NUMBER DEFAULT 2)
  RETURN BOOLEAN IS
    v_result BOOLEAN;
  BEGIN
    CASE
      WHEN ((p_number = 2) OR (p_number = 3)) THEN
        v_result := TRUE;
      WHEN ((REMAINDER(p_number, 2) = 0) OR (REMAINDER(p_number, 3) = 0)) THEN
        v_result := FALSE;
      ELSE
        IF (x * x <= p_number) THEN
          IF (REMAINDER(p_number, x) = 0) THEN
            v_result := FALSE;
          ELSE
            v_result := is_prime(p_number, x + y, 6 - y);
          END IF;
        ELSE
          v_result := TRUE;
        END IF;
    END CASE;

    RETURN (v_result);
  END is_prime;

  ----------------------------------------
  -- calculate:
  --   Calculates the result.
  --
  --   p_number:  the Number to check
  --   p_current: the calulated Number
  ----------------------------------------
  FUNCTION calculate(
    p_number    NUMBER
    , p_current NUMBER)
  RETURN NUMBER IS
    v_current NUMBER(11, 0);
    v_result  NUMBER(11, 0);
  BEGIN
    IF (p_number = 0) THEN
      v_result := p_current;
    ELSE
      v_current := p_current + 1;

      CASE is_prime(v_current)
        WHEN  TRUE THEN v_result := calculate(p_number - 1, v_current);
        WHEN FALSE THEN v_result := calculate(p_number,     v_current);
      END CASE;
    END IF;

    RETURN (v_result);
  END calculate;

  ----------------------------------------
  -- Implementation:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- nth:
  --   Given a number p_number, determine
  --   what the nth prime is.
  --
  --   p_number: determine what the nth
  --             prime is
  ----------------------------------------
  FUNCTION nth (
    p_number IN NUMBER)
  RETURN NUMBER IS
  BEGIN
    IF (p_number IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20001, 'Input Parameter is NULL.');
    ELSIF (p_number = 0) THEN
      RAISE INVALID_ARGUMENT_ERROR;
    END IF;

    RETURN (calculate(p_number, 1));
  END nth;
END prime#;
/

CREATE OR REPLACE PACKAGE ut_prime# AS
  PROCEDURE run;
END ut_prime#;
/

CREATE OR REPLACE PACKAGE BODY ut_prime# AS
  PROCEDURE test (
    i_descn VARCHAR2
    , i_exp VARCHAR2
    , i_act VARCHAR2) AS
  BEGIN
    IF (i_exp = i_act) THEN
      dbms_output.put_line('SUCCESS: ' || i_descn);
    ELSE
      dbms_output.put_line('FAILURE: ' || i_descn || ' - expected ' || nvl(i_exp, 'null') || ', but received ' || nvl(i_act, 'null'));
    END IF;
  END test;

  PROCEDURE run AS
    l_act NUMBER(11, 0);
  BEGIN
    test(i_descn => 'test first',     i_exp =>      2, i_act => prime#.nth(    1));
    test(i_descn => 'test second',    i_exp =>      3, i_act => prime#.nth(    2));
    test(i_descn => 'test sixth',     i_exp =>     13, i_act => prime#.nth(    6));
    test(i_descn => 'test big_prime', i_exp => 104743, i_act => prime#.nth(10001));

    BEGIN
      l_act := prime#.nth(0);

      dbms_output.put_line('FAILURE: test_weird_case - expected invalid_argument_error to be raised, but received ' || NVL('' || l_act, 'null'));
    EXCEPTION
      WHEN prime#.invalid_argument_error THEN
        dbms_output.put_line('SUCCESS: test_weird_case');
    END;
  END run;
END ut_prime#;
/

Tags:

No tags generated

[ErikSchierboom]

Exercise: nth-prime

Code

create or replace package prime#
is

  type primes_type is table of pls_integer;

  invalid_argument_error exception;

  function nth(input in pls_integer) return pls_integer;

end prime#;
/

create or replace package body prime#
is

  function check_prime(primes in primes_type, cur_val in pls_integer) return boolean is
  begin
    for idx in 2 .. primes.count loop
      if cur_val mod primes(idx) = 0 then
        return false;
      end if;
    end loop;
    return true;
  end;

  function nth(input in pls_integer) return pls_integer is    
    primes primes_type := primes_type(); -- Initialise it
    cur_idx pls_integer := 3;
    cur_val pls_integer := 5;
     is_prime boolean := true;
  begin

    case
      when input <= 0 then raise invalid_argument_error;
      when input = 1 then return 2;
      when input = 2 then return 3;
      else null;
    end case;    

    primes.extend(2); -- Extend it
    primes(1) := 2;
    primes(2) := 3;

    while cur_idx <= input loop
      is_prime := check_prime(primes, cur_val);
      if is_prime then
        primes.extend();
        primes(cur_idx) := cur_val;
        cur_idx := cur_idx + 1;
      end if;
      if not is_prime or cur_idx < input then
        cur_val := cur_val + 2;
      end if;
    end loop;

    return cur_val;
  end;

end prime#;
/

Tags:

construct:add
construct:assignment
construct:boolean
construct:case
construct:comment
construct:constructor
construct:exception
construct:for-loop
construct:function
construct:function-overloading
construct:if
construct:indexed
construct:initializer
construct:integer
construct:invocation
construct:logical-or
construct:loop
construct:named-argument
construct:null
construct:nullability
construct:number
construct:package
construct:parameter
construct:table
construct:table-of
construct:then
construct:type
construct:variable
construct:visibility-modifiers
construct:while-loop
paradigm:imperative
paradigm:object-oriented
technique:boolean-logic
technique:exceptions
technique:looping

[ErikSchierboom]

Exercise: leap

Code

create or replace package year# 
is 
    function is_leap (i_annee                   number
    ) return varchar2;

end year#;
/

create or replace package body year#
is
    function is_leap (i_annee               number
    ) return varchar2
  is 
    begin
        if mod(i_annee, 400) = 0 OR ( mod(i_annee, 4) = 0 AND mod(i_annee, 100) != 0 ) then
            return 'Yes, ' || i_annee || ' is a leap year'; 
        end if;
            return 'No, ' || i_annee || ' is not a leap year';
    end is_leap;
end year#;
/

Tags:

construct:boolean
construct:body
construct:boolean
construct:comment
construct:create-or-replace
construct:function
construct:if
construct:logical-and
construct:logical-or
construct:number
construct:package
construct:parameter
construct:return
construct:string
construct:then
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:declarative
technique:boolean-logic

[ErikSchierboom]

Exercise: grains

Code

create or replace package grains#
is
  function at_square (in_square number) return number;
  function total return number;
end grains#;
/
create or replace package body grains#
is
  function at_square (in_square number) return number is
    grains number := 0;
  begin
    return power(2, in_square - 1);
  end;

  function total(in_square number, in_out_sum number) return number is
  begin
    if in_square = 0 then
      return in_out_sum;
    else
      return total(in_square-1, in_out_sum + at_square(in_square));
    end if;
  end;

  function total return number is
    res number := 0;
  begin
    return total(64, 0);
  end;

end grains#;
/

Tags:

construct:add
construct:assignment
construct:body
construct:create-package
construct:function
construct:function-overloading
construct:if
construct:invocation
construct:method
construct:number
construct:package
construct:parameter
construct:recursive-call
construct:return
construct:subtract
construct:then
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:object-oriented
technique:recursion

[ErikSchierboom]

Exercise: binary

Code

REATE OR REPLACE PACKAGE binary# AS
  ----------------------------------------
  -- Declaration:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- to_decimal:
  --  Convert a binary number, represented
  --  as a string (e.g. '101010'), to its
  --  decimal
  --
  --   p_number: the square
  ----------------------------------------
  FUNCTION to_decimal (
    p_binary IN VARCHAR2)
  RETURN NUMBER;
END binary#;
/

CREATE OR REPLACE PACKAGE BODY binary# AS
  ----------------------------------------
  -- Implementation:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- to_decimal:
  --  Convert a binary number, represented
  --  as a string (e.g. '101010'), to its
  --  decimal
  --
  --   p_number: the square
  ----------------------------------------
  FUNCTION to_decimal (
    p_binary IN VARCHAR2)
  RETURN NUMBER IS
    v_binary VARCHAR2(512 CHAR);

    v_result NUMBER(11, 0);
  BEGIN
    v_binary := TRIM(p_binary);

    IF (v_binary IS NULL) THEN
      RETURN(0);
    ELSIF (v_binary IS NULL) THEN
      RETURN(0);
    ELSIF (NOT (REGEXP_LIKE(v_binary, '^[0-1]*$'))) THEN
      RETURN(0);
    END IF;

    v_result := 0;

    FOR i IN 1..LENGTH(v_binary) LOOP
      v_result := (v_result * 2) + TO_NUMBER(SUBSTR(v_binary, i, 1));
    END LOOP;

    RETURN(v_result);
  END to_decimal;
END binary#;
/

CREATE OR REPLACE PACKAGE ut_binary# AS
  PROCEDURE run;
END ut_binary#;
/

CREATE OR REPLACE PACKAGE BODY ut_binary# AS
  PROCEDURE test (
    i_descn VARCHAR2
    , i_exp VARCHAR2
    , i_act VARCHAR2) AS
  BEGIN
    IF (i_exp = i_act) THEN
      dbms_output.put_line('SUCCESS: ' || i_descn);
    ELSE
      dbms_output.put_line('FAILURE: ' || i_descn || ' - expected ' || nvl(i_exp, 'null') || ', but received ' || nvl(i_act, 'null'));
    END IF;
  END test;

  PROCEDURE run AS
  BEGIN
    test(i_descn =>               'test_binary_1_is_decimal_1', i_exp =>    1, i_act => binary#.to_decimal(          '1'));
    test(i_descn =>              'test_binary_10_is_decimal_2', i_exp =>    2, i_act => binary#.to_decimal(         '10'));
    test(i_descn =>              'test_binary_11_is_decimal_3', i_exp =>    3, i_act => binary#.to_decimal(         '11'));
    test(i_descn =>             'test_binary_100_is_decimal_4', i_exp =>    4, i_act => binary#.to_decimal(        '100'));
    test(i_descn =>            'test_binary_1001_is_decimal_9', i_exp =>    9, i_act => binary#.to_decimal(       '1001'));
    test(i_descn =>          'test_binary_11010_is_decimal_26', i_exp =>   26, i_act => binary#.to_decimal('      11010'));
    test(i_descn =>  'test_binary_10001101000_is_decimal_1128', i_exp => 1128, i_act => binary#.to_decimal('10001101000'));
    test(i_descn => 'test_invalid_binary_postfix_is_decimal_0', i_exp =>    0, i_act => binary#.to_decimal(     '10110a'));
    test(i_descn =>  'test_invalid_binary_prefix_is_decimal_0', i_exp =>    0, i_act => binary#.to_decimal(     'a10110'));
    test(i_descn =>   'test_invalid_binary_infix_is_decimal_0', i_exp =>    0, i_act => binary#.to_decimal(     '101a10'));
    test(i_descn =>         'test_invalid_binary_is_decimal_0', i_exp =>    0, i_act => binary#.to_decimal(     '101210'));
  END run;
END ut_binary#;
/

Tags:

construct:add
construct:assignment
construct:boolean
construct:comment
construct:create-package
construct:divide
construct:elseif
construct:explicit-conversion
construct:for-loop
construct:function
construct:if
construct:implicit-conversion
construct:invocation
construct:lambda
construct:length
construct:loop
construct:multiply
construct:null
construct:nullability
construct:number
construct:package
construct:parameter
construct:pattern
construct:procedure
construct:return
construct:string
construct:subtract
construct:then
construct:throw
construct:variable
construct:visibility-modifiers
paradigm:functional
paradigm:imperative
paradigm:object-oriented
technique:exceptions
technique:higher-order-functions
technique:looping
technique:regular-expression
technique:type-conversion
uses:REGEXP_LIKE

[ErikSchierboom]

Exercise: binary

Code

CREATE OR REPLACE PACKAGE binary#
IS
  not_binary       EXCEPTION;
  --+--------------------------------------------------------------------------+
  -- Converts a binary string into its decimal equivalent
  --
  -- @param p_binary  string in binary format
  --
  -- @return         number in decimal format
  --+--------------------------------------------------------------------------+
  FUNCTION to_decimal (
    p_binary       VARCHAR2
  ) RETURN PLS_INTEGER;

END binary#;
/

CREATE OR REPLACE PACKAGE BODY binary#
IS

  FUNCTION to_decimal (
    p_binary       VARCHAR2
  ) RETURN PLS_INTEGER IS
    i              PLS_INTEGER := 0;
    ln_total       PLS_INTEGER := 0;
  BEGIN
    -- check if only 0 and 1 are in the input string p_binary
    IF TRIM(TRANSLATE(p_binary, '01', ' ')) IS NOT NULL THEN
      RAISE not_binary;
    END IF;

    FOR i IN 1..Length(p_binary)
    LOOP
--      dbms_output.put_line('p_binary: ' || p_binary || '; i: ' || i || '; ln_total: ' || ln_total);
      ln_total := ln_total + (POWER(2, Length(p_binary) - i) * TO_NUMBER(Substr(p_binary, i , 1)));
    END LOOP;

    RETURN ln_total;
  EXCEPTION
    WHEN not_binary THEN
--      dbms_output.put_line('p_binary: ' || p_binary || '; i: ' || i);
      RETURN 0;
  END to_decimal;

END binary#;
/

Tags:

construct:add
construct:assignment
construct:comment
construct:divide
construct:exception
construct:for-loop
construct:function
construct:if
construct:implicit-cursor-loop
construct:invocation
construct:length
construct:named-argument
construct:number
construct:package
construct:package-body
construct:parameter
construct:return
construct:string
construct:subtract
construct:then
construct:variable
construct:visibility-modifiers
paradigm:imperative
paradigm:looping
paradigm:object-oriented

[ErikSchierboom]

This is an automated comment

Hello :wave: Next week we're going to start using the tagging work people are doing on these. If you've already completed the work, thank you! If you've not, but intend to this week, that's great! If you're not going to get round to doing it, and you've not yet posted a comment letting us know, could you please do so, so that we can find other people to do it. Thanks!