plsqloracle10goracle-apex

SQL query inside PL/SQL Block is not working properly, Fetches all Data and ignoring WHERE clause

I am debugging this from last 2 hrs but I am unable to find the solution for this, please help me...

Here is the problem...

I HAVE CREATED 1 TABLE NAMED "EMPP"

`CREATE TABLE EMPP(
  EMPNO NUMBER PRIMARY KEY,
  ENAME VARCHAR2(15),
  JOB VARCHAR2(15),
  SAL NUMBER(7,2)
)`

NOW I HAVE INSERTED 1 RECORD IN IT...

INSERT INTO EMPP VALUES(1, 'A','B',2850);

AND IF I FETCH ALL RECORDS THAN AS EXPECTED I AM GETTING ONLY 1 RECORD...

OUTPUT

NOW I NEED TO SOLVE THIS QUESTION, " Create a PL/SQL block that prints details of the employees for a given employee salary. If there are multiple employees earn similar salary print appropriate user-defined message before terminating block. "

HERE IS MY SOLUTION FOR THAT QUESTION

`DECLARE
same_sal EXCEPTION;
sal EMPP.SAL%TYPE := :Enter_Salary;
no EMPP.EMPNO%TYPE;
ename EMPP.ENAME%TYPE;
job EMPP.JOB%TYPE;
total_row NUMBER;
BEGIN
SELECT COUNT(*) INTO total_row FROM EMPP WHERE SAL = sal;
    dbms_output.put_line(sal);
IF (total_row > 1) THEN
    raise same_sal;
ELSE
    SELECT EMPNO, ENAME, JOB INTO no, ename, job FROM EMPP WHERE SAL = sal;

    dbms_output.put_line('No :  ' || no);
    dbms_output.put_line('Emp Name :  ' || ename);
    dbms_output.put_line('Job :  ' || job);
END IF;

EXCEPTION
WHEN same_sal THEN
    dbms_output.put_line('There are more than 1 employee with same salary');
END;`

Now from here main problem starts, No matter what I insert in bind variable "sal", it will return the record, like if I add "100" in sal variable still it will return record with 2850 salary.

Inserting value in variable

Output

I don't know what I am doing wrong, I tried to execute query directly...

`SELECT COUNT(*) FROM EMPP WHERE SAL = 2850;  -- returns 1
SELECT COUNT(*) FROM EMPP WHERE SAL = 100;   -- returns 0
SELECT COUNT(*) FROM EMPP WHERE SAL = 1000;  -- returns 0`

P.S : I am using Oracle 10g

Solution

  • Don't name variables with the same same as the column.

    When you use WHERE SAL = sal then SAL on both sides of the comparison is taken from the local SQL scope and is not taken from the outer PL/SQL scope. You need to make sure that the PL/SQL variable has a different name so that it can be read from the outer scope.

    DECLARE
  v_same_sal  EXCEPTION;
  v_sal       EMPP.SAL%TYPE := :Enter_Salary;
  v_no        EMPP.EMPNO%TYPE;
  v_ename     EMPP.ENAME%TYPE;
  v_job       EMPP.JOB%TYPE;
  v_total_row NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_total_row
  FROM   EMPP
  WHERE  SAL = v_sal;

  dbms_output.put_line(v_sal);

  IF (v_total_row > 1) THEN
    raise v_same_sal;
  ELSE
    SELECT EMPNO, ENAME, JOB
    INTO   v_no, v_ename, v_job
    FROM   EMPP
    WHERE  SAL = v_sal;

    dbms_output.put_line('No :  ' || v_no);
    dbms_output.put_line('Emp Name :  ' || v_ename);
    dbms_output.put_line('Job :  ' || v_job);
  END IF;
EXCEPTION
  WHEN v_same_sal THEN
    dbms_output.put_line('There are more than 1 employee with same salary');
END;
/

    fiddle