pl/sql packages function doesn't return correct value

May 8, 2024

I’m learning pl/sql, I’m getting started on packages, so basically I’m trying to find how many istances of a char is there in a string, i’m using live SQL of Oracle to execute queries etc… but a function that perfectly works outside of the block when written in the package body won’t work

CREATE OR REPLACE PACKAGE string_manipulator IS
    FUNCTION char_occurrences(input_char IN VARCHAR2, input_string VARCHAR2) RETURN NUMBER;
END string_manipulator;

CREATE OR REPLACE PACKAGE BODY string_manipulator IS

    FUNCTION char_occurrences(input_char IN VARCHAR2, input_string IN VARCHAR2) RETURN NUMBER IS
        char_found_var NUMBER;
        BEGIN
            SELECT REGEXP_COUNT(UPPER(input_string), UPPER(input_char))
            INTO char_found_var
            FROM dual;
        RETURN char_found_var;
    END char_occurrences;
END string_manipulator;

DECLARE 
    input_string VARCHAR2(50) := 'test';
    char_to_find VARCHAR2(50):= 'e';
    char_found_var NUMBER;
BEGIN
    char_found_var := string_manipulator.char_occurrences(input_string, char_to_find);
    DBMS_OUTPUT.PUT_LINE(char_found_var || ' char found ');
END;

this always returns 0.

But when i do something like

DECLARE 
    input_string VARCHAR2(50) := 'test';
    char_to_find VARCHAR2(50):= 'e';
    char_found_var NUMBER;
BEGIN
    SELECT REGEXP_COUNT(UPPER(input_string), UPPER(char_to_find))
    INTO char_found_var
    FROM dual;
    DBMS_OUTPUT.PUT_LINE(char_found_var || ' char found ');
END;

it perfectly works

Any solution to this?

>Solution :

That’s because you wrongly used parameters when calling packaged function.

Commented line is yours; uncommented is mine, and it works:

SQL>     DECLARE
  2         input_string    VARCHAR2 (50) := 'test';
  3         char_to_find    VARCHAR2 (50) := 'e';
  4         char_found_var  NUMBER;
  5      BEGIN
  6         -- char_found_var := string_manipulator.char_occurrences (input_string, char_to_find);
  7            char_found_var := string_manipulator.char_occurrences (char_to_find, input_string);
  8         DBMS_OUTPUT.PUT_LINE (char_found_var || ' char found ');
  9      END;
 10  /
1 char found

PL/SQL procedure successfully completed.

SQL>