How to fix strange oracle PL/SQL error - “PLS-00049: bad bind variable”

Question

I am trying to write an execute an oracle PL/SQL function that return the number of records within a table, where one of the fields matches a certain name.

Here is my code:

create or replace function getNum
return number
as
v_x number;
begin
SELECT COUNT(*) INTO :v_x
FROM UserResponses WHERE NHSPlatform_Name = 'Improvement Data and Analytics';
return v_x;

end;
/

execute getNum();

I keep getting strange errors:

Function GETNUM compiled

LINE/COL ERROR --------- ------------------------------------------------------------- 6/22 PLS-00049: bad bind variable 'V_X' Errors: check compiler log

Error starting at line : 85 in command - BEGIN getNum(); END; Error report - ORA-06550: line 1, column 7: PLS-00905: object B7011343.GETNUM is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Answer 1

First thing is to correct your function as pmdba wrote in his answer. This would then be a correct function:

create or replace function getNum
return number
as
v_x number;
begin
SELECT COUNT(*) INTO v_x
FROM UserResponses WHERE NHSPlatform_Name = 'Improvement Data and Analytics';
return v_x;

end;
/

Then you need to call your function in correct way. This is one way of calling it:

select getNum from dual;

here is a small demo

Answer 2

Drop the ":" from the select statement:

create or replace function getNum
return number
as
v_x number;
begin
SELECT COUNT(*) INTO v_x
FROM UserResponses WHERE NHSPlatform_Name = 'Improvement Data and Analytics';
return v_x;

end;
/

select getNum() from dual;

Answer 3

Try this:

SQL> VARIABLE ret_val NUMBER;
SQL> execute :ret_val := getnum;

PL/SQL procedure successfully completed.

SQL> select :ret_val from dual;