LengthB(): Oracle PL/SQL LengthB function examples

PL/SQL String Functions: LENGTHB()

Exercise 1:

Write a PL/SQL block to calculate the bit length of the employee's first name in the employees table for all records.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_bit_length NUMBER;
BEGIN
  FOR emp IN (SELECT first_name FROM employees) LOOP
v_bit_length := LENGTHB(trim(emp.first_name)) * 8;
    DBMS_OUTPUT.PUT_LINE('Bit length of ' || emp.first_name || ': ' || v_bit_length);
  END LOOP;
END;
/

Sample Output:

Bit length of Kelly      : 40
Bit length of Jennifer   : 64
Bit length of Timothy    : 56
Bit length of Randall    : 56
Bit length of Sarah      : 40
Bit length of Britney    : 56
Bit length of Samuel     : 48
Bit length of Vance      : 40
Bit length of Alana      : 40
Bit length of Kevin      : 40
Bit length of Donald     : 48
Bit length of Douglas    : 56
Bit length of Jennifer   : 64
Bit length of Michael    : 56
Bit length of Pat        : 24
.....

Explanation:

The said code in Oracle's PL/SQL demonstrates to calculate the bit length of employee names.

The code begins with the declaration of a variable, v_bit_length, that stores the calculated bit length.

The FOR loop iterates through the rows returned by the SELECT query and each iteration assigns the current first_name value to the emp variable and in the loop, the LENGTHB function calculates the byte length of the trimmed first_name value.

The trim function removes any leading or trailing whitespace from the name.

The byte length is then multiplied by 8 to obtain the corresponding bit length.

Finally, the DBMS_OUTPUT.PUT_LINE procedure is used to display a message containing the employee's name and its calculated bit length.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to calculate the bit length of the job title for a specific employee identified by their employee ID.

PL/SQL Code:

CREATE OR REPLACE PROCEDURE calculate_job_id_bit_length(
p_employee_id IN employees.employee_id%TYPE
) AS
  TYPE EmpRec IS RECORD (
job_idemployees.job_id%TYPE
  );
v_employeeEmpRec;
v_bit_length NUMBER;
BEGIN
  SELECT job_id INTO v_employee.job_id FROM employees WHERE employee_id = p_employee_id;
v_bit_length := LENGTHB(v_employee.job_id) * 8;
  DBMS_OUTPUT.PUT_LINE('Bit length of job_id: ' || v_bit_length);
END;
/
Execute the procedure:
BEGIN
calculate_job_id_bit_length(p_employee_id => 103); 
END;
/

Sample Output:

Bit length of job_id: 56

Explanation:

The said code in Oracle's PL/SQL defines a procedure that takes an employee ID as input and calculates the bit length of the corresponding job ID in the employees table.

The procedure begins by declaring a record type named "EmpRec" with a single field, "job_id," which matches the data type of the "job_id" column in the "employees" table.

A local variable "v_employee" of type "EmpRec" is declared to store the retrieved job ID from the "employees" table based on the provided employee ID.

The bit length is calculated by multiplying the length in bytes of the job ID with 8, as each byte consists of 8 bits. The result is assigned to the variable "v_bit_length".

Finally, the procedure displays the calculated bit length using the DBMS_OUTPUT.PUT_LINE function, along with a descriptive message.

To execute this procedure, it has been called with the appropriate employee ID parameter.

Flowchart:

Exercise 3:

Write a PL/SQL function to return the maximum bit length of the department names in the departments table.

PL/SQL Code:

CREATE OR REPLACE FUNCTION get_max_department_bit_length RETURN NUMBER AS
v_max_length NUMBER := 0;
v_bit_length NUMBER;
BEGIN
  FOR dept IN (SELECT department_name FROM departments) LOOP
v_bit_length := LENGTHB(dept.department_name) * 8; -- Multiply by 8 to get the number of bits
    IF v_bit_length>v_max_length THEN
v_max_length := v_bit_length;
    END IF;
  END LOOP;

  RETURN v_max_length;
END;
/

To execute the function:
DECLARE
v_result NUMBER;
BEGIN
v_result := get_max_department_bit_length(); -- Call the function and assign the result to a variable
  DBMS_OUTPUT.PUT_LINE('Maximum department name bit length: ' || v_result);
END;
/

Sample Output:

Maximum department name bit length: 120

Explanation:

The said code in Oracle's PL/SQL defines a function that calculates the maximum bit length among all department names in the departments table.

The function declares two variables: "v_max_length" and "v_bit_length."

The "FOR" loop iterates over each department name in the "departments" table and calculates the length of the current department name using the "LENGTHB" function, and it is multiplied by 8 to convert it to bits.

If the calculated "v_bit_length" is greater than the current "v_max_length," the "v_max_length" is updated with the new maximum value.

After processing all department names, the maximum bit length stored in "v_max_length" is returned as the result of the function.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: PL/SQL String Functions Exercises Home.
Next: ASCII Function.