Category: PL/SQL

February 25th, 2020 by Mathias

Have you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day.

It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Grace, and Israel to credit card numbers and IMEI numbers and misc other things.

It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the calculation used it is also referred to modulus 10.

The algorithm was invented by Hans Peter Luhn in 1954. It was meant to protect against most accidental errors, not against malicious attacks.

The algorithm

Each number in a sequence is alternating multiplied by 1 and 2 with the results added together. After that a modulus 10 is applied to the sum and the result is the check digit the function should return.

Let’s take an example, 374 is the input, we’re looking for the check digit that should be used. The calculation should be based on even number of digits in the input so we prepend with a zero, thus we use 0374 as our input. Now we’ll calculate 0*1, 3*2, 7*1, and 4*2 – multiply each digit in the input with 1 and 2 alternating.

0*1 = 0, 3*2 = 6, 7*1 = 7 and 4*2 = 8. Add them together and we have 0 + 6 + 7 + 8 = 21. Here is where we use modulo, 21 modulo 10 is 1. That however is not the check digit, the check digit is the reverse. That is, what do you need to add to this to make the result 10. In our case we arrived at one after the modulo operation so our check digit is 9.

There is a special case however. If a digit that is multiplied with two is between five and nine then the result will be two digits (5*2 = 10 and so on). That result is then added together resulting in all results being added together as single digits. This if we have 7 we get 7*2=14, 14 is now taken each digit and added so the result we get for the 7 is 5 (1+4). Instead of adding the digit like that, one can also subtract 9 from the result as it arrives at the same end result (14-9=5).

If you find my explanation above to rushed or hard to follow, take a look at wikipedia.

With that explanation out of the way, lets look at three ways to implement this. From slowest to fastest. If you only need this occasionally then it does not matter, they are all fast. I however faced a situation where I had to generate valid numbers that has correct check digits för a few hundred million rows.

PL/SQL

Let’s first look at a basic implementation in PL/SQL. I say basic because it is probably the most straight forward implementation of the pseudo code version of the algorithm. It is in my experience the most common way to implement it. When this feature is needed it tends to end up in a traditional function/procedure in PL/SQL.

This code is “borrowed” from Daniel Ekberg, just changing some variable names to make it easier to understand for the rest of us.

CREATE OR REPLACE FUNCTION get_value_w_chk (inv IN VARCHAR2)
  RETURN VARCHAR
AS
  in_val     VARCHAR2 (30);
  pos        int;
  total      int := 0;
  multiplier int := 0;
  result_pos int := 0;
  in_len     int;
  chk_digit int;
BEGIN
  in_len := LENGTH (inv);
  in_val := inv;
 
  FOR i IN 0 .. (in_len - 1)
  LOOP
    pos := in_len - i;
    multiplier := MOD (multiplier + 1, 2);
    result_pos := TO_NUMBER(SUBSTR(in_val, pos, 1), '0') 
                * (multiplier + 1);
 
    IF result_pos > 9 THEN
     total := total + result_pos - 9;
    ELSE
     total := total + result_pos;
    END IF;
  END LOOP;
 
  chk_digit := MOD (1000 - total, 10);
 
  RETURN in_val || chk_digit;
END get_value_w_chk;
/

The code essentially loops through a string of digits and makes the calculation for each digit, adds it to a total and finishes up with a modulo operation to end up with the check digit, it then returns the parameter concatenated with the check-digit.

PL/SQL recursive function

The cool kids are writing recursive functions all day long or so they would have you believe. This particular algorithm lent it self to it so I wanted to test doing that. Also, next time one of the cool kids claims it cannot be done in legacy languages like PL/SQL I can just point them to this blog post (hello future reader).

