Scribbles for my bad memory

TL;DR

In PL/SQL, COUNT, LAST, FIRST, and NEXT are essential for handling collections, especially if there are gaps (unpopulated indexes). Here’s a quick summary:

• COUNT: Gives the number of populated entries.
• LAST and FIRST: Identify the highest and lowest populated indexes, respectively.
• NEXT: Finds the next populated index, skipping gaps automatically—great for sparse collections.

In PL/SQL programming, collections like nested tables, VARRAYs, and associative arrays are powerful tools for handling sets of data. However, understanding how to work with populated and unpopulated indexes can be tricky. That’s where COUNT, LAST, FIRST, and NEXT methods come into play. These methods let you manage and iterate over collections effectively, especially when dealing with gaps (unpopulated indexes). Let’s look at each one and how to use it.

1. COUNT

• Purpose: Returns the number of populated elements in a collection.
• Use Case: COUNT is useful when you need the exact count of entries in a collection, especially for fully populated collections without gaps.

Example:

DECLARE
  TYPE NumberTable IS TABLE OF NUMBER;
  v_numbers NumberTable := NumberTable(1, 2, 3, 4, 5); -- Fully populated
BEGIN
  DBMS_OUTPUT.PUT_LINE('COUNT: ' || v_numbers.COUNT);  -- Outputs: 5
END;

2. LAST

• Purpose: Returns the highest populated index in the collection, whether all indexes are filled or not.
• Use Case: When you need to find the highest valid index, such as for looping through all possible entries.

Example:

DECLARE
  TYPE NumberTable IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_numbers NumberTable;
BEGIN
  v_numbers(1) := 10;
  v_numbers(3) := 30; -- Gap at index 2
  v_numbers(5) := 50;

  DBMS_OUTPUT.PUT_LINE('LAST: ' || v_numbers.LAST);  -- Outputs: 5
END;

3. FIRST

• Purpose: Returns the lowest populated index in the collection.
• Use Case: Similar to LAST, FIRST is used when you want to start iterating from the lowest populated index.

Example:

DECLARE
  TYPE NumberTable IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_numbers NumberTable;
BEGIN
  v_numbers(2) := 20;
  v_numbers(4) := 40;

  DBMS_OUTPUT.PUT_LINE('FIRST: ' || v_numbers.FIRST);  -- Outputs: 2
END;

4. NEXT

• Purpose: Given an index, NEXT returns the next highest populated index. If there are no more populated indexes after the current one, it returns NULL.
• Use Case: NEXT is perfect for iterating only over populated elements, skipping gaps automatically.

Example:

DECLARE
  TYPE NumberTable IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_numbers NumberTable;
  v_index PLS_INTEGER;
BEGIN
  v_numbers(1) := 10;
  v_numbers(3) := 30;
  v_numbers(5) := 50;

  v_index := v_numbers.FIRST;
  WHILE v_index IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('Value at index ' || v_index || ': ' || v_numbers(v_index));
    v_index := v_numbers.NEXT(v_index);
  END LOOP;
END;

Output:

Value at index 1: 10
Value at index 3: 30
Value at index 5: 50

Choosing the Right Method

• COUNT is ideal for knowing how many elements are populated.
• LAST and FIRST are great for determining the range of populated indexes.
• NEXT is best when you want to loop through only populated elements, especially if there are gaps.

Key Takeaways

Using COUNT, LAST, FIRST, and NEXT effectively allows you to handle PL/SQL collections with gaps, optimize loops, and avoid errors when accessing unpopulated indexes. For sparse collections (with gaps), FIRST and NEXT are preferred over looping with 1..LAST to ensure you only interact with valid, populated indexes.

Best Practice for Gapped Collections

When working with collections that might have gaps, avoid using 1..COUNT or 1..LAST directly in loops, as they may access unpopulated indexes and cause errors. Instead:

1. Use FIRST and NEXT to loop through only populated elements:

   • Start with v_index := v_collection.FIRST and use v_index := v_collection.NEXT(v_index) to move to the next populated index.
   • This skips gaps automatically, making it the most efficient way to handle sparse collections.

2. Add NULL Checks if Necessary:

   • Even with FIRST and NEXT, you can add an IF condition to verify a non-null value for specific fields if you expect incomplete records.

Example:

DECLARE
  TYPE MyCollection IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_collection MyCollection;
  v_index PLS_INTEGER;
BEGIN
  v_collection(1) := 10;
  v_collection(3) := 30;
  v_collection(7) := 70;

  v_index := v_collection.FIRST;
  WHILE v_index IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('Value at index ' || v_index || ': ' || v_collection(v_index));
    v_index := v_collection.NEXT(v_index);
  END LOOP;
END;

This approach ensures your code only processes valid elements, skipping over unpopulated entries smoothly.

