User-defined function with IN VARCHAR parameter in PL/SQL Oracle

Question

I need some help. It is the first time when I am asking for help here, so please excuse me for my bad english. I am so mad, because I can not figure out how to fix this error. I work in Oracle 10g. The output is:

Function created.
No errors.
Function created.
Enter value for v_numes: 'SECTIA1'  --SECTIA1 is the name of section(department) from sectii table
Sectia exista in baza de date SECTIA1
Numele sectiei este: SECTIA1 --the name of the department(v_numes)
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.FUNCTION0", line18
ORA-01403: no data found
ORA-06512: at line 17

Here it is my code:

set serveroutput on 
set verify off
CREATE OR REPLACE FUNCTION function0(p_section_name VARCHAR) RETURN NUMBER IS
    v_procent1  CONSTANT REAL:=0.01;
    v_cods      sectii.cods%TYPE;--the table is named sectii, which means sections
    v_ok1       NUMBER:=1;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Numele sectiei este: '||p_section_name);
    SELECT cods
    INTO v_cods
    FROM sectii
    WHERE den=UPPER(p_section_name);
    DBMS_OUTPUT.PUT_LINE('Codul sectiei pentru '||p_section_name||' este '||v_cods);

    RETURN (v_ok1);
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        RETURN 'Prea multe sectii cu acest nume.';
    WHEN NO_DATA_FOUND THEN
        RETURN 'Nici o sectie cu acest nume.';
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20011,'Unknown Exception in function0');
END function0;
/
show errors;
/
DECLARE
    v_numes             VARCHAR2(255);
    v_numesu            VARCHAR2(255);
    v_numesl            VARCHAR2(255);
    v_boool             NUMBER:=1;
    e_invalid_section   EXCEPTION;
BEGIN
    v_numes:=&v_numes;
    IF SQL%NOTFOUND THEN
        RAISE e_invalid_section;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Sectia exista in baza de date '|| v_numes);
    END IF;
    v_numesu:=UPPER(v_numes);
    v_numesl:=LOWER(v_numes);
    IF v_numes=v_numesl OR v_numes=v_numesu THEN
        DBMS_OUTPUT.PUT_LINE('FUNCTION0 '||function0(v_numes));
    END IF; 
    --v_numes:=TO_CHAR(v_numes);
    --DBMS_OUTPUT.PUT_LINE('RETURN FUNCTIE0: '||v_boool);
EXCEPTION
    WHEN e_invalid_section THEN --exceptie definita de utilizator
        DBMS_OUTPUT.PUT_LINE('Nu exista aceasta sectie in tabelul sectii.');    
END;
/

You probably don't want to store your functions and tables in the SYSTEM schema. — Jon Heller, Jun 23 '13 at 05:24

score 4 · Answer 1 · edited May 23 '17 at 12:05

The declaration of FUNCTION0, states that the function returns a number:

CREATE OR REPLACE FUNCTION function0(p_section_name VARCHAR) RETURN NUMBER IS

The return statements in your exception blocks return characters...

EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        RETURN 'Prea multe sectii cu acest nume.';
    WHEN NO_DATA_FOUND THEN
        RETURN 'Nici o sectie cu acest nume.';

One thing to note, your error was extremely specific:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.FUNCTION0", line18
ORA-01403: no data found
ORA-06512: at line 17

This means you're converting a character to a number at line 18 and a NO_DATA_FOUND exception was raised at line 17...

To answer all of your comments in one place, you're getting a NO_DATA_FOUND exception because of the WHERE clause, as I've already said.

WHERE den = UPPER(p_section_name)

Run this SELECT statement:

select * from cods where den = upper(den)

It will not return any rows because it is not true. If you're uppercasing the parameter p_section_name you also need to uppercase the column den. You need to search case-insensitively, see Case insensitive searching in Oracle for more details.

User-defined function with IN VARCHAR parameter in PL/SQL Oracle

1 Answers1