CREATE OR REPLACE FUNCTION get_chk(p_i_val IN VARCHAR2) 
       RETURN VARCHAR2 AS
  v_inval VARCHAR2(50) := 
                CASE
                  WHEN MOD(LENGTH(p_i_val), 2) = 0 THEN p_i_val
                  ELSE           <wbr />                       '0' || p_i_val
                END;
 
  v_num NUMBER;
  v_result VARCHAR2(51) := p_i_val;
 
  FUNCTION recur_value( p_i_mult_with IN NUMBER
                      , p_i_val       IN VARCHAR2) RETURN NUMBER AS
    v_num_char NUMBER(2);
    v_num_tot  NUMBER(2);
    v_sum      NUMBER(2);
  BEGIN
    v_num_char := TO_NUMBER(SUBSTR(p_i_val,1,1)) * p_i_mult_with;
 
    IF v_num_char &gt; 9 THEN
      v_num_tot := v_num_char - 9;
    ELSE
      v_num_tot := v_num_char;
    END IF;
 
    IF LENGTH(p_i_val) &gt; 1 THEN
      v_sum := v_num_tot + recur_value( CASE 
                                          WHEN p_i_mult_with = 1 THEN 2 
                                          ELSE                        1 
                                        END
                                      , SUBSTR(p_i_val, 2));
    ELSE
      v_sum := v_num_tot;
    END IF;
 
    RETURN v_sum;
  END recur_value; 
 
BEGIN
  RETURN v_result 
      || (10 - MOD( recur_value( p_i_mult_with =&gt; 1 
                               , p_i_val       =&gt; v_inval)
                  , 10)); 
END get_chk;
/

Side note: I have now spent an inordinate amount of time on what looks like a broken numbering of the code above. No idea what happens, but I need to find a better code formatter.

The code takes the inparameter and calls the recursive function which works by peeling of one digit from the parameter and calling itself with the rest. This call continues until a chain of calls has been done and the innermost invocation only has a single digit as the inparameter, then it returns it’s calculation and it starts summing each returned value with the calculation for the peel off digit eventually getting the total for all calculations returned to the outer function which performs the same kind of modulus operation as we saw in the first code block.

Pure SQL

The previous versions are what you probably want to start with if your value is already in PL/SQL, but what if it starts in SQL or you generate data with SQL? Then calling a PL/SQL function is not optimal.

In my case I had a need to get a correct check digit for an invented value. When you are to do it with a few hundred million rows you do not want to call a function for every row.

This is what I came up with to calculate the check digit in SQL.

WITH dl AS
(
  SELECT 1 rad FROM dual
  UNION ALL
  SELECT 2 rad FROM dual
)
, a AS
(
  SELECT CASE
           WHEN MOD(LENGTH(:1), 2) = 0 THEN :1
           ELSE                          <wbr />   '0' || :1
         END arg
    FROM dual
)
--select * from a;
, b AS
(
SELECT LEVEL rn, TO_NUMBER(SUBSTR(arg, LEVEL, 1)) dgt
  FROM a
  CONNECT BY LEVEL &lt;= LENGTH(a.arg)
)
--select * from b;
, c AS
(
SELECT b.rn
     , b.dgt
     , CASE
         WHEN MOD(b.rn, 2) = 0 THEN dgt * 2
         ELSE                       dgt
       END calc
  FROM b
)
--select * from c;
SELECT 10 - MOD(SUM(CASE
                      WHEN dl.rad = 1 AND c.calc &lt;  10 THEN c.calc
                      WHEN dl.rad = 1 AND c.calc &gt;= 10 THEN TRUNC(c.calc / 10)
                      WHEN dl.rad = 2 AND c.calc &lt;  10 THEN NULL
                      WHEN dl.rad = 2 AND c.calc &gt;= 10 THEN c.calc - 10
                    END), 10) chk
  FROM c, dl
WHERE dl.rad = 1
    OR(dl.rad = 2
   AND c.calc &gt; 9);

What is going on in this SQL? It looks long but it really is broken up into small chunks. We have four WITH-selects before we get down to the real one.

The first prefactoring SQL – dl – is just getting us a “table” with two rows. It is used later to handle the situation of the multiplication of a digit being > 9.

Next is “a”, all we do here is to fix the input in cases where it is of an uneven length. In such cases we prepend the input with the digit zero. An alternative would have been to change so we start multiplying the digits with 2 instead of 1, it achieves the same result.

After that comes “b” where we split the string into one row per digit in the input. The digits are also converted to numbers as we’re going to use them in multiplication in the next step.

In the next step “c” we calculate the value for each digit, The first is multiplied by 1, the second by two, the third by one again and so on.