DECLARE
    -- Procedure to add a column if it does not exist
    PROCEDURE add_column_if_not_exists (
        p_owner       VARCHAR2,
        p_table_name  VARCHAR2,
        p_column_name VARCHAR2,
        p_column_type VARCHAR2
    ) IS
        l_sql    VARCHAR2(1024);
        l_count  NUMBER;
    BEGIN
        -- Check if column exists
        SELECT COUNT(*)
        INTO l_count
        FROM ALL_TAB_COLUMNS
        WHERE OWNER = p_owner
          AND TABLE_NAME = p_table_name
          AND COLUMN_NAME = p_column_name;

        -- Add column if it does not exist
        IF l_count = 0 THEN
            l_sql := 'ALTER TABLE ' || p_owner || '.' || p_table_name || ' ADD (' || p_column_name || ' ' || p_column_type || ')';
            DBMS_OUTPUT.PUT_LINE(l_sql);
            EXECUTE IMMEDIATE l_sql;
        END IF;
    END;

    -- Procedure to drop a column if it does exist
    PROCEDURE drop_column_if_exists (
        p_owner       VARCHAR2,
        p_table_name  VARCHAR2,
        p_column_name VARCHAR2
    ) IS
        l_sql    VARCHAR2(1024);
        l_count  NUMBER;
    BEGIN
        -- Check if column exists
        SELECT COUNT(*)
        INTO l_count
        FROM ALL_TAB_COLUMNS
        WHERE OWNER = p_owner
          AND TABLE_NAME = p_table_name
          AND COLUMN_NAME = p_column_name;

        -- drop column if it does exist
        IF l_count = 1 THEN
            l_sql := 'ALTER TABLE ' || p_owner || '.' || p_table_name || ' DROP COLUMN ' || p_column_name;
            DBMS_OUTPUT.PUT_LINE(l_sql);
            EXECUTE IMMEDIATE l_sql;
        END IF;
    END;
BEGIN
...
END;
/

I met this issue when I SELECT CASE WHEN d.COUNTRY != 'AU' THEN 'Australia' ELSE countries.NAME END

BTW, I asked ChatGPT for help about this issue but unfortunately I didn't get a good answer.
One of the correct answers was

SELECT CASE WHEN d.COUNTRY != 'AU' THEN 'Australia' ELSE TO_CHAR(countries.NAME) END ...

I found the above answer myself and I told ChatGPT this answer. It replied

Great to hear that you were able to solve the issue! Yes, you can use the TO_CHAR function to convert the character set of the countries.NAME column to match the character set of your database. This is a common solution for character set mismatch errors. Using TO_CHAR function, you can explicitly specify the character set you want to convert to, like this:

TO_CHAR(countries.NAME, 'NLS_CHARACTERSET')

where 'NLS_CHARACTERSET' is the character set of your database. By default, Oracle uses the database's character set for character data that has no specified character set, so if you don't specify a character set for TO_CHAR, it will use the database's character set.

another answer is to use the CAST function in PL/SQL, here's an example from stackoverflow.com

We sometimes need to include a single quote in an SQL statement, I believe you already know the normal way, use two single quote characters to express one single quote. However, if you are using oracle 10g or a more recent version, the following way could be better:

This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This is particularly useful for building dynamic SQL statements that contain quoted literals.

The mechanism is invoked with a simple "q" in PL/SQL only. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

• !
• ❎
• { }
• ( )
• < >

Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer).

If you want samples, see Reference

Occasionally, we will need to define local used functions in a public procedure or a function. Fortunately, PL/SQL supports embedded functions. I spent more than an hour trying to find the correct way to use this feature. Yes. forgive me, I am a little dumb. According to my retries, the best position to put your local functions is sharp before the BEGIN symbol. And here's a sample.

set serveroutput on;
CREATE OR REPLACE PROCEDURE tmp12345(v_full_name IN VARCHAR2) AS
	localv varchar2(100);
	-- local function definitaions start ---
	FUNCTION FETCH_REAL_NAME(v_statement_name IN VARCHAR2) RETURN VARCHAR2
	AS
		pattern VARCHAR2(100) := '^(Dr|Ms|Mx|Mr|Mrs)\s+';
		full_name varchar2(100);
	BEGIN
		-- replace possible multiple spaces to one space
		full_name := TRIM(REGEXP_REPLACE(v_statement_name, '\s+',  ' '));
		IF (REGEXP_LIKE(full_name, pattern, 'i')) THEN
			-- REGEXP_REPLACE(string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ]) 
			RETURN REGEXP_REPLACE(full_name, pattern, '', 1, 0, 'i');
		ELSE
			RETURN full_name;
		END IF;
	END;
	FUNCTION FETCH_MIDDLE_NAME(full_name IN VARCHAR) RETURN VARCHAR AS
		middle_name VARCHAR2(100);
	BEGIN
		middle_name := SUBSTR(full_name, INSTR(full_name, ' ') + 1, INSTR(full_name, ' ', 1, 2) - INSTR(full_name, ' ') - 1);
		IF (LENGTH(middle_name) > 10) THEN
			-- return initial instead
			RETURN SUBSTR(middle_name, 1, 1);
		ELSE
			RETURN middle_name;
		END IF;
	END;
	-- local functions definitaions end ---
BEGIN
	-- main logic here
	localv := v_full_name;
	DBMS_OUTPUT.PUT_LINE(localv);
	DBMS_OUTPUT.PUT_LINE(FETCH_MIDDLE_NAME(FETCH_REAL_NAME(v_full_name)));
END;
/