Search in procedure Oracle sql

Question

I want to search data from database(Oracle) by using procedure.

My procedure is:

PROCEDURE CUSTOMER_SEARCH(
v_customerName VARCHAR2,
v_Result OUT SYS_REFCURSOR)
AS
BEGIN
OPEN v_Result FOR
SELECT * FROM CHAT_CUSTOMER
WHERE CUSTOMERNAME like '%'+v_customername+'%';
END CUSTOMER_SEARCH;

But when I test this procedure by input v_customerName = 'a', it shows error:

ORA-01722: invalid number

Is there any suggestion to correct my procedure?

score 3 · Accepted Answer · answered Aug 21 '15 at 10:45

Yes: use the correct concatenation characters! In Oracle, + is for addition of two numbers, whereas || is used to concatenate two strings together. So, your procedure should be:

PROCEDURE CUSTOMER_SEARCH(
    v_customerName VARCHAR2,
    v_Result OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN v_Result FOR
  SELECT * FROM CHAT_CUSTOMER
  WHERE CUSTOMERNAME like '%'||v_customername||'%';
END CUSTOMER_SEARCH;
/

Bear in mind that you may wish to make both CUSTOMERNAME and v_customername uppercase by using the UPPER() function in order to make the search case-insensitive.

Search in procedure Oracle sql

1 Answers1