After that the “real” SQL is executed. It takes the rows with sums by digits and creates two of them using “dl”. For rows where dl.rad is 1 we take the first digit (if calc is 15, we take 1), for situations where c.calc is over 9 we use the dl.rad rows where it is 2 to get the second digit (if calc is 15, we take 5). All those numbers are the added together and the usual modulus operation is performed returning the check digit.

To simplify following and troubleshooting I have the “select * from” rows left in the SQL. Uncomment one and run the SQL to see the result up to that point. It is by far the best way to understand what happens in the SQL, even better if you follow my explanation here and uncomment one to see the result I try to describe.

If this helps you please leave a comment or even more so if you have one variant of this that is simpler or faster.

Posted in PL/SQL, SQL, Uncategorized

March 15th, 2013 by Mathias

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a mode page item to the form. We will use this to know if the form is invoked in insert or in update/delete mode.

  • Right-click on “Form on Stored Procedure” on the page where your form is (page 4 in my example)
  • Select “Create Page Item”
  • Select Hidden
  • Give the page item a name, like P4_MODE.
  • Click “Next”
  • Click “Next” (again)
  • Select “Create Item”

You now have a new page item which is not displayed, but whose value you can reference on this page.

To make this example easy, we’ll start with just implementing update functionality. But first we’ll go to the report and change this link column to pass in a “U” for the mode page item when it is clicked.

In the page with the report, perform the following steps.

  • Right-click “List of Employees”
  • Click “Edit Report Attributes”
  • Scroll down to “Link Column”
  • For Item 2, enter the name and value we want the newly created page item to be set to. P4_MODE and U in my case.
  • Apply Changes

Now with that done, let’s head back to the page with the form. But before we make any changes there, we need to change the package to add a procedure for updating a row in the emp table.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_emp_rec  in t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
    begin
      select empno
            ,ename
        into p_out_emp_rec.empno
            ,p_out_emp_rec.ename
        from emp
       where empno = p_in_empno;
  end read_emp;
----------------------------------------------------
  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      update emp
         set ename = p_in_emp_rec.ename
       where empno = p_in_empno;
  end write_emp;
end tb_access;

This code adds the procedure write_emp wich also takes empno and the record used for read_emp, only this time they are both in parameters as we’ll not return any data from the procedure that writes to the table. The actual code in the procedure is a very simple update that sets the name to the ename in the record for the row that has the empno passed in.

With this in place we’re ready to change the form to call this procedure upon submit. We do this by taking the code used for reading in the record (The process in page rendering) and put it into the “Run Stored Procedure” process we commented out in page processing.

The code we copy looks like this:

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;
end;

Replace the code in “Run Stored Procedure” with the above and change it to look like this:

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_out_emp_rec => in_emp_rec);
end;

In the “Run Stored Procedure” change the condition to “- Select condition type -” to not have a condition for when the process is executed. It is currently conditioned on running only when the save button is clicked, leave that condition on for now.

With that in place, it is time to test the functionality of the forms new functionality. Run the report, click on the link and update the name of an employee. Assuming you followed the above and got the changes made in the right places, the form will now update the name of the employee.

The next step in this will be to add insert functionality to the form. First off is to set up the package to support inserting an employee. As we’re now going to have both insert and update through the same procedure, we need to include the mode in the signature of the procedure.

Let’s first update the package specification so the write_emp looks like this:

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_mode     in varchar2
                     ,p_in_emp_rec  in t_emp_rec);

The procedure in the package needs to be updated to look like this:

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      case p_in_mode
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'I' then
          insert into emp
             (empno
             ,ename)
           values((select max(empno) + 1 from emp) 
                 ,p_in_emp_rec.ename);
      end case;
  end write_emp;

Before the form is functional again, we need to change the call in “Run Stored Procedure” to match the new signature of write_emp.

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => :p4_mode
                     ,p_in_emp_rec  => in_emp_rec);
end;

With that change in place the form now works and the mode we set on the link column is now passed to the procedure to make sure an update is performed and not an insert.

