Search code examples
oracleplsqloracle-sqldeveloper

Run a cursor with a substitute variable

I'm trying to run a cursor with a substitute variable. I try to enter Pa and it throws the following error:

identifier PA must be declared

DECLARE

CURSOR c_emp(p_cad varchar2) IS SELECT * FROM employee
                                WHERE fname LIKE ''||p_cad||'%';  
                                
v_fname employee.fname%TYPE:=&Idemp;  --substitution variable 
v_count INT:=0;
BEGIN
FOR r IN c_emp(v_fname)
LOOP
    dbms_output.put_line('Emp Id: '||r.emp_id);
    dbms_output.put_line('FName: '||r.fname);
    dbms_output.put_line(' ');
    v_count:= v_count+1;
END LOOP;
    dbms_output.put_line('------------------');
    dbms_output.put_line('Total de Empleados: '||v_count);
END;
Solution

  • A substitution variable is prefixed with & and not $ so your code should be:

    v_fname employee.fname%TYPE:=&Idemp;

    However, if you want a bind variable, then it should be prefixed with :

    v_fname employee.fname%TYPE:=:Idemp;