Bad bind variable error in store procedure – PL/SQL – Oracle

CREATE OR REPLACE PROCEDURE demoStoreProc
(
  stringNums IN VARCHAR2   
)
AS
  stepCount NUMBER;

BEGIN

SELECT REGEXP_COUNT (stringNums ,',')+1 INTO :stepCount FROM "SYS"."DUAL" ;

END 

So in the above stored procedure, the input is like

stringNums  = 12,13,14,15

and in variable stepCount I want to insert 4 , as there is 4 numbers are present

But I get this error:

Bad Bind variable error for stepCount

Answer

You don’t need that select

CREATE OR REPLACE PROCEDURE demostoreproc (
     stringnums IN VARCHAR2
) AS
     stepcount   NUMBER;
BEGIN
     stepcount := regexp_count(stringnums,',') + 1;
END;
/

But, what’s the procedure doing anyway?

Rather, you may want a function.

 CREATE OR REPLACE function demostoreproc (
     stringnums IN VARCHAR2
)  RETURN NUMBER AS
     stepcount   NUMBER;
BEGIN
     stepcount := regexp_count(stringnums,',') + 1;
     RETURN stepcount;
END;
/