To introduce insert functionality, we want to add a new button on the page with the report that invokes the form in insert mode. To add the button follow these steps on the page with the report.

  • Right-click on “List of Employees”
  • Select “Create Region Button”
  • Set name and label to “Add”
  • Click Next
  • Set position to “Right of Interactive Report Search Bar”
  • Set Alignment to “Left”
  • Click Next.
  • Select action “Redirect to Page in this Application”
  • Set the page to your form-page (4 in my example)
  • Set Request to INSERT
  • Set “Clear Cache” to 4
  • Set “Set these items” to P4_MODE
  • Set “With these values” to I.
  • Click Next.
  • Click “Create Button”

If you test the page, you’ll see that it ends up immediately to the right of the search bar. Do not click on the button just yet.

Now go to the page with the form and open the process in rendering (Read Emp).

  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

This ensures that we’re only reading a row from emp when updating data. When inserting, there is no data to be read and displayed.

You may also want to set the same condition on the page item for empno to hide it when inserting as there is no empno to display then.

Test the function now by running the report page and click on the “Add” button. It adds a row with the name you enter on the form. The rest of the columns will have null as their value. Adding more columns in the procedures and on the form is an exercise left to the reader.

With the function now supporting insert and update, it is time to add the delete function so we can get rid of some of the test employees we’ve created. This function will be implemented only on the form and by adding supporting code in the package. Thus, the delete will be performed by clicking on the link and then clicking on a delete button on the form.

To implement this we’ll update the implementation of the write_emp procedure once more.

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      case p_in_mode
        when 'I' then
          insert into emp
             (empno
             ,ename)
           values((select max(empno) + 1 from emp) 
                 ,p_in_emp_rec.ename);
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'D' then
          delete from emp
           where empno = p_in_empno;
      end case;
  end write_emp;

With the package updated, our next step is to change the code in the “Run Stored Procedure” process on the form page.

declare
  in_emp_rec tb_access.t_emp_rec;
  v_mode     varchar2(1);
begin
  in_emp_rec.ename := :p4_ename;
  v_mode           := :p4_mode;

  if :REQUEST = 'DELETE' then
    v_mode := 'D';
  end if;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => v_mode
                     ,p_in_emp_rec  => in_emp_rec);
end;

The code is now changed such that a click on the delete button (not implemented yet) will set the mode to D. This is done by checking the REQUEST which we will set to DELETE for the button we’ll create now. REQUEST is a standard attribute available on buttons and branches in APEX. By setting it to a unique value, we can check for different invocation methods in code. This allows us to deal with different scenarios with just one block of code.

To add the button, follow these steps.

  • On the form-page, right-click on “Form on Stored Procedure”.
  • Select “Create Region Button”
  • Set “Button Name” and “Label” to “Delete”.
  • Click Next
  • Set “Position” to “Top of Region”
  • Set Alignment to “Left”
  • Click Next
  • Click Next (again)
  • Click Create Button

The value of “Button Name” is used to set REQUEST when the page is submitted. With this in place our form should now support deleting rows also. Update the branch on the page to not have a condition on the button pressed. Take it out for a spin.

There is one thing left to do, hide the delete button when the form is invoked to add a row.

  • Right-click the delete-button under rendering.
  • Select “Edit”.
  • Scroll down to conditions.
  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

Now you’ve got a CRUD solution based on an interactive report with a form that supports insert, Update, and Delete. The total amount of code required is very small and you still have isolated the APEX-application from changes to the table structure by using a view, even the record can have fields added to it without impacting the functionality of your application. Developing using the demonstrated methods here allows you to create these applications almost as fast as with making them table based, but you have much more control and can make a lot of changes without touching the application,

Posted in APEX, Oracle, PL/SQL, SQL

October 24th, 2012 by Mathias

Do you use forms based on procedures in APEX? If not, why not?
Do you use records in the procedures you use for forms in APEX? If not, why not?

I like procedures and records and with APEX I like them even more. Why you say? Should you not just point forms against tables and let the APEX magic take care of it all? No, I do not think you should.

The reason is that I love that feature for prototyping, but not for production quality code. The reason is that I think the classic design pattern of separating presentation logic from business logic and data layer is as important with APEX as it is with any other technology.

That is, APEX applications should contain as much logic for presenting data as possible, and as little data about the data layer as possible. Thus, I think any table should be able to be renamed without making any change to the APEX application.

One key to that is to use procedures to hide what tables are updated or read. That allows the tables to be changed with just updating the procedure. No change to the APEX application would be needed and thus no new deploy of it would be required.

But what if a column is added or removed from the table, does that not require a change to the signature of the procedure? Sure it does, if every column in the table is reflected in the signature as individual fields. This is why using records and procedures together is such a powerful combination.

Lets take a look at a basic implementation using a form based on a procedure that uses a record.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno    in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
    begin
      select empno
            ,ename
       into p_out_emp_rec.empno
           ,p_out_emp_rec.ename
       from emp
      where empno = p_in_empno;
  end read_emp;
end tb_access;

This is a procedure that just returns a row of a certain emp_no from the emp table. The columns emp_no and ename are returned in the record.

To create a page in APEX using this procedure, follow these steps.

  1. Create a new page
  2. Select Form
  3. Select Form on Procedure
  4. Select the procedure (tb_access-read_emp) + next
  5. Change page and region names if you want to. + next
  6. Next (Do not use tabs)
  7. Next (No invoking page)
  8. Select a page to brach to on cancel and submit. + next
  9. Select to include only the columns that are in the record (empno and ename).
  10. Select display only as the display type for empno. +next
  11. Create

That was pretty easy, pretty much the same amount of work as to create it based on a table. However, there is one more thing we need to do. The reason for this is a bug. Hopefully this step is not needed in future versions. You know you have encountered this if the page items on the new page has names such as p4_pno and p4_ame. That is the first two letters in the name of the fields in the record were lost when the page items were created. This does not happen for fields in the signature, only for fields in a record that is in the signature.

It is easy enough to change the names of the page items. It is of course not needed technically speaking, but you will save yourself a lot of grief by having page items you know what they are used for.

The next step is to update the call to the procedure. In the “page processing” section there is a “run stored procedure”. The code will look something like this.

#OWNER#.TB_ACCESS.READ_EMP(
EMPNO => :p4_PNO,
ENAME => :p4_AME);

Yes, the bug affects the name of the page items used to provide values to the call. Not only is that wrong, the call is not using a record so the call will not work. A third problem is that the call is in the page processing section, but we want it to read data to be displayed.

As we will not update any data in this post, just comment out this code and copy it. Set the condition to never to avoid the call to even be attempted. Now create a process in “before regions” in page rendering. Paste the code you copied and update it to look like this.

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;
end;

This code fetches a row from the emp table based on an empno that the page item p4_empno has been set to. This is of course assumed to be done by a page that branches to this page.

This code is of course something that is very much a template that once you have it set up the way you want in one place it is just a matter of adjusting it for the needs of different pages. It really does not take much more time than widget based page creation using a table. This however gives much more flexibility in changing the underlaying datamodel as well as having some people focus on developing the presentation logic and others focused on writing data access logic.

This form is now just a read-only form, but it takes very little work to make it a full fledged crud by supporting insert, update, and delete in the same form. I may build on this form in a future post to show one way of doing that.

As a last thing, let’s just show that this page is now immune to changes to the record. Lets change the package to add another field to the record and populate it in the SQL.

create or replace package tb_access as
  type t_emp_rec is record ( 
        empno  emp.empno%type
       ,ename  emp.ename%type
       ,sal    emp.ename%type);

    procedure read_emp(p_in_empno     in emp.empno%type
                      ,p_out_emp_rec out t_emp_rec);
end tb_access;
create or replace package body tb_access as
    procedure read_emp(p_in_empno     in emp.empno%type
                      ,p_out_emp_rec out t_emp_rec)  is
      begin
         select empno
               ,ename
               ,sal
           into p_out_emp_rec.emp_no
               ,p_out_emp_rec.ename
               ,p_out_emp_rec.sal
           from emp
          where empno = p_in_empno;
      end read_emp;
end tb_access;

This change is now available to any user of the procedure, while the existing page just keeps working. It does not even need a resave/compile. The signature of the procedure is the same and so all callers will keep working. This allows a change to be made without being forced to also change code or recompile all places where a call to the procedure is made.

Posted in APEX, PL/SQL